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?