Managing Backups on Multiple Servers

Central Management Server

If you read up on this, it talks about setting up groups of servers, necessary if you’re managing QA differently than production. It also has scripting against multiple servers, another great thing. Finally it lets you evaluate policies against multiple servers. Cool. It sounds perfect. Let’s explore.

Setting up the Central Management Server

Creating a Central Management Server (CMS) is pretty easy, though it does have to be on SQL Server 2008 or better. You also can’t make the CMS server one of your managed servers. Other than that, just pick a machine and go to the Registered Servers window in SSMS by clicking “View -> Registered servers” in the menus. Right there in the registered server tree on the left is a folder called “Central Management Servers” (and yes, you can set up more than one, or set up Management Servers for your Management Server, or…). Right-click on that folder and select “Register Central Management Server” from the context menu; and then supply it with the credentials for the server you want to use. That’s now, automagically, your CMS. There are some entries in the MSDB for the stuff you’re configuring. Now you need to add Server Groups.

Right-click on the CMS that you’ve registered and then select “New Server Group…” from the context menu. I’ve created two groups locally, one for all my regular machines and one for my virtuals. You could break it down into Development and Production, or whatever you need. By grouping the servers, it gives you the capability to treat them differently, to run different scripts against them and evaluate different policies in different ways for these various groups of servers. It’s a big management step. Plus, you can still run scripts against all the servers at once.

With the Server Groups in place, you just have to add servers to the groups. Right-click on the Server Group where you want to add a server, and select “New Server Registration…” from the context menu. Type in the appropriate connection information and you’re good to go. Here’s my current set-up:

Working with Servers

Setup was nice ‘n’ easy, right? Now what can you do with it? Right now, it just looks like yet another list of servers that you have to manage, which is true, but, this list is available to other people. Anyone who connects up to the defined CMS will see the servers that have been assigned to it to manage. And, if they have the correct privileges on all the servers under management they can do what we’re about to do.

Central Policy Management

You’re using Policy Based Management (PBM), right? If not, I’d go work on setting that up now, as it’s a great piece of software. My favorite policies are actually backup-related (yes, I haven’t forgotten that the point of the article is managing backups: We’re getting there, calm down). When I set up backups through SQL Agent, I always put in methods that raise errors that will cause alerts to fire (either directly through SQL Agent or through third party monitoring solutions). But, I’ve noticed that all of them can fail under certain circumstances, so I’ve found that putting an additional check on top of the others is worthwhile doing. The additional check comes through PBM.

I’ve set up a simple check that will see whether the last backup was completed within the last 24 hours. It just validates that the daily backups I want done are being completed. I could script this out and run it against every server, or, I could just use CMS. Let’s assume I want to check my physical boxes, which are in the folder labeled ‘Red Court.’ If I right-click on that folder and select “Evaluate Policies” from the context menu, I get a new window that’s going to evaluate those policies that I decide to include against the servers in my list.

First, I have to select the policies that I want to evaluate, which means I can pick from files on my system, or I can pick from one of the servers that I manage. I chose the latter. Here’s the screen ready for the evaluation of my policy:

Now all I have to do is to click on the ‘Evaluate’ button and this policy will be run against all the servers that are currently under management, and bring back a full set of results. This will allow me to see how many, if any, of my databases have not been backed up within the last 24 hours. Let’s see what happens:

Oh my. Most of the databases on the server GRANT-RED1\GFR1 have been backed up within the last 24 hours, but it looks like none of the databases on BOB\SKULL have been. You can actually check the evaluation results for each database on each server. Here’s one set of results from BOB\SKULL:

Ouch. In short, backups have never taken place on any of the databases on that server. Someone needs to get backups scheduled there as soon as possible. I wonder if we could use the CMS to make that happen.

Central Script Execution

I need to run backups on all my servers and on all the databases on those servers. CMS offers a mechanism to make this happen. Again, you can select a server, a list of servers, or all servers registered under CMS. Right-click in the appropriate place, and then select “New Query.” This will open a query window that looks a lot like another query window. But, if you look at the information bar at the bottom of the window you can see that you are not connected to a server:

I’m connected up to my production server group, Red Court. Any query that I run there will run under the context listed, NEVERNEVER\Grant, against all the servers in my list. So if, for example, I wanted to run a backup against all the databases on all the servers on the list, I could do this:

DECLARE @BuScript NVARCHAR(MAX) ;
SET @BuScript = 'IF ''?'' <> ''tempdb''
BEGIN
BACKUP DATABASE ? TO DISK = ''\\myshare\documents\bu\' + @@SERVICENAME
    + '\?.BAK WITH INIT;''
END' ;
EXEC sp_MSforeachdb @BuScript ;

Executing the script, the results look like this:

The results are mixed because the query is running on both servers at the same time and the results come back as each backup process completes.

But, though it’s nice to quickly run these backups across multiple servers, I’m not going to sit down every day and run routine backups by hand. Here’s another option. If I have this script as a backup job that I want to run on all my servers, I could create a script that creates a SQL Agent job on each and every server like this:

IF NOT EXISTS ( SELECT  name
                FROM    msdb.dbo.syscategories
                WHERE   name = N'[Uncategorized (Local)]'
                        AND category_class = 1 )
    BEGIN
        EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB',
            @type = N'LOCAL', @name = N'[Uncategorized (Local)]'
        IF ( @@ERROR <> 0
             OR @ReturnCode <> 0
           )
            GOTO QuitWithRollback

    END

DECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'Backup All Databases',
    @enabled = 1, @notify_level_eventlog = 0, @notify_level_email = 0,
    @notify_level_netsend = 0, @notify_level_page = 0, @delete_level = 0,
    @description = N'No description available.',
    @category_name = N'[Uncategorized (Local)]',
    @owner_login_name = N'NEVERNEVER\grant', @job_id = @jobId OUTPUTIF ( @@ERROR <> 0
     OR @ReturnCode <> 0
   )
    GOTO QuitWithRollback/****** Object:  Step [Database Backup Script]    Script Date: 12/05/2011 08:36:44 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,
    @step_name = N'Database Backup Script', @step_id = 1,
    @cmdexec_success_code = 0, @on_success_action = 1, @on_success_step_id = 0,
    @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 0,
    @retry_interval = 0, @os_run_priority = 0, @subsystem = N'TSQL',
    @command = N'DECLARE @BuScript NVARCHAR(MAX) ;

SET @BuScript = ''IF ''''?'''' <> ''''tempdb''''
BEGIN
BACKUP DATABASE ? TO DISK = ''''\\192.168.1.80\documents\bu\'' + @@SERVICENAME
    + ''\?.BAK WITH INIT;''''
END'' ;

EXEC sp_MSforeachdb @BuScript ;', @database_name = N'master', @flags = 0IF ( @@ERROR <> 0
     OR @ReturnCode <> 0
   )
    GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF ( @@ERROR <> 0
     OR @ReturnCode <> 0
   )
    GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId,
    @name = N'Weekly', @enabled = 1, @freq_type = 8, @freq_interval = 1,
    @freq_subday_type = 1, @freq_subday_interval = 0,
    @freq_relative_interval = 0, @freq_recurrence_factor = 1,
    @active_start_date = 20111205, @active_end_date = 99991231,
    @active_start_time = 0, @active_end_time = 235959,
    @schedule_uid = N'a24f9a3a-1990-455f-9d4b-d1ab8437b7d5'IF ( @@ERROR <> 0
     OR @ReturnCode <> 0
   )
    GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId,
    @server_name = N'(local)'IF ( @@ERROR <> 0
     OR @ReturnCode <> 0
   )
    GOTO QuitWithRollbackCOMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:IF ( @@TRANCOUNT > 0 )
    ROLLBACK TRANSACTIONEndSave:

GO

With this, I can set up backups throughout my enterprise, all from a single location. That’s great news. If I make modifications to my backup script, I can transmit it to all the servers, all at once. I’ve just eliminated the need to connect up to each server to set up backups.

There’s one issue with this though. I can set up and maintain my scripts across multiple servers, but I can’t get the results of all those Agent executions back to the CMS server to so that I know whether or not they’ve executed successfully.

Automation

Unless you bring in some outside agent to work, such as PowerShell, you can’t automate this final reporting task. Sorry, but there it is. Here’s a great way to hook into CMS from PowerShell. With that, you could set up jobs through SQL Agent that then call out to PowerShell scripts that take advantage of the CMS lists. But other than that, there’s nothing built-in.

While I can manually do a lot against multiple servers through CMS, I can’t automatically do enough unless I start programming with other languages and tools. What I can’t do is set up a SQL Agent job that runs through CMS to manage my servers bringing the results back to a common location. Or… can I?

Multi-Server Management

This is totally different from CMS. Instead of using something that is sort of hidden with Management Studio, you’re taking advantage of additional functionality built into SQL Agent. The concept is that you create Agent jobs and provide them with targets against multiple servers. The results all come back to a common server so you can see what ran and didn’t. You can even set up alerts from this server so you know when a job failed or had problems. Let’s see how it works.

Setting Up Multi-Server Management

Conceptually, this is very simple. You just right-click on “SQL Server Agent” in SSMSs Object explorer, use “Multi-Server Management” and then “Make this a Master” from the context menu. It’ll open a wizard that lets you pick the target servers and you’re off. In practice… well, not so much.

I was unlucky. In order to get my setup to work, right out of the gate I had to mess about in the registry. However, once that was done it was pretty simple.

If you right-click on the SQL Agent icon in the Object Explorer window, you’’ll spot a context menu “Multi Server Management.” Selecting this opens another context menu that will allow you to “Make this a Master…” or “Make this a Target…” For the central server that’s going to manage all the others, you make it a Master server. This opens a wizard that starts off with you picking the servers you want to manage as Targets:

The beautiful thing is that it appears to work from the CMS. Well, it does and it doesn’t. You can see the CMS and you can use it to select servers but, once selected, they’re just managed within the Multi-Server Management interface. There are no longer any sets of folders or groups of servers that let you readily manage them as a set of servers. Instead, it’s just a way to quickly add lists of servers, which is not the same thing at all.

Anyway, the next step is to set up the security context. You have options here. You can set up a common login:

This is necessary if the SQL Agent on one or more of your servers is running with insufficient security settings to allow it access to the Master server. If it is, you can click this off:

Then you click on the “Next” button and it shows you a nice summary page of what you’re about to do:

Of course you’re going to click on the “Finish” button, and since this is a wizard, everything will work:

… or not. See, there’s another setting in the Registry that you have to change before the different agents will all communicate with each other. You can see it in the error for the Enlist statement. Fix this, and it all, finally, works.

Yeah, that was a pain.

Creating Jobs

This part of Multi-Server Management could not possibly be easier. You’re going to use SQL Agent. That means that all the stuff you’re used to with SQL Agent, Jobs, Schedules, Alerts, Operators, are in place. I won’t insult your intelligence by showing you how to set up an Agent Job. Just bear in mind that that the same types of “universal” coding practices you followed in the scripts that run from the CMS will apply here. Unless of course you have an X: drive on every server, then you’d be better off using UNC for your backup locations.

The only wrinkle is that you now have a Target for your job. Here’s what it looks like. The nice groupings that were available in CMS are not evident here:

Once you create a Job and set the execution Schedule, it will start running against both servers. It’s that easy. You can monitor the jobs, but not edit them, from any of the target servers. You can monitor all servers from the master server.

When you open your Agent on the Master server, you’ll see that your Jobs folder now has two sub-folders, one for Local and one for Multi-Server jobs. This is where you can manage and maintain the jobs. If you take a look at the history of the jobs, you can see every server that the job was run against:

Overall, it’s very functional. But the fact that you lose the CMS groupings that enabled you to define sets of servers certainly hurts it. While you can set up different jobs for different groups of servers, you have to go in and identify those servers yourself, manually. You can’t just pick a group and have all servers automatically get selected. That’s a shortcoming.