Shulou(Shulou.com)06/01 Report--

Case sharing: database mirroring failover failed

For critical databases, we configured synchronous database mirroring with a witness to allow automatic failover. Everything worked fine until there was a sudden power outage in the data center. Database mirroring failed over, but the operation and maintenance staff reported that the application hung. When we switched back manually, the application worked properly. Why didn't the application fail over as well?

This is a reasonable common problem with database mirroring, and production applications like this fail because they are not tested for failover after mirrored deployment. We found it tricky after the failed failover.

In order to avoid production application downtime, we copied the online mirror environment in the test environment. After confirming that the application and database mirroring are working properly, we shut down the primary server and the application hangs completely.

We checked that the mirror server has successfully initialized a failover and is online as the primary server. We also checked that the mirror database is online and can be accessed locally by the new primary server, and the primary server can be accessed by remote clients as if it were used by the application.

Then, let's examine the application. Having a chat with the developer, he confirms that the application is using ADO.NET to connect to SQL Server and uses explicit client redirection to specify the mirror server name in the ConnectionString property of SqlConnection. (by the way, using explicit client redirection is always better than implicit client restart, implicitly relying on the name of the mirror server that is automatically cached when the client connection is established)

So why didn't the application fail over?

I delved into how the application handled connection failures and found that there was no code to deal with connection failures at all! Basically, when the application initializes, the application opens a connection to the SQL Server and never attempts to reconnect.

It turns out that although DBA deploys database mirroring, high availability has not been discussed with the developer, so the application code has not changed. After development and repair, the application connection layer can deal with connection failures and perform reconnection logic, and works perfectly after database failover.

This story tells us that we need to rebuild in response to connection failures and reconnections in order for the failover to work correctly. In this case, if we tried database mirroring failover in the test environment before deploying in the production environment, the client would have found this problem.

For specific principles and application code examples, please refer to the following articles:

Connect the client to a database mirroring session (https://msdn.microsoft.com/zh-cn/library/ms175484.aspx)

Implementing Application Failover with Database Mirroring (https://technet.microsoft.com/en-us/library/cc917713.aspx#EDAA)

Sample Application to test database mirroring failover (https://blogs.msdn.microsoft.com/grahamk/2009/01/16/sample-application-to-test-database-mirroring-failover/)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.