Monitor Your Database Servers with DTS, Part 1: Backup SLA Report
It is difficult to keep track of critical things like backup, space, and error logs in large environments. Different database management tools from third party vendors can help you monitor these but the tools are normally expensive. There is an inexpensive alternative. In this series of three articles, I will describe how to monitor a large number of servers from a centralized location using Data Transformation Services (DTS). DTS is a wonderful tool that comes free with all SQL Server installations. DTS provides an easy way to bring the information from many Microsoft SQL Servers and other RDBMS to a central place and run queries against that data. I will cover monitoring of three elements of SQL servers:
- Backup SLA (Service Level Agreement) Report — to find any database that has not been backed up in the last 24 hours.
- Errorlog check — to report errors in SQL Server errorlog for any of the servers in an enterprise.
- Space monitoring — to report databases that have less that 10 percent free space.
Backup SLA Report
You will agree that backups are the most important responsibility of production support DBAs. The first and foremost thing to do is to have a solid backup policy. The backup policy should ensure the recoverability of your databases according to your business needs. In this article I will explain the setup of a report that ensures that backups are happening as per the backup policy. The report will list all the backups that have not happened as per their predefined frequency. This report can keep you updated at enterprise level on whether you have met the SLA for backups or not.
These are the three parts of the backup SLA reporting process:
- Populate the data using a DTS package
- Analyze the fetched data to find the backups which have not run as per the SLA
- Send an email report.
SQL Server keeps a history of all the database backups in the table “backupset” of the system database (msdb). The backup frequency (SLA) for each server can be stored in a table, which in this case is “tb_bkp_schedule.” The report uses the DTS package to bring the backup history from all SQL Servers in the enterprise to a single repository. You can then run queries to check your backup SLA compliance. In this case the central repository server is “SQLSRVCNTREP” and the database with the central repository is “dbamaint.” (Please change your script to replace the server names and database names according to where you keep your central repository.) SQL Server 6.5 stores the backup information in a different table than later versions. The query extracting the data from different versions can be a bit different. While writing the query, keep in mind the collation of SQL Server.
Introduction to Various Components of the Reporting Process/Job
1. Bkp_sla_main.bat: This is the main script running the exception report. The script can be scheduled in any scheduler. If there is no enterprise level scheduling software being used, you can also schedule it in NT Scheduler. (View the text of the scripts.)
2. Table “tb_bkp_schedule”: The table contains the list of all the servers and their backup schedules. This table is what defines the SLA for backups. Create this table in your central repository database. (View the create table script.)
3. Table “tb_bkp_history”: The table contains the history of the backups for all the servers. This server is populated by the DTS package. Create this table in your central repository database. (View the create table script.)
4. Before_bkp_sla.sql: This SQL cleans up the data from previous runs on the same day. The SQL deletes all the data from “tb_bkp_history” for the current date.
5. Bkp_sla_report.sql: The SQL checks tb_bkp_history, compares the data with its backup schedule, and reports the backups that did not run as per their predefined schedule.
6. DTS Package “Bkp_exception_report”: The DTS package connects to all the servers. It brings the data to the central repository and stores it in the table “tb_bkp_history.” You need to create a package with the name Bkp_exception_report. These are the steps to create the DTS package:
- Connect to the server SQLSRVCNTREP using Enterprise manager.
- Go to “Data Transformation services” > Local packages.
- Create a new DTS package with the name Bkp_exception_report.
- Add the connection to your Central repository server (in this case SQLSRVCNTREP).
- Click OK. Use this connection as the destination for all your future DTS tasks being created while adding a server.