5 Best Practices for CMS Database Adminstration

At Diagram, we work with and host many CMS based sites, and a large number of these CMS sites use Microsoft SQL Server databases. Over the years, I have fielded many questions from clients related to CMS SQL Server database best practices. Below are 5 best practices I recommend to all our clients:

Use a Dedicated Site Account

Set up and use a dedicated Windows or SQL Server account for each website to access its database(s). Make sure this account has only the rights required to run the site and nothing more. Never use a system administrator or SQL Server SA level account to run your site unless told to do so by your CMS provider. Consult your CMS provider’s setup manual or support team for details on the rights required for your CMS.

By using a dedicated account to run the site and following the recommendations of your CMS provider, you are lowering the likelihood of your site being compromised and limiting your exposure if it is.

Pre-Size the Database Data File

Look at the amount of data that will be in your CMS’s database and set the data file(s) (MDF and NDF file types) to the correct size before adding or importing data into the site. The correct size of the data file(s) can be found by looking at the databases of other sites running the same CMS, setting up a test database and importing similar data to what will be in the production site, or by looking at the site’s authoring or staging environment database.

By pre-sizing the site’s database, you will reduce the amount of fragmentation in the data files, which will improve performance over time.

Enable Autogrowth Settings

Autogrowth should be enabled on most CMS databases. This is done by default in most SQL Server instances, but the default data file growth setting of 1MB at a time is only correct for very small databases (under 20MB). To find the correct file growth settings, you should look at the size the data file(s) will be once the site is in production, as well as the type of storage you will be using for the data file(s). The larger the autogrowth increment, the less data fragmentation you will have, but because the site will not be able write data to the database during the data file autogrowth process, you want to make sure the increments are not so large that they will impact site performance.

By using the correct autogrowth setting for your site, you will reduce the amount of fragmentation in the data files, which will improve performance over time.

Address Index Fragmentation

Monitor the indexes in your database and address index fragmentation when it is found. This can be done by using T-SQL scripts or by using index management tools from companies such as Idera or Red Gate.

By finding and addressing index fragmentation, you will be improving the performance of your site’s database just when you need it most, when finding and retrieving data.

Set Up Reporting Services

Install and use SQL Server Reporting Services to monitor your SQL Server instances and databases. Installing SQL Server Reporting Services gives you access to standard reports such as SQL Server Dashboard, Activity reports, and Performance reports. You can also write your own custom reports and import them into any SQL Server instance running SQL Server Reporting Services.

SQL Server Reporting Services Dashboard View

By installing and using SQL Server Reporting Services, you have a simple but powerful tool that allows you to monitor your SQL Server instance to quickly find trouble spots without having to write your own tools or install third party software.

You can find more information on the above items as well as many other SQL Server related topics at MSSQLTips. Do you need more information about CMS hosting or Microsoft SQL Server databases? Please feel free to leave a comment below, or contact us to speak to a hosting expert.