SQL server Read scaling on Amazon AWS
Let me know if this is a good idea.
- We create a small server that essentially is running all the time. This is the one to which we would be pushing data. (Master)
- Create an AMI that acts as a mirror or Replica from the master. (secondary)
- Create an Elastic Load balancer with the primary server running behind it all the time.
- Create an auto scale group which launches secondary servers when cpu load increases to 50% and kills when cpu drops to 10%
- On-demand secondary servers are launched, syncs to primary, gets diff and is ready for answering queries.
a) Can ELB be used to load balance queries for SQL server ? b) What kind of replication should I use transactional / merge ? c) I am looking into doing this essentially for read scaling ?