What is the fastest SQL server availability checker?

Question

What is the best way to check if SQL server exists or not?

I am trying to execute Microsoft.SqlServer.Management.Smo.Server.PingSqlServerVersion () and it works fine if the server exists and is accessible. But it is rather slow if there is no such server.

Is there a fast enough verification method without specifying user credentials (server name only) if a server exists?

What do you recommend to use?

Answer 1

You can simply use the TcpClient class to query the server and check if any port is open, maybe something like this:

using System.Net; using System.Net.Sockets; public bool CheckServerAvailablity(string serverIPAddress, int port) { try { IPHostEntry ipHostEntry = Dns.Resolve(serverIPAddress); IPAddress ipAddress = ipHostEntry.AddressList[0]; TcpClient TcpClient = new TcpClient(); TcpClient.Connect(ipAddress , port); TcpClient.Close(); return true; } catch { return false; } } 

Answer 2

You can still use Microsoft.SqlServer.Management.Smo.Server.PingSqlServerVersion() , but use it asynchronously. for example, you can call it using the BackWorker class. The DoWork event will call Microsoft.SqlServer.Management.Smo.Server.PingSqlServerVersion() . RunWorkerCompleted would simply set the boolean to true. How you could turn it off, wait how much you need, check the boolean value, and if it is not, you know that the SQL server has not responded yet, and you can cancel BackgroundWorker.

Answer 3

You can try and open the tcp socket on port 1433 (standard SQL port) with a short timeout and see if it responds.

To do this, the SQL server must have the TCP / IP protocol.

Answer 4

To add to Mikael's, you can also run the ping command first, as this will be the fastest response if the server is down.

Of course, all this assumes that you are trying to go to a remote server via TCP / IP.

Answer 5

After using Ben Robinson's answer, I came up with this and it works well for me. I used the connection string to open and then close the connection in the try block, but when I was running on Windows 8.1, the exception never caught and the program crashed.

 public unsafe bool OdbcConnectionTest(string sConnectionString , out int actualTimeMs) { DateTime dtme = DateTime.Now; OdbcConnectionStringBuilder con; Microsoft.SqlServer.Management.Smo.Server svr; Microsoft.SqlServer.Management.Common.ServerVersion sVer; Microsoft.SqlServer.Management.Smo.Database db; try { con = new System.Data.Odbc.OdbcConnectionStringBuilder(sConnectionString); object sServer; if (con.TryGetValue("server", out sServer)) { svr = new Microsoft.SqlServer.Management.Smo.Server((string)sServer); if (svr != null) { sVer = svr.PingSqlServerVersion((string)sServer); if (sVer != null) { object sDb; if (con.TryGetValue("database", out sDb)) { if (!String.IsNullOrWhiteSpace((string)sDb)) { db = svr.Databases[(string)sDb]; if (db != null && db.IsAccessible) { TimeSpan ts = DateTime.Now - dtme; actualTimeMs = (int)ts.TotalMilliseconds; return true; } } } } } } } catch { actualTimeMs = -1; return false; } actualTimeMs = -1; return false; }