Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I'm looking at options for high-availability for my SQL Server-powered application.

The requirements are:

  • HA protection from storage failure.
  • Data accessibility when one of the DB servers is undergoing software updates (e.g. planned outage for Windows Update / SQL Server service-packs).
  • Must not involve much in the way of hardware procurement.
  • The application is an ASP.NET web application.
  • The web application's users have their own database instances.

I've seen two main options: SQL Server failover clustering, and SQL Server mirroring.

I understand that SQL Server Failover Clustering requires the purchasing of a shared disk array and doesn't offer any protection if the shared storage goes down (so the documentation recommends to set up a Mirroring between two clusters).

Database Mirroring seems the cheaper option (as it only requires two database servers and a simple witness box) - but I've heard it doesn't work well when you have a large number of databases. The application I'm developing involves giving each client their own database for their application - there could be hundreds of databases. Setting up the mirroring is no problem thanks to the automation systems we have in place.

My final point concerns how failover works with respect to client connections - SQL Server Failover Clustering uses MSCS which means that the cluster is invisible to clients - a connection attempt might fail during the failover, but a simple reconnect will have it working again. However mirroring, as far as I know, requires that the client be aware of the mirrored partners: if the client cannot connect to the primary server then it tries the secondary server.

I'm wondering how this work with respect to Connection Pooling in ASP.NET applications - does the client connection failovering mean that there's a potential 2-second (assuming 2000ms TCP timeout policy) pause when the connection pool tries the primary server on every connection attempt?

I read somewhere that Mirroring can be used on top of MSCS which means that the client does not need to be aware of mirroring (so there wouldn't be any potential delays during connection, and also that no changes would need to be made to the client, not even the connection string) - however I'm finding it hard to get documentation or white papers on this approach. But if true, then it means the best method is then Mirroring (for HA) with MSCS (for client ignorance and connection performance).

...but how does this scale to a server instance that might contain hundreds of mirrored databases?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

You're right in that mirroring and clustering can be used together, but I think your conception of how it works is a bit off. If you want your application to be mirroring-aware, you have to set the connection string such that it has the failover partner specified in it. Now, if you have a cluster failover, what happens depends on how you have mirroring set up. If you have it set to automatic failover, the mirror will take over and your application will connect to the mirroring partner. If you have it set to manual failover, one of the other nodes in the cluster for the primary will start the SQL service and the primary will continue to serve traffic. Check out http://technet.microsoft.com/en-us/library/ms191309.aspx for more info.

As an aside, SQL 2012 introduced a new way to do this called "Always On". It might solve your problems with less effort.

share|improve this answer
    
SQL Server 2012's "AlwaysOn" feature seems exactly what I'm looking for - it combines mirroring with MSCS for client-invisible resiliancy. Thanks. –  Dai Apr 12 '12 at 15:53
    
One of the things about mirroring is that each mirrored database consumes 2 or 3 sql server threads from a limited (and shared with other things) thread pool, making it hard to get "hundreds of databases" all mirrored on a single instance. See KB article 2001270. I haven't been able to find anything about AlwaysOn and thread usage with some quick googling, but I would be very careful before I committed to putting many databases that need to be mirrored or "AlwaysOn-ed" onto a single instance. –  darin strait Apr 12 '12 at 19:27
    
I have used mirroring on instances with 100+ databases and the thread issue is indeed a problem, but a quick configuration change to increase the threads will fix the problem. FYI, AlwaysOn is an Enterprise Edition only feature, but it is a very nice solution. –  Jason Cumberland Apr 13 '12 at 17:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.