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 "ping" a SQL Server from C# one with minimal code and sql operational requirements?

Barrington

Simply pinging the server wouldn't be enough, it's possible for the server to be running but the SQL instance to be stopped. Creating an actual ado.net connection to the instance is the best option. – Bathelda

As you know there is a big difference between MS-SQL and SQL-server, specially if we are talking about ports and pinging them. What a democracy, everybody has to use the same tag. No options! You should add another tag no problem with that, but why to take out the one I choose!!!! – Barrington

MS-SQL IS SQL Server - the product name IS SQL Server. – Nonsuit

SQL Server may refer to: - Any database server that implements the Structured Query Language - Microsoft SQL Server, a specific implementation database server from Microsoft - Sybase SQL Server, a relational database server developed by Sybase. ------- As you can see there are more than one meaning for SQL server and that's why I used the MSSQL tag – Barrington

@Barrington yes, and while true; if you mouse over the sql-server tag you will see it is Microsoft specific. I also say mssql and mysql etc... – Drawshave

Bit late, but shouldn't mssql be aliased to sql-server? – Baton

Answer 1

Execute SELECT 1 and check if ExecuteScalar returns 1.

Nuriel

That's very good, no need to have any object created in a database, but I need to have a database and a user to make a query. I just want to know if the service is up in the MSSQL port. Anyway, you're solving almost 75% of the problem. This would be an option. – Barrington

You have master db anyway :) The cleanest way to check if SQL Server is running is connect to it. To connect you need db and login anyway. All other solutions (like pinging SQL Server port) won't guarantee that SQL Server is running properly and anyone could connect to it. – Nuriel

I accidentally down-voted this answer while my real intention was to up-vote. I realized this one day after, so I cannot remedy my mistake any more. – Kozloski

@GiorgosBetsos - I fixed your mistake :-D – Dewar

Answer 2

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.

/// <summary>
/// Test that the server is connected
/// </summary>
/// <param name="connectionString">The connection string</param>
/// <returns>true if the connection is opened</returns>
private static bool IsServerConnected(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        try
        {
            connection.Open();
            return true;
        }
        catch (SqlException)
        {
            return false;
        }
    }
}

Kruger

no need for the connection.Close(); in this case, the using clause will do it for you upon termination. – Cookhouse

You should put the try catch outside of the using statement. (https://mcmap.net/q/117113/-try-catch-using-right-syntax/1248177 or https://mcmap.net/q/212640/-where-do-i-put-try-catch-with-quot-using-quot-statement-duplicate/1248177). – Cid

@Cid - It depends doesnt it, Are they testing to see if they can connect to a database and only in the exceptional circumstances they can not, they return false. OR do the want so swallow exceptions to do with constructing a SQL connection incorrectly too, I know there is a difference as I have just coded against the above. – Acculturize

Answer 3

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);
}

Rootlet

Answer 4

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.

Penelope

Indeed, simply use ADO net to Connect - if there is no response within the timeout period then the database is not available. You don't need to issue a query to ascertain that. – Emaciation

To check with ADO.NET I need a user, I just want to check if the service is up and running, no problem about if a database is up. I need something like telnet an SMTP server. No need to have a user to obtain a response. – Barrington

@backslash17: The "Login failed for user..." response should be enough to confirm that 1) the machine is up and 2) the service is running. If you get a connection timeout, then the service isn't running/working. – Bathelda

@Rory: Good point! Is just matter of checking the error in a try/catch block. Thanks! – Barrington

@Barrington Check that "Login failed for user" has separate error code or something that helps you to determine it. It is very bad practice to differ errors by exception message. – Nuriel

Answer 5

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.

---

You know, I first wrote this in 2010. Today, I'd just try to actually connect to the server.

Deville

For what Joel Coehorn suggested, have you already tried tcping [elifulkerson.com/projects/tcping.php]. 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.. – Servomechanism

Answer 6

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.]

Servomechanism

Answer 7

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("select 1", 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<T>(T value) where T : IConvertible
    {
        _messageBuilder.Append(value);
        return this;
    }

    public MessageString Append(string value)
    {
        return Append<string>(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<T>(T value) where T : IConvertible
    {
        Append<T>(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<T>(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
}

Condescend

Answer 8

Thanks @27k1

https://mcmap.net/q/211283/-what-39-s-the-best-way-to-test-sql-server-connection-programmatically

I have added some code here:

/// <summary>
        /// Test that the server is connected
        /// </summary>
        /// <param name="connectionString">The connection string</param>
        /// <returns>true if the connection is opened</returns>
        public static (bool isConnected, string sqlErrorMessage) IsServerConnected(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    return (true, "");
                }
                catch (SqlException ex)
                {
                    return (false, ex.Message);
                }
            }
        }

Then call 👇

string connectionString = $@"Server={txtServerName.Text.Trim()};Database={txtDatabaseName.Text.Trim()};
                                            User Id={txtLogin.Text.Trim()};Password={txtPassword.Text.Trim()};";
            var response = IsServerConnected(connectionString);
            if (response.isConnected)
            {
                lblConnectionState.Text = "✔";
                txtSqlConnectionError.Text = "";
            }
            else
            {
                lblConnectionState.Text = "❌";
                txtSqlConnectionError.Text = response.sqlErrorMessage;
            }

Catherinacatherine

Answer 9

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.

Askew

Answer 10

Here is my version based on the @peterincumbria answer:

using var scope = _serviceProvider.CreateScope();
var dbContext = scope.ServiceProvider.GetRequiredService<AppDbContext>();
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:

Grubstake

Answer 11

I normally do this by open a connection but I had some cases where a simple test via Open caused a AccessViolationException

using (SqlConnection db = new SqlConnection(conn))
{    
  db.Open(); // -- Access Violation caused by invalid Server in Connection String
}

So I did a TCP check before the open like recommanded by Joel Coehoorn. C# Code for this may be:

string targetAddress = "";
try
{
  targetAddress = GetServerFromConnectionString();
  IPAddress ipAddress = Dns.GetHostEntry(targetAddress).AddressList[0];
  IPEndPoint ipEndPoint = new IPEndPoint(ipAddress, 1433);

  using (TcpClient tcpClient = new TcpClient())
  {
       tcpClient.Connect(ipEndPoint);           
  }
            
}
catch (Exception ex)
{
    LogError($"TestViaTcp to server {targetAddress} failed '{ex.GetType().Name}': {ex.Message}");
}

Dagmardagna