Deploy SQL Server on Amazon EC2

To launch Microsoft SQL Server using Amazon Elastic Compute Cloud (Amazon EC2) instances with Windows Server, perform the following steps according to your use case.

New SQL environment deployments are classified under three categories:

• SQL Server standalone

• SQL Server Failover Cluster Instances (FCI)

• SQL Server Always On availability groups (AG)

Considerations

Before you launch SQL Server on your instance, consider the following:

• If you use an AWS provided AMI, you must initially manage SQL Server as the local administrator. For more information, see Connect to Microsoft SQL Server on Amazon EC2.

• The built-in availability form of clustering in Windows Server is activated by a feature named Failover Clustering. This feature allows you to build a Windows Server Failover Cluster (WSFC) to use with an availability group or failover cluster instances (FCI).

• Always On is an umbrella term for the availability features in SQL Server, and the term covers both availability groups and FCIs. Always On isn't the name of the Always On availability group (AG) feature.

• The major difference between FCI and AG is that all FCIs require some sort of shared storage, even if it's provided through networking. The FCI's resources can be run and owned by one node at any given time. AG doesn't require that shared storage is also highly available. It's a best practice to have replicas that are local in one data center for high availability, and remote ones in other data centers for disaster recovery, each with separate storage.

• An availability group also has another component called the listener. The listener allows applications and end users to connect without needing to know which SQL Server instance is hosting the primary replica. Each availability group has its own listener.

Deployment options

Use one of the following options to deploy SQL Server on Amazon EC2.

Deploy SQL Server on Amazon EC2 with AWS Launch Wizard

AWS Launch Wizard is a service that guides you through the sizing, configuration, and deployment of enterprise applications following AWS Cloud best practices. AWS Launch Wizard for SQL Server supports both high availability and single instance deployments according to AWS and SQL Server best practices. For more information, see the AWS Launch Wizard for SQL Server User Guide.

Always On availability groups (AG)

Deploy your SQL Server Always On availability groups with primary and secondary replicas for database level protection. Each replica is hosted by a SQL Server instance with its own local storage.

Always On Failover Cluster Instances (SQL FCI)

Deploy SQL Server Always On using Failover Cluster Instances (FCI) for instance-level protection. A single SQL Server instance is installed across Windows Server Failover Clustering (WSFC) nodes to ensure high availability and storage sharing.

Launch Wizard uses Amazon FSx to provide the following shared storage options required for SQL FCI deployments:

• Amazon FSx for NetApp ONTAP using Microsoft iSCSI endpoint

• Amazon FSx for Windows File Server using SMB 3.0 continuously available Windows file share

For more information on how to deploy SQL Server with Launch Wizard, see Deploy an application with AWS Launch Wizard for SQL Server on Windows in the AWS Launch Wizard User Guide.

Deploy SQL Server standalone

For a SQL Server standalone deployment, you can use one of the license-include AMIs provided by AWS or by using your own licensed media. For a list of SQL Server AMIs provided by AWS, see Windows AMIs. For more information on licensing options, see Understand licensing options and considerations for Microsoft SQL Server on Amazon EC2.

Deploy SQL Server failover cluster instances (FCIs)

Failover cluster instances (FCIs) provide availability for the entire installation of SQL Server known as an instance. Everything that is included in the instance, such as databases, SQL Server Agent jobs, and linked servers, move to a different server when the underlying server fails.

You can use AWS Launch Wizard to deploy SQL Server FCIs in the AWS Cloud. Launch Wizard identifies the AWS resources to automatically provision the SQL Server databases based on your use case. For more information, see Get started with AWS Launch Wizard for SQL Server.

You can reference the following AWS blogs to manually deploy SQL Server FCIs:

• (Amazon FSx) Deploy a SQL Server FCI using SMB 3.0 Continuously Available File Shares (CAFS) as shared storage

• (Amazon FSx) Deploy a SQL Server FCI using Microsoft iSCSI Initiator as shared storage

• (Amazon EBS) Deploy SQL Server FCI using Amazon EBS Multi-Attach with Persistent Reservations (MAPR)

Deploy SQL Server Always On availability groups (AG)

Always on availability groups provide high availability and disaster recovery of user databases through data replication. Availability groups can also distribute read operations amongst member nodes.

You can use AWS Launch Wizard to deploy a SQL Server Always On availability group in the AWS Cloud. Launch Wizard identifies the AWS resources to automatically provision the SQL Server databases based on your use case. For more information, see Get started with AWS Launch Wizard for SQL Server.

To manually deploy a SQL Server Always On availability group, perform the following steps:

Prerequisites

Before you manually deploy a SQL Server Always On availability group, you must perform the following prerequisites.

• Launch two Amazon EC2 instances with Windows Server 2016 or later and SQL Server 2016 or later Enterprise edition across two Availability Zones within an Amazon VPC. If the deployment is for testing purposes only, you can consider using SQL Server Developer edition.

• Configure secondary Amazon EBS volumes to host SQL Server Master Data File (MDF), Log Data File (LDF), and SQL Backup files (.bak). For more information on the volume types that you can use, see Amazon EBS volume types in the Amazon Elastic Compute Cloud User Guide.

• Deploy the cluster nodes in private subnets. You can then use Remote Desktop Protocol (RDP) to connect from a jump server to the cluster node instances.

• Configure inbound security group rules and Windows firewall exceptions to allow the nodes to communicate in a restrictive environment.

• Open all necessary ports for Active Directory domain controllers so that the SQL nodes and witness can join the domain and authenticate against Active Directory. For more information, see Active Directory and Active Directory Domain Services Port Requirements in the Microsoft documentation.

• Join the nodes to the domain before you create the Windows failover cluster. Ensure that you are logged in with domain credentials before you create and configure the cluster.

• Run the SQL Database instances with an Active Directory service account.

• Create a SQL login with sysadmin permissions using Windows domain authentication. Consult with your database administrator for details. For more information, see Create a login using SSMS for SQL Server in the Microsoft documentation.

• Properly configure the SQL browser for SQL Server named instances.

Configure the secondary IPs for each cluster node elastic network interface

Two secondary IP addresses are required for each cluster node elastic network interface.

Note

If you do not plan to deploy a SQL Group Listener, add only one secondary IP address for each cluster node elastic network interface.

1. Navigate to the Amazon EC2 console and choose the AWS Region where you want to host your Always On cluster.

2. Choose Instances from the left navigation pane, and then select your Amazon EC2 cluster instance.

3. Choose the Networking tab.

4. Under Network interfaces, select the network interface and then choose Actions > Manage IP addresses.

5. Choose the network interface Id to open the expandable section, and then choose Assign new IP address. You can enter a specific IP address or keep the default entry as Auto-assign. Repeat this step to add a second new IP address.

6. Choose Save > Confirm.

7. Repeat steps 1 through 7 for the other Amazon EC2 instance that will be included in the cluster.

Create a two-node Windows cluster

Perform the following steps to create a two-node Windows cluster.

1. Connect to your Amazon EC2 instance with RDP, using a domain account with local administrator permissions on both nodes.

2. On the Windows Start menu, open Control Panel, and then choose Network and Internet > Network and Sharing Center.

3. Choose Change adapter settings from the left navigation pane.

4. Choose your network connection, and then choose Change settings of this connection.

5. Choose Internet Protocol Version 4 TCP/IPV4), and then choose Properties.

6. Choose Advanced.

7. Under the DNS tab, choose Append primary and connection specific DNS suffixes.

8. Choose OK > OK > Close.

9. Repeat steps 1 through 8 for the other Amazon EC2 instance to include in the cluster.

10. On each instance, install the cluster feature on the nodes from the Server Manager, or run the following Windows PowerShell command:

    Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools

11. Open the command line as an administrator and enter cluadmin.msc top open the Cluster Manager.

12. Open the context menu (right-click) for Failover Cluster Manager, and then choose Create Cluster.

13. Choose Next > 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 to validate the cluster. We recommend that you perform a cluster validation. If the cluster does not pass validation Microsoft may be unable to provide technical support for your SQL cluster. Choose Yes or No, and then choose Next.

16. Enter a Cluster 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 %systemroot%\cluster\reports.

19. In the Cluster Core Resources section of Cluster Manager, expand the entry for your new cluster.

20. Open the context menu (right-click) for the first IP address entry, and then choose Properties. For IP Address , choose Static IP Address, and then enter one of the secondary IP addresses associated with the eth0 elastic network interface. Choose OK. Repeat this step for the second IP address entry.

21. Open the context menu (right-click) for the cluster name, and then choose Bring Online.

Note

We recommend that you configure a File Share Witness (FSW) in addition to your cluster to act as a tie-breaker. You can also use Amazon FSx for Windows File Server with Microsoft SQL Server.

Create Always On availability groups

Perform the following steps to create Always On availability groups.

1. Open SQL Server Configuration Manager.

2. Open the context menu (right-click) 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 menu (right-click) for the SQL instance, and then choose Restart.

5. Repeat steps 1 through 4 on the other cluster node to include in 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

   We recommend that you use the same MDF and LDF directory file paths across the SQL instances.

8. Create a test database. Open the context menu (right-click) for Databases, and then choose New Database.

   Note

   Make sure that you use the Full recovery model on the Options page.

9. Enter a Database name, and then choose OK.

10. Open the context menu (right-click) for the new database name, choose Tasks, and then choose Back Up For Backup type, choose Full.

11. Choose OK > OK.

12. Open the context menu (right-click) for Always On High Availability and then choose New Availability Group Wizard.

13. Choose Next.

14. Enter an Availability group 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. Enter a Server name for the secondary replica and then choose Connect.

18. Decide which Availability Mode you want to use for each replica, and then choose either Synchronous commit or Asynchronous commit.

19. Choose Next.

20. Choose your data synchronization preference, and then choose Next.

21. When the validation is successful, choose Next.

    Note

    You can safely ignore Checking the listener configuration because you will add it later.

22. Choose Finish > Close.

Add a SQL Group Listener

Perform the following steps to add a SQL Group Listener.

1. Open SQL Server Management Studio (SSMS) and expand Always On High Availability, Availability Groups, <primary replica name>.

2. Open the context menu (right-click) for Availability Group Listeners and then choose Add Listener. Enter a DNS Name.

3. Enter Port 1433.

4. Choose Static IP for Network Mode.

5. Choose Add.

   For the 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.

6. Choose OK.

Note

If you receive errors when you add a SQL Group Listener, you may be missing permissions. For troubleshooting see:

• Troubleshooting AlwaysOn availability group listener creation in SQL Server 2012

• Create Availability Group Listener Fails with Message 19471, ‘The WSFC cluster could not bring the Network Name resource online’

Test failover

1. From SSMS, open the context menu (right-click) for the primary replica on the navigation menu, and then choose Failover.

2. Choose Next > Next.

3. Choose Connect > Connect.

4. Choose Next, and then choose Finish. The primary replica will become the secondary replica after failover.