{"id":174,"date":"2017-06-26T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/aws-database-migration-service"},"modified":"2026-03-18T21:52:57","modified_gmt":"2026-03-18T21:52:57","slug":"aws-database-migration-service","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2017\/06\/26\/aws-database-migration-service\/","title":{"rendered":"AWS Database Migration Service – SQL Hammer"},"content":{"rendered":"

I have begun writing a few blog posts focused on launching your databases into the cloud. Specifically targeted on database migration methodologies. Choosing a migration method involves making a lot of decisions up-front. This post will assume that Managed Relational Database Service (RDS), which is a Platform as a Service offering by Amazon Web Services (AWS), has been chosen. Also, this post will focus on a homogeneous database migration between an on-premises Microsoft SQL Server database and a SQL Server database on RDS.<\/p>\n

How to use Database Migration Service<\/h2>\n

The AWS Database Migration Service (DMS) aids in migrating databases from several different data platforms to Amazon EC2, Amazon RDS, and Amazon Redshift instances. It can be setup to copy your database in a single full load or load and continue to replicate changes, keeping the target database up-to-date.<\/p>\n

Setting up DMS is simple. The architecture is comprised of, the source database, the target database, an endpoint for each database, and a replication instance.<\/p>\n

\"\"\"\"<\/a><\/p>\n

This walk through is going to start with an on-premises Microsoft SQL Server already in existence and no AWS resources instantiated, yet.<\/p>\n

Building a SQL Server database in Amazon RDS<\/h3>\n

There are many sizing and parameter decisions to make when creating an Amazon RDS database. For the purposes of this demonstration the AWS Free Tier<\/a> will be used. The free tier limits some of the choices but this is not a problem because the focus of the article is DMS, not the intricates of RDS.<\/p>\n

Begin building the database by logging into the AWS console. Search for and select RDS.<\/p>\n

\"\"\"\"<\/a><\/p>\n

Select Instances<\/em> and then Launch DB Instance.<\/em><\/p>\n

\"\"\"\"<\/a><\/p>\n

Select Microsoft SQL Server Express.<\/p>\n

\"\"\"\"<\/a><\/p>\n

Configure the database according to performance and size needs. Given that this demonstration is in the free tier, db.t2.micro is selected with the included, and free, SQL Server Express license. I was impressed to see SSD storage in the free tier.<\/p>\n

Define a unique database instance identifier and the initial SQL authentication account.<\/p>\n

\"\"\"\"<\/a><\/p>\n

Clicking next will bring up the networking and instance configuration options. Ensure that the instance is publicly accessible and allow the wizard to create a new security group. Leave the rest of the options default and click Launch DB Instance<\/em>.<\/p>\n

\"\"\"\"<\/a><\/p>\n

AWS can take up to 20 minutes to provision a new database instance. Given how small this instance is, it takes closer to 5 minutes. The instance cannot be connected to until its status is available<\/em>. The endpoint information is listed when the instance is selected in the console.\"\"\"\"<\/a><\/p>\n

Configuring network security<\/h3>\n

While the instance builds, move on to setting up the virtual private cloud<\/a> (VPC) security group<\/a>. Navigate back to the AWS console and search for VPC.<\/p>\n

\"\"\"\"<\/a><\/p>\n

Once at the VPC dashboard, go to Security Groups<\/em>, select the group created by the RDS wizard, and create a new inbound rule. In the screenshot below, I have added a rule for my home network\u2019s public IP address. It is also possible to create a rule for 0.0.0.0\/0 which opens up all TCP traffic. Later in this post, a rule will need to be created to allow the DMS replication instance to connect.<\/p>\n

\"\"\"\"<\/a><\/p>\n

Setting up the Database Migration Service<\/h3>\n

Once again, from the AWS console, search for and select DMS.<\/p>\n

\"\"\"\"<\/a><\/p>\n

Click on Get Started<\/em>.<\/p>\n

\"\"\"\"<\/a><\/p>\n

Like most wizards in the world, this one begins with a welcome page. One important piece of information that is provided, however, is the tip about using the AWS Schema Conversion Tool<\/a>. The conversion tool is most useful for heterogeneous migrations, such as Oracle to RDS Microsoft SQL Server. This demonstration is using homogeneous data platforms; therefore, it is not needed.<\/p>\n

\"\"\"\"<\/a><\/p>\n

Leave most of the replication instance choices default. A name and description is required. Then, expand the advanced section.<\/p>\n

\"\"\"\"<\/a><\/p>\n

In the advanced section, select the security group that was used to create the RDS instance. This will make connectivity a little bit easier. Then, click next to move on.<\/p>\n

\"\"\"\"<\/a><\/p>\n

The replication instance will begin to build at this time. Populate the endpoint information for the source and destination connections. The endpoints to each will need to pass a connection test, located at the bottom of the page. The tests are not available until the replication instance is built, however.<\/p>\n

\"\"\"\"<\/a><\/p>\n

The next, and final, page defines the replication task. This demonstration is going to focus on a single full load of the database into RDS. Allowing DMS to configure SQL Server Transactional Replication for on-going data updates, is also an option.<\/p>\n

If there is LOB data in the database, make sure to select Full LOB mode<\/em> to avoid truncation of data. In Limited LOB mode<\/em>, default, DMS will truncate all LOB values to the maximum bytes which is configured.<\/p>\n

I highly recommend enabling logging. DMS works well and is reliable but, if there are any problems, the log becomes invaluable for troubleshooting.<\/p>\n

Finally, there must be at least one inclusion rule. If the entire database is to be migrated, the rule can have a wild card (%) in both the schema and table name fields. Multiple rules can be created to cover only the objects that are desired to be included in the migration.<\/p>\n

\"\"\"\"<\/a><\/p>\n

After clicking create task<\/em>, the task will begin to build. Selecting the task and navigating to the logs<\/em> tab will present a link to Cloud Watch and the log stream. If you did not create a security rule for TCP 0.0.0.0\/0, earlier in this process, then you will need to navigate to the replication instances<\/em> page. Record the IP address so it can be added as a new security rule in your security group.<\/p>\n

\"\"\"\"<\/a><\/p>\n

In addition to the logs, the replication status can easily be viewed in the table statistics<\/em> tab and the high-level status columns in-line with the task selector.<\/p>\n

\"\"\"\"<\/a><\/p>\n

Wrap-up<\/h2>\n

Once all of the tables are completed, the full load is done. The hardest part of this process is connecting the replication instance to the source and destination and that is not difficult at all. All of the synchronization process is handled behind the scenes. It is one of the easiest methods that I know of for migrating an on-premises database into a PaaS database.<\/p>\n","protected":false},"excerpt":{"rendered":"

I have begun writing a few blog posts focused on launching your databases into the cloud. Specifically targeted on database migration methodologies. Choosing a migration method involves making a lot of decisions up-front. This post will assume that Managed Relational Database Service (RDS), which is a Platform as a Service offering by Amazon Web Services […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-174","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/174","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=174"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/174\/revisions"}],"predecessor-version":[{"id":436,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/174\/revisions\/436"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=174"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=174"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=174"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}