Monitor Your Database Servers with DTS, Part 1: Backup SLA Report

How to Add/Remove a New Server to the Script?

There are two steps involved in adding a server to the exception report:

1. Adding the server to the DTS package.
2. Adding the server to the tb_bkp_schedule table.

Adding the Server to the DTS Package.

1. Connect to the server SQLSRVCNTREP using Enterprise manager.
2. Go to “Data Transformation services” > Local packages.

   

3. Double click on Backup_exception_report.
4. Create a new connection in the DTS package by clicking on the link for “Microsoft OLE DB provider for SQL Server.”

   

5. Fill in the connection details in the pop-up window for “Microsoft OLE DB provider for SQL Server.” In the example, I am adding msdbs05 to the SLA report.

   

6. Click OK.
7. Create a DTS task that will bring the data from the new server to SQLSRVCNTREP. Click on the DTS task icon. The mouse pointer shows that you need to select the source. Once the source is selected, click on the target. It then sets up a task arrow going from source to target.

   

8. Double click on the task arrow.

   

9. Change the description to the server name.
10. Change the source to SQL query and put the query in the query text window. The query is given below:

    select Convert(Varchar(25),server_name) as ‘Server Name’ ,convert(varchar(25),database_name) as ‘Database’ ,getdate() as reportdate, max(backup_finish_date) as ‘Last_backup_date’ from msdb..backupset Where database_name in (select name from master..sysdatabases) and server_name = @@servername group by server_name,database_name order by Last_backup_date desc

11. Go to the Destination tab.
12. Change the table to dbamaint.dbo.tb_bkp_history.
13. Go to the Transformations tab and confirm that the source field and target field have the same name.

    

14. Click OK.
15. Select Package > Save to save the package.
16. To test the change, connect to SQLSRVCNTREP in query analyzer and change the database to dbamaint. Run the following query:

    select * from tb_bkp_history where server_name=’msdbs05′

    You would typically get no rows in the result.

17. Select the newly created task. Right click on the task and select execute task.
18. Run the select query (from 16 above) again. The record should now be there for that server. If not, take a backup of a small database on the server and repeat steps 17 and 18.

Adding the Server to the tb_bkp_schedule Table.

Find out the schedule for when the backups will be run for the server. Populate the fields using a query similar to the one given below:

insert into tb_bkp_schedule values (‘MSDBS05′,’DAILY’,’Y’,’Y’)

You can follow the same procedure to add all the SQL Servers in your environment to the backup SLA report.

The next article in the series will explain how to check and keep track of any errors in SQL Server errorlog on all the servers in the enterprise.

]]>