There are so a lot more patterns of high availability architectures other than load balancing.
Distributed Queues, Pub/Sub, Gossip just a few that come to mind.
In your example, you are using what is called a classical three-tier web architecture - a Load Balancer + Stateless Nodes + Scalable Storage. The most interesting part of HA setup in a three-tier web architecture is HA setup of the persistent storage component. It looks like you actually haven't figured that out that piece yet and are waiting for a vendor (Microsoft) to solve this for you.
You can improve availability of your persistent storage (MSSQL) in several simple (or not so simple ways):
1. Use a SQL proxy load balancer (or a cluster setup) - a similar load balancer HA pattern you are already using
2. Shard. You will scale writes and significantly reduce the probably of your system becoming completely unavailable.
Distributed Queues, Pub/Sub, Gossip just a few that come to mind.
In your example, you are using what is called a classical three-tier web architecture - a Load Balancer + Stateless Nodes + Scalable Storage. The most interesting part of HA setup in a three-tier web architecture is HA setup of the persistent storage component. It looks like you actually haven't figured that out that piece yet and are waiting for a vendor (Microsoft) to solve this for you.
You can improve availability of your persistent storage (MSSQL) in several simple (or not so simple ways):
1. Use a SQL proxy load balancer (or a cluster setup) - a similar load balancer HA pattern you are already using
2. Shard. You will scale writes and significantly reduce the probably of your system becoming completely unavailable.