Tuesday, 20 April 2010

Centralized Management Servers

This is pretty much an interim post until I get around to writing up a more detailed article, and forget half the set up details along the way. Centralized Managed Servers, other than just letting you run a query on multiple servers simultaneous, also let you manage jobs centrally. In brief, you create a master management server, and can push jobs out to the respective target servers.

By default, this behaviour only works when using Certificates. Which for playing with is a pain in the arse. However, there is a registry change you can make that eliminates the need to issue certificates for each master and target management server. As below:

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLerver\instance_name\SQLServerAgent\MsxEncryptChannelOptions (REG_DWORD)

This needs to be changed to 0 (zero) from 2 (two).