What s the best way to test SQL Server connection programmatically

Question

I need to develop a single routine that will be fired each 5 minutes to check if a list of SQL Servers  10 to 12  are up and running  Is there a way to simply  quot ping quot  a SQL Server from C  one with minimal code and sql operational requirements

User · Accepted Answer

Execute SELECT 1 and check if ExecuteScalar returns 1

User · Answer

public static class SqlConnectionExtension        region Public Methods      public static bool ExIsOpen          this SqlConnection connection  MessageString errorMsg   null                if  connection    null    return false            if  connection State    ConnectionState Open    return true             try                       connection Open                return true                    catch  Exception ex    errorMsg  Append ex ToString               return false             public static bool ExIsReady          this SqlConnection connction  MessageString errorMsg   null                if  connction ExIsOpen errorMsg     false    return false            try                       using  var command   new SqlCommand  quot select 1 quot   connction                 return   int command ExecuteScalar       1                      catch  Exception ex    errorMsg  Append ex ToString               return false              endregion Public Methods    public class MessageString   IDisposable        region Protected Fields      protected StringBuilder  messageBuilder   new StringBuilder          endregion Protected Fields       region Public Constructors      public MessageString                    public MessageString int capacity                 messageBuilder Capacity   capacity             public MessageString string value                 messageBuilder Append value               endregion Public Constructors       region Public Properties      public int Length           get   return  messageBuilder Length            set    messageBuilder Length   value               public int MaxCapacity           get   return  messageBuilder MaxCapacity                endregion Public Properties       region Public Methods      public static implicit operator string MessageString ms                return ms ToString               public static MessageString operator   MessageString ms1  MessageString ms2                MessageString ms   new MessageString ms1 Length   ms2 Length           ms Append ms1 ToString             ms Append ms2 ToString             return ms             public MessageString Append lt T gt  T value  where T   IConvertible                messageBuilder Append value           return this             public MessageString Append string value                return Append lt string gt  value              public MessageString Append MessageString ms                return Append ms ToString                public MessageString AppendFormat string format  params object   args                 messageBuilder AppendFormat CultureInfo InvariantCulture  format  args           return this             public MessageString AppendLine                  messageBuilder AppendLine            return this             public MessageString AppendLine string value                 messageBuilder AppendLine value           return this             public MessageString AppendLine MessageString ms                 messageBuilder AppendLine ms ToString             return this             public MessageString AppendLine lt T gt  T value  where T   IConvertible               Append lt T gt  value           AppendLine            return this             public MessageString Clear                  messageBuilder Clear            return this             public void Dispose                  messageBuilder Clear             messageBuilder   null             public int EnsureCapacity int capacity                return  messageBuilder EnsureCapacity capacity              public bool Equals MessageString ms                return Equals ms ToString                public bool Equals StringBuilder sb                return  messageBuilder Equals sb              public bool Equals string value                return Equals new StringBuilder value               public MessageString Insert lt T gt  int index  T value                 messageBuilder Insert index  value           return this             public MessageString Remove int startIndex  int length                 messageBuilder Remove startIndex  length           return this             public MessageString Replace char oldChar  char newChar                 messageBuilder Replace oldChar  newChar           return this             public MessageString Replace string oldValue  string newValue                 messageBuilder Replace oldValue  newValue           return this             public MessageString Replace char oldChar  char newChar  int startIndex  int count                 messageBuilder Replace oldChar  newChar  startIndex  count           return this             public MessageString Replace string oldValue  string newValue  int startIndex  int count                 messageBuilder Replace oldValue  newValue  startIndex  count           return this             public override string ToString                 return  messageBuilder ToString               public string ToString int startIndex  int length                return  messageBuilder ToString startIndex  length               endregion Public Methods

User · Answer

Wouldn t establishing a connection to the database do this for you   If the database isn t up you won t be able to establish a connection

User · Answer

Similar to the answer offered by Andrew  but I use   Select GetDate   as CurrentDate  This allows me to see if the SQL Server and the client have any time zone difference issues  in the same action

User · Answer

Here is my version based on the  peterincumbria answer  using var scope    serviceProvider CreateScope    var dbContext   scope ServiceProvider GetRequiredService lt AppDbContext gt     return await dbContext Database CanConnectAsync cToken    I m using Observable for polling health checking by interval and handling return value of the function  try-catch is not needed here because

User · Answer

I have had a difficulty with the EF when the connection the server is stopped or paused  and I raised the same question  So for completeness to the above answers here is the code        lt summary gt      Test that the server is connected      lt  summary gt       lt param name  connectionString  gt The connection string lt  param gt       lt returns gt true if the connection is opened lt  returns gt  private static bool IsServerConnected string connectionString        using  SqlConnection connection   new SqlConnection connectionString                 try                       connection Open                return true                    catch  SqlException                        return false

User · Answer

See the following project on GitHub  https   github com ghuntley csharp-mssql-connectivity-tester  try       Console WriteLine  Connecting to   0    AppConfig ConnectionString       using  var connection   new SqlConnection AppConfig ConnectionString                 var query    select 1           Console WriteLine  Executing   0    query            var command   new SqlCommand query  connection            connection Open            Console WriteLine  SQL Connection successful              command ExecuteScalar            Console WriteLine  SQL Query execution successful             catch  Exception ex        Console WriteLine  Failure   0    ex Message

User · Answer

For what Joel Coehorn suggested  have you already tried the utility named tcping  I know this is something you are not doing programmatically  It is a standalone executable which allows you to ping every specified time interval  It is not in C  though  Also  I am not sure If this would work If the target machine has firewall  hmmm      I am kinda new to this site and mistakenly added this as a comment  now added this as an answer  Let me know If this can be done here as I have duplicate comments  as comment and as an answer  here  I can not delete comments here

User · Answer

Look for an open listener on port 1433  the default port    If you get any response after creating a tcp connection there  the server s probably up

[c#] What's the best way to test SQL Server connection programmatically?

Examples related to c#

Examples related to sql-server

Examples related to database-connectivity