PhillipBlanton.com

"Save me, oh God, from people who have no sense of humor."
— Ludlow Porch

Downsides to SQL Server Mirroring

I have been designing and developing applications targeting Microsoft SQL Server for about 14 years. I moved from Clipper and BTrieve to SQLServer '97 at Hewlett-Packard back in about 1998.

Today I am a senior solution architect with a large consulting firm, and am on a team that is migrating a large organization from physical hardware to VMWare. My job is to evaluate each of their more than fifty ASP.net applications, determine if they can be be made SQL Server mirroring aware with no (or minor) code changes, and craft a migration plan for these applications.

As part of my investigation, I have learned a lot about SQL Server Mirroring. One of the big differences between Mirroring and Clustering, is that mirroring is done on a database by database basis, while clustering is handled at the SQL Server instance level. I'm sure you have all heard or read that and think you understand it, but you may not competely appreciate the gravity of that statement. Let's analyze the situation using a real world scenario.

Suppose you have an ASP.net application that uses a single, read-write database for application data, and a separate read-write database for SQL Server session state management. This is not an uncommon situation. Though it is easy enough to install the SQL Server Session State management to your application database, many architects separate the app db from the session db. That's perfectly acceptable, as long as you realize what kind of challenges mirroring brings to the table.

In the event that you are using mirroring, and the application or session database fails, SQL Server will fail-over to the mirror for ONLY THAT SINGLE DATABASE. If your assumption is that the app db and session db are existing on the same server, then your application will find itself in an unstable state.

The solution is to provide a separate connection string in the config file FOR EACH DATABASE that it uses. This will allow applications that use ADO.NET or the Native .NET client, to provide a connection string for each database, that will provide for robust failover.