Migrate a self-managed Microsoft SQL Server database to a fully managed database on Amazon RDS

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 the Engine type. Then choose the edition and version of SQL Server you want to use. When using SQL Server in Amazon RDS, the commercial license is included so that you do not have to worry about managing licenses yourself.

The database creation wizard includes templates to make it easier to configure the settings for your Amazon RDS database. If you are creating this database to be used in production, you should choose the production template.

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. You should make this choice based on your estimated capacity. If you are managing your own database on Amazon Elastic Compute Cloud (Amazon EC2), you can compare your current Amazon EC2 instance size to an Amazon RDS instance size.

If you want to increase or decrease your database instance size in the future, Amazon RDS allows you to do that easily. However, you may incur some downtime to change your database size.

Next, configure the storage options for your Amazon RDS database. There are two storage options in Amazon RDS: general purpose and provisioned IOPS, or I/O operations per second. With general-purpose storage, you receive 3 IOPS per GiB of storage allocated. Thus, 100 GiB of storage would have 300 IOPS. Additionally, you receive burst capacity up to 3,000 IOPS.

With provisioned storage, you provision IOPS separately from your storage capacity. This allows you to tune your storage and operations settings to fit your needs.

Next, decide whether to create a standby instance. A standby instance is a replica of your data that is available in the event of failure. It is located in a different Availability Zone in the same AWS Region as your Amazon RDS database to limit the impact of failures within a single data center. If you are running a production database where uptime is essential, a standby instance is recommended.

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.

If you are migrating from a self-managed database instance on Amazon EC2, you can use the same Amazon VPC and security groups as your existing database.

If you are migrating from a database that is not hosted on AWS but your application is hosted on AWS, choose the same Amazon VPC that is used for your application. Then create a new security group for your database instance.

You also can configure additional options, including settings for backups, monitoring, maintenance, and automated upgrades. The default settings work for most situations, but you should review them to ensure that they work for your needs.

The end of the database creation wizard shows you the estimated monthly costs for your database instance. Choose Create database to create your database instance.

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.

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. The instance class you use depends on the size of your existing database and the amount of data flowing through it.

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, choose an Amazon VPC for your replication instance. Choose the same Amazon VPC in which you provisioned your Amazon RDS database to ease network access for the replication instance.

You may choose to have a Multi-AZ setup for your replication instance for redundancy. If you use AWS DMS to keep two databases in sync over a long period of time, you may want to use a Multi-AZ setup. If you are performing a one-time migration of your data from an existing database to a fully managed database in Amazon RDS, you likely don't need a Multi-AZ setup.

Finally, choose whether your replication instance should be publicly accessible. If your existing database is in the same Amazon VPC as your new database and your replication instance, you don't need your replication instance to be publicly accessible. If not, you need your replication instance to be publicly accessible.

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 Amazon RDS database. This allows your replication instance to access your Amazon RDS database.

You may also edit the maintenance and tags settings.

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

After you choose Create, AWS provisions your replication instance. It shows a Status of 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 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. Remove the existing IP address and enter the name of the security group used for your Amazon RDS database instance and 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, let's create the endpoint for your target database. This is the 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 Target endpoint. Choose the check box for Select RDS DB instance, and then choose your newly created Amazon RDS database in the drop-down menu.

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, test the connection to ensure that it was configured correctly. To test it, open the Test endpoint connection section.

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 that you configured your security group and endpoint correctly. To save your endpoint, choose Create endpoint.

To get started, navigate to the Replication 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 fully managed database in Amazon RDS.

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.

If you are migrating your application from using a self-managed database to using a fully managed database, you want to use both types. The first type copies all data in your database, and the second type ensures that all additional updates are replicated to your new database until you switch your application to use the new database.

For the migration type, choose Migrate existing data and replicate ongoing changes. Note that this requires you to have logical replication enabled on your source database.

In the Table mappings section, tell AWS DMS which tables to copy. Enter the name of the schemas and tables you want to copy. You can use % as a wildcard character to copy multiple tables or schemas.

When you are ready, choose Create task to start your replication 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 initialized, its Status is Starting.

After the migration of existing data is complete, it shows a status of Load complete, replication ongoing. Any updates to your source database at this point are copied to your target database.

When your initial migration is complete and all data is synced to your new database, you are ready to use your new database as your primary database.

You have two ways you can handle this:

1. If you feel confident about your migration, you can change the database configuration in your application to use your new database. This ensures all reads and writes go to your new database.
2. If you want to follow a more cautious approach, you can read from and write to both databases for a period of time. This allows you to compare the results from each database for accuracy while still maintaining the correct data in your existing database.

Whichever method you choose, you should thoroughly test your new database before making it your primary database. After you have switched to using your primary database and are confident in the results, you may want to delete your AWS DMS infrastructure.

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 then choose Stop.

It takes a few moments to stop the task. When it is stopped, choose it again, and then 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.