Chapter 15. Multiserver management

 

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 automate 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 service 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.

Figure 15.1. Make the instance a Master to get started.
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.

15.3. Creating and using multiserver management jobs

15.4. Ideas for multiserver jobs

15.5. Hands-on lab