CODEWITHSUNDEEP
c#sqlservice
ove
ove

Reputation: 3202

SQL server recovery mechanism

I have a service application in C# which queries data from one database and inserts it into another SQL database.

Sometimes, the MSSQLSERVER service crashed for unknown reason and my application will crash as well. I want to do a SQL recovery mechanism that where I check to make sure the sqlconnection state is fine before i write to the database but how i do that?

I tried stopping MSSQLSERVER service and sqlconnection.State is always open even when the MSSQLSERVER service is stopped.

Upvotes: 1

Views: 334

Answers (3)

Alex Aza
Alex Aza

Reputation: 78457

I think that the approach you chose is not very good.

If your application is some kind of scheduled job, let it crash. No database - no work can be done. This is ok to crash in this case. Next time it runs and db is up it will do its thing. You can also implement retries.

If your application is a windows service inside and some kind of scheduled timer, you just make sure that your service doesn't crash by handling SqlExcpetion. Retry again until server is up.

Also, you might want to use distributed transactions. To guarantee integrity of the copy procedure, but whether you need it or not, depends on the requirements.

[Edit] In response to retry question.

var attemptNumber = 0;
while (true)
{
    try
    {
        using (var connection = new SqlConnection())
        {
            connection.Open();

            // do the job
        }
        break;
    }
    catch (SqlException exception)
    {
        // log exception
        attemptNumber++;
        if (attemptNumber > 3)
            throw; // let it crash
    }
}

Upvotes: 0

C. Dragon 76
C. Dragon 76

Reputation: 10062

The general strategy of checking the connection state before calling a SQL command fundamentally won't work. What happens if the service crashes after your connection check, but before you call the SQL command?

You probably will need to figure out what exception is thrown when the database is down and recover from that exception at the appropriate layer of code.

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300559

First: Fix your real problem. SQL Server should be very, very stable.

Second: consider using MSMQ (or SQL Service Broker) on both the client application and server to queue updates.

Upvotes: 1

Related Questions