5 Best Practices for CMS Database Adminstration
Dan Sales#Hosting

These recommended best practices can help ensure the stability, security, and performance of your CMS database.
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.
Have questions or comments about this post? We'd love to hear from you.
Related Posts

The Episerver Optimizely Rebrand: What It Means For Your Business
Episerver has officially announced its rebrand to Optimizely. Here’s why—and how your business can benefit.

INBOUND 2021 Recap
Diagram's Kristen Sydelko gives her unique perspective as a first-time attendee to Hubspot's annual conference, INBOUND 21.
Results Matter.
We design creative digital solutions that grow your business, strengthen your brand and engage your audience. Our team blends creativity with insights, analytics and technology to deliver beauty, function, accessibility and most of all, ROI. Do you have a project you want to discuss?
Like what you read?
Subscribe to our blog "Diagram Views" for the latest trends in web design, inbound marketing and mobile strategy.