Wednesday, August 11, 2010

Configuring Multi Server Administration In SQL Server

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

Here I am used SQL SERVER 2008 for Examples....

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

(3) Expand Master Server and Navigate to SQL Server Agent--> Right Click on SQ Server Agent--> Select Multi Server Administration --> Make this as 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 essage. (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.)
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

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

(13) Multi Server Administration Setup will end successfully.

(14) 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---> you 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.
Multiserver Administration. Automated Administration Across an Enterprise. Multi-Server Administration. SQL Server Multi Server Administration. SQL Server 2008 R2 Multi-server Administration. SQL Server Agent Multi-Server Administration. Troubleshooting Multi Server Administration. how to create multi server administration in sql server 2008 R2. how to create multi server administration in sql server 2012. how to create multi server administration in sql server 2005.


You made my job very easy. Thank you so much

Post a Comment