It’s always handy to know if there any problems with your servers before you get into work in the morning. This is why I wrote a program that goes round all our SQL instances at 7am and checks they are all up. If it doesn’t get a reply from one it emails or pages me to let me know. So I know what to expect when I get in and can “hit the ground running”.
I have a table with the names of all the servers and instances and my program simply works its way through the list this code actually check the state of the Windows service. The service name for the default instance is straightforward but you will need to work out the correct name for your named instances.
Of course there may be occasion where the service reports as running but actually SQL isn’t responding. An alternative method would be to run a SQL command against each instance and see if there is a SQL response. I use that for DBMS and SSAS but it doesn’t work for SSIS and a lot harder for SSRS since it’s a web service.
1:
2: using System.ServiceProcess;
3:
4: string strServiceName = "";
5: switch (ServerType)
6: {
7: case "SSIS 2005":
8: strServiceName = "MsDtsServer";
9: break;
10: case "SSIS 2008":
11: strServiceName = "MsDtsServer100";
12: break;
13: case "SSRS":
14: strServiceName = "ReportServer";
15: break;
16: case "SQLServer":
17: strServiceName = "MSSQLServer";
18: break;
19: case "SQLAgent":
20: strServiceName = "SQLServerAgent";
21: break;
22: case "SSAS":
23: strServiceName = "MSOLAP";
24: break;
25:
26: }
27:
28: try
29: {
30: ServiceController sc = new ServiceController(strServiceName, ServerName);
31: switch (sc.Status)
32: {
33: case ServiceControllerStatus.Running:
34: Console.WriteLine(ServerName + "Running");
35: break;
36:
37: default:
38: Console.WriteLine(ServerName + " ***** Error: " + "Service is " + sc.Status);
39: break;
40: }
41: }
42: catch (Exception ex)
43: {
44: Console.WriteLine(ServerName + " ***** Server Morning Check Error:" + ex.Message);
45:
46: }
