Site Collection Management in SharePoint

April 17, 2015 // By Matt Youngstrom

It’s important to properly manage how site collections are placed into content databases within a SharePoint Farm. This is because a content database size can negatively affect performance, especially when it grows to hundreds of gigabytes or even terabytes of data. There are numerous ways in which the content database size can be kept in check. SharePoint does a good job of making sure that newly created site collections are evenly distributed across content databases if multiple databases exist. Sometimes, however, you may want more control over which content database a site collection calls home. In that case, you do have options. This blog article will explain how SharePoint manages site collection distribution and how you can take the reins to ensure a highly performing SharePoint site for your end users.

SharePoint Methodology for Distribution of Site Collections

When you have multiple content databases in place for a web application, SharePoint has logic that distributes newly created site collections evenly across the databases.

The rules SharePoint follows are:

• SharePoint site collections are always associated with one and only one content database. Site collections cannot be distributed across content databases.
• Sites created within a site collection cannot be distributed across content databases.
• Multiple site collections can exist in one content database. “My Sites” is an example of a content database with multiple site collections.
• SharePoint distributes newly created site collections created across on-line content databases evenly and the order in which they are placed is not arbitrary, but their location is not readily apparent.
• Content databases that are off-line are still being used by resident site collections, but are not eligible to contain new site collections.

If you open Central Administration, navigate to Application Management, and click on Manage Content Databases under the “Databases” section, you will be taken to a listing of content databases.

There are a number of settings listed, but for the purposes of this article, we are interested in “Current Number of Site Collections,” “Site Collection Level Warning” and “Maximum Number of Site Collections.”

Current Number of Site Collections (Site Count) is the number of current site collections in the content database.

Site Collection Level Warning (Site Warning Limit) is the number of site collections at which SharePoint warns that it is getting close to the maximum number.

Maximum Number of Site Collections (Max Site Limit) is the absolute limit for the number of site collections in the content database. SharePoint will not add any site collections beyond this number.

These numbers are important, because the way in which site collections get distributed among multiple content databases can be different based on how they are set:

Uniform Site Collection Dimensions

Given that each content database has been set to the same exact dimensions in regard to site warning limit and max site limit, the site collection to content database ratio is 1:1. In a scenario when there are N content databases attached to a single web application and, no prior site collections have been created, one site collection will exist in one content database for all newly created collections until all content databases have been filled. At that point, SharePoint continues to add site collections one at a time, always keeping them as even as possible.

Non-Uniform Site Collection Dimensions

When each content database has been set to different site warning limit and max site limit dimensions, SharePoint will distribute newly created site collections so that the site count to site warning limit between all content databases are as even as possible. To do this, it will create a new site collection in the content database that is farthest away from its site warning limit.

Example:

In this scenario, when a new site collection is created, it will go into Content DB “C” as its site count is farthest from its site warning limit.

Although site collection distribution is kept as even as possible, it’s not always apparent which content database a site was actually placed in when multiple content databases are on-line. This is because there is no central administration page to show this information. In theory, the example above should have gone to content database “C.” To verify this, you can use a PowerShell command-let to find out where it was actually placed:

Get-SPContentDatabase | %{Write-Output "- $($_.Name)”; foreach($site in $_.sites){write-Output $site.url}}

If you want SharePoint to be consistent in how it distributes site collections, configure your content databases with uniform dimensions. Sometimes, however, that configuration is not possible, in which case non-uniform dimensions must be used. In such instances, SharePoint will do its best to keep things as even as possible.

Putting Site Collection Management in Your Control

While SharePoint follows the above set of rules to help keep things even, it may not always make the most sense in terms of performance for your organization. You may find that you want a site collection in one particular database because you know it will grow large. Or you may find that a site collection in a content database is growing much faster than the rest and you want to move it out.

In any case, your tool belt includes (but is not limited to) the ability to:

1. Force SharePoint to place new site collections into a particular content database
2. Force SharePoint to place one and only one site collection in a content database
3. Merge site collections in different content databases
4. Split site collections in a content database into multiple content databases

Limiting content databases to single site collections/forcing creation in one content database

There is a trick to force SharePoint to place a newly created site collection into a content database of your choice and prevent further site collections from being added to that content database.

There are two ways to do this:

1. Use stsadm –createsiteinnewdb or the PowerShell command-let New-SPSite with the contentdatabase parameter. This will create the new site collection in a new content database.
2. Create a new content database in central administration for a web application.
1. Mark all content databases but the new one as off-line.
2. Create a new site collection using central administration for the web application.
3. Mark all content databases that were off-line back to on-line.

In both cases, you need to take the new content database off-line after the site collection is created. This will prevent any new site collections from being sent to the new content database.

Merging site collections into a single Content Database

Merging content databases means migrating site collections from different databases into one single content database. Many times, there are site collections spread out on many different content databases (perhaps due to SharePoint’s distribution rules), and they are not becoming as large as once thought. Consolidating them into one content database simplifies backups and helps with load balancing.

You cannot merge site collections utilizing central administration. You must use either the PowerShell command-let Move-SPSite (preferred method) or the legacy STSADM.EXE command stsadm –o mergecontentdbs.

Keep in mind that these operations are with respect to a single web application. You cannot use them to move sites to content databases in different web applications.

Splitting Site Collections into Different Content Databases

This is the reverse of merging, in which you pull out a site collection from a content database and move it into a new or different one. Again, utilize the Move-SPSite PowerShell command-let to perform this operation.

Conclusion and Best Practices

• SharePoint itself follows a very good procedure in making sure that site collections are evenly distributed across content databases. However, it isn’t perfect, and it does require some manual tweaking to keep things humming. To that end, you have some very powerful tools and options to manage where site collections ultimately live.
• Follow these best practices when managing site collections and their content database homes on an ongoing basis:
• Use wildcard inclusion managed paths, and don’t turn on self-service site creation (SSSC) for the web application unless there is a compelling business reason to do so. The exception is My Sites, which will require SSSC.
• Associate site collections with single content databases when the site collection is expected to start or grow beyond 10GB of space. Otherwise, use a single content database hosting all of your site collections. Monitor their usage and move site collections to new content databases if the content database is reaching approximately 50GB.
• If one site collection in a content database is relatively large compared to the rest, move the smaller site collections into their own content database and leave the large one by itself.
• Use site collection quota templates to place limits on site collection size. This is especially useful if SSSC is turned on for a web application.
• As a general rule, smaller content databases perform better and are easier to back-up and restore. For example it would be easier to manage ten 50GB databases than one 500GB database. The smaller databases would be quicker to restore, attach and detach. With a multi-threaded backup, it may be five times faster – or more – to backup and restore. At 500GB, performance will degrade, as you might find that creating and deleting sites is slow or even causes SQL blocking in some cases. If you're looking for a good max database size, start with 50GB. Even with a 5TB SQL server, you still only end up with 50 databases, so it really is a decent number to work with. //

Categories // Software Development
Tags Site Collection Management, SharePoint