Startup program has intermittant problems connecting to local database

Question

I'm writing an application that runs from the start-up folder, and tries to connect to a local SQLServer2008-r2 database as part of the startup process of the application. The database i'm connecting to is set to use windows authentication, and my connection string in the application is your standard old connection string: Data Source=localhost\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=True

The code to actually establish the connection looks like this:

String connectionstring = "Data Source=localhost\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated   Security=True";
SqlConnection cn = new SqlConnection(connectionstring);

bool databaseUp = false;

void Start()
{
    CheckSQLService();

    for (int i = 0; i < 600; i++)
    {
        if (ConnectToDB())
        {
            Console.WriteLine("Database is up and running.");
            databaseUp = true;
        }
        else
        {
            Console.WriteLine("Database Connection Failed");

            CheckSQLService();

            Console.WriteLine("Trying again");
            Thread.Sleep(1000);
        }
    }

    if(!databaseUp)
    {
        Console.WriteLine("Database Not Connected: exited loop.");
    }
}

bool ConnectToDB()
{
    try
    {
        cn.Open();
        return true;
    }
    catch (Exception e)
    {
        Console.WriteLine(e.ToString());
        return false;
    }
}

void CheckSQLService()
{
    System.ServiceProcess.ServiceController SC = new System.ServiceProcess.ServiceController("MSSQL$SQLEXPRESS");
    Console.WriteLine(" = = = Checking SQL Service Status: = = = " 
        + "\n ServiceName : " + SC.ServiceName
        + "\n State       : " + SC.Status
        + "\n Stoppable   : " + SC.CanStop
        + "\n Pausable    : " + SC.CanPauseAndContinue
        + "\n Can Shutdown: " + SC.CanShutdown);
}

Basically I loop until I get a connection, and if it fails, I sleep and try again.

Most of the time (about 60%) I am able to successfully establish a connection with no problem on first try. Sometimes, it takes several attempts for this to be successful, and the application waits for some time. I will also note that the read-out of CheckSQLService() verifies every time I call it that the SQL service is running - Only sometimes it rejects my windows credentials apparently.

Anybody have ideas why this might be happening intermittently? Again this is part of an application that runs when windows first starts up, so there could may understandably be many factors at play (e.g. other processes or services not fully loaded, or starting at same time etc).

Answer 1

Just because the SQL Server service is running does not mean it is ready to accept connections.

Since you are always able to connect eventually, and your program runs as the computer is booting, I strongly suspect that you sometimes run before SQL Server is fully initialized and ready for connections.

Many things happen during a typical boot sequence. It is not surprising that your program is sometimes running before SQL Server is ready, and vice-versa.