What is it? Why do I need it?<\/a>
Getting set up<\/a>
Create a Task using the UI<\/a>
Use the rules to Select<\/a>
Transform<\/a>
Create Task<\/a>
Additional Resources<\/a><\/strong><\/p><\/blockquote>\n\n\n\n
\n\n\n\nWhat is it? Why do I need it?<\/h3>\n\n\n\n
AWS Database Migration Service (AWS DMS) is a web service that allows you to migrate data from one database to another. You can move database objects from the same kind of database engine or a different database engine. The only requirement is that one of them needs to be an AWS service.<\/p>\n\n\n\n
With that out of the way, this is how it works and how I found the experience.<\/p>\n\n\n\n
\n\n\n\nGetting set up<\/h3>\n\n\n\n
To get the ball rolling you need to set up a replication instance<\/a>. There are some decisions to make based on your use case that you can read about in the documentation<\/a> but the console makes this a straightforward process.<\/p>\n\n\n\n
You then need to create the endpoints<\/a>. The source and target data stores can be on an Amazon EC2<\/a> instance, and an RDS instance, or an on-premises database.<\/p>\n\n\n\n
Image Source: AWS Documentation<\/figcaption><\/figure><\/div>\n\n\n\n
\n\n\n\nCreate a Task using the UI<\/h3>\n\n\n\n
The good thing about using this service is that you can use the UI to run your tasks. This part requires you to specify the database endpoints and a description.<\/p>\n\n\n\n
Image from AWS Documentation<\/em><\/figcaption><\/figure><\/div>\n\n\n\n Limitations<\/strong><\/p>\n\n\n\n
- While the UI is user friendly you need to create these one at a time and can’t clone them to repeat the process if you have multiple tables.<\/li><\/ul>\n\n\n\n
\n\n\n\nUse the rules to Select<\/h2>\n\n\n\n
Use the Selection rule to isolate which table or group of tables you want to migrate:<\/p>\n\n\n\n
<\/figure>\n\n\n\n
\n\n\n\nTransform<\/h2>\n\n\n\n
Use the Transformation rules to make sure the data arrives as expected. Options here are to make changes at Schema, Table and Column level.<\/p>\n\n\n\n
<\/figure><\/div>\n\n\n\n Limitations<\/strong><\/p>\n\n\n\n
- I found I was unable to use two transformation rules of the same type. If I used two Table<\/strong> transformation rules sometimes it would pick one of them and sometimes both, resulting in tables that needed to be cleaned up. Not so good when you don’t have the permissions on the database side and need to sheepishly ask your DBA to script a fix.<\/li>
- Timeouts were very frustrating at this point. The console would refresh and if you were midway through creating transformation rules you would have to start over.<\/li><\/ul>\n\n\n\n
\n\n\n\nCreate a Task<\/h2>\n\n\n\n
When you select ‘Create task’ the task runs and gives you a progress report from the main screen.<\/p>\n\n\n\n
If you make a mistake you can select ‘Modify task’<\/a> from the main dashboard screen and rerun the task with your changes.<\/p>\n\n\n\n
<\/figure>\n\n\n\n Limitations<\/strong><\/p>\n\n\n\n
- The documentation says that eight tasks should run at once, at max we found that four could run concurrently.<\/li>
- There were issues with encoding and dealing with UTF-8 and UTF-16 characters. We got around it by changing the datatype length on the destination column but it was tedious.<\/li>
- The documentation<\/a> sold us on the ability to read ongoing changes from the source database but sadly this is not supported on SQL Server Standard. Keep this in mind if you are not on the Enterprise edition.<\/li><\/ul>\n\n\n\n
\n\n\n\nUsing the Schema Conversion Tool<\/a> and Database Migration Service was useful. There were a few limitations to be aware of but overall it went smoothly.<\/p>\n\n\n\n
\n\n\n\nAdditional Resources<\/h3>\n\n\n\n
- Troubleshooting pages<\/a><\/li>
- Webinars and videos from AWS<\/a><\/li>
- Best Practices<\/a><\/li><\/ul>\n\n\n\n
\n\n\n\nPhoto by Daria Shevtsova on Pexels <\/p>\n","protected":false},"excerpt":{"rendered":"
The AWS Database Migration Service promises to take a lot of the pain out of migration, here’s how it works at a high level.<\/p>\n","protected":false},"author":1,"featured_media":2602,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"Layout":""},"categories":[11,147],"tags":[171,173,177],"yoast_head":"\n
Moving data with the AWS Database Migration Service | Helen Anderson<\/title>\n<meta name=\"description\" content=\"The AWS Database Migration Service promises to take a lot of the pain out of migration, here's how it works at a high level\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.helenanderson.co.nz\/database-migration-service\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Moving data with the AWS Database Migration Service | Helen Anderson\" \/>\n<meta property=\"og:description\" content=\"The AWS Database Migration Service promises to take a lot of the pain out of migration, here's how it works at a high level\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.helenanderson.co.nz\/database-migration-service\/\" \/>\n<meta property=\"og:site_name\" content=\"Helen Anderson\" \/>\n<meta property=\"article:published_time\" content=\"2018-10-30T12:30:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-08-24T00:58:29+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/helenanderson.co.nz\/wp-content\/uploads\/2018\/10\/aws-database-migration.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1280\" \/>\n\t<meta property=\"og:image:height\" content=\"853\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@helenanders26\" \/>\n<meta name=\"twitter:site\" content=\"@helenanders26\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\">\n\t<meta name=\"twitter:data1\" content=\"3 minutes\">\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebSite\",\"@id\":\"https:\/\/13.237.200.153\/#website\",\"url\":\"https:\/\/13.237.200.153\/\",\"name\":\"Helen Anderson\",\"description\":\"Data Analyst | Technical Writer\",\"publisher\":{\"@id\":\"https:\/\/13.237.200.153\/#\/schema\/person\/4677a271385757403307fb29bd14d7bf\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":\"https:\/\/13.237.200.153\/?s={search_term_string}\",\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-NZ\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.helenanderson.co.nz\/database-migration-service\/#primaryimage\",\"inLanguage\":\"en-NZ\",\"url\":\"https:\/\/helenanderson.co.nz\/wp-content\/uploads\/2018\/10\/aws-database-migration.jpg\",\"width\":1280,\"height\":853,\"caption\":\"aws-database-migration\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.helenanderson.co.nz\/database-migration-service\/#webpage\",\"url\":\"https:\/\/www.helenanderson.co.nz\/database-migration-service\/\",\"name\":\"Moving data with the AWS Database Migration Service | Helen Anderson\",\"isPartOf\":{\"@id\":\"https:\/\/13.237.200.153\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.helenanderson.co.nz\/database-migration-service\/#primaryimage\"},\"datePublished\":\"2018-10-30T12:30:39+00:00\",\"dateModified\":\"2020-08-24T00:58:29+00:00\",\"description\":\"The AWS Database Migration Service promises to take a lot of the pain out of migration, here's how it works at a high level\",\"inLanguage\":\"en-NZ\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.helenanderson.co.nz\/database-migration-service\/\"]}]},{\"@type\":\"Article\",\"@id\":\"https:\/\/www.helenanderson.co.nz\/database-migration-service\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.helenanderson.co.nz\/database-migration-service\/#webpage\"},\"author\":{\"@id\":\"https:\/\/13.237.200.153\/#\/schema\/person\/4677a271385757403307fb29bd14d7bf\"},\"headline\":\"Moving data with the AWS Database Migration Service\",\"datePublished\":\"2018-10-30T12:30:39+00:00\",\"dateModified\":\"2020-08-24T00:58:29+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.helenanderson.co.nz\/database-migration-service\/#webpage\"},\"publisher\":{\"@id\":\"https:\/\/13.237.200.153\/#\/schema\/person\/4677a271385757403307fb29bd14d7bf\"},\"image\":{\"@id\":\"https:\/\/www.helenanderson.co.nz\/database-migration-service\/#primaryimage\"},\"keywords\":\"aurora,aws,project\",\"articleSection\":\"AWS,Data Technology\",\"inLanguage\":\"en-NZ\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/13.237.200.153\/#\/schema\/person\/4677a271385757403307fb29bd14d7bf\",\"name\":\"Helen Anderson\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/13.237.200.153\/#personlogo\",\"inLanguage\":\"en-NZ\",\"url\":\"https:\/\/helenanderson.co.nz\/wp-content\/uploads\/2019\/11\/helen-anderson-profile-selects-FA-1000.jpg\",\"width\":1000,\"height\":1000,\"caption\":\"Helen Anderson\"},\"logo\":{\"@id\":\"https:\/\/13.237.200.153\/#personlogo\"},\"description\":\"Hi, I'm Helen. I'm a data analyst, technical writer, and AWS Data Hero. I interpret the story behind the numbers, build data applications, and grow analyst and developer communities - currently at Kiwibank.\",\"sameAs\":[\"http:\/\/www.helenanderson.co.nz\/\",\"https:\/\/www.instagram.com\/helenanders26\/\",\"https:\/\/www.linkedin.com\/in\/helenanders26\/\",\"https:\/\/twitter.com\/helenanders26\",\"https:\/\/www.youtube.com\/channel\/UCttVhJizwkhgmMlDBMUE0wQ\"]}]}<\/script>\n","_links":{"self":[{"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/posts\/434"}],"collection":[{"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/comments?post=434"}],"version-history":[{"count":22,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/posts\/434\/revisions"}],"predecessor-version":[{"id":3451,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/posts\/434\/revisions\/3451"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/media\/2602"}],"wp:attachment":[{"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/media?parent=434"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/categories?post=434"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/tags?post=434"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}