Multiserver administration is the process of automating administration across multiple instances of Microsoft® SQL Server™.

With multiserver administration, you must have at least one master server and at least one target server. A master server distributes jobs to and receives events from target servers. A master server stores the central copy of job definitions for jobs run on target servers. Target servers connect periodically to their master server to update their list of jobs to perform. If a new job exists, the target server downloads the job and disconnects from the master server. After the target server completes the job, it reconnects to the master server and reports the status of the job.

In this topics we will talk about how to set up Multi Server Administration with SQLServer 2005.

Steps to setup MultiServer Administration:

(1) Check if Master and Target Server have installed SQLServer 2005 SP2.

(2) SQLServer Services and SQLAgent Services should not be runnng with localaccount.

(3) In case if u want to use default Encryption (no certificate) or Encrytion with Certificate Certificates

Open SQL Server Configuration , navigate to SQL Server 2005 Network Configuration and Select the Instance  u want to Set Encryption.

 

 

 

 

 

Click to Open Properties Window

 

 

 

 

 

To enable default encryption select option(yes) and click OK.In Case if u want to enable Encryption with Certificate.First Install the certificate on master and target server ,navigate to the Certificate Tab of Instance Protocol Properties and select the Cetificate.

Note : Encryption need to enable on both Master and Target Server.

Once Encryption is enabled at Master and Traget Server,restart the services of master and target server.

Lets Setup Multiserver Administration

Steps: Setup Multiserver Administration

(1) Register the Master Server and Target Servers in Consol Management of SQLServer 2005.

 

 

 

 

 

 

(2) Select the Master Server from Registered SQL Server and Connect.

 

 

 

 

 

 

 

 

(3)Expand Master Server and  Navigate to SQL Server Agent–>Right Click–>Select Multi Server Administration –>Make this a Master

Master Server Wizard will pop up.

 

 

 

 

 

 

 

 (4)Click Next

 

 

 

 

 

 

(5) In Master Server Operator Wizard Provide email address for notification.As master server operator will be created on the master server and each of the target servers.

 

 

 

 

 

 

 

(6)Select the target server from the registered server window and move the same to target server list box.If need you can provide the description of the target server.Connect the target server from the connect box under target server list box.Click Next to Check the version compatibility of the master and target server.

 

 

 

 

 

 

(7) Checking Server version Compatibility

 

 

 

 

 

(8)Click Close.You will be naviagated to Master Server Login Credential window.

I have unchecked the option to create new login for master server, as i am logged in with my domain id with full permission.

 

 

 

 

 

(9)Click next for  Summary Wizard.

 

 

 

 

(10) Click Finish to setup the Multi Server Administration Task.Enlist Task should willl pop up an error message. (MSX enlist failed for Job Server ‘xyz’)

 

If u read the error message it states “The Target Server cannot establish an encrypted connection to the master server.”

By default SQLServer Registery values have setup encryption of level 2 (Enables full SSL encryption and certificate validation between target server and the master server.)

 

In our case we are not using SSL encryption and Certificate validation, change the value from 2 to 0.

To configure the appropriate level of security required for a specific master server/target server communication channel, set the SQL Server Agent registry subkey HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server<instance_name>SQLServerAgentMsxEncryptChannelOptions(REG_DWORD)
on the target server.If you are not using a certificate for Secure Sockets Layer (SSL) encrypted communications bet

(11)Check Default Value for  SQL Server Agent registry subkey

 

 

 

 

(12) Change the Default value of SQL Server Agent registery key (MsxEncryptionOptions) to 0

Close the registery editor window and start again from Steps: Setup Multiserver Administration till Step 10.

Multi Server Administration Setup will end successfully.

 

 

 

After Successfull Setup Master Server SQL Agent will show (MSX) and Target Server SQLAgent will show (TSX).

 

 

 

 

Lets expand Master Server SQL Agent

SQL Server Agent(MSX)–> Jobs–>

U will see two new folder gets created by the name Local Jobs and Multi-Server Jobs.Local Job is for Master Server Only, where as Multi-Server will be responsible for all Target Server.We can Create the Job at Master and it will get deployed to the Targeted Server

 

 

 

 

Lets Create a Job on Master Server and see how it gets created or modified on Target Server.

Create a new job in Master Server

 

 

 

 

Create Job steps

 

 

 

 

 

Select the Target Server for Job to be Deployed.

 

 

 

 

 

Now Create the Job. The same will get create at master fisrt and later get syncronized to the selected target server.

 

 

 

 

 

Master will take few second to deploy or modify the job at target server.
Lets Check the Target Server

 

 

 

 

 

 

Job can be executed from Master Server and Target Server.Last Execution history can be viewed from Master server aslo.

The Step detail can only be seen from the Target Server only.