MS SQL 2005: creating a multi-server environment

If you’re not using SQL Server for your daily job, please ignore this post.

If you come to this post after hours of Googling and cursing, this is the place for you to share.

If you wonder why I have a beginning like this, read this post which I found from Google myself.

It took me 2 man-days to finally set up a master-target server configuration on 2 instances of SQL Server 2005. Various problems along the way but I think you won’t miss it if you follow each and every of the steps below:

1. Read this article and do not swear for whatever reason http://msdn2.microsoft.com/en-us/library/ms191305.aspx

2. Make sure that all instances are on the same level of service pack. Actually we can have a Master server (MSX) instance running on SQL Server 2005 (no SP) and a Target server (TSX) instance running on SQL Server 2005 SP2 but we can not make it vice-versa.

3. Make sure that the account running SQL Server Agent is a domain account and has sufficient rights in both instances. For me I use the same account.

4. On the master server’s registry, go to \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance_name>\SQLServerAgent\MsxEncryptChannelOptions (REG_DWORD) and change the value to 0 (It is 2 by default). Most probably your <instance_name> will be something like MSSQL.1

5. On the target server’s registry, go to the same place and change the value of AllowDownloadedJobsToMatchProxyName to 1.

6. Restart both servers, then run SQL Server Management Studio and connect to the database engine. Right-click SQL Server Agent and choose “Multi Server Administration” >> “Make this a Master” and follow the Master Server Wizard

7. When this completes, you’ll see the SQL Server Agent on the master server become something like SQL Server Agent (MSX) and on the target server it becomes SQL Server Agent (TSX : master_server)

If you have some error messages when running the “Multi Server Administration”, most notoriously “The enlist operation failed (reason: SQLServerAgent Error: Unable to connect to MSX)” then it’s something to do with 2, 3 and 4 above (I’ve had all of them but in a different order.) Then if you can even find out other problems then let me know so I can update this post and help people like the guys starting this post , this post and this post.

Happy screwing SQL Server! :)

One Comment

  1. Nebojsa Gojnic:

    Excellent page,
    I just can’t believe guys from Microsoft did it this way.
    Do they really expect me to restart production servers?

Leave a comment