1. Database migration overview
All right, in this section we are going to talk about database migration, DMs and Sat. And this is a very important topic as far as the certification exam is concerned, as a good part of the exam would contain questions about migration, DMs and Sat. So let’s begin, shall we? So why do we need database migration? Migration? Let’s look at some of the use cases of database migration. So you have a source database and a target database, and you move your data, let’s say, from a legacy database to Cloud. So that’s one of the use case that we can think of. We use database migration to switch the database solution. But that’s not all the database migration solutions that AWS offers.
DMs and SCT can be used for many more use cases than just migrating from legacy to Cloud. So database migration is a little more than just migration. The use cases are expanding. We can also use the Database Migration Service for replicating databases across regions. We use it for cross region replication, we can use it to replicate to streaming platforms. We can use DMs to perform in place database upgrades. We can use it to archive data to S three and so on. Here are a couple of migration scenarios we can migrate from on premise to AWS Cloud. We can migrate from relational to non relational database.
We can migrate from a database hosted on EC Two to a managed AWS service like RDS and Aurora. So these are a couple of migration scenarios we can use with Database Migration Service. All right, now let’s look at some of the migration tools or migration solutions that are available to us. So for migration, we can use native tools like MySQL Dump, and we can also use the AWS tools like FCT and DMs. SCT stands for Schema Conversion Tool. And DMs stands for Database Migration Service. And AWS also provides us with migration playbooks. These are ready to use templates along with best practices guides that assist us during our migration process.
AWS provides us with three tools or three solutions SCT, DMs and Playbooks. SCT is for schema conversion, DMs is for data migration, and Playbooks provide you with templates and best practices guides. All right, these are the tools that are part of the migration toolset from AWS. All right, so let’s look at a typical migration process. First, you plan your migration, perform assessment of your migration, then you convert your Schemas, and finally you migrate your data. SCT or the Schema Conversion tool helps us in the first three steps planning, assessment, and schema conversion. And for migration, we use the DMs or Database Migration Service.
Again, this is a generic representation. The migration process flow is something like this. So if we have a homogeneous database migration, in a homogeneous migration, the source as well as the target database engine is same. For example, we could have a SQL Server running on premise and we migrate it to SQL Server running on RDS. So we migrate between the same database engines, SQL Server in this case. So that’s called as homogeneous migration. Then we have heterogeneous migration where source and the Target Database Engine is different. So for heterogeneous migration, there are two steps.
First we convert the schema of the source database to match the Target Database engine. So we use SCT for that. And once we have migrated the schema, then we can migrate the data in the second step using DMs. So we use SCT for schema conversion in the first step. And then in the second step, we migrate data using DMs. So this is the process for heterogeneous database migration. And a third scenario is data warehouse migration. In data warehouse migration, we use SCT for schema conversion. And then we move the data to s three. And then from s three we move the data to Redshift, which is the data warehouse solution from AWS. So this is the migration flow for data warehouses. All right, so that’s about it. Let’s continue to the next lecture.
2. DMS sources and targets
Now let’s talk about different sources and targets supported by DMs. So we have these different sources and targets. These are the relational database sources and targets. So we can migrate from Oracle SQL Server, MySQL Maradb, Postgres, RDS, Aura, SAPS, DB Two, and SQL Azure to one of these databases that run on RDS or Aura. So we can migrate to Oracle SQL Server, MySQL MariaDB postgres, SQL on RDS and Aura. So these are the relational database sources and targets. For non relational databases, we can migrate from MongoDB or Cassandra to DynamoDB or Document. And in case of analytical workloads, we can use s three and snowball to migrate to s three kinesis or elasticsearch service. In case of data warehouses, we can migrate from any of these data warehousing solutions like Oracle SQL Server, Teradata, Netza, Vertigo, Green, Plum, to Redshift.
So essentially, you can migrate from any supported DMs source to any supported DMs target. So you can essentially migrate from any of the 14 sources that are listed for databases to any of the twelve targets that are listed for databases. And of course, in case of data warehouses, you can migrate from any of the listed data warehouses to Redshift. So for database migration, we use DMs, and this is true for relational databases NoSQL as well as analytical workloads. And for data warehouse migration, we use something called as SCT extractors. And we’re going to talk about this in detail later in this section. All right, so that’s about different sources and targets. Let’s continue to the next lecture.
3. DMS architecture and overview
Now, let’s talk about database migration service. We use DMs to migrate data from source database to a target database. So DMs allows us to quickly and securely migrate databases to AWS cloud. It’s resilient and self healing. And when we use DMs, the source database remains available during the migration process. And DMs migration typically results in minimal to zero downtime. And costs as little as dollar 3 /tb DMs supports both homogeneous as well as heterogeneous migrations for heterogeneous migrations as I said we use DMs along with SCT. DMs also supports continuous data replication using CDC or change data capture, which is what gives us minimal to almost zero downtime.
All right, so let’s continue and take a look at DMs architecture. This is what DMs architecture looks like. We have a source endpoint, we have a target endpoint. These endpoints include the database connection information or the credentials information, so DMs can read from the source database and write data to the target database. And to do this, DMs uses what’s called as a replication instance, which is nothing but an easy to instance that’s running in a VPC. And you can place this instance in a Multiac setup as well for high availability. And this replication instance runs what’s called as a replication task. Replication task is what migrates your data from source to the target.So you can use the replication task to specify the data that you want to migrate as well as the data transformation rules.
So the way you perform DMs migration is you first start a replication instance. You connect to the source and target databases, then select the table schemas or databases. And then DMs reads data from source and loads it into the target database and keeps it in sync if you are using CDC or change data capture. And once the data is in sync, you can switch applications over to the target any time and remember, the DMs replication instances will not appear in your EC to Console. You can only see them in your DMs console. But what you can see in your EC to Console is the DMs network interfaces that get created when you set up your application instance.
All right, so DMs can create target tables, primary keys if they do not exist on target, or you can also create them manually. But remember that DMs only creates those objects that are really required for an efficient migration. So it will create the primary keys or unique indexes, but it’s not going to create the remaining elements like secondary indexes, foreign key constraints, and so on. This is what is called as a basic Schema copy, and you can definitely use SCT or other native Schema export tools to migrate the remaining database elements or Schema. That was a quick overview of DMs. Now let’s go into a hands on session and we’ll perform a simple migration using DMs.
4. Migration with DMS in action – Hands on
All right, in this lecture I’m going to carry out a simple DMs migration. This is going to be a very basic bare bones example just to help you get familiar with the interface or with the DMs console. So here I have two MySQL databases running on RDS and what I’m going to do is I’m going to migrate data, data from database One to database Two using DMs. All right? So I have these two databases created and I have my clients connected to the source database and the Target database. So as you can see, the Target database is empty. I have a database called My Database in the Target MySQL Database and the source database is also empty currently.
What I’m going to do is I’m going to create a simple table. I’m going to create this table named instructors with an ID and a first name and last name. All right? So the table is created, let’s add some data to it. All right, so I have added about two records to this instructor’s table. So let’s query that. So you have two records in this table and you have one table in this database. So what I’m going to do is I’m simply going to migrate this My database into the Target database. So if I go to the Target database now if we look at the Target database, there is no data in here. If you run the same query in the Target, it’s not going to work. Right? So there is no such table in the Target My Database database.
All right? So this is our simple, simple use case that we are going to use to experiment with DMs. Okay? So these are the two databases that we just connected to. Let’s go into the DMs service. And the way we do migration is we first create a replication instance that carries out your migration tasks. Let’s create one my replication instance. You can give a description if you like, DMs replication instance and you can use any of the instance class. I’m going to use T two micro. Then you can use a VPC. I’m going to use the default one. It should be publicly accessible for our use case and both of our databases are also publicly accessible. Then you can look through the advanced configuration, but I’m not going to change any of this.
And both my databases are also in the same VPC. All right? So you can choose the maintenance and tags if you like and create your replication instance. So it’s going to take a while for this instance to be ready. So I’m going to pause the video here and come back once this instance is ready. All right, now our replication instance is available. So let’s go ahead and create the source and the target endpoints. So click on Endpoints from the left side menu and here we can create the source and destination endpoints or the source and target endpoints. So click on Create endpoint and first we’ll create the Source endpoint. And since our Source and Target databases are hosted on RDS, we can select this checkbox and then choose our Source instance from here.
And what this is going to do is going to automatically populate the connection information for the source database. Simply put in your password and that’s about it. You can configure additional settings here, like any additional connection attributes or the Kms master key tags and so on. We don’t require any of these, so we can skip that. And finally we can test the connection. So choose the VPC and run the test. And it’s testing right now and we can see that the connection test is successful. So let’s go ahead and create the endpoint. Similarly, we create the Target endpoint. So choose Target endpoint and select the Rdsdb instance and choose Database Two. Provide your password and you can do a test connection in a similar fashion and the connection test is successful. So let’s go ahead and create the Target endpoint.
We have created the endpoints. We have created the replication instance. Now we can go ahead and create a database migration task. So let’s click on the Database migration tasks and then here we can create a task. Let’s click on the Create button and here we can configure our migration task. So give a name to the task. Let’s say MySQL migration task. You can choose the replication instance that we just created, choose the Source database endpoint and the Target database endpoint, and then we can choose the migration type. So we can choose to migrate existing data. You can choose to migrate data and replicate ongoing changes. This is like full load with CDC and we can replicate data changes only. So this is CDC only, so we want to do a full load.
So we choose migrate existing data. And if you keep this checkbox selected, then your task will run as soon as it gets created. All right, then further you can specify additional settings like the Target table preparation mode. So if you want to drop tables on Target, you can choose this. For our case this doesn’t matter because our Target database is empty. Then here you can choose the log mode. Again, this does not apply to our case and we are going to discuss all these settings later in the course. Park it for now. You can also enable validation if you like. We have only two records, it doesn’t require any validation, but if you enable validation, then DMs is going to compare the source and Target data after migration to ensure that the data has been migrated successfully.
And it does take some additional time to complete. So I’m not going to enable validation, but we are going to talk about and discuss the validation options later in this section. All right, then you can also enable Cloud Watch logs to monitor the migration process. Okay, we don’t need that. Here, so I’m going to skip that. Then under table mappings, we can provide the source table selection rules as well as the data transformation rules. So under selection rules, let’s add a new selection rule. And here we can choose a schema. Under Schema we can give the name of our database, for example. So in our case it is my database. And you can confirm this by looking at the source database. You have my database as the schema and the table name.
You can specify a particular table name or you can choose to migrate all the tables. An action would be to include this particular table or exclude it. So we are going to include all the tables. We only have a single table. So if you like you can also specify the name of our table, which is Instructors. Okay, I’ll simply leave it at the wild card setting. All right, you choose the table name and you can also provide transformation rules. Here we simply want to migrate data as is. We don’t need any transformation rules. Then we have advanced task settings. And here you can enable different control tables. The apply exceptions table is enabled by default, and if you like you can enable the remaining three tables as well.
And control tables are typically used for troubleshooting your migration tasks. All right, for our case, we don’t need to touch these advanced task settings, so we can simply leave them to the default values. And then we have tags which are only for organization purposes, so we don’t need them either. We can go ahead and create the task and this is also going to run the task once the task gets created. So I’m going to pause the video here and come back once this task completes. All right. And now we can see that the task is created and the migration task is starting. Let’s come back once the migration completes. And now the migration task is running and it is complete.
Now the full load is complete. Let’s click on the migration task for more details. So if we look at the table statistics, we can see that the table load is complete. So we loaded two rows and we did not enable validation, so it is not enabled, but the load has been complete. So let’s go back to our SQL electron client. And now if we run this select statement again, we should see the data from our source database. So let’s do that. And here we go. So we have successfully migrated source data from MySQL database to another MySQL database. So that’s about it. And I hope this simple example gives you a basic understanding of how to use DMs for data migration. All right, with that, let’s continue to the next lecture.
5. SCT overview
Now, let’s talk about SCT or the Schema conversion tool. We use SCT to migrate or convert source schema into the target Schema. So SCT converts your database or data warehouse schema from the source to target. And this includes all the procedures, views, secondary indexes, foreign key constraints, and so on. This is mainly for heterogeneous database migrations and data warehouse migrations, but you can definitely use it for homogeneous migrations as well. And in that case, you can use it for migration assessment. And we’re going to talk about this later in this section. So SCT is a tool that you can install on an EC to instance or locally on your computer.
So ideally, you install it closer to your source database. And SCT also supports application conversion, so it can convert your SQL statements embedded in your application code. So that’s terrific, right? And it can also help you with script conversion. So it can convert your ETL scripts like Oracle Microsoft Teradata scripts and convert them into Aura scripts or rest shift specific scripts. And you can use SCT to optimize your rest shift. Schemas? So the SCT optimizer recommends us different distribution and sort keys that you can use for your Redshift tables.
And this is definitely different from the native Redshift advisor that we have in the Redshift console. All right. And in addition to Schema conversion, SCT also provides us with an assessment report. This is a granular report that shows us which objects can be converted automatically and which of the objects will require a manual intervention. And it provides this information with color codes. So we can see objects in green get converted automatically. Then some of them will require some simple actions, some of them will require medium actions, and some objects will require complex actions. For conversions, all these objects are marked in different colors.
So it’s easy for you to assess your migration before you actually perform it. So you can use this assessment report to create a business case for your migration that you can present to your management. The assessment report also helps you identify issues, limitations, or actions for Schema conversion and for objects that cannot be created manually. SCT does provide some guidance to help you create the equivalent Schema manually. So this is really a good tool for premigration assessment, and it’s definitely recommended even if you are doing a homogeneous migration. All right, let’s continue to the next lecture.
6. Workload Qualification Framework (WQF)
Now let’s talk about workload qualification framework or WQF. So this is a standalone app that’s included with your SCT installation and you can use it for workload assessment. It provides two types of assessments, so it essentially qualifies your OLTP workloads based on either the ease and complexity of migration or the estimated time and effort required. So you get one report that talks about the complexity of migration and one report that talks about the time and effort required for the migration. And it also recommends migration strategies and tools that you can use for your migration. And as mentioned earlier, this is part of SCT. It’s integrated with SCT and DMs and you simply have to provide database connection strings. And WQF is going to generate these assessment reports for you. And this is available as an AMI on ECT Two or the Amazon Machine image on ECT so you can get started with it quickly.
