Business Productivity and Information Architecture

  • Michael Nemtsev

    Hello, my name is Michael Nemtsev and I welcome you on my blog about Microsoft technologies, Business Productivity and Information Management

    I'm a Senior Consultant in "Enterprise Services" team @Microsoft, based in Sydney, Australia, helping clients to architect solutions in Business Productivity area. I'm an Ex-MVP (2005-2011) in .NET/C# and SharePoint Server

    If you have any questions about Information Management and Business Productivity feel free to ask via Contact Form page







    Me on Twitter





    Windows Live Alerts

Tags

Email Notifications

Archives

Tips to create a Site Collection in new Content Database

Intro

Ken Zheng posted a good overview of how to create a Site Collection in new Content Database http://littletalk.wordpress.com/2008/10/10/creating-a-site-collection-in-a-new-content-database/

I’d like to add some notes to Ken’s post, regarding planning site collections and sub-sites among different content databases.

There are some reasons, why you could have several content databases:

  • MS recommends to have content database not more the 100Gb, otherwise it could hinder performance.
  • Data usage optimization
  • It simplifies farm backup and restoration.
  • It provides flexibility for Disaster Recovery (DR) strategy.

 

Scenario - Data Usage

The major advance of several content databases is splitting your site collection and sub-sited among several content databases based on the content type and content usage. For example, you can have media sites and media data stored in separate content database and separate SQL server, which is tuned, indexed and partitioned to work with BLOB data; or sites which are used for document management, in separate SQL server with content database optimized for textual information.

If you will store different type of content information inside one big single content database in won’t help you to use all advantages of SQL Server to optimize your data usage.

Scenario – Backup/Restore

Having several content database makes you data restoration faster, because you need to restore only that content database, where origin site/data located. 

Scenario – DR

Usually when you implement DR strategy your DR box locates outside current network and outside the current organization, and you could have slow network connection there (reasonably slower then inside your network). So, setting SQL mirroring takes a lot of time if you have very big database. Moreover, with the different content databases you can leverage the importance of your data and mirroring to different locations, for example mirroring your vital financial data in one content database to the box outside your enterprise, and other content databases inside your network. 

Tips and Tools

Ken describes how to create new sites in new content databases, but what if you already have 200Gb content database and what to split and reorganize your data across several new content databases?! SharePoint doesn’t provide OOTB UI features for this. The standard way to reorganized you data for the sub-sites is to use STSADM tool (I will discuss moving site collections separately).

The way to move your sub-sites to new content database is to follow the next steps

  1. Go to Central Administration > Application Management > Content Databases and set the existed content database to “Offline” mode
  2. Add new content database in Central Administration > Application Management > Content Databases. (you can use different SQL server). Now all new sites will be created in this content database
  3. Use the following command to export your site to the disk “stsadm -o export -url http://<path_to_site> -filename c:\<exportedSite>.exp -overwrite -nofilecompression -includeusersecurity -cabsize 1024”.
  4. Delete your exported site collection in Central Administration > Application Management > Delete Site Collection
  5. Create new empty location with the same path, via stsadm –o createweb/createsite;
  6. Import you site to the same url, and site will be created in the new content database. Use the following command for this “stsadm -o import –url http://<path_to_site> -filename c:\<location_of_your_exorted_package>.exp -nofilecompression -haltonfatalerror –includeusersecurity”

Now you will have you sub-site in new content database. It’s not very user friendly approach, and is an error proned a bit.

But there is one small tool - SharePoint Administration Tookit, which helps you to to reorganise the whole site collections. This feature will install a new section inside “Applications”and you can move your site collection via Central Administration interface. 

Resume

Plan you site collections / sub-sites content with you database administration guys

Take into account that you can’t split site and site content between content databases, for example you can’t specify storing all site’s *.avi files in separate content db. I hope it will be changed in the next version of SharePoint, but now site and site content are stored together.

Mirror: Tips to create a Site Collection in new Content Database

Comments

jc said:

Great Article.

How do you delete a Site collection that has no Database?  I've tried from stsadm and CA .. neither works.

Also, apparently stsadm -o deletesite does not delete the Database of healthy sites either.. and apparently deleting the dataabase from SQL creates problems like the one I'm having... the site shows up in CA, but has no DB.

please email me at jc_pineiro(at)yahoo(dot)com

# November 10, 2008 2:29 PM

CB said:

If you delete your database before deleting the site collection, you can go into the Application Management -> Content databases and delete the reference to the database there, then the site will disappear.

# January 21, 2010 9:52 PM

Linda Chapman said:

Correction to your article, Microsoft does NOT recommend you keep your DB under 100GB for "performance" reasons. They only recommend that because of how long it takes to RESTORE a Content Database in a recovery situation. Many of us have content databases way over 100GB with no performance problems what so ever. So if you don't have an SLA for recoverying that Content DB to meet then going over 100GB is no problem.

# May 4, 2010 5:25 PM

Michael said:

The 100GB recommendation comes from the MS tests.

Having large content db affect the performance in the way database table become locked.

SharePoint stores information in large tables, the are shared actoss all uses in one site collection

So, when you have large collaborative site SQL start locking the tables and non all users are able to work.

MS tests showed that the recommended size of content 100GB, that means you can use even 1TB data, but everything depends on the usage scenarios.

as soon you have table lock you need to add new content DB and move sites collections there

# May 4, 2010 6:26 PM

Senthamil said:

Nice post. It solved my issue

# June 1, 2010 9:34 PM

Bob said:

Linda is correct. Michael you need to provide a reference to what you are claiming is MS recommendation.

# October 8, 2010 1:28 PM

Michael said:

# October 8, 2010 6:19 PM

Ben said:

Good info! Thanks

# February 2, 2011 9:40 PM

Ramesh Yadav said:

Very helpful. Thanks.

# September 15, 2011 9:01 AM

Satheesh said:

As per the link given by Michael, I found the below info. Apparently, the limit is raised to 200 GB now.

Content database size (general usage scenarios)

200 GB per content database

We strongly recommended limiting the size of content databases to 200 GB, except when the circumstances in the following rows in this table apply.

If you are using Remote BLOB Storage (RBS), the total volume of remote BLOB storage and metadata in the content database must not exceed this limit.

# November 17, 2011 2:52 AM

Erica Toelle said:

Thank you for this post!

# May 16, 2012 7:51 PM
Leave a Comment

(required) 

(required) 

(optional)
 

(required) 

If you can't read this number refresh your screen
Enter the numbers above: