How do I launch Microsoft SQL Server on an EC2 Windows instance?

Last updated: 2021-06-22

I want to launch Microsoft SQL Server on my Amazon Elastic Compute Cloud (Amazon EC2) Windows instance. How can I do this?

Short description

New SQL environment deployments are classified under three categories:

  • SQL Server stand alone
  • SQL Server failover cluster instances (FCI)
  • SQL Server Always On availability groups

Before launching SQL Server on your instance, keep the following in mind:

  • The built-in availability form of clustering in Windows Server is enabled 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 FCI.
  • Always On is an umbrella term for the availability features in SQL Server and covers both availability groups and FCIs. Always On isn't the name of the 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.
  • For a list of supported versions and minimum requirements, see Using SQL Server in Windows 8 and later versions of Windows operating system.

Resolution

Deploy SQL Server standalone

For SQL Server standalone deployment, you can use one of the AMIs provided by AWS with an included SQL license. You must use the version of SQL that comes with the AMI. However, you can customize it for your needs using the setup included in the AMI. For more information, see SQL Server AMIs provided by AWS.

Deploy SQL Server FCI

FCIs provide availability for the entire installation of SQL Server, known as an instance. This means that everything inside the instance, including databases, SQL Server Agent jobs, linked servers, and so on, move to another server if the underlying server fails.

To provide a shared storage for FCI use one of the following solutions:

To deploy SQL Server FCI, do the following:

Before you begin, deploy Microsoft S2D or AWS FSx share using the instructions provided in the preceding section.

After deploying a two node cluster, deploy SQL Server on to it using the following steps:

  1. Connect to the first virtual machine by using RDP.
  2. In Failover Cluster Manager, make sure that all core cluster resources are on the first virtual machine. If necessary, move all resources to that virtual machine.
  3. Locate the installation media. If the virtual machine uses an Amazon Machine Image (AMI), then the media is located at C:\SQLServerSetup\Setup media.
  4. Select Setup. In the SQL Server Installation Center, select Installation.
  5. Select New SQL Server failover cluster installation. Follow the instructions in the wizard to install the SQL Server FCI.
    Note: The FCI data directories need to be on clustered storage. With Storage Spaces Direct, it's not a shared disk. Instead, its a mount point to a volume on each server. Storage Spaces Direct synchronizes the volume between both nodes. The volume is presented to the cluster as a CSV. Use the CSV mount point for the data directories.
  6. After you complete the instructions in the wizard, setup installs a SQL Server FCI on the first node. Connect to the second node using RDP.
  7. Open the SQL Server Installation Center, and then select Installation.
  8. Select Add node to a SQL Server failover cluster. Follow the instructions in the wizard to install SQL Server and add the server to the FCI.

Deploy SQL Server Always On availability group (AG)

Connect to SQL Server

After deploying SQL Server on your instance, you can connect to it using one of the following tools: