SQL Server Multi Server Administration

Problem

I have multiple SQL Servers that need the exact same SQL Server Agent Job created. I know I can script a SQL Server Agent Job out and run the T-SQL script on multiple servers or bounce from server to server creating the SQL Server Agent Jobs, but isn't there an easier way? Check out this tip to learn more.

Solution

I seem to notice a lot of people do not use or even know about Multi Server Administration for their SQL Server Agent Jobs and Maintenance Plans. Multi Server Administration can be really useful when you need to create and run the same jobs or maintenance plans across numerous SQL Server instances. Let's walk through setting up Multi Server Administration for SQL Server.

Here’s a basic look at how to setup Multi Server Administration. My environment has two separate VM's running in VM Workstation with Windows 2008 R2 Datacenter Edition and SQL Server 2008 R2 Enterprise named Principal and Mirror.
First thing I’m going to do is register both servers in SQL Server Management Studio (SSMS). If you do not see the Registered Servers tab in SSMS navigate to 'View' | 'Registered Servers' or press 'Ctrl + Alt + G'.

Once both servers are registered I can start configuring Multi Server Administration. To do this right click on 'SQL Server Agent' on your master server and choose 'Multi Server Administration' | 'Make this a Master…' to begin the process.

Click Next on the Welcome for the Master Server Wizard.

The next screen is where we can configure an operator. Just like when we create an operator for local jobs, we can create one for our Multi Server jobs. You can also leave this section blank if you prefer not to have an operator although I would not recommended this practice. Once completed, press the 'Next' button to continue.

On the Target Servers screen is where we specify our target servers. You can have multiple targets, but for this example I’m only using one. You can select target servers using your 'Registered Servers' from your SSMS session or you can add a connection by clicking the 'Add Connection...' button. I’m going to select Mirror as my target and click the 'Next' button to continue the process.

Next, SQL Server will check that the versions of the master and target server are compatible as shown on the screen shot below.

On the Master Server Login Credentials screen security is checked between the master and target servers. If needed, the check box can be selected to create a login and assign it rights to the master server. Click the 'Next' button to continue the process.

Once you click the 'Finish' button on the Complete the Wizard screen it will start the configuring the servers.

Be aware that a common problem exists during configuration and the following error (The enlist operation failed (reason: SQLServerAgent Error: The target server cannot establish an encrypted connection to the master server 'Server Name'. Make sure that the MsxEncryptChannelOptions registry subkey is set correctly on the target server.) (Microsoft SQL Server Error: 22026)) may be thrown as shown on the screen shot below:

As the error indicates, we need to make a registry change on the target server. To accomplish this, logon to the target server and navigate to 'Start' | 'Run' | type 'Regedit'. As a best practice, be sure to backup your registry before making changes. From the Registry Editor browse to 'HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\\SQLServerAgent and change the MsxEncryptChannelOptions' value to '0'. Save the change and close the Registry editor.

Once this is complete, run the wizard from the master server again and it should finish successfully as shown below.

To verify everything was configured correctly, connect to both servers and beside SQL Server Agent it should have either '(MSX)' or 'TSX:' as shown below.

To set up a Multi Server Job under SQL Server Agent you must connect to the master server in SSMS and navigate to 'SQL Server Agent' | 'Jobs' | 'Multi-Server Jobs". Configure the job as needed and on the 'Targets tab' select the target servers you want this job to execute on.

Once finished, click the 'OK' button to save job to both servers.

The same general process applies to Maintenance Plans, connect to the master server and navigate to 'Management' | 'Maintenance Plans' | right click and choose 'New Maintenance Plan…'. This process starts by naming the job as shown below.

Configure the Maintenance Plan as needed and on the Servers button select the servers you want this Maintenance Plan to execute on.

Once finished, click 'Save and Close' to save both Maintenance Plans to both servers as shown below.

Sources:

http://www.mssqltips.com/sqlservertip/2445/sql-server-multi-server-administration/

SQL Server 2008 R2 Multi-server Administration – A First Look

One of the most useful new features of SQL 2008 R2 for DBA’s is the ability to manage and administer multiple instances of SQL Server from a central location using the new SQL Server Utility tool.

SQL Server Utility provides a unified view of the managed instances of SQL Server providing details such as CPU utilization, database file space utilization and volume space utilization by each managed instance. It provides a Utility Control Point (UCP) as a central repository for configuration and management of performance data collected for managed instances, and a Utility Explorer as a dashboard to view detailed and summary reports of the utilization, resource health and configuration information of all instances enrolled in the SQL Server Utility.

To setup the SQL Server Utility you need to:

(1) Create a UCP from the SQL Server Utility.

(2) Enroll instances of SQL Server with the UCP.

(3) Define Global and Instance level policies, and manage and monitor the instances.

To create a UCP, start by launching the SQL Server Utility and then select View > Utility Explorer in SQL Server Management Studio (SSMS). This will launch the getting started page:

The first option on this page is Create a Utility Control Point (UCP). Clicking this will launch the first screen of the UCP wizard as shown below.

This wizard will take you through a series of 5 steps to  create and set up the UCP.  Click Next to begin and you will be prompted to specify the name for the UCP, and select the SQL Server Instance to act as the UCP.

Next, you will need to specify the SQL Server proxy account for the data collection set as shown below:

                                                                                             

Next, the Instance Validation screen that will show the progress and results of the validation conditions for an instance.  

Next, the wizard will confirm the settings to be applied upon installation:

Finally, click Finish to close the wizard.

Since the UCP itself becomes a managed instance automatically, once the wizard closes  you will be directed to the Utility Explorer Content page displaying a graphical view of the various parameters like CPU utilization, database file space utilization and volume space utilization that are measured for the UCP instance.

Once you have created the UCP, you can start enrolling instances of SQL Server you wish to monitor and manage. This is done in the Enroll Instance Wizard, launch it by expanding the Utility Explorer tree, right-clicking on Managed Instances and selecting Enroll Instance. You will see the Introduction screen (as in the below two screenshots)

                                           

This wizard is also a series of five steps that will collect information regarding the SQL Server instance to be enrolled and register it with the UCP. On the first screen (below) click Next to proceed with the wizard.

 Next, as in the UCP wizard, click on the Connect button to bring up the Connect to Server dialog box (see below) where you select the Instance to be added, and then click Next to proceed.

Next, either specify a Windows Domain Account or a SQL Server Agent service account for the utility collection set. Microsoft recommends using a Windows domain account. Even if a SQL Server Agent account must be used, it should not be a built-in account such as LocalService, NetworkService or LocalSystem, but a windows domain account. Click Next to continue (see below)

The Instance Validation screen (see below) shown above is similar to the one you see in the UCP creation wizard. Click Next to proceed after confirming all Action Results are a Success.

The Summary shows the settings of the SQL Server Instance to be added. When you click Next, you will come to the final step of the wizard – Enrollment of SQL Server, showing the status of actions taken to enroll the instance in the UCP. Once you confirm the Success of  all actions, click Finish to close the wizard (see below two screenshots)

In the Utility Explorer Content page, there are several sections showing a graphical representation of the resources utilization. The Utility Summary box shows how many instances are managed. The Managed Instance Health and Data-tier Application Health indicates the overall health of instances. The Managed Instance with Overutilized Resources shows how many instances have overutilized the available resources. Likewise, the Managed Instance with Underutilized Resources shows how many instances are underutilizing the available resources. A similar explanation holds for the Data-tier Application boxes on the right. The two boxes in the bottom shows the utilization history of the utility control point managed instance.

A breakdown of utilization of each resource by individual managed instances is also available. In the Utility Explorer page, you click on the Managed Instances node, and on the right-hand side, in the Utility Explorer Content Page select the individual instances and look through the utilization of each resource in detail.

Sources:http://www.sql-server-performance.com/2010/multi-server-administration/