Answers: <p>Not impossible, very easy in fact with <a href="https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server?view=sql-server-2017" rel="nofollow noreferrer">read-only routing</a>. 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.</p>
  
Answers: CC BY-SA 4.0
Answers: 2018-05-11T13:47:29.283Z
Answers: 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?
Answers: Marshall Tigerus
Answers: CC BY-SA 4.0
Answers: 2018-05-11T14:09:27.017Z
Answers: Yep, there’s even [a wizard](https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/use-the-add-replica-to-availability-group-wizard-sql-server-management-studio?view=sql-server-2017)
Answers: Gaius
Answers: CC BY-SA 4.0
Answers: 2018-05-11T10:39:46.620Z
Answers: 2018-05-11T10:56:11.317Z
Answers: 2018-05-11T10:56:11.317Z
Answers: Gaius
Body: <p>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.  </p>
  
  <p>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.</p>
  
  <p>That said, things in the cloud for our DBs are out of the question at the moment. </p>
  
Tags: <load-balance><sql>
Title: Is it possible to set up Microsoft Sql Server in a scalable way?
UserDisplayName: Marshall Tigerus
ThreadText: # Is it possible to set up Microsoft Sql Server in a scalable way?
  
  (Asked by: username_0 on 2018-05-10)
  
  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.
  
  **Tags:** load-balance, sql
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2018-05-11. Score: 1
  
  Not impossible, very easy in fact with [read-only routing](https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server?view=sql-server-2017). 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.
  
  > **Comments:**
  > **username_0**: 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?  
  > **username_1**: Yep, there’s even [a wizard](https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/use-the-add-replica-to-availability-group-wizard-sql-server-management-studio?view=sql-server-2017)  
  
  ---
  
  