Clustering Microsoft SQL Server

on

Amazon Web Services

using

Zadara Storage

 

For more information; support@zadarastorage.com

This document describes how to use Zadara supplied iSCSI volumes attached to AWS EC2 instances to build a traditional SQL Server failover cluster across availability zones in a VPC. At AWS Zadara uses redundant, low latency Direct Connect interconnects to supply traditional, high performance, highly available iSCSI LUNs to EC2 instances.

Audience

This document is designed for an audience familiar with Zadara Storage, AWS EC2 and VPCs, Microsoft SQL Server, and Microsoft Failover Clustering.

Prerequisites

• An AWS account.
  • Click here to sign up.
• An IAM role with permissions to run ‘assign-private-ip-addresses’ and ‘associate-address’
  • Click here for the policy
• A Zadara Storage account.
  • Click here to sign up.
• A Virtual Private Cloud (VPC) with two public subnets.
  • For VPC documentation, click here.
• A connection between your VPC and Zadara storage.
  • For setup by step directions, click here
• A Zadara Virtual Private Storage Array
  • For setup by step directions, click here
• Two free Elastic Network Interfaces (ENI) with a EIP attached to each. Make note of the Allocation ID of each EIP.
  • Creating a ENI
  • Allocating an EIP
• An iSCSI LUN for the quorum device and additional LUNs for SQL Server data, logs and indexes.
  • For setup by step directions, click here

Getting started

Start by launching two Windows 2008 Datacenter edition AMIs (as of 7/2014 ami-c77932c6 in us-east–1), or Windows 2012 Standard edition AMIs (ami–21622920), or their variants with SQL Server Standard installed, one in each of your public subnets. Make sure to attach the role you created to each instance, also attach one of the ENIs to each instance. Make note of what ENI you attached to each instance, you will need to know this for the next step.

On each instance download two scripts to ’c:' -

https://s3.amazonaws.com/zadarastorage-public/AWS_Win2008_Cluster/GenericScriptRes.vbs

https://s3.amazonaws.com/zadarastorage-public/AWS_Win2008_Cluster/move_ips_role.bat

Use notepad to modify the ‘move_ips_role.bat’ script on each instance-

On line 4 set ‘NW_IFACE’ = the ENI-id of the ENI attached to the instance.
On line 7 set ‘IP_ADDR’ = a RFC 1918 address that would be valid in your VPC. This value should be the same on both instances.
On line 10 set ‘ALLOC_ID’ = the Allocation ID of the EIP attached to the local ENI.

Save the modified scripts.

Create a Microsoft Failover Cluster

Use your Zadara VPSA to create and zone at least two iSCSI LUNs to your Windows instances, 1 for the quorum device and the other for SQL Server.

Windows 2008

http://technet.microsoft.com/en-us/library/cc755009.aspx

Windows 2012

http://technet.microsoft.com/en-us/library/dn505754.aspx

On your new cluster create a Generic Script Resource, referencing ‘c:\GenericScriptRes.vbs’.

http://msdn.microsoft.com/en-us/library/aa373089(v=vs.85).aspx

Now when the cluster fails over the AWS EC2 IPs will moved automatically.