Transcript

SQL Server Backup Monitoring

One question I get asked by a lot of DBAs (database administrators) is, “How can SQL Diagnostic Manager help me monitor my backups?” Alright, so there are a few different ways to do that. The easiest way really is to go to the databases tab for a particular instance and see at a glance which databases you may be backing up more regularly than others and which backups maybe you are not ever backing up.

SQL Server Alerts

So one thing you can do to configure alerts for this particular instance. We are going to configure an alert called ‘days since the last backup’ which happens to be at the very top. Now our default threshold is ten and thirty days. You can see I have modified that and you can modify that however you want to.

SQL Server Backup Thresholds

What you can also do is configure backup thresholds for the particular databases, as I have done here with my master database. So if I go to edit that, I am just generating a critical alert if I have not backed up my master database in one day. You can make these as granular or as general as you want to. I would recommend adjusting this default threshold to at least a more reasonable value that satisfies your service level agreements with your organization.

What we can also do is apply that threshold to a template. So if you have templates for different environments, like tests and QA [quality assurance], or different levels of backups that you need to abide by, then you can set that for those alert templates. You can also configure that for a tag of instances as well or just a number of different instances that you select.

SQL Server Backup Management

Another thing you can do is, if a backup has not been run on a particular database or a group of databases on any particular instances, you can create an alert action response to run a job that performs those backups.

So I have many examples I have created here. I will create one from scratch here we will just name it Backup Rerun. And then you have some conditions to select here in Step One. I am just gonna make this simple and add where the SQL Server instance is in a specified list. I am just gonna select my SQL Server 2012 named instance. You can, of course, select more than one instance or select a tag of instances as well. And then I am going to select where the metric is in a specified list, so I am going to select my days since the last backup metric, and that is right here. And I am going to change my severity levels to warning or informational. I am just going to uncheck informational here and make this actually just run with a warning or critical.

If I click OK, now I have some actions to select, and I am just going to run a SQL Server agent job. So I am going to select my 2012 named instance. I am going to browse from my job list, and I have some SQL Safe Backup jobs here.

SQL Safe Backup is IDERA’s enterprise SQL Server backup and recovery solution, so if you want more information on that you can go to IDERA.com, and you can even download a fully functional trial that lasts 14 days.

I am just going to choose to run my full backup. Choose a step to start, and I only have one step. It just runs the backup job here. So click OK, and now I am done.

To review my SQL Server 2012 named instance, if my day since the last backup alert goes into a warning or a critical state, I am going to run my SQL Safe Backup full backup job on my SQL Server 2012 named instance. It is that easy to really create an alert action response in the case that databases are not being backed up on a regular basis or that particular databases are not being backed up.

SQL Server Configuration

Another thing you can do is run a prescriptive analysis. What is a prescriptive analysis? Prescriptive analysis allows SQL Diagnostic Manager to create recommendations based on things that may have been overlooked.

For example, if we right-click on a particular instance, my SQL Server 2012 named instance, all of these items will be unchecked out of the box when you install it. So you will need to choose if you want to schedule this to run and you will definitely need to select at least one category. And the one category that pertains to backups and whether your recovery models are set differently than they should be is in the Disaster Recovery.

SQL Server Disaster Recovery

If we go to advanced settings, I just want to highlight some of those recommendations that are related to disaster recovery. So here is one where the backups should be on separate volumes from your data and log files. I do that just because it is a demo environment and I like to trigger these recommendations, so that is definitely a big no-no in a production environment. You will want to have a separate backup, or just a separate backup file server established for that.

Databases use these simple recovery models, so you lose that point-in-time recovery capability that you would get with a full recovery model. If these databases are in a production environment, you will definitely want to consider changing that to a full recovery model.

Outdated SQL Server Backups

Outdated backups are a huge issue as well, and that relates back to our day since the last backup alert configuration. Also if the recent backup has been moved or deleted this could have implications for disaster recovery processes.
Also for testing backups, if you have automated restore set up for your different environments, test dev, staging, development, or whatever that may be, that may have some implications on development because they are not getting a refresh of production data.

SQL Server Transaction Log Backups

Again, pointing to point-in-time recovery, if you are not backing up your transaction log in a reasonable amount of time, you are going to experience data loss. If we look at my environment here under the Analyze tab, here is where you will see the results of that tab. So if we go up to the top here, I have some backups on the same volume as my data files here. If we scroll here to the bottom, it is going to give me some lower-priority items that I definitely need to look at, those having to do with some of my databases being in a simple recovery model, and having outdated backups here.

What is nice about prescriptive analysis is that it will give you a detailed explanation of the issue here in altitude backups. You know, that could hinder disaster recovery processes. And it gives you a recommendation on what to do to ensure that your backups are up-to-date and added to your backup jobs or whatever backup process that you follow.

Another thing is if you are in full recovery mode and you have not done a T log backup in a certain number of days, SQL Diagnostic Manager gives me a recommendation on why I should back up my T [transaction] log. Obviously, for a point-in-time recovery so that you minimize your data loss. And it also will give you an article on either the Microsoft knowledge base or IDERA Wiki site to give you a more detailed explanation of that as well.

Alright so hopefully this session has been useful for you, and I appreciate your time. Have a great day.