Prerequisites:
Configure two secondary IPs for each cluster node elastic network interface. Then, use Remote Desktop Protocol (RDP) to connect as a Domain Administrator account to the cluster node instances. Finally, create a two-node Windows cluster and SQL Server Always On availability groups.
- Launch two Amazon Elastic Compute Cloud (Amazon EC2) Windows Server instances (Windows Server 2012 R2 or later) across Availability Zones inside a Virtual Private Cloud (VPC).
- Note that SQL Server 2014 64-bit Enterprise edition or later is required.
- Configure secondary Amazon Elastic Block Store (Amazon EBS) volumes to host SQL Server Master Data File, Log Data File, and SQL Backup files. It’s a best practice to choose Provisioned IOPS SSD (io1) EBS volumes for large SQL Server database workloads.
- Deploy the cluster nodes in private subnets. You can then use RDP to connect from a jump server to the cluster node instances.
- Configure security group inbound rules and Windows Firewall exceptions to allow the nodes to communicate in a restrictive environment.
- Active Directory (AD) domain controllers must have all necessary ports opened for the SQL nodes and witness to join the domain and authenticate against AD.
- Join the nodes to the domain before creating the Windows failover cluster. Verify that you are logged in using domain credentials before creating and configuring the cluster.
- Run the SQL DB instances with an AD service account.
- Create a SQL login with sysadmin permission using Windows domain authentication. Consult your Database Administrator for more information.
- Verify that the SQL browser is configured properly. This is required only for SQL Server named instances.
Configure the secondary IPs for each cluster node elastic network interface
Two secondary IPs are required for each cluster node eth0 elastic network interface.
Note: If you don’t plan to deploy a SQL Group Listener, add only one secondary IP for each cluster node elastic network interface.
1. Open the Amazon EC2 console, and then choose the AWS Region that will host your Always On cluster.
2. Choose Instances from the navigation pane, and then select your EC2 cluster instance.
3. On the Description tab, choose eth0.
4. Choose the Interface ID elastic network interface.
5. For Actions, choose Manage IP Addresses.
6. Choose Assign new IP. You can select a specific IP or leave the field as Auto-assign. Repeat this step to add a second new IP.
7. Choose Yes, Update.
8. Repeat steps 1-7 for the other EC2 instance that will participate in the cluster.
Create a two-node Windows cluster
1. Connect to your EC2 instance using RDP with a domain account that has local Administrator privileges on both nodes.
2. On the Windows Start menu, open Control Panel, and then choose Network and Sharing Center.
3. Choose Change adapter settings from the navigation pane.
4. Select your network connection, and then choose Change settings of this connection.
5. Select Internet Protocol Version 4 (TCP/IPv4), and then choose Properties.
6. Choose Advanced.
7. On the DNS tab, choose Append primary and connection specific DNS suffixes.
8. Choose Ok, choose Ok, and then choose Close.
9. Repeat steps 1-8 for the other EC2 instance that will participate in the cluster.
10. On each instance, install the cluster feature on the nodes from the Server Manager, or run the following PowerShell command:
Install-WindowsFeature –Name Failover-Clustering –IncludeManagementTools11. Open cmd as Administrator, and enter cluadmin.msc to open the Cluster Manager.
12. Open the context (right-click) menu for Failover Cluster Manager, and then choose Create Cluster.
13. Choose Next, and then choose Browse.
14. For Enter the object names to select, enter the cluster node hostnames, and then choose Ok.
15. Choose Next. You can now choose whether you want to validate the cluster. It’s a best practice to run a cluster validation, as Microsoft might not support your SQL cluster if it doesn’t pass validation. Choose Yes or No, and then choose Next.
16. For Cluster Name, enter a name, and then choose Next.
17. Clear Add all eligible storage to the cluster, and then choose Next.
18. When the cluster creation is complete, choose Finish.
Note: Cluster logs and reports are located at the following path:
%systemroot%\cluster\reports
19. In the Cluster Core Resources section of Cluster Manager, expand the entry for your new cluster.
20. Open the context (right-click) menu for the first IP Address entry, and then choose Properties. For IP Address, choose Static IP Address, and then enter one of the secondary IPs associated with eth0 elastic network interface. Choose Ok. Repeat this step for the second IP Address entry.
21. Open the context (right-click) menu for the cluster name, and then choose Bring Online.
Note: It’s a best practice to also configure a File Share Witness (FSW) to act as a tie breaker.
Create Always On availability groups
1. Open SQL Server Configuration Manager.
2. Open the context (right-click) menu for the SQL instance, and then choose Properties.
3. On the AlwaysOn High Availability tab, select Enable AlwaysOn Availability Groups, and then choose Apply.
4. Open the context (right-click) menu for the SQL instance, and then choose Restart.
5. Repeat steps 1-4 on the other cluster node part of the cluster.
6. Open Microsoft SQL Server Management Studio (SSMS).
7. Log in to one of the SQL instances with your Windows authenticated login that has access to the SQL instance.
Note: It’s a best practice to use the same MDF and LDF directory file paths across the SQL instances.
8. Create a test database. Open the context (right-click) menu for Databases, and then choose New Database.
Note: Be sure to use the Full recovery model on the Options page.
9. For Database name, enter a name, and then choose Ok.
10. Open the context (right-click) menu for the new database name, choose Tasks, and then choose Back Up.
For Backup type, choose Full.
11. Choose Ok, and then choose Ok.
12. Open the context (right-click) menu for Always On High Availability, and then choose New Availability Group Wizard.
13. Choose Next.
14. For Availability group name, enter a name, and then choose Next.
15. Select your database, and then choose Next.
16. A primary replica is already present in the Availability Replicas window. Choose Add Replica to create a secondary replica.
17. For Server name, enter a name for the secondary replica, and then choose Connect.
18. For Availability Mode, decide which availability mode you want, and then choose Synchronous commit or Asynchronous commit for each replica.
19. Choose Next.
20. Choose your data synchronization preference, and then choose Next.
21. When the validation has succeeded, choose Next.
Note: You can safely ignore Checking the listener configuration, as you’ll add it later.
22. Choose Finish, and then choose Close.
Add a SQL Group Listener
1. Open SSMS, and then expand Always On High Availability, Availability Groups, [primary replica name].
2. Open the context (right-click) menu for Availability Group Listeners, and then choose Add Listener.
For Listener DNS Name, enter a name.
For Port, enter 1433.
For Network Mode, choose Static IP.
3. Choose Add.
For IPv4 Address, enter the second secondary IP address from one of the cluster node instances, and then choose Ok. Repeat this step, using the second secondary IP address from the other cluster node instance.
4. Choose Ok.
Test failover
1. Using SSMS, open the context (right-click) menu for the primary replica on the navigation menu, and then choose Failover.
2. Choose Next, and then choose Next.
3. Choose Connect, and then choose Connect.
4. Choose Next, and then choose Finish. The primary replica will become the secondary replica after failover.
