Migrate a SQL Server Database to the Cloud

Migrating a SQL Server database to the cloud is similar to migrating a database to a new server. The cloud can simplify the process through the use of managed services such as AWS Lightsail.

AWS Admin
Amazon Employee
Published Aug 9, 2023
Last Modified Jun 21, 2024
For an IT administrator, restoring a database from a backup isn't unusual. Migrating an on-premise database to the cloud follows a similar process. However, instead of using the AWS console, we will automate the process using scripts. In this tutorial, we'll walk through how to migrate a SQL Server database to the cloud, using the command line to instantiate and configure the database server, rather than a console application.


✅ AWS Level200 - Intermediate
⏱ Time to complete45 minutes
💰 Cost to completeFree when using the AWS Free Tier or USD 1.01
🧩 Prerequisites- An AWS account: If you don't have an account, follow the Setting Up Your AWS Environment tutorial for a quick overview. For a quick overview for creating account follow Create Your AWS Account.
- AWS credentials: Follow the instructions in Access Your Security Credentials to get your AWS credentials
- A git client: Follow the instructions to Install Git for your operating system.
💻 Code SampleCode sample used in tutorial on GitHub
📢 FeedbackAny feedback, issues, or just a 👍 / 👎 ?
⏰ Last Updated2023-08-09

What You Will Learn

  • How to create a browser-based environment for working with cloud resources
  • How to create storage and attach it to a database server
  • How to deploy and configure SQL Server with a Lightsail blueprint
  • How to restore a MS SQL database on a Windows Server VPS

Module 1: Setting Up a Working Environment

In this tutorial, you will use the command line. There are advantages to using the command line. First, the command line offers a larger set of options for creating services. Second, commands can be chained together so that one command returns an output that can be used by another. Third, commands can be combined into a script that can be reused.
Whether it’s on-premise or in the cloud, deployments and configuration are frequently done on a local computer. However, a case can be made for using an online environment. Browser-based terminals or integrated development environments (IDEs) offer a consistent environment customized to a specific tasks. They can be configured with custom tool chains that reduce dependency conflicts. In addition, they can be accessed from any browser and maintain state and history from the last time they were used. AWS offers Cloudshell, a browser-based terminal, and Cloud9, an in-browser IDE.
You will be using the AWS CLI and writing scripts. Cloud9 is ideal because you can write and store commands and scripts in the IDE and execute them in the included terminal. To start with Cloud9, use the AWS console Search bar to find the Cloud9 service.

Step 1: Create a Cloud9 Environment

Open Cloud9 service in the AWS console
Choose Create Environment.
Create a Cloud9 environment
In the Details panel of the Create Environment page, name the environment. You can also add an optional Description to specify how it's used and to differentiate it from other environments.
Name the Cloud9 environment
Accept the default values for New EC2 Instance and Networking panels. Choose Create. When the environment is created, choose Open to start the environment.
Open the environment
Your Cloud9 environment is ready.
Cloud9 environment is ready

Step 2: Configure the Environment with Tools

This tutorial uses the AWS CLI and the Lightsail plugin. The current version of Cloud9 includes the 1.x version of the CLI, but the Lightsail plugin requires the 2.x version of the AWS CLI. You will have to upgrade to CLI 2.x version and install the Lightsail plugin.
In the Cloud9 terminal, download and install the version 2.x of the CLI.
Check the installation.
Your Cloud9 environment is ready.
Tip: Cloud9 is a full-featured IDE and you can save command snippets and scripts in a code repository like Github or Gitlab. Cloud9 includes Git Panel for managing repositories. An advantage of using a code repository is that it is available anywhere and you can use it to manage snippets and scripts for reuse.

Module 2: Create a SQL Server Instance from a Lightsail Blueprint

AWS Lightsail includes templates for instantiating VPS with a popular software package. You will instantiate a Windows Server with SQL Server installed with Lightsail.
In the Cloud9 terminal, use the AWS Lightsail CLI to create a VPS with SQL Server using a blueprint. You can find blueprints with the aws lightsail get-blueprints command. The get-blueprints command lists all the blueprints available; you can find a specific blueprint by piping the output of the command to the unix grep utility.
There are several different versions of SQL Server Express available. Use the latest SQL Server version, windows_server_2022_sql_2022_express. Create a VPS with with this blueprint. In addition, the --user-data parameter creates a directory to store the back SQL Server backup file. The --bundle-id sets the size of the server.
Note that this SQL Server instance is not a managed Relational Database Service (RDS). You are responsible for any system or security updates and backing up data files.

Module 3: Create Storage for Data and Log Files

Databases typically store data files and logs in separate drives attached to the server. With Lightsail, you can create both object storage and block storage. Block storage is similar to a physical drive and can be formatted with a file system and attached to a server. In this section, you will create block storage. In a following section, you will attach it to a VPS and format it with the NTFS file system.

Step 1: Create Block Storage

In the Cloud9 terminal, use the AWS CLI to instantiate storage with the aws lightsail create-disk command. Name the disk and set the availability zone.
If successful, Lightsail returns a status message.

Step 2: Attach Block Storage to the Database Server

Attach the block storage to the database server. The server will recognize it as a drive.
When the disk is attached, it returns a JSON message to confirm the operation.
Although the storage is attached to the server, it is an unformatted disk. You will format it with a Windows Server.

Module 4: Configure the SQL Server VPS

The VPS is a Windows Server. Use the RDP button in the Lightsail console. Choose the terminal icon in the pcgSQLServer panel.
Note: The RDP button may take 5-to-7 minutes before it becomes active and you can connect to the server.
Use RDP window to log into the SQL Server Database server
We need to format the disk to store data and log files. Here are the steps to make the disk usable by a Windows Server.

Step 1: Find the Disk Number of the Unformatted-But-Attached Disk

In the RDP window, open a Powershell window and use the Get-Disk commandlet to find the unformatted disk, which is attached but offline.
Once you know the Disk Number, initialize and partition the disk with the New-Partition commandlet. The commandlet also assigns a drive letter and use the entire disk.

Step 2: Download the Wide World Importers Sample Database

Microsoft distributes an example SQL Server backup file. Use this file to restore a backup. When you instantiated the SQL Server VPS, a backup directory was created. Download the Wide World Importers backup file to that directory.

Step 3: Create Directories for Data Files and Logs

The data files and logs for the Wide World Importers database are stored in a separate drive. Create the directories in the attached drive.

Module 5: Restore the Database from the Backup File

To restore the database from Powershell, use Transact-SQL (T-SQL), a Microsoft version of SQL. The sqlcmd utility lets you use T-SQL commands against SQL Server. Save the following script to a file in the C:\backup directory and name it restore_database.sql, use notepad or an editor of your choice.
Run the script using sqlcmd.
If the restore is successful, you will see many status messages ending with this message:
You can use SQL Server Management Studio to verify the restore was successful and run a SQL query.
Open SQL Server Management Studio from the Windows Start button.
Open SQL Server Management Studio
When SQL Server Management Studio is open, choose File > Connect Object Explorer.
Open Object Explorer from the menu
The Connect to Server window will open. Select the server name by choosing Browse for more, then the server name under Database Engine.
Connect to the database by choosing the server
We will use Windows Authentication which requires choosing the Options>> button and selecting Trust Server Certificate. Then choose Connect.
Set option to use Windows Authentication
With the database connected to SQL Server Management Studio, you can query the Wide World Importers database by choosing New Query.
Start a new database query
In the query window, type in the SQL query. This query lists all the rows in the Website.Customers view.
To run the query, choose Execute in the menu.
Execute the SQL query from the menu
The Results pane displays the rows from the view.
SQL Server Management Studio
Congrats! You’ve migrated a SQL Server database to the cloud.

Module 5: Clean up

Delete the VPS, the attached disk, and the Cloud9 environment to prevent further charges.

Step 1: Delete the VPS

In the Lightsail console, choose Instances, then select the three dots in pcgSQLServer. Choose Delete.
Select Delete
Choose Yes, delete.
Delete SQL Server VPS

Step 2: Delete the Disk

In the Lightsail console, choose Storage, select the three dots in sqlserver-data-and-logs. Choose Delete.
Select Delete
Choose Yes, delete.
Delete disk

Step 3: Delete the Cloud9 Environment

In the Cloud9 console, choose pcg-database and select Delete.
Delete Cloud9 environment

What Did You Accomplish?

You deployed a SQL Server in the cloud and restored a database. However, the true takeaway is that you did this in a terminal using the AWS CLI and Lightsail plugin. You accomplished the task without using the console. As an additional challenge, try to complete this tutorial by writing a script to deploy the SQL Server and configure storage and a Powershell script to restore the database. Learning how to use the AWS CLI is the first step to building reproducible and testable infrastructure. The Practical Cloud Guide will use this approach to building and maintaining infrastructure.
If you want to do a deep dive into SQL Server in the cloud, checkout the Amazon RDS for SQL Server Workshop to get hands-on with SQL Server at AWS.

What's Next?

Instantiating SQL Server from a Lightsail blueprint is not complicated. However, you are responsible for maintaining that instance, which includes updates and backups. An important distinction between the cloud and on-premise data centers is the option to have a fully managed solution. In a managed solution, the cloud provider is responsible for maintaining the resource. In the following tutorial, you will deploy a relational database to scale a web application.

Any opinions in this post are those of the individual author and may not reflect the opinions of AWS.