Coding for seamless SQL Server database mirroring

SQL Server database mirroring can provide high availability to your database. Expert Roman Rehak shares his favorite pieces of code for flawless mirroring.

SQL Server database mirroring has been providing a high-availability option since 2005. One feature is automatic application failover to the secondary server when the primary server is no longer available. This article discusses automatic application failover and how to implement it in your code in a way that minimizes interruptions.

Database mirroring can be set up in several different configurations. You can use a witness server or you can set it up without one. You can choose between synchronous mode for high safety or asynchronous mode for high performance. If you want your ADO.NET-based applications to automatically connect to the mirror if the primary server is not available, you will have to use the synchronous mode and set up a third server as a witness. Once it is created in SQL Server, the setup on the application side is easy. You just need to specify the name of the mirror server in the ADO.NET connection string using the “Failover Partner" property. Here is a sample connection string that specifies ServerA as the primary and ServerB as the mirror server:

string cnString = "Server=ServerA;Failover Partner=ServerB;Database=Northwind;Trusted_Connection=True;";

That's really all you need to do on the application side. If SQL Server is turned off on ServerA, or if the computer reboots or shuts down for whatever reason, the databases should failover to ServerB and the applications using this connection string will continue working. The applications should work whether the database is on ServerA or ServerB, and they will continue working even if one server is down and the mirrored database is in "Disconnected" state, which means the mirroring is on but the data is not getting to the mirror.

For more on SQL Server database mirroring

Top tips for database mirroring and more

Why have a witness in your database mirroring project?

While database mirroring with automatic failover is a great feature, there is a small hiccup. When a database fails over, any existing connections to the old primary server enter an invalid state and will need to reconnect. This is a problem mainly due to connection pooling -- the process uses a connection to execute and when done, ADO.NET keeps the connection in the pool so that it is available to another process to execute in SQL Server. The first time an invalid connection is used, you will get this error:

"A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

After you get this error and try to execute a command again, ADO.NET gets a fresh connection to the new primary server. While the first few application users will get an error, when they refresh the Web page it will start working again. Luckily, you can make your code a little more robust and write it to retry executing a command or to get data back if it fails on the first try. In this example, the code executes a command inside of a try/catch block:

 try
{  
 cn.Open();  
 cmd.ExecuteNonQuery();
}
catch
{  
 cn.Open();  
 cmd.ExecuteNonQuery();
}

If the ExecuteNonQuery() method fails in the TRY block, the code tries to do it again in the CATCH block. If the error occurred because of the problem described above, it should connect and execute OK in the CATCH block and the user should not notice any errors as a result of a database failing over to the mirror server. The following example is very similar, but it shows how to handle a case where data is returned to the application from SQL Server.

try
{
 dataAtapter.Fill(dataset);
}
catch
{  
 dataAtapter.Fill(dataset);
}


The code assumes the SqlDataAdapter and DataSet objects have already been declared and initialized. Again, we are doing the same thing -- if fetching data from SQL Server fails, we will give it one more try. This simple trick of retrying a failed command can make your application failover seamless to the user.

There is one more scenario and a potential problem you should know about. A connection string that is set up for SQL Server database mirroring could actually cause an error in the application when you remove mirroring in SQL Server. The error you get looks something like this:

"Server ServerB, database Northwind is not configured for database mirroring."

For a long time I was puzzled as to why I would sometimes get this error when mirroring was removed. As it turns out, you get this error if you remove mirroring and the active database ends up running on the server that's specified in the failover partner property (basically, the original mirror). If the database ends up running on the original primary that's been specified in the server property in the connection string, you do not get this error.

So using the example above, suppose ServerA crashes and the database failed over to ServerB. Since it will take a few days to fix ServerA, you remove mirroring because you don't want the transaction log on ServerB to grow too large (if a database is mirrored and mirroring is in a suspended or disconnected state, the transaction log doesn't truncate even after a log backup). If you used the connection string from the above example, you would get the error I just described.

But if it was ServerB that crashed and now you are running the database on ServerA with mirroring removed, that same connection string would work. So, at this point you have two choices. You can remove the failover partner property from the connection string and just have a regular connection string with ServerB set as the primary server. Once you fix ServerA and recreate mirroring, you will have to modify the connection string for automatic failover by adding back the failover partner. Your second option is to just reverse ServerA and ServerB so that ServerB is specified as the primary server. The advantage of doing it this way is that you do not have to go back and modify the connection string again once you re-establish mirroring.

This was first published in October 2012

Dig deeper on SQL Server High Availability, Scalability and Reliability

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close