What's the best way to test SQL Server connect

问题:

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.

I can try to obtain a simple query in each one of the servers but this means that I have to create a table, view or stored procedure in every server, even if I use any already made SP I need to have a registered user in each server too. The servers are not in the same physical location so having those requirements would be a complex task. Is there a way to simply "ping" from C# one SQL Server?

Thanks in advance!

回答1:

Execute SELECT 1 and check if ExecuteScalar returns 1.

回答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;
        }
    }
}

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

回答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.

回答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.

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

回答7:

Why not just connect to telnet session on the sql server port. If it connects, sql server is up and happy, if not, you're out of luck.

This other StackOverflow post might be a good place to start with that.

EDIT: OK, now I've fully read the other posts this isn't exactly the best solution... Still, if you just want to ping the port....

回答8:

public static class SqlConnectionExtension
{
    #region Public Methods

    public static bool ExIsOpen(this SqlConnection connection, MessageString errorMsg)
    {
        if (connection == null) return false;
        if (connection.State != ConnectionState.Open)
        {
            try
            {
                connection.Open();
            }
            catch (Exception ex) { errorMsg.Append(ex.ToString()); }
        }
        return true;
    }

    public static bool ExIsReady(this SqlConnection connction, MessageString errorMsg)
    {
        if (ExIsOpen(connction, errorMsg) == false) return false;
        try
        {
            using (SqlCommand command = new SqlCommand("select 1", connction))
            using (SqlDataReader reader = command.ExecuteReader())
                if (reader.Read()) return true;
        }
        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
}

回答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.

回答10:

Connecting to mssql via C# is very problematic.

The handles will not be consistent once we connect, although we have close the connection after connect.

I did read somewhere it was .Net 4.0 issue, and if you use .Net 3.5 it should be ok.