What is Multi Server Administration?
When you have multiple SQL Servers that need the exact same SQL Server Agent Job created or maintenance plans, then rather than creating scripts and running on each server, configure and manage your multiple server via Central Server Administration option
This option is very useful when you need to create and run the same jobs or maintenance plans across many SQL Server instances
Steps to set up Central Server Management:
- Register all servers in SQL Server Management Studio (SSMS)
- Create Server Groups if necessary for collective administration
- Select Multi Server administration on SQL Agent and select which Server should be Master and which should be Targets and follow the wizard
(If this 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 pops ups, then we have to go to the ‘regedit’ and configure the registry and change the ‘HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\\SQLServerAgent and change the MsxEncryptChannelOptions’ value to ‘0’. Save the change and close the Registry editor
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.