Coding for seamless SQL Server database 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.
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
This was first published in October 2012
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.
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.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation