Importance of DB architecture in SharePoint


I have seen many SharePoint Database’s poorly architected, which will leads to the poor performance of the over all Farm. If you are dealing with huge SharePoint farms with more than 1 or 2 TB of data for the ease of maintenence Sharepoint DB architecture comes in to picture.

Often we land in the trouble of splitting the content DB by using SPMergeContentDB and this may fail due to isignificant site collection size, user traffic, and SQL Server load. When the STSADM MergeContentDBs command fails, both the source and destination databases can be corrupted. http://support.microsoft.com/?id=969242

Another importance to DB architecture is during the migration. Since SP2013 supports only database attach migrations, its crucial to keep the DB’s with ease of maintenance.

This blog post is to discuss about few DB governance tactics to avoid these issues.

DB Governance:

Microsoft’s recommendation is not to keep any DB’s more than 100 GB and 200 GB is the maximum hard limit. To achieve this, analysis of the DB growth is important. If you are migrating from lower sharepoint versions or from other CMS such as Lotus notes, Documentum etc you will already have an idea of this. If not, a survey with the stake holders (Department managers) is a good idea.

I am going to discuss about few strategies of DB governance. Before adopting to any one of these, the following dependencies should be considered:

  • Data growth
  • Site spaces
  • Ease of Database maintenance &
  • Data isolation

Strategy 1:

Round robin way: This is a simplest database architecture you can have if the site collection growth is more or less same across all the site collections and if you don’t have enough resources for SharePoint DB maintenance plans, follow the below strategy.

Scenario:

If the growth of the database is calculated to be 2TB of data for the next 2-3 years (Probably you need to upgrade to next version) follow the below steps:

  1. Create 2,000 GB/100 GB -> 20 content databases with names WSS_Content_1 to WSS_Content_2
  2. Verify all the databases are online

Whenever a new site collection is created, it will use the least used db (DB with least no of site collections) in the round robin way. One of the caveat with this approach is if you have a huge site collection (>50GB), you should use a dedicated content DB.

  1. Create a new content DB WSS_Content_Large
  2. Set other DB’s to offline state such that site collection will be created in new DB
  3. Create new site collection
  4. Make other DB’s online
  5. Make WSS_Content_large offline to avoid creation of new Site collections.

Strategy 2:

If your farm demands high performance and if your SharePoint administrator is handy enough to move the sites with quarterly or half yearly DB plans below strategy will be helpful. If you have a farm with more than 200 site collections you can consider adopting to this strategy:

Idea is to segregate databases according to the site collection sizes and implement a DB maintenance to keep them consistent i.e, first create Content DB’s with represents sizes of site collections (Eg: WSS_Content_2GB, WSS_Content_4GB etc), Initially all the sites will reside under WSS_Content_2GB and when the Site collection grows beyond 2 GB it will be moved to 4 GB and goes on. If the size of DB grows beyond 100 GB create a new DB (Eg: WSS_Content_2GB_01).

Detailed steps are explained below:

Create databases WSS_Content_2GB, WSS_Content_4GB, WSS_Content_6GB

Set WSS_Content_4GB and WSS_Content_6GB offline such that new site collections will be created at WSS_Content_2GB

Quarterly DB maintenance activity: Every 3 or 4 months, the below steps should be performed to keep the DB sizes consistent

List all the site-collections with content DB name and size using Powershell or STSADM

Get-SPSite -Limit ALL | select url, @{label=”Size in MB”;Expression={$_.usage.storage/1MB}} | Sort-Object -Descending -Property “Size in MB” | ConvertTo-Html -title “Site Collections sort by size” | Set-Content SizeReport.html
Now identify all the site collections which are more than 2 GB. Now move the sites which are between 2-4GB to WSS_Content_4GB and more than that to WSS_Content_6GB.
You can use either STADM SPMergeContentDB or Powershell command Move-SPSite as explained below.
  • After some time, if any of the DB’s exceeds 90 GB make that DB offline and create a new DB.For an eg, if WSS_Content_2GB exceeds 90 GB, create a new DB WSS_Content_2GB_01 and make the former offline.
  • Now If there is a requirement to create a site collection of 25 GB, best practice is to allocate a separate a site content DB.For this create a new content DB, WSS_Content_25GB and create the site collection using powershell, rather than central admin.
This process will ensure, at any situation none of the database exceeds 100 GB and its very easy for the SharePoint Admin to maintain the DB’s during migrations to next versions.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s