Configuring Sql Server AlwaysOn in AWS

This post primarily concentrates on setting up the listener for a SQL Server Availability Group in AWS in a multi-subnet cluster.  Just a warning, the listener does not function in AWS as you would expect on local instances.  Don’t assume this article solves listener issues.  It does not.  I’ll outline what the issues are with the listener in a later post.

I’m assuming you’ve already done the following:

  1. Installed Sql Server 2012 on a couple of EC2 instances located in different subnets.
  2. Created a few test databases (and done full backups) on one of said database EC2 instances.
  3. Created an availability group hosting said databases.

Before I begin, a shout out is deserved to Jeremiah Peschka (blog | twitter), as I’d never have gotten this figured without this post.

Create the listener

In your availability group, create your listener

image

Enter in the Listener DNS Name, enter in the port (I left the default port of 1433 for this demonstration to keep it simple), and select StaticIP for the Network Mode. 

image

Next, at the bottom of the New Availability Group Listener dialog, click the Add button.  This will bring up the Add IP Address dialog.  Enter in your static IP addresses.  If these IP addresses already exist, you’ll get this error message:

The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057.  The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid.  For information about this error code, see "System Error Codes" in the Windows Development documentation.
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator.
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (Microsoft SQL Server, Error: 41009)

 

You’ll want to create new IP addresses for your listener that are not being used by the failover cluster itself.  The IP addresses will be added to DNS automatically, as will be illustrated further down in the post.  For example, currently on my servers I have addresses of 192.168.10.70 & 192.168.10.50 for one node, and 192.168.11.70 & 192.168.11.50 for the second node.  For my IP addresses for the listener, I will add IP addresses of 192.168.10.77 & 192.168.11.77.

Obviously, you have to run this as an account that has the proper permissions to add entries to DNS. In my case, I had god rights.  You’ll probably want to be a bit more restrictive. 

image

Next, if you look in your DNS, you’ll see it added the listener name with both IP’s. 

image

Next, go to your AWS console.  In the console under your EC2 Dashboard under Network & Security, click the Network Interfaces. 

image

Select the network interface that is associated with your AMI and click Manage Private IP Addresses.

image

Now add the IP address that the listener created to said network interface.  Do this for both IP addresses on each of the network interfaces.  Only add the .10 to the AMI in the .10 subnet, and add only the .11 to the AMI in the .11 subnet.  (It won’t allow you to add an 11 to the 10 subnet, but just trying to be clear.  Winking smile)

image

Your listener should now be ready for testing.  This is where the fun really begins.  I’ll split the testing out to another post, as this one is getting pretty big.

Advertisements

4 Comments on “Configuring Sql Server AlwaysOn in AWS

  1. Hello,

    From all the guides I’ve read in regards to AlwaysOn in AWS, they missed out a very important step which was to add the IP’s to the Network Interface.

    Really helped.

    Cheers.

  2. Thank you spent 2 days troubleshooting. In my case I already have 3 IPs for each node and each eNI associated with EC2 has similar IPs.
    3 IPs (10.11.11.40, 10.11.11.41, 10.11.11.42)
    – Primary IP (stays with Machine) – 10.11.11.40
    – Secondary IP (used for WSFC setup) – 10.11.11.41
    – Secondary IP ( used for AG listener) – 10.11.11.42
    I was still getting this error. I can try making a DNS entry if this solves the problem.

    Thank you again

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: