How To: Add a new Content Database in SharePoint 2010

You can use the procedures that are described in this article to create a new content database and attach it to a Web application.

In this article:

• To add a content database to a Web application by using Central Administration
  
  
• To add a content database to a Web application by using Windows PowerShell
  

To add a content database to a Web application by using Central Administration

1. Verify that the user account that is being used to perform this operation is a member of the Farm Administrators SharePoint group. If you are using Windows authentication to connect to SQL Server, the user account must also be a member the SQL Server dbcreator fixed server role on the SQL Server instance where the database will be created. If you are using SQL authentication to connect to SQL Server, the SQL authentication account that you specify when you create the content database must have dbcreator permission on the SQL Server instance where the database will be created.
   
2. On the SharePoint Central Administration Web site, click Application Management.
   
3. In the Databases section, click Manage content databases.
   
4. On the Manage Content Databases page, click Add a content database.
   
5. On the Add Content Database page:
   
   
   
   1. Specify a Web application for the new database.
      
      
   2. Specify a database server to host the new database.
      
      
   3. Specify the authentication method that the new database will use and supply an account name and password, if they are necessary.
      
      
      

      Important:
      The account name and password must already exist as a SQL Server login.

   4. Specify the name of the failover database server, if one exists.
      
      
   5. Specify the number of top-level sites that can be created before a warning is issued. By default, this is 9,000.
      
      
   6. Specify the total number of top-level sites that can be created in the database. By default, this is 15,000.
      
      
      

      Note:
      For information about how to determine the number of top-level sites that the system can support, see Performance and capacity technical case studies (SharePoint Server 2010).

   7. Click OK.
      

To add a content database to a Web application by using Windows PowerShell

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin. If you are using Windows authentication to connect to SQL Server, the user account must also be a member the SQL Server dbcreator fixed server role on the SQL Server instance where the database will be created. If you are using SQL authentication to connect to SQL Server, the SQL authentication account that you specify when you create the content database must have dbcreator permission on the SQL Server instance where the database will be created.
   
2. On the Start menu, click Administrative Tools.
   
3. Click SharePoint 2010 Management Shell.
   
4. At the Windows PowerShell command prompt, type the following command:
   
   
   New-SPContentDatabase -Name  -WebApplication 

   Where is the name of the content database to create and is the name of the Web application to which the new database is attached.
   For more information, see New-SPContentDatabase.
   
   Adopted from: Microsoft TechNet Article
   

Creating Site Collection in a separate Content Database

Since, most of us ignore the site collection basics and seldom plan our site collection content database storage needs. We are often unaware of SharePoint limitations in terms of content storage which affects our design at a later point in time.

Here is the simple yet very powerful stsadm command to create a site collection with a separate content database:

stsadm -o createsiteinnewdb -url http://webspinners/dept/hr -owneremail SharePoint@webspinners.com -ownerlogin Webspinners\SPAdmin -sitetemplate sts -title “Human Resources” -databaseserver "SERVER5" -databasename “HR Content Database”

In this example, a new site collection is created at the URL http://webspinners/dept/hr.
The Site Collection Owner is set to Webspinners\SPAdmin and the Site Collection owner email is set to SharePoint@webspinners.com.
The default team site template is used to create the site (the template is simply named ‘sts’, without the quotes.)
The database server is SERVER5 and the name of the database created is HR Content Database.
There are some reasons, why you could have a different content databases per site collections:

1. MS recommends to have content database not more the 100Gb for MOSS 2007 and 200GB for SP 2010.

2. It simplifies your backup and restore operation

3. It provides flexibility for Disaster Recovery (DR) strategy.

4. Modularity and Flexibility of data storage eliminates maintenance nightmares.

Possible Scenarios:
a. Media management for a site collection that stores heavy audio/video media files like .avi, .mpeg, .wav, .mp3

b. Knowledge Management requirement where you might think of implementing a separate KM site collection for managing thousands of word, excel, powerpoint, visio, pdf artifacts etc.

These scenarios need you to have a separate Content Database for your site collections, otherwise the overall application performance would degrade, searching these documents will be affected, backup and recovery will be a nightmare and moving these site collections into separate SP farms will be trouble some.

If you have already made the mistake of not having a separate content database store for your Site Collection, and want to move your Site Collection to a separate Content database in MOSS 2007, please follow these links :
http://msmvps.com/blogs/laflour/archive/2008/10/14/tips-to-create-a-site-collection-in-new-content-database.aspx
 and
http://blogs.msdn.com/b/mcsnoiwb/archive/2007/08/20/how-to-create-site-collection-in-a-specific-content-database.aspx

For moving site collection to a new Content DB in SharePoint 2010,  follow directions given by Microsoft : http://technet.microsoft.com/en-us/library/cc825328.aspx