Sharepoint And Partitioning

Sharepoint and SQL Server have a somewhat rocky relationship. Sharepoint likes to use GUIDs everywhere and likes to handle database maintenance on its own. It sometimes seems like the database design was specifically concocted as an example of how NOT to design databases for best performance. On the other hand, Sharepoint is really good about automatically leveraging features of SQL Server if they are available. For example, if Sharepoint detects it is using SQL Server Enterprise edition, will will automatically use Enterprise-only features. One such feature is partitioned tables and, if you are not aware of this, it can really mess up your backup and restore strategies and scripts.

In my case, I have a Sharepoint server that is configured to use Web Analytics. This Sharepoint feature provides lots of reports regarding the way people use your website. If Sharepoint is using SQL Server Enterprise Edition to store its data, Web Analytics will automatically enable table partitioning for its tables. It aggregates data on a weekly basis and stores each week’s worth of data in it’s own partition. This provides improved reporting performance.

From a DBA standpoint, this means your web analytics database will get a new .NDF data file added to it each week. If your Sharepoint admin did not set up a data retention policy, as time passes, you can end up with hundreds of database files for your web analytics database. This may cause problems with any backup restore scripts you may have or any automated processes that have to manipulate database files. In fact, this was how I discovered this behavior. I have a routine that randomly selects backups to restore to a test server to check their validity. It parses the header of the backup files and builds the restore command to restore the database to a test server. When it encountered my Sharepoint web analytics database, the number of .NDF files had grown so large that it exceeded the length of the variable I used to construct the restore command.

This Sharepoint behavior is documented here.


Leave a Reply

Your email address will not be published. Required fields are marked *

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.