Nothing too complicated today but something that some people aren’t aware of and ask if it can be done – having more than one content database so they can, for example, keep sensitive data separate from their general day to day content.
It’s simple enough to create multiple content databases in Central Administration:
Within Application Management, click the Manage Content Databases link. This will take you to the /_admin/CNTDBADM.aspx page.
From that page, click the Add a content database link. Ensure the Web Application is set to the one where you want new site collection to be created in, fill in the new content database form and click OK. After a short while your new content database will be ready to use.
There are a couple of ways to setup the content database as the default database for new site collections. The first is:
Method 1
Click on every content database that you do not want the new site collection to be created in and set the Database Status to Offline.
This does not delete\remove the content database or make it unavailable. All it does is prevent SharePoint from creating new site collections within that database.
I don’t recommend leaving your content databases in an offline state once the site collection has been created, as the user profile store will become out of sync as SharePoint will skip the sites within the offline databases. This is due to how SharePoint stores user information at each site collection level, meaning updates made will not propagate to the User Profile application.
Method 2 (My preferred method)
A better way of achieving this is to keep all content databases online (status of Ready) unless you have a specific reason for taking it offline. To force SharePoint to create new site collections within a single database, set the Maximum number of sites that can be created in this database to the current number of sites for every database except for the one you want to use.
and the edit page for WSS_Content:
Subsequently, when a new site collection is created, SharePoint will create it within the content database (WSS_Content_2 in the example above) that has capacity to take on more sites, which should only be the one database that you left with room for new sites.
