2

I am trying to setup a highly-available DB architecture in an EC2 servers. We are forced to use SQL 2005 for now.

What kind of solutions would work in this context? Has anyone done it?

I can't find a way to setup an active - active cluster in AWS. My other idea is using replication, but we would need an NLB or something in front to swap between DB servers seamlessly.

I'm sure this is a problem other people have had to solve, what have others done?

Jorge Guzman
  • 337
  • 2
  • 4
  • 14

2 Answers2

3

Afaik EC2 does not offer Windows clusters, and as such you will not be able to install a SQL Server Cluster. This leaves you with database mirroring or replication, which are both deployable on EC2 for high availability and disaster recoverability.

But your description of the 'solution' (you meantion 'NLB' and the mytical 'active-active') would indicate that you are not actually thinking at high availability, but instead you are thinking at load balancing solution with scale out. Such is not achievable with SQL Server clustering, nor with Database Mirroring. It is achievable with Peer-To-Peer Transactional Replication which is an Enterprise Edition only feature, not available in the Standard Edition offered on EC2. You would have to purchase you own EE licenses and run them on EC2.

When you add all these up, and consider the quite bad IO performance ESB storage offers, you will likely realize that for SQL Server high availability in the cloud, SQL Azure is actually significantly better.

Remus Rusanu
  • 8,283
  • 1
  • 21
  • 23
  • Thank you Remus. I don't necessarily need an NLB and our main concern is high-availability much more than load balancing. With database mirroring in Amazon, do you need some funky DNS assignation. How does Amazon allow for the DNS to failover to the secondary in case of issues? – Jorge Guzman Apr 25 '11 at 13:19
  • Why would you need funky DNS? You just do ordinary DNS and don't try to mask the failover behind DNS change. Use client connection string that is mirroring aware (`failoverpartner=...`). – Remus Rusanu Apr 25 '11 at 14:53
  • thank you! this is exactly what I needed, now off to implement a solution! :) Thanks again – Jorge Guzman Apr 25 '11 at 17:13
2

I've setup mirroring within EC2 using SQL Server and it works well.

Clustering isn't available within EC2, unless you get into some very funky configs.

For example, I could take 2 servers which both run StarWind's iSCSI target software and mirror between each other. Then I could setup two machines running SQL and cluster them using iSCSI storage from the two StarWind machines.

Would this be supported by anyone, probably not. Would it be possible, yes.

mrdenny
  • 27,174
  • 4
  • 41
  • 69
  • thanks! may I ask how did you setup the DNS for the mirror within Amazon? Thanks again – Jorge Guzman Apr 25 '11 at 13:22
  • 1
    I'm not sure if anything special was done to setup DNS. I just setup the mirroring and told them how to do the connection string with auto failover. – mrdenny Apr 25 '11 at 16:14