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?
Best Answer
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)