Some Best Practices for Content Databases in SharePoint 2010 (or: Attack of the 32-Character GUIDs)
One of the coolest things about SharePoint’s platform (and one of its biggest selling points, if you ask me) is its scalability. It can follow a business from its humble beginnings to its days of Fortune 500 glory, providing a familiar platform for collaboration and knowledge capture all along the way.
Since SharePoint 2007, Microsoft has made it easy to move around Web applications, shared services and content databases when the time comes to scale up. One Windows Server instance running both the Web frontend (WFE) and SQL Server back-end can quickly be broken into a two machine topology by detaching the content database and moving it to a new server. Two servers can become three by migrating the more process-intensive service applications to a third machine, or by introducing a new WFE. This process can continue ad infinitum, though at some point you’ll want to begin using a load balancer between your web front-ends.
There are some best practices that I’ve picked up as a SharePoint consultant that can make the scaling-up process easier, and today I’d like to share some tips that make managing your content databases a snap. These guidelines are especially important if Sharepoint will be sharing a SQL Server with the databases from other business applications, like ERP or LOB systems.
All SharePoint databases use the same collation, which you should take to heart: LATIN1_General_CI_AS_KS_WS. The software will refuse to recognize a database if it does not use this collation.
You should develop a naming convention for all of your SharePoint databases before you begin setting up SharePoint for the first time. I like to group them functionally.
For example: All SharePoint-related databases begin with with SP_ Configuration and Central Admin databases have names like SP_Admin_CA and SP_Admin_Config Service databases (such as Search, MySites, or managed metadata) should be named SP_Service_Search, SP_Service_MySites, SP_Service_ManMetaData, etc. Content databases for site collections should be named SP_Content_Portal, SP_Content_Management, SP_Content_BI, etc. If you care about the names of your content databases, DO NOT let SharePoint Central Administration create them!
Otherwise, you may be looking at a bunch of not-so-descriptive names like WSS_Content_[32-character GUID]. Mixing a bunch of poorly-named databases into an otherwise tidy SQL Server is a great way to make enemies with your database administrator.
Following good naming conventions and keeping things tidy will make moving content databases and diagnosing any potential issues with your SharePoint server easier.