January 15, 2014 at 8:18 am
I need to design and implement a highly available SQL Server 2012 clustering on Amazon AWS. In the white paper presented by Amazon, they provided an example of Active/ Passive SQL Cluster. Do you know whether Active/ Active is also possible?
Thanks
January 20, 2014 at 6:13 am
If you are going Active/Active, then it may be worth looking at SQL P2P replication. We decided to use P2P instead of clustering when we moved to AWS.
P2P needs some care in order to minimise problems. We use a DNS alias to identify what we call the master node, and put all our updates through this node. The other node(s) can all be used for read-only queries, which is good for distributing the workload.
At failover, we update the DNS alias to point to a new master. We also have an automated process to update identity seed values on the new master node to be a bit higher than the high-water mark on the old master. After all this is done, and some regression tests are complete, we open up the new master to end-user access.
The advantages we see in P2P over clustering are:
a) Each P2P node is in a different AZ. This gives us very good high-availability capability. We run in Europe and after Amazon open a Europe East region we plan to put a P2P node in the new region which will further improve our HA.
b) We can do read-only queries on the passive nodes, and have distributed a lot of reporting workload in this way.
c) We make much better use of our compute capability than is possible with clustering.
We have also looked at AlwaysOn mirroring in place of P2P, but at present we use each P2P node as a publisher for further read-only scale-out within its AZ. If we used AlwaysOn we would have to include all our scale-out servers within the FCI cluster and put them all on Enterprise Edition. It is cheaper for us to stick with P2P and use Standard Edition for scale-out.
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
January 20, 2014 at 8:32 am
Thanks for the reply.
From the books online: Peer-to-peer replication is available only in Microsoft SQL Server 2012 Enterprise
Are you sure you can implement P2P replication in standard edition?
January 20, 2014 at 9:14 am
Sadly P2P requires Enterprise Edition
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply