EC2 Database Server Recovery Strategy

Question

I plan to deploy my web application in EC2. I have several instances of a web server. I have 1 primary database instance. I have 1 database instance on failure. I need a strategy to redirect web servers to an instance of the failover database IP address when the primary database instance fails.

I was hoping I could use Elastic IP in my connection strings. But web servers cannot access the Eliver IP address. I have some brute force ideas to solve a problem. However, I am trying to find the most elegant solution.

I use all .Net and SQL Server. My connection strings are encrypted.

Does anyone have a strategy for discarding a database instance in EC2 using some form of automation or DNS configuration?

Please let me know.

Answer 1

http://alestic.com/2009/06/ec2-elastic-ip-internal

tells you how to use Elastic IP's public DNS.

Answer 2

Did not use EC2, but you also need:

(a) add your interface to some kind of user service mode that you define when switching IP; and the front panel takes the necessary steps to manage potential data integrity and data loss issues associated with the previous server, and the new server when it enters and leaves your user maintenance mode

OR, for a zero latency system:

(b) designing the system at the facility / relational and transaction level from scratch to support failure with zero downtime. This is not something you can point to quicjkly on any application.

(c) use some database support to automatically fail over. I do not know if SQL Server support for fault tolerance exists that is suitable for your application or is relevant here. I suggest adding the "sql-server" tag to the question to start searching for the right audience.

If Elastic IPs do not work (which sounds strange if you don't talk about it with EC2), you may need to instruct your interface for which the new database IP address will be used at the same time, as they say, to go from maintenance mode to normal mode.

Answer 3

If you are willing to shell out a little extra money, take a look at Rightscale tools ; they created custom server images and supporting tools that handle the transition to a database backup (among many other things). This link explains how to do this with MySQL, so hopefully you will find some principles even if it doesn't use SQL Server.

Answer 4

I always thought that there was such a possibility in the connection string

This is performed (but not yet verified) from How to add a Failover Partner to the connection string in VB.NET :

If you connect to ADO.NET or SQL Native Client in a database that is mirrored, your application can take advantage of the ability of drivers to automatically redirect connections when a database mirror backup is taking place. You must specify the start of the main server and database in the connection string and switch to another resource partner server.

Data Source=myServerAddress;Failover Partner=myMirrorServerAddress; Initial Catalog=myDataBase;Integrated Security=True; 

There are many other ways to write a connection string using database mirroring, this is just one example, indicating a switch to another resource functionality. You can combine this with other connecting lines available options.

Answer 5

To extend the gareth response, cloud management software typically solves this type of problem. RightScale is one of them, but you can try enStratus or Scalr (disclaimer: I work in Scalr). These tools provide fault tolerant solutions, for example:

• Backups: You can schedule automatic snapshots of an EBS volume containing data
• Fault-tolerant database: in the event of a failure, the slave master advances and the mounted storage will be switched if the failed master and the new master are in the same AZ or a snapshot taken with the volume

If you want to create your own solution, you can replicate the process described below that we use in Scalr:

• Is there a subordinate in the same AZ? If yes, advance this, switch EBS volumes (which are limited to one AZ), switch any ElasticIP can have, reconfigure replication of other slaves.
• If not, is there a slave fully replicated in another AZ? If so, promote it, then do it higher.
• If there is no slave in the same AZ and no slave is replicated to another AZ, and then creates a snapshot from the master volume, and use this snapshot to create a new volume in the AZ where the slave works. Then do the following.