Home > Multi Server Administration > Automating administration across multiple instances of SQL Server (MultiServer Administration)

Automating administration across multiple instances of SQL Server (MultiServer Administration)

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.

 Instance Protocol Properties

 

 

 

 

Click to Open Properties Window

 Instance Protocol Properties

 

 

 

 

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.

 

Registered SQL Servers

 

 

 

 

 

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

 Select Master Server from all Registered SQL Servers

 

 

 

 

 

 

 

(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.

 Setup_MasterServer

 

 

 

 

 

 

 (4)Click Next

MasterServer_Wizard

 

 

 

 

 

 

(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.

MasterServer_Operator

 

 

 

 

 

 

 

(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.

Setup_TargetServer

 

 

 

 

 

 

(7) Checking Server version Compatibility

Master_TargetServer_Version_CompatibilityCheck

 

 

 

 

 

(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.

MasterServer_Login Credentials

 

 

 

 

 

(9)Click next for  Summary Wizard.

MasterServer_Finish

 

 

 

 

(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’)

 

MasterServer_Final_Finish

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_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance_name>\SQLServerAgent\MsxEncryptChannelOptions(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

UpdateRegisteryValue_1

 

 

 

 

(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.

MasterServer_Final_Finish_successfull

 

 

 

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

masterserver_msx_finish

 

 

 

 

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

 sqlagent_msx1

 

 

 

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

MasterServer_MSX_Create_Job

 

 

 

 

Create Job steps

 masterserver_msx_create_job_step

 

 

 

 

Select the Target Server for Job to be Deployed.

masterserver_msx_create_job_settarget

 

 

 

 

 

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

masterserver_msx_create_jobcreated_master

 

 

 

 

 

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

 masterserver_msx_create_jobcreated_target2

 

 

 

 

 

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.

 

 

  1. Jeff Fasher
    September 17, 2009 at 3:25 am

    well done . right on the button
    was having great issues with this

    cheers

  2. July 20, 2010 at 11:04 am

    Good day a very interesting site. If yuo are searching for work or jobs in the UK please have a look at http;//wwww.coventry-jobs.com

  3. kavitha
    September 25, 2014 at 8:16 pm

    I have multi server admin jobs runs on all my database, how to stop the job on particular database?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: