BIFocal LLC

Author: jhimmel

  • Speaking at SharePoint Connections Coast to Coast 2011 in Chicago

    Chicago

    My friend, Cornelius J. van Dyk, and I will be presenting at this year’s SharePoint Connections Coast to Coast conference in Chicago held August 8-10 at the Hyatt Regency McCormick Place. (Anyone sensing a reoccurring theme here?  Any guesses which conference we are speaking at in early November? Here’s a hint! )

    We will be bringing back our acclaimed ITPro Pre-Conference “SharePoint Collaboration Bootcamp” plus presenting three sessions, “Heavy Metal PowerPivot”, “Time Is Money. How SharePoint Logging will Save You Both!”, and “Battle Scarred But Still Standing: A SharePoint Admin’s Tell-All”. We have updated all of these sessions to be fresh, current, and exciting so come out and see us if you’re anywhere in the Chicago area during that time.

    We are also participating in a Speakers Panel for the Chicago SharePoint Users Group on Tuesday evening August 9th starting at 5:30pm.  CSPUG is hosting us and will be providing pizza and lively discussion.

    We hope to see you there!

  • How to use PowerShell to get quota information in a usable way

    There are many good resources out there about how to create, modify, & set quotas using PowerShell, that isn’t an issue. Zach Rosenfield has a good post on Managing Bulk Site Collection Quotas in PowerShell and there are dozens of others out there who can give you all of the information about making changes to quotas that you want. My problem was how do I tell what a site’s quota template assignment is and how close they are to the limits using PowerShell rather than having to do a one by one click through the UI?

    The Painful Process

    You have a number of different quota templates and a reasonable number of site collections in your farm. In order to stay ahead of the curve you want to be able to monitor the growth of your site collections and know when they are heading toward their quota limits. In the UI you will go to Central Administration and visit the Application Management Section where you will find “Configure quotas and locks” as seen below:

    a

    Next you will change the Site Collection selector to the site you want and that site’s information will be displayed like this:

    b

    Valuable information & the ability to change the quota all in one place. Simple, easily, elegant. Only one problem: you have to do walk through this for EVERY site collection to get the information if you are simply trying to do an audit of the information.

    The PowerShell Solution

    I searched the blogosphere high and low looking for an answer to this problem. I pinged experts and friends who I expected would have had to solve this solution. The response I got in most cases was “there are pay products that have that functionality” or “I really hadn’t thought about that yet. Let me know when you find an answer”.

    Here is what I came up with: (download a copy as a text or PowerShell from GitHub)

    code

    The output of this script looks like this:

    Url : http://sharepoint2010
    Storage Used/1MB : 3
    Storage Available Warning/1MB : 0
    Storage Available Maximum/1MB : 0
    Sandboxed Resource Points Warning : 100
    Sandboxed Resource Points Maximum : 300
    Quota Name : No Template Applied

    Url : http://sharepoint2010/my/personal/spadmin
    Storage Used/1MB : 3
    Storage Available Warning/1MB : 80
    Storage Available Maximum/1MB : 100
    Sandboxed Resource Points Warning : 200
    Sandboxed Resource Points Maximum : 300
    Quota Name : Personal Site

    Url : http://sharepoint2010/sites/large
    Storage Used/1MB : 0
    Storage Available Warning/1MB : 800
    Storage Available Maximum/1MB : 1024
    Sandboxed Resource Points Warning : 400
    Sandboxed Resource Points Maximum : 500
    Quota Name : Large Site

    Url : http://sharepoint2010/sites/medium
    Storage Used/1MB : 0
    Storage Available Warning/1MB : 400
    Storage Available Maximum/1MB : 500
    Sandboxed Resource Points Warning : 200
    Sandboxed Resource Points Maximum : 300
    Quota Name : Medium Site

    This script loops through ALL of your web applications and retrieves information about ALL of your sites.

    You can specify a web application to run this against in line 3 just before the pipe. Additionally, if you want to use the script as is you will need to create the text file quotaoutput.txt in your C: root. You can change the name and location of that output file or just remove the “>> c:quotaoutput.txt“ all together and it will merely print to the screen.

    This is the information that I found useful. If you want to tweak the script to fit your needs please check out the MSDN articles about SPQuota Members and SPQuotaTemplate Members

    Caveat: If you have lots of sites, you might want to consider specifying a web application or a site collection. While I have a dispose statement in here, the script is loading the quotatemplate and spsite information into memory for every site in order to give you the information. This can be a long running operation and take up some significant resources on your server while it is happening. Know your environment and don’t allow this to cause you an issue. I send this information out to a file because I run this over night on my app server and can then look at the information later. The information can be shared with the team who handles support and customer relations so that we can work with the customer to either reduce the size of their site, or raise their quota as needed (usually comes with an additional cost).

    Does Storman.aspx solve this issue in SP1?

    As of this blog post I have not yet played with SP1 as I am methodically, to my detriment, walking through a complete new build of SharePoint 2010 SP1 on top of SQL 2008 R2 SP1 and Denali CTP3 side by side to do a comparative article (hint on future content). As a result I have been delayed in getting to validate and test this, so the honest answer is I am just not sure yet. Regardless, I know that there are going to be lots of people who will take months to upgrade to SP1 and hopefully this script will be able to help them in the mean time.

    Even if Storman.aspx does give a better UI interface, having the text file output of this information is going to be vitally important for better customer support in my opinion.

    Credit where credit is due

    Shannon Bray, the co-author of one of my current favorite tech books, Automating SharePoint 2010 with Windows PowerShell 2.0, pointed me to a lead in his book that started me on a path and numerous unnamed blog articles each lent a bit to the final answer which you see above. Additionally, two of my co-workers were instrumental in getting me over the last hurdles of this process, Jim Rearick & JJ Willette (supplied the nifty code to get the quota name). Thanks for all of your contributions.

    powershell PowerShell Get-QuotaInfo Script from GitHub

    notepad Text Get-QuotaInfo file

  • What’s new in SQL Server Code Name “Denali” CTP3

    Be sure to check out the What’s New (SQL Server “Denali”) site for full details, but here are the highlights in this PUBLIC release (no TAP NDA material here!) are:

    SQL Server Installation

    1. Installing Prerequisites During SQL Server Code-Named “Denali” Setup
      1. Windows PowerShell 2.0 is a prerequisite, but not installed by the SQL Server Setup wizard
      2. .NET Framework 3.5 SP1 is a requirement, but is not installed by the SQL Server Setup wizard, it  requires you to download and install manually
      3. .NET Framework 4 is a prerequisite and is installed as a part of the SQL Server Setup wizard
      4. Minimum OS configuration is now Windows 7 SP1 & Windows Server 2008 R2 SP1
        1. check out the Hardware and Software Requirements for Installing SQL Server “Denali”
    2. You can now install Data Quality Services (DQS) using the SQL Server Setup wizard
    3. Product Update is a new setup feature which allows you to download the latest updates and apply them during install
    4. Server Core Installation is now supported
    5. SQL Server multi-subnet clustering makes it so that you can have failover nodes on different subnets
    6. Local Disk is now a supported storage option for tempdb for SQL Server failover cluster installations
    7. BUILTINadministrators and Local System (NT AUTHORITYSYSTEM) are not automatically provisioned in the sysadmin fixed server role
    8. Setup now offers default accounts for the SQL Server services whenever possible
    9. The Active Directory Helper service is no longer installed because it is no longer needed
    10. SQL Server Itanium editions are no longer supported

    Database Engine (too much here to list all of the good ones, this is a MUST READ)

    1. AlwaysOn SQL Server Failover Cluster Instances
    2. AlwaysOn Availability Groups
    3. Indirect Checkpoints

    Manageability Enhancements

    1. The Database Engine Query Editor introduces enhanced functionality for Transact-SQL debugging and IntelliSense.
    2. The Upgrade a Data-Tier Application wizard has been updated to perform an in-place upgrade, which replaces the side-by-side upgrade process
    3. Users import the sqlps module into PowerShell, and the module then loads the SQL Server snap-ins.
    4. The bcp Utility and sqlcmd Utility utilities now have the -K switch, which allows you to specify read-only access to a secondary replica in an AlwaysOn availability group.
    5. Database Engine Tuning Advisor Enhancements allow you to use the query plan cache to avoid having to create manual workloads from a script or trace.

    Programmability Enhancements

    1. You can no longer use CREATE ENDPOINT or ALTER ENDPOINT to add or modify SOAP/HTTP endpoints
    2. The FileTable feature leverages FILESTREAM to allow files and documents to be stored in special tables and accessed using Windows applications, as though they were stored on the file system without making changes to the applications
    3. Semantic search builds upon the existing full-text search feature in SQL Server but enables new scenarios that extend beyond syntactical keyword searches
    4. Full Text Search now supports property-scoped searching on properties emitted by IFilters, Customizable NEAR option of the CONTAINS predicate or the CONTAINSTABLE function
    5. New Word Breakers and Stemmers (this one has a a bit baffled, I thought I was doing well understanding the Programmability stuff until I got to this one…)
    6. The EXECUTE statement can now specify the metadata returned from the statement by using the WITH RESULT SETS argument
    7. You can specify a range of rows returned by a SELECT statement based on row offset and row count values that you provide
    8. Three new sub-data types for geometry and geography data types can be used to store circular arc segments, CircularString,CompoundCurve, and CurvePolygon. (and my ITPro brain just imploded…)
    9. A sequence object is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. It operates similar to an identity column, but sequence numbers are not restricted to use in a single table.
      • I know lots of Oracle DBAs who are excited by this one, personally I haven’t wrapped my head around  it completely yet…  Going to have to read more on Sequence Numbers
    10. The THROW statement can be used to raise an exception and transfer execution to a CATCH block of a TRY…CATCH construct
    11. 14 new functions:
    12. 1 changed function:
    13. New and Enhanced Query Optimizer Hints
    14. New XEvent Enhancements
    15. New Analytic Functions:

    Scalability and Performance Enhancements

    1. New data warehouse query acceleration feature called columnstore indexes
    2. Indexes containing varchar(max), nvarchar(max), and varbinary(max) columns can now be rebuilt as an online operation
    3. 15k partitions are now supported by default instead of 1k

    Security Enhancements

    1. User-defined server roles are now available.  This allows a role to be defined at the server level and grant or deny access across all databases
    2. You can now define a default schema for a Windows group
    3. Significant SQL Server Audit Enhancements
    4. Access to contained databases is permitted through contained database users which do not require logins
    5. The HASHBYTES function now supports the SHA2_256, and SHA2_512 algorithms
    6. The RC4 algorithm is only supported for backward compatibility
    7. The maximum length of private keys imported from an external source is expanded from 3,456 to 4,096 bits
    8. SMK and DMK encryption changes from 3DES to AES

  • How to disable Shutdown Event Tracker in Windows Server 2008 R2

    Scenario: You have people in your office, you are in the middle of work in 2 different Dev VMs plus Visual Studio on your host or you are working on several servers plus your local Dev VM while writing admin scripts (making this viable for Admin and Dev types alike).  Time for a reboot of your Dev VM so you click on reboot and flip back to your other work and figure you will check back in 10 minutes once it has had time to reboot.  An hour goes by and you are finally rearing to go on the Dev VM so you flip back to it only to find this screen:

    Shutdown1

    Anyone else want to throw a virtual rock at the virtual screen at that point?

    I am in the middle of building out 5 new VMs for my lab and always find having to enter a reason for rebooting to be painful, so I FINALLY decided to do something about it. 

    There are a number of articles out there on how to do this, but many of them are out of date.  I tried every one of them and this was the one that I found that works every time:

    How to enable and disable Shutdown Event Tracker

    1. Open gpedit.msc

    2. Go to Computer Configuration | Administrative Templates | System

    3. Set “Display Shutdown Event Tracker” to Disabled

    shutdown2

    A reboot is not required for this to take effect.  You set a Group Policy if you are in a domain or multi-machine situation, but for standalone systems, this works just fine.

    CAVEAT: I highly recommend this only be used in development/test VMs.  This makes it so there is no safety net if someone accidentally clicks restart or shutdown instead of logoff. 

    I am big on giving credit to those who I find information from, but in this case there were so many different options that I lost track of who I got this lead from, so thank you to the unnamed tipster for this lead.

  • Review of the new storage “guidance” for SharePoint 2010 SP1

    Rob D’Oria, father of StoragePoint, has written a very comprehensive review of the new storage “guidance” from Microsoft with regard to the limits on ContentDB size when using RBSEBS.  It is a very long post, but completely worth reading from start to finish. 

    For a peak at what you can expect, here is a direct excerpt from one of my favorite sections:

    Everyone hold on, here’s the super-hard backup/restore process you have to follow in this brave new world:

    Backup

    1. Backup content database
    2. Backup BLOB store(s) referenced by content database

    Restore

    1. Restore BLOB store(s) referenced by content database
    2. Restore content database

    Did anyone pass out?  Anyone hyper-ventilating?  Anyone soil themselves?  I didn’t think so.  That’s the process in all of its complicated ugliness.  In this scenario the worst thing you end up with is some orphaned BLOBs…you have some extra BLOBs in the BLOB store back-up that are not reflected in the content DB backup because it was taken first.  Upon restoring these datasets the extra BLOBs are orphaned, but they will be garbage-collected if the EBS/RBS solution supports it.

    And, oh by the way, this is your worst case scenario.

    Please take some time and read Rob’s full post, which can be found here: http://blog.sharepointedutech.com/2011/07/11/sharepoint-contentdb-guidance-too-many-shades-of-gray-along-with-a-little-brown/

    Connect with Rob on Twitter at @robdoria or on LinkedIn at http://www.linkedin.com/in/robdoria

    Rob’s opinions in his post of ISVs are his own.  I may or may not agree with everything he says, so be sure to rub a couple of brain cells together and form your own opinions.

  • The big news: Data Storage Changes for SharePoint 2010

    I go on vacation for one week and they try to change the entire strategy on us, eh?  When I first started hearing rumbles about the changes announced by the Microsoft SharePoint Product Group via their blog it sounded like the world was completely changed and that all previously known storage strategies can be thrown out the window in favor of using RBS and storing whatever you want in the Content DB!

    Happily that is not at all what was being said.  While the recommended limits have been revised by Microsoft to suggest that significantly more can be stored, the key word in the statement is CAN, not should.

    Let’s examine a few telling things from the Product Group’s blog article:

    For a SharePoint content database up to 4 TB you need to additionally plan for the following two requirements:

    • Requires disk sub-system performance of 0.25 IOPS per GB, 2 IOPS per GB is recommended for optimal performance.

    • Requires the customer to have plans for high availability, disaster recovery, future capacity, and performance testing.

    • And you need to review additional considerations in the TechNet Boundaries and Limits article.

    What this part is suggesting to us is we can us a single mirrored set (RAID1) for a large content database and get what Microsoft is defining as “optimal performance” using a 15k RPM drive scenario (approximately 180 IOPS per drive).  Great if you are looking to get some separation and you are doing sequential I/O, however when you separate the BLOBs out of the ContentDB you aren’t doing sequential I/O anymore.

  • Office Web Apps Service Pack 1 is here!

    It has almost gone overlooked, but Office 2010 and SharePoint 2010 are not the only products to get a Service Pack 1 release today.  I know that the Office Web Apps are a bolt on to SharePoint 2010 and so they don’t get a whole lot of attention, but from an end user feature enhancement perspective, I am almost more excited about the Office Web Apps SP1 than anything else released today (Office 365 not withstanding).

    Sourced directly from Microsoft’s KB2460073:

    Overview of Office Web Apps SP1 improvements

    These are the key areas of improvement in SP1.

    All Office Web Apps

    • OpenDocument Format (ODF) support:
      Lets you view and edit ODF documents in Office Web Apps.
    • Lets you use Office Web Apps with the Google Chrome browser.
    • Outlook Web App document attachment viewing (for Exchange Online only):
      Lets you view Office document attachments in the browser directly from Outlook Web App.
    • Lets you use Office Web Apps with Internet Explorer 9 in Native mode.

    Word Web App

    Lets you print Word documents from Edit mode (in addition to View mode).

    Excel Web App

    • Added a Close button.
    • Lets you insert charts in Excel Web App.
    • Lets you copy and paste values and formulas by dragging the fill handle.

    PowerPoint Web App

    • Lets you print presentations from PowerPoint Web App.
    • Lets you edit text in more shapes, not just placeholder shapes.
    • Lets you insert clip art in PowerPoint Web App. (By default clip art support is disabled. An administrator can decide to enable it.)

     It is important to keep in mind that Microsoft has already released the June 2011 Cumulative Updates that contain security patches important enough to be released simultaneously with these Service Packs, so be sure to install that as well.

    Also, Microsoft has already released a list of known issues when installing Service Pack 1.  The Office Web Apps specific item,sourced directly from Microsoft’s KB2532126, is:

    • If Office Web Apps will be used in a mixed version environment, where Office Web Apps has SP1-or-later applied while SharePoint Server 2010 remains on the RTM version, you must install the following two updates before you install SP1 for Office Web Apps:

      • 2510639 Description of the SharePoint Server 2010 update: April 12, 2011

      • 2510648 Description of the Office Web Apps update: April 12, 2011

    Here are the download links:

    Service Pack 1 for Microsoft Office Web Apps 2010 (KB2460073)
    http://www.microsoft.com/download/en/details.aspx?id=26639

    Downloadable list of issues that the service pack fixes
    http://download.microsoft.com/download/B/0/D/B0D1540B-6CF6-4674-9C8E-7A45FD91C163/Microsoft%20SharePoint%202010%20and%20Office%20servers%20Service%20Pack%201%20Changes.xlsx

    SharePoint Foundation 2010 June 2011 CU (UPDATED)
    http://support.microsoft.com/hotfix/KBHotfix.aspx?kbln=en-us&kbnum=2536601

  • SharePoint 2010 Service Pack 1 is here!

    Today Microsoft released Service Pack 1 for SharePoint 2010 which is a rollup of all previous Cumulative Updates through April 2011, which means that deploying directly on top of RTM bits supported, as well as number of stability, performance, and security enhancements based upon customer feedback.  There is a detailed white paper that about the Service Pack that can be found at this location.​ ​

    CAVEAT: Microsoft strongly recommends installing the June 2011 Cumulative Update immediately after the installation of Service Pack 1. The June Cumulative Update includes several important security and bug fixes that are not included SP1.

    Here is the list of of the new features in SP1:

    · Support for SQL Server Code Name “Denali”

    anyone who has read any of my tweets or articles has probably noticed that I am a fan of Denali, so this is a huge feature for me

    · Shallow copy functionality

    this new feature allows sites with externalized blobs to be moved from one contentdb to another without having to reinternalize the blobs using the Move-SPSite powershell cmdlet. 

    The white paper states​:

    Prerequisite
    In order to use shallow copy, you must have installed the Microsoft® SQL Server® 2008 R2 Remote Blob Store. The Remote Blob Store is included in the Microsoft® SQL Server® 2008 R2 SP1 Community Technology Preview Feature Pack (http://go.microsoft.com/fwlink/LinkId=221775).

    There are a couple of things that jump out at me here that are concerning:

    1. The link doesn’t work
    2. SP1 supports functionality that is only available in SP1 CTP?  RBS is supported in SQL 2008 R2, does it work with that? (I fully intend to test this and post back, but it is a concern that I felt was worthy of raising)

    I’m not getting all nervous about this just yet since this all just released today.  It will be interesting to see what comes of this over the next couple of days/weeks, but it is a VERY exciting feature if it works as advertised.

    · Site recycle bin

    Great feature to allow self recovery of Site Collections and Sites without having to revert to backups or third party tools.

    · Improvements to storage management (StorMan.aspx)

    Storage Space Allocation is huge when dealing with quota enforced sites.  This page, which was available in versions prior to 2010, allows end users to view their large files and determine what fat to trim to keep within their quotas.

    · Cascading filters for Performance Point services

    Quoted from the white paper: “New cascading filter support enables you to pass the value of one filter to another.”

    · Additional browser support

    Better support for Chrome

    One more piece of really useful information to come out of the white paper:

    Updated Windows PowerShell Commandlets

    · NEW: Get-SPDeletedSite (SharePoint Server 2010)

    · NEW: Remove-SPDeletedSite (SharePoint Server 2010)

    · NEW: Restore-SPDeletedSite (SharePoint Server 2010)

    · Move-SPSite (RBSProviderMapping parameter added) (SharePoint Server 2010)

    Here are the download links:

    Service Pack 1 for SharePoint Foundation 2010 (KB2460058)
    http://www.microsoft.com/download/en/details.aspx?id=26640

    Service Pack 1 for Microsoft SharePoint Server 2010 (KB2460045)
    http://www.microsoft.com/download/en/details.aspx?id=26623

    Downloadable list of issues that the service pack fixes
    http://download.microsoft.com/download/B/0/D/B0D1540B-6CF6-4674-9C8E-7A45FD91C163/Microsoft%20SharePoint%202010%20and%20Office%20servers%20Service%20Pack%201%20Changes.xlsx

    SharePoint Foundation 2010 June 2011 CU (UPDATED)
    http://support.microsoft.com/hotfix/KBHotfix.aspx?kbln=en-us&kbnum=2536601

    SharePoint Server 2010 June 2011 CU (UPDATED)
    http://support.microsoft.com/hotfix/KBHotfix.aspx?kbln=en-us&kbnum=2536599

  • SQL Server Default Trace

    In every version since SQL 2005 there has been a “background trace” enabled by default on all SQL Server installations.  This was a bit of a shocking revelation to me, as I am not a DBA, since every time I have called Microsoft dozens upon dozens of times and talked to tier 3 support / product team members for SQL Server and none of them have mentioned this fact when we have been diagnosing issues in SQL.  We have always had to set up a new trace.

    Use the following command to validate that the default trace is enabled:

    image

    This will return something that looks like:

    traceid

    property

    value
    1 1 2
    1 2 E:MSSQL10_50.instanceMSSQLLo​glog_560.trc  
    1 3 20
    1 4 NULL
    1 5 1

    From MSDN we get a legend that helps us under stand the above output:

    Column name Data type Description

    traceid

    int

    ID of the trace.
    property int

    Property of the trace:

    1= Trace options. For more information, see @options in sp_trace_create (Transact-SQL).

    2 = File name

    3 = Max size

    4 = Stop time

    5 = Current trace status. 0 = stopped. 1 = running.
    value sql_variant Information about the property of the trace specified.

    The question I have heard most often since this discovery is “What does it matter?  Microsoft has it turned on by default so that must mean they intended it that way.  It must not cause a performance impact.  Right?”

    After I stopped laughing at the idea that because Microsoft turned it on by default it must be ok (see Windows 2000 and before where everything was turned on and wide open upon install) I explained that the default trace is dropped into the installed Logs directory.  This cannot be changed.  You can stop the default trace and create your own, but that will break some of the built in reports that rely upon it.

    The key is knowing that this is there.  While it may be lightweight and designed to be non-impactful, if you have anything else with a write intensive load directed at the same spindles you can expect to see some contention.

    The positive of it is that now that we know it is there, it is extremely useful in troubleshooting.  The default trace can be queried by using the following command to pull back the most useful data (querying * pulls back WAY too much data for my taste):

    image

    Enjoy!