Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

 Implementation :How to monitor multiple sql servers from any one of the master server. Solution:First of all let us see what is it..

Scenario:Let’s say you are at client site and suddenly you are asked to create no of same SQL jobs and Maintenance plan on multiple server which will do the same on each servers.This can be achieved through Multi server administrator option available in sql server.

Infrastructure: Here in my case there is two instances one is default and other is named instance on same machine. Both instances are sql server 2008  standard edition.

Steps: Open the any one of the sql server database that can be said later Master sql server which will be using as monitoring server for other sql servers.

• Open the SSMS
• Go to menu bar and view and select registered server or simply type ctl+Alt+G
•  Go to local server groups.right click on it and select new server registration.
• Here you will see new server registration window.

• Enter the server name in to the group from drop down option.test the connection and save it.
• You can add multiple server in this group for querying tsql at one place.
• After the server registration you will see the added servers list on left side of the SSMS.
• Right click on Local server group and select new query and just check the output.What ever queries will be fired that gives the output for all added sql servers.

• Now Connect the SSMS and make one server as a Master Multi server administrator

• Master Wizard will get open here.

•  Above Wizard will help you to make it master server.With the help of this server you can make jobs and maintenance plan for multiple servers plus notification alerts as well.
• Master server operator will be created on master server and each of the target server.
• Specify the email address for getting multiserver jobs alerts.

• Add target server here.

 

• You can add more target server here from ‘add connection ‘ button.
• Next, sql Server will check  the versions of the master and target server are compatible.

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

• It will start the configuring the servers.Here you may get the following error while finishing the wizard.

• To resolving the issues go to sql server configuration manager and change the log on account for all sql agent to domain\user format.

• Also one more you can see here

• To resolve this error you are supposed to go to target servers registry.
• Please take the backup of registry before making any changes over here.
• Go to target server open run and type ‘regedit’ and press enter button.Follow the below path given in screen shot and change value for ‘MsxEncryptChannelOptions’ value to ‘0’.

• Now Finish the Configuration Wizards.
• Go and connect the all instances including target server and Master server.
• Now to test it,create sql Job on master server.

• Create a sql job and here you will see one more option on left page i.e. targets.Now go to this page and add the listed target servers.

• Here you will see the same job on all target servers.
• You can Implement the Maintenance plan as well.
• So done!!!!!
• Good Luck……….