SQL Server Connection #

Question

HI As a daily check, I have to check the SQL connection with all our servers. At the moment, this is done by manually entering the SQL server management studio. I'm just wondering if this can be encoded in C # so that I can run it first thing in the morning, and checks each server and instance for a valid SQL connection and reports to tell if the connection is live or not.

Thanks Andy

Answer 1

Here is an example of a small console application that will cycle through the list of connections and try to connect to each, to report success or failure. Ideally, you might want to expand this to read connection strings from a file in the list , but this will hopefully get you started.

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

namespace SQLServerChecker
{
    class Program
    {
        static void Main(string[] args)
        {
            // Use a dictionary to associate a friendly name with each connection string.
            IDictionary<string, string> connectionStrings = new Dictionary<string, string>();

            connectionStrings.Add("Sales Database", "< connection string >");
            connectionStrings.Add("QA Database", "< connection string >");

            foreach (string databaseName in connectionStrings.Keys)
            {
                try
                {
                    string connectionString = connectionStrings[databaseName];

                    using (SqlConnection connection = new SqlConnection(connectionString))
                    {
                        connection.Open();
                        Console.WriteLine("Connected to {0}", databaseName);
                    }
                }
                catch (Exception ex)
                {
                    // Handle the connection failure.
                    Console.WriteLine("FAILED to connect to {0} - {1}", databaseName, ex.Message);
                }
            }

            // Wait for a keypress to stop the console closing.
            Console.WriteLine("Press any key to finish.");
            Console.ReadKey();
        }
    }
}

Answer 2

Take a look at the SqlConnection Class. It includes a base sample. Just loop around this pattern to connect to each server, and if any server cannot connect, it throws an exception.

Then just set it as a scheduled task on Windows.

, SmtpClient.Send ( .

Answer 3

Why c #? You can create a simple batch file that does this with the osql command.

osql -S servername\dbname -E -Q "select 'itworks'"

Answer 4

You can also view this method:

SqlDataSourceEnumerator.Instance.GetDataSources()

It provides you with a list of SQL servers available on the network.

Answer 5

You know, they make monitoring tools that tell you if your sql server is down.,