I'm looking for a way to poll different servers and check that SQL server is up and running. I'm writing my code in C#. I don't particularly care about individual databases, just that SQL server is running and responsive.
Any ideas?
Add a comment
|
6 Answers
Well, the brute force solution is to attempt to initiate a connection with the database on each server. That will tell you whether it's running, though you could have timeout issues.
The more elegant (but more difficult... isn't that always the way?) solution would be to use WMI to connect to the remote machine and find out if the SQL server process is running.
3 Comments
Perpetualcoder
ummm..just a poke at the brute force solution, say you had a list of connections, in Framework 4 you could think of foreach.parallel and wait for yielded result async. still see perf issues Danny? I am just trying to see where the parallel stuff makes sense...
Perpetualcoder
list of connection string to be precise
TheSmurf
?? Maybe I missed something, but I'm not seeing the point you're trying to make.
System.Data.Sql.SqlDataSourceEnumerator will return all instances of SQL Server currently running.
MSDN Link
1 Comment
steve_mtl
I get the same results as when I use the SQLDMO code 3 out of 6 recognised.
Use the TCPClient Class to create a generic function that connects in TCP to a given IP address.
Then iterate over the list of servers you want to test and try to open a connection to port 1433.
Comments
If you need specific servers, use WMI. If you just want all available servers:
2 Comments
surfmuggle
The link above is down. Does
WMI stand for Windows Management Instrumentation like here learn.microsoft.com/en-us/dotnet/framework/wcf/diagnostics/wmi?
Stu
Nice and timely answer: yes.
SqlDataSourceEnumerator gives you all instances but they are not necessarily running. For local instances of SQL, you can use ServiceController object, namespace System.ServiceProcess. Service name is concatination of "MSSQL$" and "InstanceName" from SqlDataSourceEnumerator. Set ServiceName property of the ServiceController object, and you can check "Status" property - Stopped, Running, Pended etc. Hence, you can filter "Running" ones
Comments
I would certainly go with Vincent's answer. Just make absolutely certain you are closing and disposing the tcp connections properly etc. WMI seems a bit of overkill to me if that is all you're after.
