Multiserver management is one of the cooler things you can do with SQL Server.
The idea is to use SQL Server Agent to “push” a given job to one or more other SQL Server instances, running that job in parallel on all of them. It’s a great way to auto-mate the administration of multiple servers, helping to improve consistency and helping to save you time.
15.1 What is multiserver management?
Multiserver management is a feature of SQL Server Agent, which means every SQL Server instance that you want to participate needs to have its SQL Server Agent ser-vice running. You’ll designate one SQL Server instance as your master server, and enroll one or more other instances as targets. When you run multiserver jobs, Agent will coordinate their execution on each target, and even track job progress for you, all in one central location.
15.2 Creating a multiserver management hierarchy
You have to have a master instance in order to use multiserver management, so let’s start by creating one. Right-click SQL Server Agent in Object Explorer, select Multi Server Administration, and select Make this a Master…. Figure 15.1 shows where to find those options.
TRY IT NOW Follow along if you have two instances of SQL Server installed on your lab computer. If you don’t, consider installing a second instance now, then proceeding with the rest of this chapter.
138 CHAPTER 15 Multiserver management
You’ll run through a little wizard, which will start by prompting you for the email address of a “multiserver operator.” As you learned in the previous chapter, SQL Server Agent jobs can send notifications to operators; with multiserver jobs, only the operator you designate here can receive notifications. Whatever contact information you provide will be used to create an operator on the master and on any target instances that you later enroll. That way, they all have the same operator to send notifications to.
Figure 15.2 shows the next screen, where you can start enrolling targets. I’m set-ting up my SQL2 instance as the master instance, but my lab computer also has a default instance installed, and you can see that I’ve decided to enroll it as a target.
The next screen, shown in figure 15.3, may differ a bit depending on your server configuration. It’s asking you what authentication the target servers should use;
technically, targets pull (or download) jobs from the master, so the targets need a way to authenticate.
Figure 15.1 Make the instance a Master to get started.
Figure 15.2 Specify target instances for this master.
139 Creating a multiserver management hierarchy
You might run into an error, like the one shown in figure 15.4, which indicates that SQL Server’s default security configuration doesn’t allow the extended stored proce-dures needed to enable multiserver administration.
This is a common error, and usually indicates that SQL Server Agent has never been started on the target instance by using SQL Server Management Studio.
Figure 15.3 Tell the targets how to authenticate to the master.
Figure 15.4 This is a common error when enrolling a target.
140 CHAPTER 15 Multiserver management
NOTE You can read more about the Agent XPs, and the security implications of XPs in general, at http://technet.microsoft.com/en-us/library/ms178127.aspx.
You can fix the problem by using SQL Server Management Studio to connect to the instance in question, right-clicking SQL Server Agent in Object Explorer, and choos-ing Start. If the service is already started, stop and restart it by uschoos-ing SQL Server Management Studio. That’ll both start the SQL Server Agent service and enable the Agent XPs.
Another common error is shown in figure 15.5, and indicates that the multiserver administration components on the target weren’t able to establish a secure connec-tion to the master.
This is documented at http://technet.microsoft.com/en-us/library/ms365379.aspx, where you’re directed to edit a specific registry key. This has some security implica-tions, so I’d prefer you to read through that article and understand the ups and downs, rather than me telling you to make a change. You have the option to have SQL Server Agent not use encrypted communications between the target and master, which should only be done when you’re certain that the network connection between them can’t be compromised by an attacker.
Once you’ve done all that, you’ll need to rerun the wizard to make your instance a master. This time, it should succeed. You’ll have a master, and you’ll have enrolled whatever targets you specified. It’s entirely possible to enroll additional targets in the future. I usually do so by connecting to the target, right-clicking SQL Server Agent, choosing Multi Server Administration, and then selecting Make Target…. All you have to do then is specify the name of the master instance. Always make sure those Agent XPs are enabled on the target before you try doing that.
15.3 Creating and using multiserver management jobs
Let’s set up a multiserver job that runs a simple T-SQL command. Start in SQL Server Management Studio on your master instance:
Figure 15.5 This error indicates an encryption problem.
141 Ideas for multiserver jobs
1 Expand SQL Server Agent.
2 Right-click Jobs and choose New Job….
3 On the General page, give the job a name. I used Multi Test.
4 On the Steps page, add a new job step. Configure the step as shown in figure 15.6.
I’m having it run the DBCCCHECKDB command on the master database (because my second instance doesn’t have any other database for me to play with). Click OK on the job step.
5 On the Targets page, select Target multiple servers and check the available tar-get, as shown in figure 15.7.
6 Click OK to create the job.
7 To test the job, expand Jobs so that you can see the new job. Right-click the job and select Start job at step….
This posts a request to the targets, asking them to connect to the master, download the job definition, and execute the job. It doesn’t happen instantly, though, so you can use the Job Activity Monitor to keep an eye on it, and the job’s history will show you when it was last run (along with its results). Jobs that are given a schedule will run more reli-ably, because the targets can check in, get the job, and run it on that schedule.
15.4 Ideas for multiserver jobs
I use multiserver jobs in a couple of different ways. One way is when I need to make a one-time configuration change across a large number of SQL Server instances. For that reason, I tend to have one instance of SQL Server that’s my “personal” instance;
Figure 15.6 Configure the job step as shown.
Figure 15.7 Target multiple instances with a single job.
142 CHAPTER 15 Multiserver management
I’ll use it to host small databases that I use to track computer inventory information and other operational tasks. That instance becomes my master for multiserver jobs, and I enroll all of my “real” SQL Server instances as targets. That way, when I need to make some configuration change, I can write the necessary T-SQL statements into a job and then assign that job to all of the targets. Bam! Mission accomplished.
Another way to use multiserver jobs is for routine maintenance, such as updating index statistics, reorganizing indexes, and so on. I’ll admit that, although I’ve seen some administrators use a centralized multiserver job for those tasks, I don’t do it much myself. I tend to want those kinds of things customized on a per-instance or per-database basis, which eliminates multiserver jobs as a good option. But, they’re certainly an option for some kinds of routine maintenance, and you should keep them in mind as a possibility.
15.5 Hands-on lab
Because multiserver management necessarily requires multiple servers, I’m not going to ask you to do a hands-on lab for this chapter. However, if you have the ability to install multiple SQL Server instances on your lab computer, do so, and walk through the examples in this chapter as your hands-on experience.
143