This is my first time load-balancing with high-availability using Windows and SQL Server. Looking for some validation and input on my planned setup.

Now, typically when I want to create a HA load-balanced website, I'll separate the database out to a Multi-AZ RDS instance and then spawn a number of Ec2 instances that all point to the RDS. This has always worked fine with MySQL, but SQL Server RDS does not support Multi-AZ.

I read that SQL Server can be set up as a witness, which (correct me if I'm wrong) mirrors a database in real time unless it cannot reach the database, at which point it becomes the primary db. Will the setup below properly load-balance incoming requests while using the same database data? If the primary instance does go down at some point, and then comes back up, does it automatically "catch up" to the data from the server? Would you do this differently?

load balance setup


Based on the diagram you have provided you would then have a 3rd SQL Server box to allow for an automatic fail over of the mirrored database. See this link for a description of mirroring in an HA setup - http://msdn.microsoft.com/en-ca/library/ms179344(v=sql.105).aspx.

This setup will work as long as you have the 3rd SQL Server instance to act as the witness role. This will work a bit like the way Windows Clustering works where you will need to have the majority of servers online in order to keep the mirrored database online. If your primary server goes offline then the mirror (secondary\backup) server will take over the primary role. Once the server then comes back online, the database should synchronize itself.

Also another consideration for this setup is that your application only relies on the one database. Since mirroring is database specific rather then server specific there may be cases where only one database "flips" over to the secondary server. Where if the web application is reliant on multiple databases then extra consideration will need to be given on how to handle this (this is solved in SQL Server 2012 with availability groups though)

  • Ahhh... so the witness server doesn't do anything with the data, it just monitors the other DBs and takes action when one fails. Makes sense. – Alec Sanger Feb 18 '13 at 21:25
  • Yes - really it just provides an additional access point to ensure the server is network connected. For example, if the secondary server and witness go offline, then the primary will think it has lost connectivity and disable access to that database. But if only the secondary goes offline but the primary can still see the witness it will leave the database online. – Ian Chamberland Feb 18 '13 at 22:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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