Load balancing and failover with SQL 2016 and IIS on AWS

Question

I apologize if this question is too general, but i just wanted a sanity check for a setup I've been planning.

Right now i have an application that runs on an AWS server, with SQL and IIS both installed on the same server with about 4 GB RAM. Nothing big, and we seem to be able to handle about 100 concurrent users comfortably.

I'm looking to scale out to 1000 concurrent users, as well as introduce some disaster recovery. The setup I'm considering (all in AWS mind you) is below:

• IIS1 : IIS server, running windows 2016 set up in a web farm
• IIS2 : IIS server, running windows 2016 set up in a web farm
• SQL1 : SQL server, running windows 2016 and SQL 2016 set up with Always on Availability as the primary
• SQL2 : SQL server, running windows 2016 and SQL 2016 set up with Always on Availability as the unreadable secondary (for failover)
• SQL3 : SQL server, running windows 2016 and SQL 2016 set up with Always on Availability as the readable secondary (for load balancing)
• DC This is just a general domain controller to keep all of the associated servers linked. This server could possibly also have the IIS load balancing installed on it, or maybe i would just have a 3rd IIS server to do that separately.

Looking to up the SQL servers to 16 GB RAM, and probably similar settings for the IIS servers as well. Not expecting a whole lot of traffic on the DC, unless it is the web farm load balancer.

So my main question is, does this setup seem reasonable? Am I missing anything that i should be adding? Part of my thinking is that if this proves to be not enough, I can either add another IIS server to the farm, or similar to the SQL server, based on whereever I find my bottleneck. Any thoughts would be appreciated.

Answer 1

That SQL Server setup is a little expensive. Because you want 3 members in the Availability Group, and you want to offload reads to a secondary, that means you're going to need to license at least 2 of them with SQL Server Enterprise Edition.

I'd pencil out how expensive this setup is going to be, and then have a discussion with management about your RPO and RTO goals. (Disclaimer: my blog post about that topic.)

Once you look at the monthly costs of those SQL Servers, something a little simpler like Amazon RDS SQL Server might make more sense - especially if you don't really need to offload reads to a secondary server.