Get started with AWS DMS Schema Conversion- AWS Database in 15

Key Takeaways

foreign [Music] thank you for joining me today for this online tech talk my name is Rachel Foo and I am a senior technical product manager with the AWS database migration service so today I'm going to talk about a recent feature launch which is DMS schema conversion so in this presentation we will first cover a brief overview of AWS database migration service then an overview of the new DMS schema conversion feature finally we'll run through a demo of how the new feature works so one quick note before we begin at any time during this session if you have questions feel free to post them in your viewing Channel we have experts who are monitoring to be able to answer your questions so DMS focuses on discovering and assessing a fleet of databases and then subsequently converting and migrating them to AWS and this is all with minimal downtime and zero data loss so DMS supports a wide variety of sources both relational and non-relational and supports an even wider variety of targets so when you're migrating with DMS you can keep your application running during the migration since DMS provides continuous replication by leveraging you know the source databases underlying change data capture or redo log type of Technology so in the assessment phase of migration DMS provides Fleet advisor and this is really geared towards customers looking to move a fleet of databases to the cloud so this tool inventories and analyzes your data to quickly create a migration plan in the mobilize phase AWS provides the schema conversion tool or SCT and this tool assesses schema compatibility of source databases it attempts to convert all schema and code objects in the migrate and modernize phase DMS migrates the data with features like pre-migration assessments and data validation to help ensure that the migration is completed properly in today's session we're really going to focus on this mobilized phase so the existing AWS schema conversion tool is locally installed and it really offers three main features so the first feature is generating an assessment report that provides a detailed summary of the recommended best Target engine for migration as well as a detailed level of effort in order to complete the migration so SCT also attempts to convert all schema and code objects to the Target engine and this includes stored procedures and functions as well so finally SCT also has local extractors for migrating data warehouses to redshift this type of migration also includes the conversion of ETL related business logic so that the ETL processes can be run in AWS glue so because AWS SCT is locally installed and lives outside of DMS customers have to move back and forth between DMS and this external tool to be able to complete a migration with the launch of DMS schema conversion DMS now provides SCT functionality integrated into DMS as a fully managed capability so DMS now offers an end-to-end database migration solution under one centrally managed service and this makes it simpler and faster to plan assess convert and migrate to the cloud this can reduce manual pre-migration tasks from taking something like weeks or months down to something like hours so today DMS schema conversion supports conversion between Oracle or Microsoft SQL Server to mySQL or postgres and we have plans to expand the engines that we support as well so now let's take a look at the features that are provided by DMS schema conversion so similar to SCT DMS schema conversion provides a schema assessment report that summarizes all of the schema conversion tasks so the summary tab shows the number of items that DMS schema conversion can automatically convert for database storage objects and database code objects so this tab also provides an estimate of the required effort to create those schema items in your target database instance that are equivalent to the ones in your source so the action items tab contains a list of items that DMS schema conversion can't automatically convert to a format that's compatible with the target database engine for each action item DMS schema conversion provides the description of the issue as well as prescriptive guidance on manual conversion DMS schema conversion groups similar action items and then displays the number of occurrences so after connecting to your source and Target databases you can convert your Source database objects to a format that's compatible with your target database DMS schema conversion displays your Source database schema on the left panel in the tree view format and then the targets on the right so each node of the database tree is what we call Lazy loaded when you choose a node in the tree view DMS schema conversion requests the schema information from your Source database at that time if you want to browse the database objects faster you can load the metadata so that DMS schema conversion reads the database metadata and stores the information on an Amazon S3 bucket you can convert the whole database schema or you can choose any schema item from your Source database to convert if the schema item that you choose depends on a parent item then DMS schema conversion also generates the schema for that parent item so for example when you choose a table to convert DMS schema conversion creates the converted table and the database schema that the table is in so after you've converted your Source database objects you can choose an object in the left panel to view the source and converted code for that object you can also see the properties or parameters of the object that you've selected so DMS schema conversion automatically stores the converted code as part of your migration project it doesn't apply these code changes to your target database automatically when you're ready applying changes is an action that you initiate with a simple click in the console if you don't want to apply the converted code directly to your target database in DMS schema conversion you can save the code to a file as a SQL script you can then review these SQL scripts edit them where necessary and then manually apply these SQL scripts to your target database all right so now let's jump into the demo so here is what schema conversion looks like inside of DMS so we have this new section in the side navigation called convert that has these new entities migration projects instance profiles and data providers so migration projects are projects that are used to migrate and this is where DMS schema conversion is launched from instance profiles or common security and network settings across different projects and then finally data providers are information about source and Target database effectively it's the connection information so let's go ahead and start with creating an instance profile so here we can provide the name for the instance profile optional description in Arn you have options for IPv6 and then we're choosing standard AWS properties like VPC and the subnets so here we also select an S3 bucket where we want to save the migration project and then we also need to provide the IAM role that gives DMS access to that S3 bucket so now we can go ahead and create that instance profile so next we're going to go in and create the data providers that are needed for source and Target so in this case um we are going to connect to a SQL Server RDS database instance you can give a descriptive Arn if you want here we have a standard server name Port database name as the connections information even though we're using an AWS resource for this demo you could of course put the address of an on-prem system instead so we've created our source data provider next we're going to create the target data provider which is an RDS for MySQL Target so the migration we're looking at is from SQL Server to mySQL um so similarly we're going to put in the name we've got the connection information populated and we're not using SSL for for this demo so from here we can go and create the migration project so we'll create that new migration project we'll give it a a name and then we're going to select the instance profile that we created and then we're going to select what we're migrating from so our source SQL Server database the database credentials are stored in Secrets manager so here we select the secret ID that contains the credentials and then the IAM role that gives DMS access to Secrets manager so for the Target we're similarly selecting uh you know our MySQL data provider and then again the secret ID with the database credentials and then that same IAM role to give DMS access to that secret so optionally you can add a transformation so for this case we are going to add a suffix which is called just converted but as you saw you can do other Transformations as well so we're going to go in and create that migration project so when we select the migration project that we created we can click on the schema conversion Tab and this is where we're going to launch schema conversion so when we hit launch we're launching an ec2 instance to do the assessment and conversion for you so normally this takes some time but we're speeding it up for the demo so once the instance is launched it brings up the IDE which is really similar to SCT with the source on the left and the Target on the right so note that DMS schema conversion uses database metadata for doing assessments and conversions it doesn't access any customer data so we're going to first choose the database that we want to run an analysis on so in this case it's the one at the top and the first thing we're going to do is run the assessment so after we run the assessment we're going to get the assessment report results here and this is telling us you know how much can easily be converted between our source and Target so you can browse down through the tree to see each individual item so that you can see the assessment reports down at the level of you know a given table or given procedure that you choose and this gives you an idea of you know how easy or hard this particular item is going to be to convert so when you select an object on the left you can also drill down and see the SQL code that's used to create that procedure so you can then export the assessment report to either CSV or PDF and you can use this document to help support a business case or assign manual conversion action items to different database specialists so here we're going to view that assessment report PDF it's generated in your S3 bucket um so we can take a look here it's got a summary of those charts and executive summary and then also kind of a listing of the action items all right so once that's done um the next step is to convert the database so we see that uh once we've done this conversion so we'll see that all the objects on the left are converted to the right in our mySQL database instance so we can see we have some case differences and the schemes have been renamed according to those transformation rules that we specified foreign so as you click into each object you can see what the original SQL Server code looked like on the left and then the converted MySQL code on the right so as we dig down into this um we can see that you know not everything is automatically converted so we can see an action item that we can dig into to see okay what do we need to do to handle this conversion manually um for other objects that were you know converted automatically we can see no action items and then we can see from the summary um you know that there was 100 percent uh conversion automatically happened so at this point now we can apply the changes to the Target so as I mentioned before the code changes are stored in the migration project and so here is where we're actually applying those changes to the Target database so the other option that we have is um to save the code changes uh as SQL and so this is what we're doing here and then we can view the SQL code that is uh saved as a zip file to our S3 bucket um as well so we'll go take a look at our S3 bucket and then we can view that SQL code um that was saved here so instead of you know applying the code changes in DMS schema conversion we could look at this SQL code we could edit it do whatever we need here and then we could use it to apply the changes manually all right so now let's go in and take a look at our Target database so here we can see the converted objects effectively we have Empty Tables defining the schema with that converted uh suffix that we added and then from here we can move on to our next step of migrating the data all right and that concludes the presentation thank you so much for watching and I really hope you have a great rest of the week