SQL 2005 DB Partitioning for SharePoint

Question

Background

I have a massive db for a SharePoint site collection. It is 130GB and growing at 10gb per month. 100GB of the 130GB is in one site collection. 30GB is the version table. There is only one site collection - this is by design.

Question

Am I able to partition a database (SharePoint) using SQL 2005s data partitioning features (creating multiple data files)? Is it possible to partition a database that is already created? Has anyone partitioned a SharePoint DB? Will I encounter any issues?

Answer 1

You would have to create a partition set and rebuild the table on that partition set. SQL2005 can only partition on a single column, so you would have to have a column in the DB that

• Behaves fairly predictably so you don't get a large skew in the amount of data in each partition
• IIRC the column has to be a numeric or datetime value
• In practice it's easiest if it's monotonically increasing - you can create a series of partitions (automatically or manually) and the system will fill them up as it gets to the range definitions.

A date (perhaps the date the document was entered) would be ideal. However, you may or may not have a useful column on the large table. M.S. tech support would be the best source of advice for this.

The partitioning should be transparent to the application (again, you need a column with appropriate behaviour to use as a partition key).

Unless you are lucky enough to have a partition key column that is also used as a search predicate in the most common queries you may not get much query performance benefit from the partitioning. An example of a column that works well is a date column on a data warehouse. However, your Sharepoint application may not make extensive use of this sort fo query.