Create a SharePoint content database in SQL Server

There are several ways of creating a SharePoint content database. First you can create a content database via the SharePoint Central Administration console. However this approach disregards any settings the DBA may wish to employ based on the SQL Server model database. Even if the “Model” system database is configured in SQL Server, the content database will not pick up the autogrowth settings. So the preferred approach for IT Pros is for the DBA to first create the content databases in SQL Server itself. The System Administrator can then create web apps and site collections that reference these databases. Taking this approach, what options are available?

1)      Use PowerShell scripts to create the content databases.

2)      Create the databases in SQL Server directly.

For Option 2, I recommend you watch the following video that explains step-by-step how to do this – see “Tuning SQL Server 2012 for SharePoint 2013 – Best Practices for SQL Server Database Settings” at http://channel9.msdn.com/Series/Tuning-SQL-Server-2012-for-SharePoint-2013/Tuning-SQL-Server-2012-for-SharePoint-2013-02-Best-Practices-for-SQL-Server-Database-Settings#time=20m24s

Things to bear in mind:

1)      Set the Model system database with your default Initial database and log Initial Size and Autogrowth configuration settings:

2)      Ensure the Model system database “Options” Recovery model is set to “Full” on the Production Server:

3)      Set the correct Collation, so that SharePoint web apps and site collection can pair up with the content databases.

4)      Now the SharePoint System Administrator can start creating site collections in Central Admin or PowerShell and pair with the newly created content databases.

Note: Ensure that if content databases are taken “Offline” (to avoid site collection pairing) that they are brought back to “Ready” when done; otherwise search crawls etc. will exclude the content databases. You can also set the maximum number of site collections on a content database to ensure that a new site collection does not pair with the content database.

The PowerShell approach is explained further in the following references:

Reference Link
SQL Server PowerShell Provider http://msdn.microsoft.com/en-us/library/cc281947.aspx
Add content databases in SharePoint 2013 http://technet.microsoft.com/en-us/library/cc825314.aspx
SQL Server Community (Toad World) http://www.toadworld.com/platforms/sql-server/default.aspx