Introduction:

For customers who use SQL Server as their database engine of choice, SQL Server Developer is a great option for reducing the costs for running non-production workloads. Customers will often use SQL Server Developer for both their development and pre-production test environments.

Deploying SQL Server Developer in AWS typically means logging into EC2 instances, downloading the install media to the individual instance, and then deploying manually. While this solution works fine for a single deployment, it presents challenges when customers plan to deploy SQL Server Developer to dozens of instances. In this case, the deployment becomes a time consuming task that often results in inconsistent deployments.

The SQL Server Deployment Tool

AWS has launched an open-source deployment tool for SQL Server Developer that overcomes these challenges. The tool can be obtained from GitHub.

By using this deployment tool, customers are able to leverage AWS Systems Manager (SSM) to deploy SQL Server Developer. This solution works equally well for deployment to individual or groups of Windows EC2 instances running in AWS. The deployment is fully automated, without the need to log into the instances, and results in a standardized deployment of SQL Server Developer.

How to use the automation

Customers should implement the following five steps in order to use the deployment tool:

1. Install AWSPowerShell cmdlet into PowerShell
2. Download the SQL Server Deployment tool from GitHub.
3. Download the SQL Server Developer install media from Microsoft
4. Deploy the automation tool to your AWS account
5. Deploy SQL Server Developer to your instances

Prerequisites

In order to proceed with using this tool, you must have administrative access to your AWS account. This automation deploys resources to your AWS account including an IAM role and instance profile for EC2 instances. The tool will also upload files to an Amazon S3 bucket in your account.

Install AWSPowerShell cmdlet into PowerShell

The AWSPowerShell cmdlet is required in order to use this tool. If you already have this installed, then you can skip forward to the next session.

The following commands require the 64-bit version of PowerShell. Do not run these commands in the 32-bit version of PowerShell.

In PowerShell, run the following command to install the AWSPowerShell cmdlet:

Prompt> Install-Module AWSPowerShell

PowerShell will first install the NuGet provider, and then download and install the AWSPowerShell cmdlet for you to use. Confirm both of these options when prompted.

Download the Automation tool

The automation tool can be downloaded from GitHub. Customers can choose to download either the ZIP file, or clone the repository via the Git command line.

After downloading, unzip the files into a local directory that you will be able to access via PowerShell.

Download SQL Server Developer

Download the SQL Server Developer installation package:

Choose the option to save the file locally.

Once the installation file has been downloaded, run the installer.

From the installer, choose the option to “Download Media.”

Accept the default options to download the ISO formatted install media and click Download.

Once your install media has been downloaded, move the ISO file from your download directory into the directory ssm-automation-deploy-sql-developer-main\s3DeploymentFiles.

This directory will already contain a file configurationFile.ini.

Deploying the tool to your account:

In PowerShell, change directory into the root directory for the downloaded tool.

In PowerShell run the following command:

Prompt> Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

This will allow you to run the PowerShell script that is part of the tool download.

In PowerShell run the following command:

.\DeployToAWS.ps1
This command will take approximately 5 minutes to deploy the tool into your account.

Once the command has completed, you can open the AWS Management Console into your account. From the console, select the AWS CloudFormation service. You find a new stack has been deployed into your account.

If you open this stack, you will find the resources that have been created for you.

These resources will be used to deploy SQL Server Developer to your instances. Take note of the IAM instance profile and role. These are set with the necessary permissions to allow the automation to deploy SQL Server Developer to EC2 instances. Specifically, the role has permissions to read from the S3 bucket, which was created as part of the deployment, and to access AWS Systems Manager.

The stack also creates three parameters in SSM Parameter Store:

By default, these parameters are set to:

The SQL.Developer.DefaultAdmin parameter specifies the account that will be set up as the default SQL Server Administrator when using Windows Authentication. You can update this parameter if you want a different account to be the default administrator account for SQL Server. If the instance that you are deploying to is domain joined, you can change this to a domain user with the format “DOMAIN\User”.

The SQL.Developer.InstallBucket parameter specifies the S3 bucket where the install media has been uploaded. This bucket was created by the deployment script. It should not be necessary to update this value. If you do change this value, you will need to update the IAM role that was created as part of the deployment script, and provide read access permission to the new bucket.

The SQL.Developer.iso parameter defines the name of the installation media file. If you plan to use a different install file, then you should update this parameter. The install media must be an ISO file, CAB or EXE formatted installers are not supported by this tool.

Using the tool to deploy SQL Server to EC2 instances.

Once the deployment tool has been set up in your account, you can use it to deploy SQL Server Developer automatically to EC2 instances.

Create a new EC2 Windows instance in your account. The instance must meet the following requirements:

Network

The instance must be either in a public subnet with a public IP address assigned or in a private subnet with a path to a NAT gateway.  The private subnet option is the preferred approach as having instances in a private subnet increases the security for your database servers.

Access to internet either directly or through the NAT gateway will allow the instance to communicate with the AWS Systems Manager service, and allow Systems Manager to execute the automation on your servers. If the instance cannot communicate with the Systems Manager service, this tool cannot be used to deploy SQL Server Developer.

IAM role

Setting up the automation in your account will automatically create a role called sql-developer-deployment-automation. This role must be attached to the instance in order to use the automation to deploy SQL Server Developer. If necessary, you may change this role after the deployment has been completed.

Storage

The default configuration file will deploy SQL Server to the C:\ drive. Your instance must have sufficient disk space for the SQL Server installation and database storage. (See Customization Options)

Security group

The automation does NOT create a security group for your instances. Select a security group that is appropriate for your environment. If you want to be able to access SQL Server from a remote system, selected security group must allow access to the SQL Server port 1433.

Once your instance has been launched, wait until the instance reports that it has passed all health checks:

You can verify that the instance is successfully communicating with Systems Manager by selecting the Fleet Manager service and locating the instance:

Once the instance is being displayed in Fleet Manager, it is ready to use the deployment automation tool to deploy SQL Server Developer.

From Systems Manager, select the Documents option, and then select the option Owned By Me:

Click on the Install-SQL-Developer document title.

When the document opens, click on the Run Document option.

Under the targets section, you can choose to deploy SQL Server by selecting instances manually, or by having Systems Manager select instances by tags. Manually selecting instances for installation is a good option for deploying a small number of instances, while the tag-based selection is useful for larger deployments across many instances.

You can optionally choose to log the output to S3, and to use the Rate Limiting feature for the document execution. When you are ready, click the Run button to execute the install.

It will take the automation a few minutes to complete the install.

Once the command is completed, you will have SQL Server installed on the instance and ready to go.

Now you are ready to connect to SQL Server Developer and use it for non-production use.

Customization Options

The CloudFormation stack that is deployed into your account is stored in the file

StackDeploymentFiles\SQSLDeveloperStack.yml

The Cloud formation template was generated using CDK, and the project files are stored in the src\ directory. These files can be customized either by editing the cloud formation directly, or by using CDK to change the CloudFormation stack. If you change the stack, recreate the template using the following command:

CDK Synth > StackDeploymentFiles\SQSLDeveloperStack.yml

The configuration file in the s3DeploymentFiles\ConfigurationFile.ini controls how SQL Server will be deployed on the instances. The current files use a default installation for SQL Server Developer, with the option enabled to turn on TCP connections. You can edit this file to customize how SQL Server will be deployed. For example, you may modify configuration file if you wanted SQL Server to be deployed on the D drive of the instance.

There is no way for the installation automation to determine if the ConfigurationFile.ini is valid. If you change configuration to install SQL Server on D drive, you would have to ensure that there is a D drive on the instance before using the automation.

Cleanup

Following the instructions in this blog post will create resources in your AWS Account. If you no longer require the functionality, perform the following cleanup steps:

Remove install media

The installation media for SQL Server Developer is stored in an S3 bucket in your account that was created by the automation deployment. The bucket location is identified in the Systems Manager Parameter Store parameter “SQL.Developer.InstallBucket” You should delete all files in that bucket relating to the installation, and then remove the S3 bucket.

Delete the Cloud Formation Stack

The main tool is set up in your AWS account using Cloud Formation. From the cloud formation tool, locate the stack “AwsSQLDeveloper-Deployment” and delete the stack via the Cloud Formation template.

Remove the Automation Document

Finally, the command document for running the deployment instructions should be deleted. Browse to the Systems Manager Service. Select Documents from the left-hand navigation menu, and then select “Owned by me” from the tabs. Select the document “Install-SQL-Developer.” From the Actions menu, click the option “Delete Document” to delete the deployment automation document.

Following these cleanup steps will not remove the EC2 instances that you have deployed SQL Server Developer to, nor will it remove the installed software.

Summary

In this blog post we walked through the process of setting up the SQL Server Developer deployment tool in your account, and then walked through using the installation tool to deploy SQL Server to an instance.

By leveraging this tool, you can deploy SQL Server Developer at scale across your development and non-production environments to save on costs.