Monitoring Backups Across Instances

Monitoring Backups Across Instances
3.7 (73.33%) 9 votes

Introduction

In the last two or three months, I have been asked twice for a solution native to SQL Server that consolidates a backup report for several SQL Server instances across an enterprise. This question came from friends that did not necessarily want to spend money buying a tool but were more inclined to leverage the capabilities of SQL Server. I have thought about two possible ways to achieve this:

  1. Using Linked Servers, catalog views, SQL Agent Jobs and Database Mail
  2. Using Central Management Server

In this article, I will demonstrate the first and hope we shall have a second part of the article sometime later.

Scenario

My environment consists of a set of three instances sitting on separate servers on AWS. These “servers” are actually Amazon EC2s running SQL Server 2017 RTM CU5. We are also going to be taking advantage of Amazon Simple Email Service to configure Database Mail. In production, you can definitely use your on-premise Email Servers and achieve the same goals. You will notice later in this article that the hostname (and thus the instance names) are the same. This is because the servers were cloned from the same Amazon Machine Image (pardon the “laziness”). This will not likely be the case in production.

Take a Few Backups

Let’s start by taking a few backups of databases sitting on these three instances. This will generate the data we shall work with. We shall then verify the backups are captured in the system tables msdb.dbo.backupset and msdb.dbo.backupmediafamily. The full descriptions of these tables can be reviewed on this Microsoft Documentation or simply be using sp_columns.

Fig 3. Describing msdb.dbo.backupset
Fig 3. Describing msdb.dbo.backupset

Checking Backups

The following script takes advantage of two catalog views backupset and backupmediafamily to examine the history of backups created on an instance of SQL Server. The backupset catalog contains a row for each backup set. A backup set is defined as the content of a backup operation that is added to a media set. A media set is an order collection of media to which one or more backup operations have written.

Fig 5. Sample Output of Backup Checks
Fig 5. Sample Output of Backup Checks

Checking Backups on Other Instances

Using Linked Servers, we can extract data from remote instances. In this case, we shall use a simple linked server to extract backup history information from the msdb databases of two remote instances. The security configuration for these Linked Servers totally depends on you but we have kept is very simple here for the purpose of our objective. Listing 3 shows the script that can utilize these Linked servers to aggregate backup history data.

Fig 6. A Simple Linked Server
Fig 6. A Simple Linked Server

 

Fig 7. Linked Server for Two Remote Instances
Fig 7. Linked Server for Two Remote Instances

Incorporating SES and Database Mail

The next step we take is to automate this check and mail the result set to Database Administrators. The steps required would be as follows in summary:

    1. Configure Amazon SES. You can learn how to quickly set up email on AWS using the documentation provided at Amazon SES Quick Start. When using on-premise email service, this will not be necessary for the DBA.
    2. Configure Database Mail. This article is not intended to demonstration Database Mail so we just give a screenshot of the SQL mail account configuration:
      Fig 7. SQL Mail Account Settings
      Fig 7. SQL Mail Account Settings
      • The port number when using SES to send emails is 587 NOT 25
      • Amazon SES requires a secure connection thus the checkbox identified in lilac (Fig. 7) must be selected
      • Basic authentication using the SMTP credentials is required (i.e. anonymous authentication is not allowed).

      We just need to be aware of a few things when using Amazon SES for Database Mail:

    3. Configure SQL Agent to use the Mail Profile. SQL Server Agent must be configured to use the mail profile created during the Database Mail configuration for the agent jobs to fire emails. (See Fig. 8)
    4. Create a staging table. A staging table will contain the aggregated result set for all backup history data from the instances we have targeted using Linked Servers. The table DDL is shown in Listing 4.

Fig 8. SQL Agent Settings
Fig 8. SQL Agent Settings

We go ahead and schedule the script in listing 3 in an SQL Agent Job and we have the complete script in Listing 5.

Executing this job results in the output shown in Fig 9. The table is created using very simple HTML and can be further developed to suit your needs.

Fig 9. Email Output of SQL Agent Job Execution
Fig 9. Email Output of SQL Agent Job Execution

Conclusion

We have gone through a simple method of aggregating backup history information (and possible any other data contained in system databases) using linked servers. We further went on to automate this process using SQL Agent, Database Mail, and a little HTML. This method may seem a little crude and I am sure there are tools out there which can do much better, but this would server purpose for those just starting out with SQL Server or environments on a low budget. With a little bit of creativity, you can further customize the scripts and adapt the scripts to other uses.

References

  1. Configuring Database Mail
  2. Getting Started with Amazon SES
  3. Linked Servers
  4. Backup History and Header Information
Kenneth Igiri

Kenneth Igiri

Kenneth Igiri is a Database Administrator with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over eight years' experience with SQL Server and Oracle databases as well as related technologies. His interests include database performance, HADR, and recently, Cloud.
Also, Kenneth teaches at Children's Church and writes fiction. You can connect with Kenneth via his blog or social network account.
Kenneth Igiri

Latest posts by Kenneth Igiri (see all)