SQL Server Status Monitor

Question

My application connects to three SQL servers and 5 databases at the same time. I need to show the statuses of {running|stopped|not found} in my status bar.

Any idea / sample code I can use here? This code should not affect application speed or SQL server overhead.

Buddhi

Answer 1

I think you should use WMI (using the ServiceController class (with this constructor ). You basically query the server on which the sql server is located and check its status.

The example below assumes your application is written in C #:

 ServiceController sc = new ServiceController("MSSQLSERVER", serverName); string status = sc.Status.ToString(); 

Answer 2

"This code should not affect the speed of the application or service data for the SQL server"

This is a Schroedinger Cat scenario: in order to find out the current status of a given remote service or process, you must serialize the message on the network, wait for a response, de-serialize the response and act on it. All of which will require some work and resources from all the machines involved.

However, this work can be performed in the background thread on the caller and, if not called too often, cannot affect the target server in any measurable way.

You can use SMO (SQL Server Management Objects) to connect to a remote server and do almost everything you can do with SQL Admin tools, because they use SMO to work with their magic. This is a fairly simple API and can be very powerful in the right hands.

SMO makes it unsurprising that you have the appropriate rights to the fields that you want to control. If you do not have / cannot have sufficient rights, you can ask your friendly SQL amin team to publish a simple data feed that provides some of the data you need.

NTN.

Answer 3

If you connect (check the connection) or if the connection fails (if there is no connection), your application will have some overhead, but you can prevent the timeout by checking it asynscronously.

Answer 4

We use the following SQL query to check the status of a specific database.

 SELECT 'myDatabase status is:' AS Description, ISNULL((select state_desc FROM sys.databases WITH (NOLOCK) WHERE name ='myDatabase'),'Not Found') AS [DBStatus] 

This should have very little overhead, especially in combination with best practices such as background or asynchronous threads

Answer 5

Full disclosure, I am the founder of Cotega

If you are interested in a service for this, our service allows you to monitor the health and performance of SQL Server. In addition, you can set notifications when the database is unavailable, performance, database size or number of users, etc., deteriorate.