Is it possible to set up Microsoft Sql Server in a scalable way?

Question

I am working on some architecture where we need to have a Microsoft Sql Server database that is scalable, with one writable master and many read-only slaves. Ideally I should be able to hit a load balancer of some kind, and if it is a write, it goes to the master, and if it is a read it is load balanced through the slaves. Some kind of eventual consistency would update the slaves over time to match the data on the master.

I'm fairly sure that AWS and MySQL are capable of doing this out of the box pretty simply. However, I've got little knowledge of the Microsoft Sql Server side of things. One of my fellow architects insists it is impossible, but was not able to back up that statement with anything. We are hosting our microservices in Azure currently, but our databases are supposed to be on prem in our datacenter. This is a flagship project, so suffice it to say if there's a cool thing out there that we can use for scaling, we can probably convince our DBAs and Ops to use it for this project.

That said, things in the cloud for our DBs are out of the question at the moment.

Answer 1

Not impossible, very easy in fact with read-only routing. Clients intending to be read-only specify it in their connect string, so this is per-session rather than per-statement. You can have up to 9 replicas in an availability group. With modern hardware and storage, that goes a long way to being “scalable” - 10’s of thousands of concurrent connections easily.