2

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.

1 Answer 1

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.

2
  • is it possible to, for example, start with 1 write and 1 read replica and then as demand increases bring new read replicas online up to the max of 9? May 11, 2018 at 13:47
  • Yep, there’s even a wizard
    – Gaius
    May 11, 2018 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.