Pre-creating content databases or not?

Planning content databases is one thing to seriously care about when using SharePoint. Topics such as sizing, number of site collections, backup and restore are a few examples. Creating content database using Central Administration is easy. But there is more then meets the eye.

Manage Content Databases

With Central Administration you manage your content databases per web application. You can add and delete content databases, take a database offline, set maximum number of site collections.

I would like to focus on adding a content database. You provide the SQL Server and a name for the content database, but what happens when you click that OK button? Well, SharePoint tells that SQL Server to create a database with the name provided by you. While creating the database, some SQL Server and database settings are used. And some are not used. Now, you could ask yourself: am I happy with this? Or would I like to have more control? Think about initial size of the database, think about file locations and so on.

Default database locations

Let’s take a look at some settings. We have server settings and database settings. Log in with SQL Server Management Studio and right click the SQL Server instance you connected with and select Properties. With the server settings you can set the default location of your database (mdf), log (ldf) and backup (bak) files.

In the screenshot you see all locations are pointing to the C drive, which generally is your system drive. This is not a good idea to leave it this way for your acceptance and production environment. The system drive also contains the page file for Windows and you can improve performance by moving all your database and log files to external, faster drives. For example:

Why different locations and drives? Because you do not want to lose everything when a drive dies. You want to be back online as quick as possible. Therefore you must think about your disaster recovery (DR) plan more than 1 second. Also, using different drives can give you a performance boost.

Before you click that OK button, do make sure that the locations exist. And you must restart the SQL Server instance to take the changes in effect.

Database settings

Let’s take a look at some database settings. SQL Server uses the model system database as a template for creating database. Let’s check this model database settings:

Right click the model database and select Properties. In the database properties window go to the page Files:

Take a good look at the Initial Size column. For the data file (mdf) the initial size of the database is 3Mb. You create SharePoint content databases to store more than 3 Mb don’t you? To have better performance you should increase that value. Depending on the kind of content you expect in you SharePoint content database you can increase that value to perhaps 100Mb or even 1000Mb. Remember this is the default setting for all new database. Each database has this property as well and you can change it for that created content database if needed.

The same goes for the log file (ldf). Initially it is set to 1Mb. You should increase that as well. Especially when you use full recovery mode (and I hope you do so in production!).

The next column is Autogrowth. What if more space is needed then available? This setting automatically increases your database with the given value. Now this seems wonderful, but it comes with a price. When SQL Server needs to grow a database, you’ll experience that performance decreases. So, be careful what setting you enter. You do not want to grow too many times. Growing every time with just 1Mb is just not a good idea. You should know to some extend what the content growth could be over time. Is it 10Mb per day or 10Gb a week? This knowledge determines both the Initial Size and Autogrowth settings.

Let’s do a test. These are my settings for the model database:

I start Central Administration and I add a content database to my web application.

After adding the content database WSS_Content_IntranetDemo2 switch back to SQL Server Management Studio and open the properties page of this database.

Do you see it too? The Initial Size settings are applied, but the Autogrowth setting is not! It is still that 1Mb. So you have to adjust your database settings in SQL Server anyway!

Another great thing to do with SQL Server with your database that you cannot do with Central Administration is creating multiple data files for your database. Storing each file on different disks you can gain more performance.

Click on the Add button to add additional files for your database:

Enter a logical name, the location and the physical file name:

Notice that the values for Initial Size and Autogrowth are correct now according to the model database.

Pre-creating the database

By now you have a pretty good idea that pre-creating databases offers you better control and performance. But before you create your databases with SQL Server there is one very important thing to know. I am talking about the Collation. SharePoint uses Latin1_General_CI_AS_KS_WS and that is the only value it does support. If you try to create a database with a different Collation, SharePoint throws an exception message when adding an existing database:

Preferably, you should install your SQL Server (instance) with this Collation, so every database uses this default Collation value. If you failed to do so, you must choose the correct Collation value each time you create a database:

Summary

Pre-creating your content databases in SQL Server gives you more options, control and performance. All model database settings are set correctly for the new database, such as Autogrowth. Setting different locations for your database files can be set to gain better performance and you also have the option to set multiple data files to spread out over several disks.

More information about SQL Server. databases and SharePoint :

http://technet.microsoft.com/en-us/library/cc262869(v=office.14).aspx

https://www.microsoftvirtualacademy.com/training-courses/tuning-sql-server-2012-for-sharepoint-2013-jump-start