SQL Server Maintenance for the Full-Stack Engineer

Don’t Panic Labs is an office filled with full-stack developers. We cover all aspects of software — from JavaScript in a browser, to core business rules in C#, to performant queries and table structures in SQL Server. Full-stack developers have a vast knowledge of all technologies required to create a working system, but do not always have the time to become an expert in a single area of that system.

A majority of companies have grown far past their MVP product and have large, diversified teams of front-end web developers, software engineers, and database administrators. This is generally not the case at Don’t Panic Labs, where our engineers fill all of the mentioned positions. This means we take advantage of the many tools available to us to speed up product development.

Most all of the products that we work with use Microsoft SQL Server as persistent storage. Hosted database solutions such as SQL Azure and Amazon RDS include backup plans that are easy to use and maintain. However, a product may not live in the cloud for the first version, or a product may contain certain private information that cannot yet be hosted in the cloud.

Thankfully, Microsoft SQL Server includes the concept of ‘Maintenance Plans’. These plans make it easy to schedule maintenance for a database without requiring us to write complex custom scripts or obtain some type of Microsoft SQL Server certification to schedule the ever-important backup. Perfect for the fast moving products that we are working on!

Creating a Maintenance Plan

To create a maintenance plan, connect to the desired database using SQL Server Management Studio and expand Management, right-click Maintenance Plans, and click Maintenance Plan Wizard.

Next, enter a Name for the plan and click Change to define a schedule in which the plan should execute.

Once the basic settings have been chosen for the plan, click Next and a list of maintenance items displays. For this plan, we’ll choose Rebuild Index, Back Up Database (Full), and Maintenance Cleanup Task. With these options, our plan will rebuild all indexes in the desired database(s) to ensure the best performance with index seeks and scans, backup our database(s) to disk, and clean up any old and outdated backups that this maintenance plan has created.

After the necessary tasks are selected, click Next and the wizard configures all of the selected maintenance tasks. After the wizard is complete, this maintenance plan is listed under Management > Maintenance Plans on the database server.

Modifying a Plan

SQL Server Management Studio also offers a cool drag-and-drop editor to work with the maintenance plan workflow. To access this editor, right-click on the maintenance plan in the list and click Modify. The chosen plan displays with a workflow diagram.

Executing a Plan

We have scheduled our plan to run every night at midnight. But, you are not alone if you wish to test your maintenance plan right after it is created. To run a maintenance plan manually, simply right-click the desired maintenance plan and click Execute. The plan will begin almost immediately, regardless of the maintenance plan’s schedule.

The image below is the result of running our maintenance plan. I have opened the configured destination folder for the Back Up Database (Full) task. You can see that a .bak file is created with a generated name for our ‘SuperSecretDPLProject’ database. The Maintenance Cleanup task that we chose will ensure that the directory below only contains backup files from within the past four weeks. Anything older will be deleted.

Other Maintenance Plan Tasks

As you have probably noticed, we chose very few tasks for our maintenance plan. It is likely that an MVP product’s database may stay relatively small and not require much maintenance. However, in a larger scale application, you may want to take advantage of the other tasks available.

For example, we chose to use the Back Up Database (Full) task. While this is easiest to do in early stages of a product, it will inevitably become far less efficient if database grows to several gigabytes in size. At that point, a combination of fewer full backups combined with more frequent Back Up Database (Differential) and Back Up Database (Transaction Log) tasks will mean only changes since the last full backup will be included, resulting in a more efficient backup strategy.

For indexes, the Reorganize Index task can be a more preferred option, as it is less resource-intensive than the Rebuild Index task. In our MVP use case, we may take into consideration the usage of our app during the late hours, which has little or no traffic. In this case, we are not concerned with resources used since it has been scheduled for late night.

There are other tasks you can choose to include. Since this product is still MVP, some of the tasks may not be applicable yet – such as Check Database Integrity. The Shrink Database task will shrink the database, but the ordering of this task may be important^[1]. The Update Statistics task will allow Microsoft SQL Server to have a better idea of the distribution of data within a database, which can lead to better query plans. However, this may also have an adverse effect on query performance so we would not enable this until proven necessary.

Final Thoughts

I was excited to find this functionality, albeit long after its availability. Simple tools like maintenance plans for Microsoft SQL Server allow us here at Don’t Panic Labs to focus our time on building products, and less on operational maintenance. Any developer should be thrilled about that!

^[1]Shrink Database Task (Maintenance Plan) – https://msdn.microsoft.com/en-us/ms188432.aspx