Every company I’ve worked at had some type of standard schedule to apply (non critical) Windows patching. Most (if not all) of the time this ends up requiring a reboot of the whole environment which no matter how good your shutdown/startup documentation is tends to be a worrysome time.
If you haven’t played around with SQL Server 2008’s new Central Management Server please take some time to read this great article by Brent Ozar about it:
OK so now after reading that article you should be “in the know” about the new CMS. Some of the groupings I have in my CMS are called DEV, UAT, and PROD. So if I highlight the PROD group and select “New Query” the first thing I see at the bottom of my Management Studio Window is:
So there is my first “sniff test” that all my SQL Server Instances in my PROD group are alive and well… but what about the SQL Server Agent service… is it running as well?
Copy the code below into your Management Studio query window (UPDATED to include @@SERVERNAME):
IF EXISTS ( SELECT 1
WHERE program_name = N'SQLAgent - Generic Refresher')
SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning'
SELECT @@SERVERNAME AS 'InstanceName', 0 AS 'SQLServerAgentRunning'
Your result set should have all the instance names in your PROD CMS Group along with a True/False column as to whether or not the SQL Server Agent Service is up and running.
In utilizing a Central Management Server and running this script you now have a good indication as to whether or not you can connect to all your SQL Servers as well as the status of the SQL Server Agent Service on each of those instances. Obviously we’re just scratching the surface of the great things you can do with CMS but if you haven’t played around with this new feature this should give you a good place to build from.