Creating an SQL Server Maintenance Plan

The SQL Server Maintenance Plan Wizard allows database administrator to automate many database administration and maintenance tasks without any knowledge of Transcript-SQL.

This article will discuss creating a detailed database maintenance plan for a small ecommerce website. The article targets an audience of IT system administrators with no specialist database administration (DBA) skills but with good generalist IT, server and web service knowledge. The goal is to design, create and schedule a database management plan to suit the business requirements.

Want to be a DBA? Take a course at Udemy.com to get started

The Scenario

A small-medium business (SMB) runs an ecommerce website that sells mobile phones and accessories. The website has been running for one year and business is growing steadily. The sale’s revenue is primarily for mobile phone top-up, where by the customer buys a value between $10 and $500 and the value is credited to their pay-as-you-go account SIM card. The mobile top-up is done over the air with a notification and receipt issued via SMS. An additional feature of the website is a loyalty bonus scheme where when customers buy a mobile phone or accessories, the company rewards them with loyalty points, which the customer may redeem for a mobile top-up.

The ecommerce application connects to an on-premise database server running SQL Server. A single IT administrator maintains and supports the DB server along with the exchange and application servers. The administrator does not have DB or T-SQL skills and he backs up the entire DB each night, which is his approach to IT backups. Furthermore, the administrator has little to do with the operation of the website, except from an infrastructure stance. He is not involved with the operations of the ecommerce site.

New to database administration? You can take a course at Udemy.com to learn more.

Current Database Maintenance Plan

The administrator has scheduled (via the SQL Server Database Maintenance Wizard) a database integrity test, followed by a full backup each evening. The backup is verified with the report generated and stored in a directory.

The Problem

The administrator becomes aware of the mobile top-up sales revenue success. He is now aware that his current backup schedule is not practical.

The New Maintenance Plan

Convinced that another approach to data backup is required, the administrator consults the SQL Server Maintenance Plan Wizard. What he discovers is the list of tasks that he can automate and schedule without having to learn any T-SQL or customize script.

After consulting the wizard, the administrator feels that keeping the daily backup would be fine if he adds a schedule to perform hourly differential backups.

Looking at the other tasks, he sees several other options and they have different scheduling for each task. However, what are all these options and what do they all mean?

Check Database Integrity – checks the logical and physical integrity of all objects in the database

1. Shrink Database – shrinks the files and the logs in the specified database on a file-by-file basis. It pools log files.  Physically shrinking makes the file smaller and truncates a file in the backup.
2. Reorganize Index – uses minimal system resources and defragments indexes of tables and views by reordering the pages to match the logical order
3. Rebuild Index – drops and recreates the index reclaiming space and reorders the index rows in contiguous pages
4. Update Statistics – updating statistics ensures queries run with up-to-date statistics, but they also require queries to be recompile, can result in a performance trade-off
5. Clean Up History – deletes entries in the history tables
6. Execute SQL Server agent job – Windows service that runs scheduled jobs
7. Backup Database (Full) – Backs up the entire database, but as databases grow, the backup takes longer and requires more storage. The best solution is to use in conjunction with differential backups.
8. Backup Database (Differential) – used to back up only the data that has changed since the last full backup
9. Backup Database (Transaction Log) – used to back up individual transaction logs, which can be individually restored since the last full backup
10. Maintenance clean up task – performs housekeeping functions

The new Revised Maintenance Plan

After considering the options within the SQL Server Maintenance Plan Wizard, the administrator has decided on a database maintenance plan:

The administrator completed the Database Maintenance Plan by researching the options available and discovered that the transaction logs should be used in conjunction with the last full backup as the base. Taking differential backups would only add to the number of restores required. Taking this into account, he decided to set the last full backup as being every 6 hours. He then could set transaction logs to backup every thirty minutes.

The other options he chose to set were the “Check Integrity and Reorganize Indexes” as daily tasks along with the other database maintenance and housekeeping tasks.

He decided not to use the “shrink database” option but chose to check the log file sizes by configuring an agent task to run the command;

DBCC SQLPERF(‘LOGSPACE’) every hour to check the % of growth.

Similarly, the rebuild Indexes was not included in an auto schedule as rebuilding indexes would be run when required when fragmentation becomes an issue.

The result is that the administrator has a scheduled task for backing up and managing SQL Server logs without knowing one ounce of transact SQL code. The wizard that comes with SQL server can manage several maintenance tasks even for ecommerce website owners who recognize the need for backups but don’t have any time perform backups manually.

Understand more about SQL Server and maintenance plans at Udemy.com.