Automate SQL Server deployments with AWS Launch Wizard APIs

In this blog post, I will share details on the new AWS Launch Wizard application programming interface (APIs) that allow programmatic deployment for Microsoft SQL Server workloads. This new API release helps you integrate AWS Launch Wizard with existing deployment tools.

Introduction

The AWS Launch Wizard service guides customers through the sizing, configuration, and deployment of third-party applications on Amazon Web Services (AWS), such as SQL Server. Launch Wizard supports the deployment of SQL Server both as a single instance or in a high availability configuration. Launch Wizard automatically provisions and configures the required resources to create a fully functioning, production-ready SQL Server application.

With this new for SQL Server deployment, you can now deploy SQL Server workloads programmatically. You have the convenience of using existing deployment tools to integrate with your CI/CD pipelines, eliminating the need to login to the AWS Management Console.

AWS Launch Wizard APIs allow you to initiate the following actions:

• CreateDeployment – Creates a deployment for the workload.
• DeleteDeployment – Deletes a deployment.
• GetDeployment – Returns information about the deployment.
• GetWorkload – Returns information about a workload.
• GetWorkloadDeploymentPattern – Returns details for a workload and deployment pattern.
• ListDeploymentEvents – Lists the events of a deployment.
• ListDeployments – Lists the deployments that have been created.
• ListTagsForResource – Lists the tags associated with a specified resource.
• ListWorkloadDeploymentPatterns – Lists the workload deployment patterns for a workload name.
• ListWorkloads – Lists the workload names.
• TagResource – Adds the specified tags to the resource.
• UntagResource – Removes the specified tags from the resource.

Use Cases

• Deploy with existing tools – Programmatically deploy with existing tools to maintain your infrastructure as code.
• Speed up deployments – Building automation routines allow you to tweak parameters based on the environment and quickly provision resources.
• Optimize Disaster recovery process – These Launch Wizard API templates help you build disaster recovery plan. These API templates will bring consistency for creating Disaster Recovery resources.
• Temporary Test Environment – These API templates help to create temporary test/production environments faster.

Solution Overview

1. Create Deployment Specification file
2. Validate deployment specifications
3. Create Deployment
4. Check Status of Deployment

Prerequisites

1. Install and configure the latest AWS CLI. For instructions see the article, Install or update to the latest version of the AWS CLI. The minimum required version is AWS CLI version2.
2. IAM Role permissions for the account to access AWS Launch Wizard and create AWS resources.

SQL Server deployment specifications

The following deployment patterns are supported for SQL Server workloads.

• SQLHAAlwaysOn – Deploys SQL Server Always On Availability Groups (AGs) across multiple Availability Zones on Windows instances.
• SQLHAFCIONTAP – Deploys SQL Server using a failover cluster instance with Amazon FSx for NetApp ONTAP as shared storage on Windows instances.
• SQLHALinux – Deploys SQL Server Always On AGs and Pacemaker across multiple Availability Zones on Ubuntu instances.
• SQLHALinuxRhel – Deploys SQL Server Always On AGs and Pacemaker across multiple Availability Zones on RHEL instances.
• SQLHAWindowsFCI – Deploys SQL Server using a failover cluster instance (FCI) with Amazon FSx for Windows as shared storage on Windows instances.
• SQLLinuxSingleNode – Deploys SQL Server Always On Availability AGs on a single Linux instance.
• SQLWindowsSingleNode – Deploys SQL Server Always On Availability AGs on a single Windows instance.

You can also run the ListWorkloadDeploymentPatterns to get the deployment patterns for SQL Server (Figure 1).

aws launch-wizard list-workload-deployment-patterns --workload-name SQL 

Figure 1– Deployment patterns available for SQL Server

Walkthrough

For this tutorial, I am using the SQLHAAlwaysOn deployment pattern to deploy SQL Server Always On Availability Groups using Launch Wizard APIs. The next step is to find out what configurations are supported on the SQLHAAlwaysOn deployment pattern.

Create deployment specification file

The API uses a JSON-formatted text file to define the specific configuration details about your deployment that would normally be inputted via the AWS Console.

You can run the GetWorkloadDeploymentPattern to get the list of configurations for SQLHAAlwaysOn (Figure 2).

aws launch-wizard get-workload-deployment-pattern --workload-name SQL --deployment-pattern-name SQLHAAlwaysOn

Figure 2– Get Workload deployment configuration

Here is an example of the Configuration file I used for the SQL Server Always on Availability Groups. Use notepad to save this configuration as LWSQLAPI.json locally on the machine where you are running the deployment from.

{
"DisableDeploymentRollback": "true",
"SaveDeploymentArtifacts": "No",
"CreateVpc": "true",
"KeyPairName": "lwapiblog",
"ActiveDirectoryAdministrativeUsername": "admin",
"ActiveDirectoryAdministrativePassword": "YourPassword",
"ActiveDirectoryDomainName": "example.com",
"RemoteDesktopGatewayInstanceType": "t3.large",
"SqlServiceAccountName": "sqlserver",
"SqlServiceAccountPassword": "YourPassword",
"SqlNodesAmiId": "ami-0330215e4cc0009e4",
"UseBringYourOwnLicense": "false",
"EnableFileShareWitness": "false",
"EnableTempdbOnInstanceStore": "false",
"SqlInstanceType": "c6a.xlarge",
"EbsVolumeType": "gp3",
"EbsVolumeSpecifications": "[{\"drive-type\":\"sql-server\",\"drive-letter\":\"C\",\"volume-size\":\"100\",\"iops\":\"8000\"},{\"drive-type\":\"logs\",\"drive-letter\":\"D\",\"volume-size\":\"500\",\"iops\":\"8000\"},{\"drive-type\":\"data\",\"drive-letter\":\"E\",\"volume-size\":\"500\",\"iops\":\"8000\"},{\"drive-type\":\"backup\",\"drive-letter\":\"F\",\"volume-size\":\"500\",\"iops\":\"8000\"}]",
"CreateRemoteDesktopGateway": "false",
"CreateManagedActiveDirectory": "true",
"TenancyModel": "shared",
"EnableAppInsights": "false"
}

Validate deployment specifications

Before starting your deployment, use the optional dry-run flag of the create deployment action to validate your specifications and check if you have the required permissions for the action.

aws launchwizard create-deployment --workload-name SQL --deployment-pattern-name SQLHAAlwaysOn --name TestDP1 --dry-run --region us-east-2 --specifications file://LWSQLAPI.json  

If there are no issues with your deployment, you should get the following message:

Figure 3 – Deployment dry run status

Create deployment

After you have validated your deployment specifications using the dry-run, trigger the actual deployment using the flag –no-dry-run. Running with —no-dry-run flag will show any errors before deploying resources in your account. As you can see in Figure 4, the image id does not exist. So, I updated the image id in my specifications file.

aws launchwizard create-deployment --workload-name SQL --deployment-pattern-name SQLHAAlwaysOn --name TestDP1 --no-dry-run --region us-east-2 --specifications file://LWSQLAPI.json  

Figure 4 – Create deployment with exception

Save the specifications file in JSON format and use it as a parameter for the API. Run the CreateDeployment API with specification file mentioned in the preceding step (Create deployment specification file) to create the SQL Server resources using this AWS CLI command.

aws launchwizard create-deployment --workload-name SQL --deployment-pattern-name SQLHAAlwaysOn --name TestDP1 --no-dry-run --region us-east-2 --specifications file://LWSQLAPI.json  

When successful, you will get a deploymentId. This deploymentId can be used later to track the progress of your deployment.

{
    "deploymentId": "af726319-134e-4b01-ae5f-eb2dfa42e397"
}

 Check Status of Deployment

You can use the ListDeploymentEvents or GetDeployment action to list the events and check the status of your deployment.

aws launch-wizard list-deployment-events --deployment-id af726319-134e-4b01-ae5f-eb2dfa42e397 --region us-east-2

The screenshot shown in Figure 5 shows a sample JSON response for the list-deployments-events API command. It provides the status of your resources as COMPLETED or IN_PROGRESS.

Figure 5 – Status of current deployment

To check if the deployment has successfully completed, run the run following command to get status.

aws launch-wizard get-deployment --region us-east-2 --deployment-id af726319-134e-4b01-ae5f-eb2dfa42e397 --output json

The screenshot shown in Figure 6 shows a sample JSON response for the GetDeployment API command and the status shows COMPLETED.

Figure 6 – Status of deployment using get-deployment API

You can also run ListDeployments to get status of the deployment. The current status shows COMPLETED as shown in following Figure 7. You can go to AWS Launch Wizard console or Amazon EC2 instances dashboard to see your SQL Server resources deployed.

$deploymentid ="af726319-134e-4b01-ae5f-eb2dfa42e397"
$deployments = aws launch-wizard list-deployments --region us-east-2 --output json | ConvertFrom-Json
$deployment = $deployments.deployments | Where-Object { $_.id -eq $deploymentid }
$deployment | ConvertTo-Json

Figure 7 – Status of deployment using List-Deployment API

Figure 8 – Launch Wizard console deployment status

Figure 9 – successful creation of Amazon EC2 SQL Server instances

Troubleshooting

To troubleshoot any issues with AWS Launch Wizard for SQL Server APIs, refer to the Launch Wizard Troubleshooting Guide. For in-depth information regarding SQL APIs, you can find detailed documentation here.

Cleanup

Deploying this solution will provision AWS resources and incur costs. Once you complete testing and no longer want to keep the resources created by Launch Wizard and its associated resources, you can easily delete them by using DeleteDeployment API.

Conclusion

In this blog, you have learned about the seamless deployment of SQL Server workloads on AWS using the AWS Launch Wizard for SQL Server APIs. By leveraging this new release, you can eliminate the need to manually configure deployment steps through the AWS Management console. To learn more, please visit the AWS Launch Wizard Overview or AWS Launch Wizard documentation.

wizardwizardAWS has significantly more services, and more features within those services, than any other cloud provider, making it faster, easier, and more cost effective to move your existing applications to the cloud and build nearly anything you can imagine. Give your Microsoft applications the infrastructure they need to drive the business outcomes you want. Visit our .NET on AWS and AWS Database blogs for additional guidance and options for your Microsoft workloads. Contact us to start your migration and modernization journey today.