July 16, 2014 at 12:44 pm
I am new to AWS, and I would like to understand how SQL Server autoscales on EC2. We are considering moving to EC2 from another hosting platform. From what I have read on the white papers when autoscaling is setup, Amazon increases the number of EC2 instances, thats fine with the IIS boxes can see how that works, more IIS boxes to manage the load. Obviously we separate SQL server form the IIS box, but how does autoscaling for SQL work you just can't spin up new SQL boxes, how would it manage the data across the boxes. Is the SQL end differenet ie you can't auto-scale in same way you just have to manage scalability as you would in house ?
July 28, 2014 at 4:28 am
This is something we have looked at a few times since we moved to AWS EC2 in 2012. So far we have not seen a way to make it work for our applications. For scale-out, our applications need to access a consistent set of data on all scale-out servers. Getting this set up and verified would take a number of hours even if everything was fully scripted. The main pain point is synchronising data on the auto-scale server with what is on the master server.
The only use cases that makes any sense is to auto scale for databases that only contain transient data or only contain static data. Any data set that needs to be permanent and can change as time goes by is not a suitable candidate for auto scale. None of our data fits these use cases.
The approach we have adopted for SQL Server is to host the database servers in multiple AZs, use P2P replication to maintain master data across the AZs, and use transactional replication to distribute data to permanent scale-out servers within each AZ. We load-balance our workload across all the AZs, but maintain enough SQL Server capacity in each AZ to meet 100% of our workload if a given AZ became unusable. We accept there are periods each day where not all SQL Server instances are needed, but for us the cost of refactoring how our applications work to be able to auto-scale the database tier does not make business sense.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply