Case sharing: Database mirroring fail over

Case sharing: Database mirroring fail over

For critical databases, we configured synchronous database mirroring with a witness to allow automatic failover. Everything works until there is a sudden power outage in the data center. Database mirroring performed a failover, but operations feedback said the application was suspended. When we switch back manually, the application works again. Why doesn't the application also fail over?

This is a reasonable common problem with database mirroring, where production applications like this fail because failover testing is not done after mirroring is deployed. We find it tricky after a failed failover.

To avoid downtime in production applications, we have replicated the mirrored environment on the line in the test environment. After confirming that the application and database mirroring are working properly, we shut down the primary server and the application is completely suspended.

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 just as it is used by the application.

Then we'll check the application. and development chat, he confirms that the application is using ADO to connect to SQL Server and uses explicit client redirection to specify the mirror server name in the SqlConnection ConnectionString property. (By the way, using explicit client-side redirection is always better than implicit client-side redirection, implicitly relying on the name of the mirror server that was automatically cached when the client connection was established)

So why doesn't the application fail over?

I delve deeper into how the application handles the connection failure and discover that there is no code to handle the connection failure at all! Basically, when the application initializes, the app opens a connection to SQL Server and never attempts to reconnect.

The results showed that the application code did not change, although the DBA deployed database mirroring and did not discuss excessive availability with development. After a repair is developed, the application connection layer can handle connection failures and perform reconnection logic, which works perfectly after a database failover.

This story tells us that we need to rebuild the connection failures and reconnection that occur to make failover work correctly. In this case, if we had attempted a database mirroring failover in the test environment before we deployed in the production environment, the client would have found the problem.

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

• connecting a 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/)

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1905921

Case sharing: Database mirroring fail over