Deploy a High Availability SQL FCI on Amazon EC2

Create SQL FCI using AWS Launch Wizard Deployment Steps

• Step 1 : Review and create permissions

  If it does not exist already, AWS Launch Wizard tries to create the IAM Role name AmazonEC2RoleForLaunchWizard which comprises of the following two IAM managed policies:
  

  • AmazonSSMManagedInstanceCore &
  • AmazonEC2RolePolicyForLaunchWizard

  On the review permissions screen and on the prompt for IAM role creation check the permissions and roles including role name and role policy and click next.
  

   

  

• Step 2: Create application settings

  Next we will define the configuration and settings for the SQL Server FCI deployment using AWS Launch Wizard.

  a. Select the following options
  

  • Windows Operating System
  • High Availability Deployment
  • Always On Fail Over Cluster Instances
  • Storage Type - Amazon FSx for Windows File Server

  Also give your deployment a name e.g.  SQLFCIMAZ

  

   

  b. Create an SNS Topic as shown below by specifying a topic name (e.g. SQLFCIMAZ-SNS).

  

   

  c. Select the option to enable rollback upon failed deployment . Also select the option to create AWS Service Catalog product which can be used for repeated deployments. Specify the S3 file path to store AWS CloudFormation templates created by the AWS Launch Wizard. 

  

   

  d. Under connectivity options select or create a Key Pair name (SQLFCIMAZ_KP) to allow secure connectivity to the EC2 instance.  Then select the tenancy model option to do shared tenancy or Dedicated Host (Dedicated Hosts give you a cost-effective option to bring over BYOL workloads subject to Microsoft licensing policies). Also create a new VPC(SQLFCIMAZ-VPC) or use an existing one. Choose to set up Remote Desktop Gateway and if using Dedicated Hosts click on create new Dedicated Host for primary and secondary instance and follow steps in the next screen shot.

  

   

  e. For Dedicated Hosts provide the following details
  

  • Name tag
  • Instance Family
  •  Instance Type
  • Availability Zone &
    
  • Quantity 
    

  (Additional information about Dedicated Hosts)
  

  

   

  f. Create a new VPC and give it a name tag. Also check set up Remote Desktop Gateway option and choose My IP for Remote Desktop Gateway access as shown below.
  

  

   

  g. Provide a Domain DNS name for the AWS Managed Active Directory Instance as shown below. Also provide a user name and password for the SQL Service Account and select the appropriate AMI (e.g. License Included SQL Standard 2019 AMI).  You can also choose your own custom AMI for BYOL scenarios.

  

• Step 3: Define infrastructure requirements

  In this step, we will define the infrastructure requirements to be used while creating the database cluster.

  a. Select infrastructure options per your requirements for your database cluster as shown below.  You also have the option to define infrastructure based on instance types and you can use either of the R5, X1E or Z1D family of instances for SQL Server deployments based on your application's needs.
  

  

   

  b.  Provide tag names and values to associate with the resources as shown below and click next.

  

   

  c. Review the summary page and if satisfied with your selections click deploy.
  

  

   

  d. Review the deployment progress. It should show you the current status and estimated time to create the environment.
  

  

• Step 4: Verify the installed components

  You can now verify the installed components by clicking on the application name  SQLFCIMAZ under Deployments > MS SQL Server
  

  

   

  a.  Check SQLFCIMAZ application name and click on the Actions drop down menu. Then click and check out each of the resources for the EC2 SQL Nodes, RDGW Instance, SSM resource group and the CloudWatch application logs and CloudFormation template as shown below.

  

  

  

  

  

  

   

  b. Next search for the AWS Service Catalog service and look at the portfolio deployed by the AWS Launch Wizard ( AWS Launch Wizard Products). You can use the AWS Service Catalog Product to automate provisioning of your SQL Server Infrastructure.  (Additional information about  AWS Service Catalog).
  

  

   

  c. Click on the portfolio name AWS Launch Wizard Products and then click on the product and check out the template as shown in figure below.

  

   

  d. Connect to the SQL Server FCI nodes via the RDGDW instance and check out the SQL Server FCI instance deployed by Launch Wizard. Make note of the Amazon FSx drives provisioned for the instance. 

  

   

  e. Type Directory Service next to the services search bar and click on Directory Service.

  

   

  f. Make note of the AWS Managed Active Directory created by the Launch Wizard.
  

  

• Step 5: Troubleshooting

  Refer to this documentation link for troubleshooting AWS Launch Wizard for SQL Server deployment.
  

• Step 6: Clean up

   

  Please follow the below steps to clean up these resources and avoid incurring unintended charges.

  a. Navigate to AWS Launch Wizard > Deployments > MS SQL Server and select the application name ( SQLFCIMAZ) and click delete and then confirm once again when prompted to delete the application.
  

   

  

  

   

  b. Refresh SQL Deployment screen and verify that the Provisioning status shows Delete in progress and then Deleted after some time.