Friday, August 21, 2009

Interesting problem with SQL 2008, VCS, and MSX-TSX Servers

I thought I was going nuts today when I couldn't get a TSX server to use it's MSX server. Enrollment went through without a hitch, but the TSX would never get any jobs or even probe (although the MSX said it was probed).
After a LOT of digging I eventually found the problem. The TSX is actually a Veritas/Symantec cluster with a virtual name but when the MSX was calling things like msdb.dbo.sp_sqlagent_probe_msx it would use the hostname of the cluster node running the SQL service for the value of @server_name. This was weird. Both @@SERVERNAME and SERVERPROPERTY('SERVERNAME') returned the virtual name and the TSX enrolled as the virtual name but it was always calling the damn hostname!
The fix for this was quite obscure.
I eventually happened upon a key
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT]
"Environment"

And this key was missing for this cluster, yet there for every other SQL server I've got. I took a chance and put in the data for the key that was present under the service for this instance and bounced the service. BAMN! Just like that it downloaded all it's jobs and the MSX started issuing sp_sqlagent_probe_msx calls using virtual name!

I have no idea why this happened (SQL 2008 seems 'flaky' with permissions like this) but at least the fix was easy.
Keep an eye open for this if you're running a similar setup!