How to Setup SQL Server Mirroring in Amazon EC2

I had to pull my rusty Sys Admin skills out and had to setup a SQL Server Failover solution. Given my current environment I decided that Mirroring in a Full Safety Mode was the best situation. Below is the setup I went with within EC2

m1.large Instance (Principal) (64-bit)
   Windows Server 2008 DataCenter
   SQL Server Standard 2008 /w SP2
m1.large Instance (Mirrored)
   windows Server 2008 DataCenter
   SQL Server Standard 2008 /w SP2
m1.small (Witness)
   windows Server 2008 DataCenter
   SQL Server Express 2008 w/ SP2


Before beginning please make sure all SQL Server instances above can see each other inside the EC2 environment by either their private IP address or private DNS Amazon assigns to them.
Note: You may have to modify your Amazon Security groups and/or firewall rules to do so.

On each instance I did the following:

• I turned Off Windows Firewall on all boxes since they were just communicating internally inside the EC2 environment. None of them were setup to be exposed via Amazon's Firewall
• I made sure all of my SQL Server installs had Mixed Authentication mode for the authentication setting.
• I made sure all SQL installs, including the SQL Express install had the same SQL account on all machines with the same username and password (Mostly just for ease of remembering)

Note: either way you need to make sure you have the appropriate SQL authentication/permissions when setting up the mirroring on all sql servers.

One thing to keep in mind is that when setting up mirroring, if your servers are not exposed via the amazon firewall via an Elastic IP Address you have attached to the instance, you will need to reconfigure mirroring each time you start and stop the instance. This is because each time you start and stop an instance in Amazon EC2, it changes the private DNS, private IP address and computer name Amazon assigns to it.

For me personally, I'm ok with that because I don't want the SQL Servers exposed in anyway except to each other and any other instance in the EC2 environment.

Next I needed to setup Mirroring of the databases.

• First, I took a FULL backup of the database I want to mirror on the host machine (principal)
• Second, I took a transaction log backup of the database I want to mirror on the host machine (principal)
• third, I logged into the machine that will be the mirrored server and restored the backup I just took with NO RECOVERY.
• Fourth, I also restored the transaction log with NO RECOVERY

You'll notice they will be in Restoring/Recovery mode on the SQL Server you are designating as the mirror.

Next, go back to the prinicipal server and setup the Mirroring

1. Right click the database and go to Properties
2. select Mirroring from the options on the left
3. Click on Configure Security
4. A Wizard will pop up
5. I chose to have a witness on the first screen
6. Click Next and leave the default options
7. Click Next and leave the default options for the Principal Server Instance screen

On the next screen for the Mirror Server Instance

1. Click the Connect Button
2. Type in either the private DNS name assigned to the instance in EC2, or the private IP address
3. Enter valid credentials on that SQL Server.

On the next screen for the Witness Server Instance

1. Click the Connect Button
2. Type in either the private DNS name assigned to the instance in EC2, or the private IP address
3. Enter valid credentials on that SQL Server.

On the Service Accounts screen

1. Leave the textboxes empty (we are not using domain or trusted domain accounts)
2. Review the Complete Wizard Screen and hit Finish
3. Now you should see succeed

Click Start Mirroring on the windows that pops up.
Answer yes to warning about lacking a FQDN
 -> to have FQDN in Amazon Ec2 for this type of setup all SQL servers would need to be attached to an elastic IP and exposed to each other, with DNS A records pointing to them.
 -> again, since all of these are locked down to just the EC2 environment and the instance themselves, I chose not to go this right.

SQL Server Mirroring should now be good to go.

Now that SQL Server Mirroring is setup, now you need to make your .NET application aware of it. Thankfully .NET has made this very simple by adding an attribute to the existing SQL connection string you already have for you application.

Within your SQL connection string add the attribute Failover Partner=yourmirroredserver;


Two main issues I ran into while configuring this:

• The EC2 instances could not see each other within the cloud environment (this took some security group and firewall modifications)
• I forgot to restore the backup on the Mirrored SQL Server without the NO RECOVERY OPTION.

Here are some links to help with the concept of SQL Server Mirroring:

Database Mirroring Witness
http://msdn.microsoft.com/en-us/library/ms175191(v=SQL.100).aspx

Forced Service (with Possible Data Loss)
http://msdn.microsoft.com/en-us/library/ms189977(v=SQL.100).aspx

Synchronous Database Mirroring (High-Safety Mode)
http://msdn.microsoft.com/en-us/library/ms179344(v=SQL.100).aspx

Database Mirroring in SQL Server 2005
http://technet.microsoft.com/en-us/library/cc917680.aspx

Explaining Failover Partner Attribute
http://blogs.msdn.com/b/spike/archive/2010/12/08/clarification-on-the-failover-partner-in-the-connectionstring-in-database-mirror-setup.aspx

This link is a great tutorial as it helped me figure out the mirroring concept and setup in the first place.
http://www.databasejournal.com/features/mssql/article.php/3828341/Database-Mirroring-in-SQL-Server-2008.htm