How to migrate from Microsoft SQL Server to Amazon DynamoDB

To get started, navigate to the Amazon RDS console. On the main page, choose Create database to create a new database.

This initiates the database creation wizard. In the Engine options section, choose Microsoft SQL Server as your Engine type. Then choose SQL Server Standard Edition and use the default version.

The database creation wizard includes templates to make it easier to configure the settings for your Amazon RDS database. Because this database is being used as an example, you can choose the Dev/Test settings.

In the Settings section, give your database a name and set the master username and password. Make sure you write these down because you need them to connect to your database and create additional users.

Next, choose the database instance size. Choose the db.m5.large instance class for your instance.

Next, configure the storage options for your Amazon RDS database. You can use the default options with 20 GiB of General Purpose (SSD) storage.

The next section in the Amazon RDS database creation wizard is about connectivity. You must specify the Amazon Virtual Private Cloud (Amazon VPC) in which your database resides, as well as the network subnet and security groups for your database instance.

You can use the default VPC in your AWS account. In the Additional connectivity configuration subsection, under Publicly accessible, choose Yes so that you can connect to your database from your own computer. Then choose to create a new VPC security group and give it a name.

There are some additional configuration options you can configure, including settings for backups, monitoring, maintenance, and automated upgrades. The default settings work for the migration example in this lesson.

Choose Create database to create your database.

Note: After creating your database, you must be sure to clean up the Amazon RDS database instance as explained in the final module of this lesson. You incur charges of around one dollar for every hour the Amazon RDS database is left running.

As Amazon RDS is provisioning your infrastructure and initializing your database, the Status of your database is Creating.

When your database is ready to use, its Status is Available.

Choose your database name in the DB identifier column to see details about your database. There is an Endpoint property in the details. Save this value for the next module when you connect to your database.

Open Azure Data Studio and choose Create a connection. Enter the endpoint of your SQL Server database from the last module as well as the user name and password from when you created the database. Then choose Connect.

After you connect, choose New Query to execute a query in your database.

This opens a query window. Copy and paste the contents from the create_tables.sql file from the .zip file you downloaded. This contains SQL statements to create a database and tables for your multiplayer battle royale game.

After you paste the contents of the file into the query window, choose Run to execute the query.

Next, replace the contents of the query window with the contents from the insert_data.sql file. This contains the sample data for the game, including maps, users, games, and user-game mappings.

After you have pasted the contents in the window, choose Run to execute the query and insert the data.

Let's read some of the data to ensure everything was inserted correctly.

Enter the following query in the query window.

This fetches all of the users that are signed up for a particular game. Execute the query, and you should see 34 rows in your results.

Finally, notice how many SQL joins you had to use in that query. In NoSQL databases such as DynamoDB, there are no join operations. As your SQL Server database scales, the performance of your join operation decreases.

Instead of join operations, DynamoDB has different strategies for modeling your data. There are a number of differences when modeling your data in DynamoDB, but the three main differences are:

• With DynamoDB, you need to design your table for your access patterns rather than write your queries for your access patterns as in a relational database. In this example, you store a count of the number of players who have signed up on the Game record.
• Put multiple different entity types into a single table. This enables you to retrieve multiple, heterogeneous item types in a single request without using a join.
• Use generic names (such as PK and SK) for your primary keys. This is because you have multiple different entity types in a single table and thus no common properties across them.

For more details about DynamoDB data modeling, see the full tutorial about Modeling Game Player Data with DynamoDB.

To make your migration easier, create some tables in your SQL Server database. These tables reshape the data into the format expected by DynamoDB. You will use these tables with AWS DMS to migrate the data to DynamoDB.

Paste the contents of the create_migration_tables.sql file in the query window. This file creates four different tables to be used in the migration task. Choose Run to execute the query and create the tables.

To ensure your tables were created successfully, enter the following query in the query window and choose Run.

SELECT * FROM dynamodb_open_games

Your results should look as follows.

Notice that there are PK and SK columns in your result set. These map to the primary key values of the DynamoDB data model for the multiplayer game. Additionally, notice that the results include the map name, rather than the map ID as in the original Games table in your database. This is some of the denormalization that is done as part of the move to a NoSQL database. This table represents one of the four tables you created for the migration.

To get started, navigate to the DynamoDB console. Choose Create table to create a new table.

The first thing you need to do is give your table a name and specify the primary key for your table.

The tutorial that you are using uses a table name of battle-royale. For the primary key, there is a composite primary key with a partition key named PK and a sort key named SK. Enter those values in the table creation wizard.

The table-creation wizard allows you to use default settings for your table, and those settings work for this walkthrough. Choose Create to create your table.

As your table is being created, the Overview tab indicates that the table creation is in progress.

After your table is created, the DynamoDB console shows the details of your table with a Table status of Active.

The console also includes the Amazon Resource Name (ARN) of your DynamoDB table. You need this ARN when creating your IAM role.

To create your IAM role, navigate to the Roles section of the IAM console. Choose Create role to start the role-creation wizard.

Next, choose a trusted entity. Create a service role that can be used by AWS DMS. Choose DMS and then choose Next: Permissions.

Next, attach permissions to your role. Choose Create policy to create a new policy.

Choose the JSON tab and paste the following into the editor.

Be sure to replace <yourDynamoDBTableArn> with the actual ARN of your DynamoDB table.

Your screen should look as follows. Choose Review policy to review your policy.

Give your policy a name and description, and then choose Create policy to create your policy.

Return to the IAM role creation tab. Choose the Refresh icon to refresh the IAM policies in your account. Then find your policy name and choose the check box next to it.

Choose Next: Tags to move to the next step in the wizard.

You can skip adding tags to your IAM role. Choose Next: Review to go to the next step.

Give your IAM role a name and description, and then choose Create role.

After you create your IAM role, it appears in your list of IAM roles in the IAM console. Choose your IAM role to see more details about it.

The IAM role summary page shows the Role ARN. Save this value because you need it when configuring an endpoint in AWS DMS in a future module.

To create a replication instance, go to the Replication instances section of the AWS DMS console. Choose Create replication instance to begin the replication instance-creation wizard.

In the Replication instance configuration section, give your replication instance a name and description. Then choose your instance class. In this lesson, the amount of data is small, so choose the dms.t2.micro instance class.

Then choose an engine version for AWS DMS. Finally, choose the amount of allocated storage for your replication instance.

As you continue in the Replication instance configuration section, you need to choose a VPC for your replication instance. Choose the same VPC in which you provisioned your SQL Server database to ease network access for the replication instance.

There are options for Multi AZ configuration and whether your replication instance should be publicly accessible. Keep the default options for both of these.

Next, open the Advanced security and network configuration section. For the VPC security group(s) configuration, choose the same security group that you attached to your SQL Server database. This allows your replication instance to access your SQL Server database instance.

When you're ready, choose Create to create your replication instance in AWS DMS.

After you choose Create, AWS provisions your replication instance. The instance’s Status is Creating while AWS provisions and initializes your instance.

When your replication instance is ready to go, its Status is Available.

While you are waiting for your replication instance to be available, go to the Security Groups section in the Amazon EC2 console. You need to add a rule to your security group to allow your replication instance to access your database.

In the Security Groups section, find the security group you attached to your SQL Server database instance and your replication instance, and choose it.

Choose to Edit inbound rules for your security group.

Your security group has an existing rule that allows for access to your SQL Server instance from the IP address you used to create the database. Choose Add rule to add an additional rule. For Type, choose MSSQL. For Source, enter the security group that is attached to your SQL Server database and your replication instance.

Your screen should look as follows.

Choose Save rules to save the updated rules for your security group.

When your replication instance is available and you have updated the rules for your security group, you may move on to the next module.

First, create the endpoint for your source database. This is the SQL Server database you created in Amazon RDS.

Navigate to the Endpoints section of the AWS DMS console. Choose Create endpoint to create a new endpoint.

In the endpoint creation wizard, choose to create a Source endpoint. Choose the box to Select RDS DB instance, and choose your newly created Amazon RDS database in the dropdown.

This completes most of the Endpoint configuration details for you. You need to enter your password and a database name near the bottom of the section.

Before you save your endpoint, you should test the connection to ensure it was configured correctly. Open the Test endpoint connection section to test your connection.

Choose the replication instance you want to use, and then choose Run test. After a few seconds, you should see a status of successful. This indicates you configured your security group and endpoint correctly. Choose Create endpoint to save your endpoint.

Next, you need to create a target endpoint for your DynamoDB table. In the Endpoints section, choose Create endpoint again.

Choose Target endpoint in the Endpoint type section.

In the Endpoint configuration section, give your endpoint a name. Then choose dynamodb for the Target engine. Paste the ARN of the IAM role that you created in a previous module.

Be sure to test your endpoint connection as you did with your source endpoint. If you configured your IAM role correctly, you should see a successful connection.

Choose Create endpoint to create your endpoint.

To get started, navigate to the Database migration tasks section of the AWS DMS console. Choose Create task to create a new replication task.

In the Task configuration section, set up the parameters of your replication task. Give your task a name and choose the replication instance you created in an earlier module. Then choose the source endpoint for your existing database and your target endpoint for your DynamoDB table.

You need to choose a migration type. There are two migration types:

1. Migrate existing data, which performs a one-time process to copy data from your source database to your target database.
2. Replicate ongoing changes, which copies all ongoing operations from your source database to your target database.

In this walkthrough, you migrate existing data to your new DynamoDB table. For the migration type, choose Migrate existing data.

Skip the Task settings section and leave all the settings at their defaults.

In the Table mappings section, tell AWS DMS which tables you want to copy over and how to map them to your target database.

You need to combine records from multiple SQL Server tables into a single DynamoDB table. To do so, use a JSON configuration file for the table mappings. Choose JSON editor for the editing mode, and then paste the contents of the table_mappings.json file in the .zip file you downloaded.

After you paste the table mappings, choose Create task to start your migration task.

After you create your task, your task is shown in the Database migration tasks section with a Status of Creating.

After the task is created and the load finishes, its Status is Load complete.

Go back to your DynamoDB table and choose it in the console. Choose the Items tab to see the records in your DynamoDB table.

Earlier, you ran a SQL query to look at the users in a game in your database. You can do the same in DynamoDB to verify that you get the same results.

In the DynamoDB data model, a Game item and all UserGameMapping items have the same partition key of GAME#<GameId>. You can use the DynamoDB Query operation to fetch the Game item and the UserGameMapping items that represent users that have signed up for the game.

Choose the dropdown to change from Scan to Query. Then for the partition key value, enter GAME#0ab37cf1-fc60-4d93-b72b-89335f759581 and choose Start search.

This should return 35 records: the Game item and 34 UserGameMapping items for each user in the game.

This query is one way to validate that the data migration was successful. Before switching to your new database, perform additional data validation queries. This includes performing sample queries, as shown previously, as well as using record counts to ensure all records were transferred.

To count records in a relational database, you could use the following query to get the total number of rows in a table.

SELECT count(*) FROM <table_name>;

Be sure to replace <table_name> with the name of your table.

In a DynamoDB table, you need to use the Scan operation to read all the items in your table. The following AWS CLI command shows how to use a Scan and return the COUNT of items read.

aws dynamodb scan \

--table-name <table_name> \
--select COUNT

The Scan operation is a paginated operation and can only read 1 MB of data in a single request. If your migrated table contains more than 1 MB of data, you need to make multiple requests to get an accurate count. For more information about paginating in DynamoDB, see the documentation about Paginating the Results.

You have successfully migrated your data into DynamoDB. The data in your table should now work with the application code in the hands-on tutorial for Modeling Game Player Data with DynamoDB.

First, stop and delete the database migration task to replicate your data. Navigate to the Database migration tasks section of the AWS DMS console. Choose the task you want to remove and choose Delete.

Next, navigate to the Endpoints section of the AWS DMS console. Choose both your source endpoint and your target endpoint, and then choose Delete.

Then go to the Replication instances section of the AWS DMS console. If your replication instance is not being used for any other replication tasks, choose it and then choose Delete.

Next, delete the SQL Server database you used as a sample source database. Navigate to the Amazon RDS console, choose your SQL Server database, and then choose Delete.

Finally, you need to delete the DynamoDB table you created in this lesson. Navigate to the DynamoDB console, choose the table, and then choose Delete table.

AWS DMS created a table called awsdms_full_load_exceptions to help track exceptions. Delete this table as well.

Finally, you need to delete the IAM role and policy that you created for AWS DMS to use in the migration.

Navigate to the Roles section of the IAM console. Find the role you created for AWS DMS and choose it. Then choose Delete role.

Then go to the Policies page of the IAM console. Choose the Filter policies filter and then choose Customer managed.

On the Policies page of the IAM console, delete the IAM policy.