 Hello all and welcome to this tutorial on AWS Database Migration Services or DMS. Today we will discuss how to migrate data from my SQL Server to a S3 bucket. Before we go further ahead with the tutorial, a couple of things to keep in mind. First is that not all AWS DMS replication instances are free. So in case you happen to do this tutorial by yourself later, you can potentially incur charges for DMS service and DMS replication instances. Second thing to keep in mind that if you are doing this tutorial later on your own ensure that you clean up your resources after the tutorial is over in order to avoid any future surprise charges. Now for this particular tutorial, it requires a certain amount of preprep and all the steps that I have enlisted on this slide, I have actually gone ahead and completed those steps in order to move further ahead with the actual database migration steps. So you will require a certain amount of preprep that is setting up both the target and the source data sources and also creating a database and a table in my SQL and inserting a few records. So let us review through all the preprep steps. The first thing that you will need to do is you need to create an empty S3 bucket named my DMS bucket for CSV. Now that is the bucket name that I have used in this tutorial. We are free to use any other bucket name as well. Just ensure that the bucket is empty. Even if you have something in the bucket, it should not typically matter. The second thing is launch a MySQL RDS instance in your default VPC or in your custom VPC. Now for this particular tutorial, I have launched my MySQL RDS instance in my default VPC. You can potentially even go ahead and launch it in your custom VPC. When you are launching your MySQL RDS instance, ensure that you use a free tier and you use the instance type as dbt to micro with MySQL version 5.7x. One thing to keep in mind is that the version of MySQL is very important because DMS does not work with any version that is higher than 5.7. It is typically compatible with version 5.5, 5.6 and 5.7. The next thing after we have launched our MySQL RDS server instance is we will launch a Amazon Linux EC2 instance in the same VPC and preferably the same AZ as well. In the security group for our Linux EC2 instance, ensure that you have inbound ports 22 and 3306 open. 3306 is the port used by MySQL and since this is going to be a client and it will communicate with our MySQL server, we need to ensure that port 3306 is open and 22 is for us to SSH and create databases tables or even check insert records and check how many records are there. That's just for basic client-server interaction. The next thing that we need to do is we need to modify our RDS security group. The RDS server that we launched over here creates either its own security group or if you're using an existing security group, you need to ensure that you provide access to EC2's security group and VPC's default security group. The reason why you need the EC2, that is the instance that you created in step 3, that EC2's security group because that EC2 is going to connect to our MySQL server. It is a client and the default VPC security group is because the replication instance that we will launch, it is typically associated with VPC's default security group. If you are creating a separate security group for your replication instance, then you need to ensure that that security group is able to communicate with RDS on port 3306, just like this port right here. The next thing that you need to do after you have done your basic setup is SSH into your EC2 instance and switch or change to the root user and the command for that is sudo su and go ahead and install the MySQL client and this is the command sudo yum install MySQL. After you have installed MySQL, you need to go ahead and create a database called as library and a table named books and insert a few records in the books table. Now what I have done is I've actually created MySQL.txt. It is right here on GitHub, the URL is right here. I will even have it posted in the description of this video. So if you want, you can go ahead and download this file from GitHub as well and use it for your own tutorial. So let us switch to MySQL.txt and look at this particular file. So this is MySQL.txt and as you see the first line over here is create database library and you can use this command to create the database. The next thing that we are going to do is create the table books and this is the schema of our books table. As you see, it has four columns and the primary key is the book ID and after we have created the books table, what you need to do is you need to insert these five records in the books table. So as you see, we have five different books over here. MySQL, Oracle SQL Server, DB2 and Aurora. It is a pretty simple table and keep it simple guys so that you can focus more on the replication rather than focusing on the database and the tables contents. So I will have this particular MySQL.txt posted on this particular GitHub URL so you can probably download it from there and use it for your own tutorial when you are practicing. Okay, so these are the core steps. So let's move on to the next, the actual set of steps where we begin with the data migration. Okay, so before we go there, I'm going to switch to my AWS account and let us review through a couple of things. So the first thing that I want to review is the S3 bucket. So my S3 bucket is right here as you see. It's with the same name and it is empty. So our bucket is there. So our target is present. The next thing you want to ensure that our source is present. So this is my RDS MySQL instance as you see it's right here. MyDMS 2018 and I believe the instance is available and we should be able to see the instance details shortly especially the instance endpoint is very important. So it is right here. Scroll down. So this is the engine as you see is 5.7 dB to micro instances available and this is our endpoint and on port 3306. And again my default security group and my EC2 security group are able to communicate to this particular RDS instance on port 3306. So let's quickly just check that as well to ensure our setup is right and complete. So the security group should come up shortly and there it is. And this is inbound and there you go. This is my EC2 security group and this is my default VPC security group and they're communicating on port 3306. So perfect. And after that, I think, so I've copy pasted my SQL server endpoint right here. This is the endpoint and this is the endpoint that we will use to also connect from a client. Okay, and this is my DP username and password. As you see, they're all the same. I mean, this is just to ensure that I don't forget it guys. Okay. You don't want to spend time debugging that your password was wrong. Okay, so this is coming back to RDS instance and these are rest of the other default properties as you see the all right here. Okay, so our server is also up. So our sources up as well. And finally this is my EC2 instance right here. And this is my EC2 instance. Okay, sorry, that was not that was a security group. Let me quickly switch to my EC2 instance. So this should come up shortly. I have one EC2 instance, which is my client actually, and where the my SQL client has already been installed. And it is up and running right here. And this is the public IP. So you can copy this and you can putty into this right there. Okay. So what I have done is I've actually connected to my EC2 instance right here. As you see, so let me just go ahead and clear everything. So I'll switch to the root account now. And then we will connect to my SQL server so that so the command to connect to my SQL server is this so my SQL minus H the entire DNS of your my SQL server. So hyphen capital P is the port, the username, and then it will prompt you for the password. So I'm going to control C copy this command, paste it right here. And click enter. And again, my password is my DMS 2018. So I'm connected. So let us quickly see what databases are there on this server. So show databases as you see a library databases right there. So use library and database has been changed and we can go ahead and see then what tables are there in this database as you see a books table is present. And then we will say select star from books. And as you see, we have our five records, we can see the schema of the table as well book a title, published a description and all of fire records are available. So in short, we are all set. Okay, our server, a source server target s3 bucket and a client to SQL is also connected and we also have a database and a table up and running. Okay, so let me go back to the presentation in. Okay, so this is the presentation right here. Okay, there it is. So now let us begin with the actual steps for database migration service. So the first thing that we need to do is we need to create an IM role that provides a read write access to s3 remember we are going to create a bunch of folders and create a file in s3 and also read write access to RDS. So that is our first step guys so let us go ahead and do that. So I'm going to switch back to my AWS console. Okay, and let me switch to IM. So this is IM right there. And here we will create a new role. And again, since this is for demo purposes. And I just want to ensure that we have the right right amount of access for the for our role. I'm just going to go ahead and give full access to s3. Okay, and RDS as well. So I'm going to go ahead and create a new role right here. And it is going to be for DMS. And it will select DMS right there. Click next on permissions. And I'm just going to keep this simple guys. Again, the focus is not on security over here. The focus is on database migration. So I'm going to go and select s3. And basically I'm just going to use one of the available policies, instead of creating an inline policy with the, with a set of permissions. And I'm going to review. And I'm going to give this the name as my SQL to s3. Okay. And create the role. So our role should be created in a few minutes. There's sometimes takes a while. So there it is. Okay, so our role has been created. So let us review it and we need to also provide access to RDS. So as you see, it has access to s3 because it's going to write over there. And I'm also going to go ahead and attach policies. Again, I'm going to use one of the available managed policies for ideas, because it needs to attach RDS. So it needs to basically connect to ideas and also have permissions to read the database and the table and also extract of basically select information from our tables. I'm going to just go ahead and provide full access at this time. And the policy has been attached. And as you see, our role has two policies. Oh, I selected the wrong one. It looks like that. I'm going to go ahead and I selected Alexa by mistake. Sorry guys. Okay, there it is. So now have the Amazon RDS full access. Perfect. And this is the ARN for my role. So we'll copy this. And just going to stick it on my notepad right here somewhere. Okay, we will be using the ARN for this role when we set up the actual migration task. Okay, and the endpoints. Okay, so our role has been created. So that was the first step. The next thing is now to go to the AWS database migration service and create the replication instance. And show that you create the instance for the smallest size like dbt to micro again, even if you select the smallest size, you can still incur charges for the replication instance. Okay, only a few instances are free. Yeah, you can still potentially go ahead and still incur charges for your replication instance. I'm going to switch now. And I'm going to go to database migration services right here. And the first thing that we will do is we'll create a replication instance. So click on replication instances. So here is replication instances. And then as you see only list instances labeled free comply with our free DMS program guidelines, you can visit this page. I've, I've tried this several amount of times have typically not seen a free instance but anyways, you can feel free to you know explore this and if you're able to get a free instance. That's perfect. Okay, so I'm going to go ahead and create the replication instance now. Okay, and I'm going to give this a name DMS replication instance and I'm going to give the same as the description as well. And ensure that the T to micro is selected. So it's going to be DMS T to micro and it's going to be my default VPC. It's not going to be multi easy. And you can leave it publicly accessible. That's fine. In advanced ensure that your allocator storage is around eight GB is fine. It's going to be in my default VPC. I'm going to put this in US East one a and the security group that I'm going to associate is going to be the default security group. Remember, we modified our ideas to ensure that with easy to and default security groups can communicate over 3306 port. Okay, and let's go ahead and create this replication instance. So it generally takes a while to create the replication instance. Okay, so I'm going to refresh and see as you see is creating right now. So the next thing that we need to do is after we have created our replication instance is to create a source endpoint for my SQL table, and then test the endpoint. And after that we need to create a target endpoint for our S3 bucket and test the S3 endpoint as well. I'm going to switch back and we are going to create a couple of endpoints. Now, we can go ahead and create the endpoint guys but you will not be able to test the endpoint until the replication instance is going to be available. Okay, so they're still taking some time. So I'm going to go ahead and click on endpoints right now. And let's try and create an endpoint. This right there is coming up. So it's going to be a source endpoint and as you see it gives you an option over here to select RDSDB instance. So check this option. And as you see it automatically selected our RDS MySQL server. And then endpoint identified as a source. And again the source engine is my SQL. And it has also got our servers DNS right there the port is 3306 which is the right port. We are not going to encrypt so it's going to be SSL. And my username is my DMS 2018 and copy this over here. The same username will copy this because the same thing is my password. And. Okay, let us check the any advanced options that we need. We're good with the advanced options right here. And for VPC this is going to be in my default VPC and the replication instance is the one that we just created. I believe us instances up and running now. So it is giving us the option to run the test. So let's run this test and check if it's successful. Okay, so the replication instance is still not active. Once the replication instance is active only then it will allow me to go ahead and test this endpoint. So I'm going to give this a few minutes and I'll pause my recording. And once the instance is up and it allows me to test the endpoint. I will resume recording again. So server is up and running now and it's available as you see is right here. It's available now. So we should be able to now test this. So let us try and test this endpoint. So click on run test and generally testing the endpoint also takes a while and hopefully all our params are correct. And the test is successful if the test fails and we love to figure out if any parameters are incorrect. If you are trying this by yourself and the endpoint connection test fails, just check your the DNS of your server or the port or the username or the password. These are the generally the four parameters which you know you tend to goof up. Okay, so our connection was tested successfully and a source has been created. So we're going to go ahead and click on save right here. And as you see our source endpoint is now active. The next step for us was to basically create the target endpoint as you see over here for our S3 bucket and test that and so let's go ahead and do that. Click on create endpoint and this is going to be a target and we're going to give it the name as S3 target and the engine over here is going to be S3. And we need the service access role here and I'm going to go ahead and copy the ARN of our role right here. And paste it and the bucket name that we had was mydms bucket for CSV. I'm not going to give any folder over there in the advanced options. Just leave it blank at this time. VPC is going to be default VPC. And the instance is the instance that we just created the replication instance and click on run test. So it takes a couple of minutes to complete this endpoint testing. And hopefully this is successful. Again, if this part is not successful or if you get any errors, definitely check your bucket name. Check the role access. It should have for retried access to S3. And that was one of the main reasons. I mean, when I was trying this earlier, I was getting a lot of errors. So I just kind of decided to go ahead and give full access to the particular role. Okay, so connection was tested successfully. So our target endpoint is also active and connected. So after we have created our endpoints, the next step for us is to basically go ahead and create the actual task to migrate the data. And this task will actually migrate the data from our MySQL, which is a source and to our S3 bucket, which is our target endpoint. And after we have created the task, we need to run the task. And finally, after a task is run, and it runs successfully, hopefully. In that case, it should create a .csv file in our S3 bucket under the folder library and a subfolder called as books. Okay, so let's click on task right here and create a new task. And we are going to give this a name as MySQL to S3. Okay, this is our application instance right there. This is a source endpoint. As you see it selected our MySQL source endpoint, our S3 target endpoint, and migration type is migrating existing data. And we check the start task on create because we want this task to immediately start. In target drop tables and target is fine. Anyway, this is an S3 and you can do much on S3 except like creating a table or dropping a table or truncating. So it's not actually a table, it's a folder essentially and a file. So drop tables on target option is just fine. We don't have any LOBs. I'm not going to include any LOBs right there. And then for our table mappings right here, we're going to use the guided version. So you want to select where? So the name of the schema as you see is libraries coming up on its own because endpoint is connected. And the name of the table is books and the action is include and say add selection route. So basically it is going to look for the schema library and look for the table books and migrate all the data from this particular books table into a CSV and post that CSV in our S3 bucket. So go ahead and click on create task and after our task is created. Remember, we had checked the start task on our create option. So it should immediately start after it has been created. Now here you can see the overview and here you can see the actual table statistics as to how many records were read. And loaded to our in our S3 bucket in the CSV essentially. So keep on refreshing this as you see right here still in. It is still creating right now. So once it is in a running state, then we will be able to see so as you see starting now. So try and see okay there is nothing over here in table statistics again. And the moment it is in the running state, we should be able to see something in our table statistics, you know how many records. So remember we have five records and all those five records should be extracted and copied to a CSV file. So it is still starting. Okay. Okay, there it is. I think it's already ran. It's not it's not refreshing correctly. But as you see, library schema books, table completed so it's already completed the road. It has loaded five records in total. Okay. And there it is it was running and then it went directly to load complete awesome. So if we go back now to our S3 bucket. This is our S3 bucket right here. And we should be able to see the library folder and the sub folder called as books. And in that sub folder we should see our CSV file. So click on our bucket right here. As you see this is our library folder. Our library folder is books under books is our CSV file right there. So there it is. If you want you can go ahead and download this file and try and open it. Okay. So let me try and open this. It is probably downloaded in my downloads folder. So it is downloads there, my CSV right there. And you can open this in Excel. And it should actually have all the records in this. So as you see, This is right here. So as you see all our records, all the five records have been extracted out successfully to our CSV file. Okay. So that's pretty much it from me guys. I mean this is this was a quick tutorial on AWS database migration services. And you can pretty much migrate from any database to any database. I mean this one is my SQL to S3. You can even go to reverse that is from S3 to my SQL. And I'll have a separate video created for that, or probably from my SQL to SQL server postgres or my SQL to Aurora or potentially any other database. So hopefully this tutorial was helpful. And it cleared your doubts and later good foundation around database migration services. The steps are the same. A few properties will change here and there, depending upon the type of database but the task and the whole process of migrating data from one database to the other database is, is the same. Okay guys, so thank you very much. And please post your comment if you like my video do like it. And please subscribe to my channel. So you would be the first one to know the moment I post any new videos. So thank you and have a nice day. Bye bye. Hello all and welcome to this AWS tutorial on AWS database migration service or DMS. Today we will discuss how to migrate data from a S3 bucket to a my SQL RDS database. Before we go further ahead with the tutorial, a couple of things to keep in mind. Not all AWS DMS replication instances are free and using DMS service can incur charges. The second thing that you want to keep in mind is to ensure that you clean up resources after this tutorial is complete. This is to avoid any unwanted or surprise charges in the future. For this particular tutorial, I have actually done certain amount of prep. And before going further ahead with the actual DMS steps, please ensure that you complete these steps ahead of time. Okay, so let us review the steps that are required. The first thing that we need to do is we need to launch a my SQL RDS instance in our default VPC. Now you could launch this instance in your custom VPC as well. Ensure that you use free tier and you use the instance type as dbt to micro with my SQL version 5.7 5.7.x. Okay, now the my SQL versions that are compatible with AWS DMS are 5.5 5.6 and 5.7. So you could use pretty much any of those. Anything that is above 5.7 is not compatible with AWS DMS. After we have launched a my SQL RDS instance, the next thing that we will do is we need to launch a Amazon Linux EC2 instance with public IP in the same VPC. So if you have launched your my SQL RDS instance in your default VPC, then ensure that the EC2 instance is also in the same VPC. We will be using this EC2 instance as my SQL client to connect to my SQL RDS server. Ensure that ports 22 and 3306 are open in EC2 security group for inbound communication. The next thing that we need to do is we need to modify my SQL RDS security group to provide inbound access to our EC2 security group and VPC's default security group on port 3306. After that, we need to SSH into our EC2 instance and switch or change to the root user. The command to do that is sudo su and then the next thing after that we need to do is we need to go ahead and install my SQL client. The command to install my SQL client is sudo yum install my SQL. So after we have completed all of the above steps, the last step as a part of pre-prep is to ensure that we create an empty bucket called as myDMS2018. So I have already gone ahead and completed all of these steps. So ensure that you complete all these steps before moving further ahead and then you can follow through the tutorial step by step. So let us review through all these steps that I have completed so far. Let me switch to my AWS console right here. So this is my AWS console over here guys. And as you see, this is my security group. Hold on, let me switch to my RDS instance over here. Okay, so let me click on DB instances. So this is my SQL RDS instance right here as you see myDMS2018. If I click on that, you will see that the engine type is 5.7.23 and the DB instance class is DBT2 micro. Our RDS instance is not currently available. And this is the endpoint. So make a note of this endpoint because we will be using this endpoint to connect to our RDS instance from our databases right here. Okay. Okay, and it is going to communicate on port 3306. And this is our security group for my SQL RDS instance. So if I click on the security group, we can look at its details. Remember, as part of our prep steps, we have to ensure that our EC2 security group and our VPCs default security group are able to communicate with this server on port 3306. So as you see, this is my EC2 security group. And this is my VPCs default security group. And they are able to communicate on port 3306 with this particular my SQL RDS server. So I'm going to go ahead and cancel this now. Okay, so this is our RDS right there. And at the bottom, again, it's all the rest of the properties as you see, we have our ARN. And as you see, it's using the storage as 20 gigabits. Multi AZ is not enabled because this is just for tutorial or demo purposes. And the rest of all the other properties are still set as default. So I'm going to leave this as is. And the next thing that we will review is our EC2 instance, which is right here. Now this particular EC2 instance is my SQL client. So as you see it's running, it has a public IP associated to it. And this is my EC2 security group. So let's go ahead and click on that. You need to ensure that this particular security group has 422 and 4306 open for inbound communication. Now I have actually gone ahead and SSH into our EC2 instance right here. And I have also installed my SQL client on this machine. So as you see, the installation has been completed. Okay, so our RDS server and my SQL client, both of them are up and running. The next thing that we need to do is we need to check on our S3 bucket. So let's click on S3 over here. And this is my bucket right here. It is my DMS 2018. And as you see, this is an empty bucket at this point of time. So all our pre prep steps are complete and we are all set to move further ahead with the actual DMS step to perform migration from S3 to my SQL. So let me switch back to my presentation and go in the next slide. So these are all the steps to migrate data from S3 to my SQL. The first thing that we need to do is that we need to create a role that provides read access to S3 and write access to RDS. And after we create this role, we need to note its ARN. So let me switch back to my AWS console, which is right here. And I'm going to switch to IAM and we will create this particular role. So click on roles, create new role and choose the service that will use this role. So in this case, it is going to be DMS and click on DMS, click on next permission. Now remember, we have to provide read only access for S3. So let's search for S3 and say Amazon S3 read only access. Next review and we will give this name as S3 to my SQL. And let us click on create role. Now, along with read access for S3, we also need to provide write access for RDS, because that's where it is going to actually go ahead and create a database, then create a table and then eventually insert all the records in that table. Now, just to keep things simple, I'm actually going to go ahead and provide full access to RDS over here. But if you're doing this in production guys ensure that you create an inline policy and then provide access as per whatever actions that you need to perform. Okay, I'm just keeping this simple over here because the focus of this video is not security. The focus of this video is DMS. Okay, so let us go ahead and attach policies. And we will look for RDS. And I'm going to go ahead and attach Amazon RDS full access and click on attach policy. And as you see our policy has been attached. So now our role has full access to RDS and read only access to S3. So let us go ahead and note its ARN. So go ahead and copy this. And I'm going to keep it aside over here in my notepad. Okay, so let me switch back to my presentation. So we've created this role and we've noted its ARN. The next thing is to go through S3 to our S3 bucket and create a folder called as address and then a sub folder named zip code. So let us go over here and let us switch to S3. Now this is our bucket. And as you see, our bucket is currently empty. So we will create a folder over here called as address and save. And under address we will create another sub folder called as zip code. And then click on save again. And this is a zip code folder. So as you see, the hierarchy over here is that it's our bucket under a bucket is a folder called as address. And under address is another folder called as zip code. Now I want to highlight a couple of things to you that when you're using S3 as a source for AWS DMS, there is a specific structure that we need to follow. Well, before you go further ahead. Okay, so there's a reason why we created these folders in the specific sequence. So let me switch to this particular article. And this is the article as you see using Amazon simple storage service as a source for AWS DMS. Now, in short, what it this particular tutorial tells us is that that whatever wherever a CSE files are like as you see the CSE file is right here. The folder right above that is a table name and the folder right on the top that is two levels above our CSV is a schema name. So let us take an example over here. As you see this is an example that they have taken this is an under S3 it is there's a bucket called as my bucket. And under my bucket there's a folder called as HR and under HR there's another folder called as employee. So in this case my bucket is the bucket name. HR is the schema name or the database name and employee is the table name and under employee, we will actually have whatever, you know, XYZ or CSV, which contains the actual on data, which is in a CSV format. So if we have to apply this to our tutorial that is this particular tutorial. Okay, so in our example, we have our bucket name as my DMS 2018. So this is our bucket name address is going to be the name of our schema or our database and zip code is going to be the name of our table. Hence we created these two folders under our buckets. So you see this is our entire structure. And again, depending upon, you know, however, whatever structure you want, if you want to create a separate structure you're more than welcome to do that. But one folder above your CSV. So let's see a CSV over here. You know, you see this shortly. So I have my zip dot CSV here. So this folder one folder above it is going to be treated by default as the table name and the folder that is to above our CSV is going to be treated as our database. So this is something that DMS assumes by default. There's nothing much that you can do about this. Because this is how it treats the data in S3 when it's trying to read it. Okay. So please ensure that you keep this in mind. Okay, so let me switch back now to my presentation. So we have created these two folders that is address and zip code. And after we have created these two folders, we need to create a JSON schema for our CSV table structure. I have actually gone ahead and created the schema so that we can actually, you know, just use the schema for our tutorial. So let us review through the schema now. So as you see, we have one single table and the table name is zip code as you saw earlier. The table path is address slash zip code. Remember address is a schema or a database name and zip code is a table name. The table owner, of course, in this case is address and this particular table zip code has the following columns. So the first column is zip and it is of type integer. It is the primary key and it is definitely not nullable. So you definitely need to have a value over here, obviously, because it's the primary key. After zip, we have a column called as place. And this is a string and its column length is 50. Similarly, we have state and after state, we have state abbreviation. Again, that is string, but the column length is only two. After state abbreviation, we have a column called as county. And again, that is of type string and the column length is 50. After county, we have latitude and longitude. And both of these columns are numeric in nature. Again, the total length is six. And as you see, the column scale is four. So what this essentially means is that after the decimal point, we have four places. Okay, and before the decimal point, we have two. That makes it a total of six. So now let us review through the data that corresponds to this particular table structure. So as you see in total, we have about seven columns. So this is my zip.csv right here. And as you see, we have nine rows in this zip.csv. So the first one, as we just saw earlier, the first column. We're right before the comma is our zip code or the zip. The next is our place. After that we have a state, then we have the state abbreviation, then we have the county. After county, we have latitude. And finally, right at the end, we have longitude. And we have nine records over here as a part of the CSV. So this is our CSV as we just saw, and we are going to go ahead and upload the CSV to a zip code folder. Now you can find the zip.csv and the zip.json at this particular GitHub location if you would like to practice this tutorial on your own at a later date. Okay, so feel free to go ahead to this particular GitHub location and download these two files for practice purposes. Okay, so let me go ahead and now upload the zip.csv right over here into our CSV bucket. So click on upload, add files, and this is zip.csv right there. So select zip.csv. Next, next, and then next upload. So our zip.csv has been uploaded successfully. So after we have uploaded our zip.csv to our CSV bucket under address slash zip code. The next thing that we need to do is to go to AWS database migration services. And then we need to first create a replication instance now ensure that you use the instance type as dbt to micro to keep the charges at the lowest. Not all replication instances are free. Hence, including dbt to micro hence you can potentially incur charges for for your application instance. Okay, so just be careful with that. So let me switch back to the console. And let us switch to database migration service right here and click on replication instances and create replication instance. So let us give this replication instance a name as dms 2018. And I'm going to give the same as the description right here. Now, as you see by default, the instance classes dmst to medium but I'm going to go ahead and change this to dmst to micro. You can leave the engine version as default. I'm going to go ahead and launch this in my default VPC because that's where the rest of my other servers are and multi Aziz. We don't need multi AZ for this particular tutorial some is going to be with no. If you click on advanced, ensure that you change the allocated storage to eight. We don't have to do anything great over years. I'm just going to reduce the storage to age to keep charges to the minimum. And availability zone. If you have a preference, I'm just going to go ahead and select us East one a and the security group is going to be the default security group for our VPC. So just to remind you again a few minutes back we had actually modified our RDS security group to ensure that our EC tools and a VPCs before security group are able to communicate with my SQL RDS server on for 3306. And this was the precise reason why we did that because our replication instance will be using VPCs default security group. We will keep the rest of the properties as default and then go ahead and create this replication instance. So there it is. It is currently creating our application instance at this time. And it generally takes a few minutes to to kind of have this instance provision and be available. So I'm going to go ahead and refresh this a couple of times. But in case it is taking longer than I will have to pause the video and restart the recording once the instance is available. So as you see our replication instance is now available. And it is up and running. So let us move go back to our presentation right here. So the next thing after we have created a replication instance is to go ahead and create a source endpoint for our S3 bucket and then test it. So switching back to my AWS console click on endpoints and click on create endpoint. And the endpoint type over here is going to be source. And we are going to give the endpoint identifier as S3 source right here. The source engine is going to be S3. So go ahead and select S3. And we are going to specify the role ARN that we had just created sometime back. Remember, we had created this role with S3 read only access and RDS pull access. I'm going to go ahead and paste this ARN right here. The bucket name is going to be my DMS 2018. Scroll further down and this is where we will copy our JSON table structure. So let me switch back over here and copy this entire JSON structure and paste it right here as part of the table structure. So as you see, this is our entire JSON structure. We don't have any CDC under advanced options. I'm just going to leave everything empty at this time. And for VPC, we need to select default VPC for me, at least it is default VPC, but ensure that you select whatever VPC that you have launched your other servers in. Now go ahead and click on run tests. Now if everything is successful and it is able to connect to our S3 bucket successfully, then our connection should be successful. Sometimes it does take a while to for it to kind of test the endpoint connection. So if it takes a little longer than I will stop recording and resume recording once the testing has been completed. So hopefully this is done soon. So there it is our connection test is successfully. So let us go ahead and now save this particular endpoint. So after we have created our source endpoint, the next thing that we need to do is we need to go ahead and create our target endpoint for my SQL and then test the endpoint. So let me switch back to our console. Click on create endpoint click on target. Now as you see we have an option over here called as select RDSDB instance. So go ahead and check that. And by default, as you see our my SQL RDS instance is selected. We are going to give this an endpoint identifier as my SQL hyphen target. And the target engine is my SQL in this case. The server name is the DNS name of my SQL server or the endpoint for my SQL server. So again, I had copied this. I'm going to go ahead and copy this from our notepad and paste it right here. Port is 3306 because that's where the communication is happening. We are not going to use SSL. So I'm just going to select this as none. For this particular demo, my username password and my DB instance identifier, everything is my DMS 2018. And again, this is just to keep things simple so that we don't waste time in debugging or anything of that. So this is my username and the same is my password. Scroll further down under advance. I'm just going to keep everything as default. And then for VPC, we need to go ahead and select before VPC. And the instance type is our DMS 2018 rep that we had just created. So then we need to go ahead and test our endpoint. So click on run test. And again, sometimes this connection takes a while. So testing this endpoint might take some additional extra time. Okay, so just be patient if you're doing this by yourself. So hopefully this is done shortly. Okay, there it is. Okay, so our connection test is successfully. So let us go ahead and we can save and save our target endpoint. So after we have created a source and target endpoints, the next thing that we need to do is that we need to create a task to migrate data from S3 to my SQL. And after we create the task, we need to run the task. And after our task is run successfully, we will see that a database name address is created in my SQL and a table named zip code is created in my SQL as well. So basically we will have a database called as address and inside address we will have a table called as zip code. And this zip code should be populated with all the records that we have in our CSV. So remember our CSV has nine records and all these nine records should be populated in my SQL zip code table. Okay, so let us switch back to our AWS console, click on task, create task. And we give this the task name as S3 to my SQL. This is our application instance. Our source endpoint is S3 source and our target endpoint is my SQL target. Now, for the target tables, I'm just going to leave this by default like drop target tables. We don't have any tables on our target, the brand new database, but you can just leave it as it. We don't have any LOB columns, so I'm going to go ahead and say don't include LOB columns. Again, enable validation or enable logging is not needed. And this is the most critical part guys, this is the table mapping. Okay, so you can either go ahead and type the JSON out over here, or you can use the guided version. So we'll be using the guided version right now for this particular tutorial. So go ahead and say enter schema. And I'm going to keep the schema name as a person and the table name as person, which is just the wild card. Because if you remember our schema name and our table name are present in our JSON file. So that's pretty much it. We're basically going to go ahead and include on all the tables. So basically the action should be include over here. So go ahead and click on add selection rule. And then our selection rule has been added. And finally go ahead and click on create tasks. So if we create this task, remember, this task will be initiated or started automatically because we have checked that option right here on the top. Okay, so let's go ahead and create tasks. And our task is currently being created as you see. And this is the overview of the task as you see the entire summary is right here. So switch to table statistics. And you will see that if it creates any schema or table in our target my SQL, then you will see those that that schema and table created right here. And you will also see the number of loads that it has loaded into the table. Okay, so just keep on refreshing this. And eventually you will see the schema as a dress and the table as zip code. So refresh and starting now. So hopefully there should be something over your shortly. Okay, just keep on refreshing it and you will see the schema and the table being created. So there it is. So our address schema. And our zip code table has been created on my SQL RDS database. And let us see the rows are loaded as you see the rows currently at zero. But if you refresh, yeah, there it is. So all nine rows have been successfully loaded to our zip code table in my SQL. So let's go ahead and confirm this now. So we will switch to our my SQL client right here, and we will connect to our database. So the command for that is my SQL hyphen age, and then our database, the endpoint. So let me go ahead and copy that from here. So this is the database endpoint right here. Let's copy this, go back, paste it. Okay, and then we will use the board as 3306 username is my EMS 2018. And the password it is going to prompt us to just ensure that you ended with minus B and hit enter. The password in my case is my EMS 2018 and hit enter. And there you see we are connected to our my SQL server. Now let us see if it has created our database or not. So the command to check what databases are available on this particular server is show databases. So as you see our address database has been created. So let us now switch to this particular database so type in use address. So as you see our database has been changed. Now we need to check what tables are available under address remember we should be able to see our zip code table. So there it is a zip code table is present under our address database after a zip code table. Let us go ahead and look at a structure. So the command for that is describe zip code right here. And if you see the field is we have to zip which is big end. This is the primary key and place over here which is where care state state abbreviation county latitude longitude again it has decimal six comma four which is what we had provisioned or basically mentioned in our JSON file. So as you see this entire structure has been created as per our JSON format. Okay, so now let us go ahead and see if our records have been copied to a zip code table. So the select star from zip code. And if you see all our nine records are right here. So records have been copied in successfully. Okay, so I guess this kind of completes this tutorial logically over here. Please ensure that you release all the resources especially your application instance your ideas instance. And your EC2 instance. Okay, if you forget to do that, then you will incur some surprise charges. Okay guys so that's it from me today. Thank you very much. And please do let me know your comments. If you would like me to create a video on any specific topic, then do let me know. Otherwise, till then goodbye and I will see you shortly in a new video. Bye bye. Hello all and welcome to this AWS tutorial. In our tutorial today we will talk about AWS database migration service and discuss how we can migrate data from Microsoft SQL server installed on an EC2 instance to a Microsoft SQL server on an RDS instance. Before we go further ahead with the demo, a couple of things to keep in mind. First is that AWS resources provision in this tutorial may not be free tier eligible and can incur charges using DMS service can also incur charges. And last but not the least, and the most important point is that ensure that you clean up your resources after this tutorial is over. This is to avoid any unwanted charges later. For this tutorial, I have done certain amount of preprep. Hence, if you are doing this tutorial by yourself, ensure that you complete all the steps shown on this slide before moving further ahead. For this tutorial, I have already completed these steps. So let us review the steps before we move further ahead. The first thing that we need to do is we need to launch an Amazon Microsoft Windows server 2016 instance with SQL server 2017 standard installed on it. This is the AMI ID right here. Ensure that this particular instance has public IP enabled and you can launch this instance either in your default VPC or your custom VPC. We will use this instance as our source database server. For this particular tutorial, I have used the instance type as M5 large. Now, typically, if you want to provision SQL servers 2017 standard edition, it generally requires a large instance, or probably even sometimes an extra large instance. But for this particular demo, I have used M5 large. Ensure that when you provision this instance, check the cost associated with it because this instance is not free tier eligible. After you have provisioned this EC2 instance, ensure that inbound ports 1433 and 3389 are open in its security group. Once the instance is up and running, RDP into this EC2 instance and download and venture works database backup from the below location. So this is the URL from where you need to download this file on your EC2 instance. So just download the file and copy it in a in a location you can copy directly into your SQL in wherever. So just have this file downloaded because we will be restoring this file in our steps later. So after we are done with our EC2 instance, the next thing that we need to do is we need to launch Microsoft SQL server standard edition on our RDS instance. Now again, ensure that you launch this instance in your default VPC or your custom VPC. In this case, choose the use case as dev or test, select the DB engine version as SQL server 2017 14.00.3035.2.1 The DB instance class choose that as DB.R4.extra large with for VC CPU username password. I typically just keep it same. It's up to you whatever you want to use. But in this particular tutorial, I have used the username password as my RDS 2018. Now again, this RDS instance is not free tier eligible. So you will encourage charges if you provision this instance. Okay. And while provisioning this instance, it will actually also show you the cost associated with it. So ensure that you note the cost after your RDS instance has been provisioned. Go ahead and modify the RDS security group to provide inbound access to EC2 security group. The security group that is associated to your EC2 instance and your VPC default security group. So that they can communicate with this RDS instance on port 1433. Okay. So as I mentioned earlier, I have already completed these steps and my setup is completely ready. Okay. So now we will go further ahead with the steps to perform the actual migration from our SQL server on our EC2 instance to our SQL server on our RDS instance. So these are the steps. Now before I go further ahead with this, let me review through my setup and I will showcase my setup to you as well. So let me switch to my AWS console right here. So as you see, this is my RDS instance. It's up and running. This is the DB class. And this is the endpoint of my RDS instance. You want to keep make a note of this endpoint. And again, my EC2 security group as well as my VPC default security group has access to this particular RDS instance. And they are able to communicate over port 1433. So let us quickly review that to ensure that we don't face any challenges later in the game. Okay. So once this comes up. Okay. So this is right here and inbound communication on for port 1433 for both of these security groups is is unable. Okay. The next thing that we need to do is just scroll down. Look at all the properties as you see all the properties are right here. And this is launched in my default VPC. And my availability zone is US East 1A and rest of all the other properties are by default. Okay. So after we have reviewed our RDS instance, let us go ahead and review our EC2 instance. So my EC2 instance should be here. It is up and running now. And so this is my EC2 instance right here. And as you see this, it is it has a public IP and this is the public DNS and it is currently up and running. This is a security group associated and the security group is opened for port 1433 and 3389 for inbound communication. Okay. And I have actually gone ahead and already beat into this instance right here. So this is my instance and I have gone ahead and downloaded the adventure works database backup file and saved it on my local disk over here on C colon right here. So you can see the backup file is right here. Okay. So we have our source server up and running. We have our target server also up and running. So we are all set. So now the next thing that we need to do is go ahead and follow the steps on our on a slide. So these are the steps to migrate from EC2 MS SQL to RDS MS SQL. So the first thing that we need to do is on our MS SQL easy to instance, we need to launch our SQL server management studio and log in using Windows authentication. Okay. So let me switch to my RDS RDP over your instance and let me start and launch my SQL server management studio. So it should be somewhere right here. Let's see if I can find it. SQL server tools. SQL server management studio right there. So go ahead and launch SSMS. And once it is up and running, we will continue with the rest of the other steps. So let us review the steps in the meantime. So let me switch back to my presentation. The first thing that we need to do after we log in into SQL server is we need to change the authentication mode from Windows to mix mode. Okay. And then after that it will automatically restart the service. And once we have changed our authentication mode, we need to ensure that we go ahead and reset the password for essay. You can choose the password of your choice. I typically just keep essay, essay is just easier for me. But if you want to keep the password as essay, ensure that you uncheck enforce password policy. Okay. Because otherwise that's not like the essay password is too weak. And if the essay login is disabled, we need to ensure that we enable the essay login. And finally, we need to restart the MS SQL server. Okay. So let us see if our studio is up and running. Okay. So our SQL server management studio is up and running. So when it comes to server name, you don't have to do anything. You just have to type in a dot over here. When you type in a dot, that essentially means that it is the local server. Okay. So you don't need to give any server name or anything just, you know, type in dot as I have done right now. I don't know if you can see the dot or not. But just type in dot and use windows authentication and click on connect. And you should be connected to your SQL server on your EC to instance. So as you see, this is my SQL server on my EC to instance right now. So the first thing that we need to do is we need to change the authentication. So select the server, go to properties. And then go to security and change the server authentication from windows authentication to SQL server and windows authentication mode and click on. Okay. And as you see, it tells you that this configuration changes will not take an effect until SQL server is restarting. Okay. If you want, you can go ahead and restart the server now, or you can restart the server later. Okay. So after we have done this, the next thing that we need to do is we need to go into under security, logins, and then select essay, and then click on properties and go ahead and reset the password for essay. So I'm going to keep the password as essay right here. And that's just easier for me. But if you want to keep password as essay, ensure that you uncheck enforce password policy and click on. Okay. And as you see essay is not currently enabled. So we need to enable this login. So go back into the properties and go back to status and say and select login as enabled and click on. Okay. And click refresh. Okay. So our essay login is now enabled. And what we need to do is we need to restart this service. I'm going to go ahead and restart our SQL server. So click on yes. Yes. And it should stop and start the service shortly. So after our SQL server comes up, we need to go ahead and restore. The adventure works backup database backup on this particular server. Okay. So let us review the steps. So after we restart the MS SQL, the next thing that we need to do is restore adventure works database backup that we had downloaded earlier. And after we have restored the backup, ensure that essay has DB owner access to this adventure works database. Now you can try to give the DB owner access to essay from the console itself. If that does not work, use these two statements in the query window and provide DB owner access to essay. And after we have done restoring our adventure works database and providing access to essay, we will go ahead and count the number of rows in our adventure works. Data base on in schema. There's a schema inside call as person and that has a table also called as person. So we will go ahead and count the number of rows in this person table. Okay. So let me switch back to my SQL server and I'm going to go to databases. Right click. Say restore database. And once the pop up comes up, click on device and then say, click on this ellipsis right here and select our database file. So you'll see the backup media over here should be fight and click on add. And in this pop up go to the location where your database backup adventure works database backup was downloaded and saved. So I have it in my sequel and right here. So I'm going to go ahead and select it. So this is my adventure works 2017 backup. Click on. Okay. Okay. And click on. Okay. And our adventure works database should be restored shortly. So there it is. Our database was restored successfully. So click on. Okay. And then if you go ahead and open up databases, you will see adventure works 2017 right here. And over here, you will see tables under tables. You will see person and person right there. But before we go ahead and do a select count, we need to ensure that we have provided essay access to this database. So let's click on properties and click on user mapping. Now you can try and give it from your sometimes it gives an error. Okay. So it's giving me an error. So if it gives you this kind of an error that cannot use a special principle essay, just click on. Okay. Cancel this whole thing and open a new query window. So say new query window with the current connection and then copy these two, those two statements, these two statements right here. So I will try and copy it from my slide over here. Okay. So these are the two statements. I'm just going to copy these two and go back and paste it right here. And say that using this adventure works 2017 database, change the permission for essay. So just select these two and hit fi. Okay. I had a special character over there. So there you go. So these two statements have been executed successfully. Okay. So now let us go back to our person table right here. And we will try and select top 1000 rows first. And as you see these are it has a lot of rows in this table. Okay. So let us go ahead and select the account. So select count from adventure works person person schema person table. And if you go ahead, select the line hit fi. And you will notice that there are 19,972 records in this in this table. Okay. So let me just quickly go ahead and increase the font size so you'll be able to see my query. So this is the query guys. Select count star from adventure works 2017 person person and the number of rows that are there in this table is 19,972. Okay. So we have completed all the steps on this particular slide. So I'm going to move ahead now. So after we have, you know, restored our adventure works database on our source server. It is time to go ahead and prep our target server. Now our target server is also a SQL server. And it's an RDS instance but we can use the same SQL server management studio that is there on our easy to instance to connect to our RDS SQL server instance as well. So we will go ahead and do that. And for this particular instance remember we had given the username password as my RDS 2018. So we will be using SQL authentication to log in. So let us go ahead and do that first. So let me switch back to my easy to instance console over here. And as you see, this is my SQL server on my easy to instance. So we will go ahead and create a new connection. So click on connect database engine. And we will give the engine name instead of dot this time we will give the engine name of our RDS instance. So I have copied my RDS instance endpoint right here. So this is my RDS instance endpoint. And I am going to go ahead and copy that right here. And instead of Windows authentication go ahead and select SQL server authentication. And my username password is my RDS 2018. My RDS 2018. I hope I got the password right. Let me type it again. It's 2018. There you go. And then go ahead and click on connect. And you are now connected to your SQL server RDS instance. So go ahead and open the databases as you see it's empty. So what we will do is we will go ahead and create a demo database over here. Okay. So let me quickly switch back to the slide presentation. So the next step for us is to create an empty database named DMS demo DB on our RDS MySQL. Okay. And again we need to ensure that my RDS 2018 has DB owner access to this DMS demo DB that we will create. Okay. So let me switch back and let us create an empty database called as DMS demo DB. So right click over here, select new database and give the name over here as DMS demo DB and owner is default and click on OK. And this should create an empty database over here for us. Anything I made an error in the names. I'm going to DMS demo DB. Okay. Yes. I wish to continue. Okay. I don't have permissions to do that. So what I'm going to do is I'm just going to create another database guys. So if you happen to make such mistakes like me, then ensure that you have the right database name over here. So it should be DMS demo DB and click on OK. And then right click over here, go to properties and let us just review to the properties. So these are the properties right here. File groups, options, tracking. As you see everything is all set. Okay. So now the next thing that we need to do is let's go ahead and ensure that our my RDS 2018 user has the DB owner access to our DMS demo DB. So let us go ahead and select our my RDS 2018 user. Right click, click on properties. And if you go to user mappings over here, here it is. So if you see DMS demo DB is right here and it has, if you select this, it has the DB owner access. So we are good. So I'm just going to go ahead and cancel this. So we have completed these steps until here. So our target is now up and running and is available for performing any kind of data migration activities. So the next thing that we will do is now we will go ahead and, you know, provide all the end points and the replication instance and create a task in our AWS database migration service. So the first thing that we need to do is we need to create a replication instance. And again, the replication instance for this is going to be dbt2 medium. And this is again not free tier eligible. So you can potentially incur charges. Okay. So let us go ahead and switch to our AWS console. And I'm going to go to database migration service right here and click on replication instances and create a replication instance. You could give whatever name you like. I'm just going to give easy to SQL to RDS SQL rep. And I'm going to give the same as a description. I'm going to leave the the instance class as DMST to medium engine version. Also you can leave as default. Ensure that you provision this in the same VPC. So my easy to instance and RDS instance are in my default VPC. So I'm going to go ahead and select the Paul VPC. My multi AZ is just going to I'm just going to keep it no at this time. You can review through the advanced properties. I'm just going to leave everything default over here except I will go ahead and change the VPC security group over here as default. Rest all properties. I'm going to leave as default again. Maintenance also I'm going to leave as default and I'm going to go ahead and click on create replication instance. And our replication instance should be provisioned shortly. Now sometimes depending upon the load, it takes a while to provision this instance. So I'm going to wait for a few minutes to see if this instance has been provisioned. If not, then I will pause the recording and resume recording. Once the instance is up and running. So let us refresh this a couple of times to check if it's up and running or not. Okay, so I'm going to go ahead and pause the recording and I'll restart recording once this instance is available. So as you see our instance is now up and running and it is available right here. So we will continue ahead with the rest of the steps. So let me switch back to my presentation. So our application instance has been provisioned. So the next thing that we need to do is we need to create a source endpoint for our EC2 MySQL and we will use the essay credentials to test the source endpoint. So let me switch back over here, click on endpoints, create new endpoint, select the endpoint type as source over here and we will give the endpoint identifier as MSSQL EC2 source and we will select the source engine as SQL server and the server name is going to be the DNS endpoint of our EC2 instance. Okay, so if you go back to instances EC2 and copy the EC2 public DNS. Okay, so running instances. Okay, so this is a public DNS right here. So go ahead and copy that. And sorry, I came to the wrong window and go ahead and both paste that in your database migration service server name right here. The port is going to be one, four, three, three, SQL server more, sorry SSL more is going to be none because you're not doing any SSL encryption at this time. Username is going to be SA. My password is SA and the database name is going to be adventure works. So type in adventure works 2017. Okay, and you can review through the advanced properties, but nothing I'm not going to change anything in the advanced properties. VPC is going to be default VPC. Our application instance is the one that we just provisioned and click on run test. Now sometimes testing this endpoint connection can take a while. So if this takes a longer time, again, I will have to pause the recording and resume once the testing is complete. So still taking some time, but I'm going to wait for a couple of more seconds. Hopefully they should complete soon and it should test successfully. So I believe it says saying log in field for user SA. So I need to go ahead and check my SQL server. So it's right here. I believe we had changed the authentication type to sorry to SQL server windows authentication. Okay, let me restart this service once again just to ensure that it came into effect. Sometimes if you don't, if the server does not restart correctly, you may get this error as well. Okay, so our service has been restarted. So I'm going to try and test this thing again. So our connection tested successfully. So let us go ahead and create this endpoint. Click on save and that should create our source endpoint. So let me switch back to my presentation. So after we have created the source endpoint, the next thing that we need to do is we need to create a target endpoint to our RDS MS SQL and we will use our my RDS 2018 credentials to login and we will then test this particular target endpoint. So let me switch back over here to my AWS console, click on create endpoint. And this time the endpoint type is going to be target. So we are going to select an RDS DB instance. And as you see some of the values are pre populated over here. So it is my RDS 2018 SQL server. The endpoint identifier would be MS SQL RDS target. And the target engine is going to be SQL server. The server name I have it right here. This is my SQL server RDS endpoint. So I'm going to go ahead and paste that right here. The code is going to be 1433 SSL more is going to be none username is going to be my RDS 2018 same as the password and the database name is going to be DMS. I think what was the name. It was a DMS demo DB. Okay, so DMS demo DB. Okay, I'm going to leave the advanced attributes as default and further down select the VPC as default VPC and then click on run test. Now again, testing this endpoint might take a while. So given a hyphen on the top and it does not like that. So I'm going to remove spaces. Let me see if it's the hyphen on the space. Again, click on run test and it was a spaces. So again, testing this endpoint might take a while. And again, if it takes a longer time, I will have to pause the video and resume recording again. So hopefully this endpoint connection test successfully. So let us wait for a few moments. It's taking a while. Let's see. Okay, so I connection tested successfully. So let us go ahead and now click on save. So now our both source and target endpoints have been created. So the next thing that we need to do is we need to create a task to migrate data from our SQL server on our EC2 instance to our SQL server on our RDS instance. And what we will do as a part of this task is we will select the schema as person and the table also as person and essentially basically migrate the data from our person table, which is present on our EC2 SQL server to our RDS SQL server. So remember, we had about 19,972 records in this particular table. Okay, so let us go ahead and create a task. So click on task now and then click on create task. So we are going to give the task name over here as EC2 SQL to RDS SQL. And replication instance is the one that we just provisioned. Our source endpoint is going to be our SQL server on EC2 and our target endpoint is going to be a SQL server on RDS. For migration type, we are going to say migrate existing data. We will start this task once it is created on the target table preparation. We will say drop tables on target and we don't have any as such LOB. So you can either say don't include LOB columns or you can say limited LOB mode to about 32 KB. If you want, you can go ahead and enable logging that will help you to debug. But remember that will come with some additional charges for this demo. I'm not going to enable logging. So now this is the most critical part, which is the table mappings. So over here, let us go ahead and select the schema name. Okay, so we are going to select the schema as person. And for table, we are going to give the table name also as person. And we are going to say the action is include and click on add selection rule. So as you see, this is going to basically migrate data from schema person. And in schema person, we have a table also named as person from our EC2 SQL server to our RDS SQL server. So we are not going to add any transformation rules at this time. So let us go ahead and click on create task. So after this task is created, this start will start immediately. So our task is currently being created. And you can see the details of the task right here at the bottom. So basically it's going to migrate the data as you see the migration type is full load is currently creating. What you would want to do is you would want to go ahead and click on table statistics. And now, once the task is enabled or started, you will see that the data in this particular table over here at the bottom will be populated. So you should see the schema name as person and the table name also as person. And it will actually also showcase you the number of rows that it has loaded. So just keep on refreshing this. It is not the most efficient when it comes to refresh. So as you see our task has started now. So refresh it. And you should be able to see some information on in this table shortly. Okay. So as you see the schema name is person table name is person. The table load has been completed and it has gone ahead and loaded 19,972 records and this was a number that we were expecting as well. So let us switch back to our SQL Server Management Studio and let us connect to our RDS instance as you see is connected right here. So I'm going to go ahead and open this. Click on databases and you will see under DMS demo DB. We should have a table over here called as a person person. So this is a table and it has been created under the schema name also as person. So let us go ahead and now select the top 1000 rows in this table that just got copied over. And as you see it has a lot of rows in this. I'm going to go ahead and remove all the columns and we will go ahead and select count for this table. So select count star. And now as you see we have our entire query right here. So we have a select count star from DMS demo DB person person. And this should be equal to 19,972. So as you see all our records have been copied over successfully from our SQL Server on our easy to instance to our SQL Server on our RDS. So this is it guys. I hope this was helpful. This is a pretty common scenario you will actually face the scenario in your day to day life when your client is trying to move their data from probably their SQL Server on premise. Or let's say if they have done and a lift and shift migration to AWS. And now they want to use SQL RDS. So you can use this to migrate data either from your on premise SQL Server, or even from your SQL Server on your easy to instance. Basically migrating from SQL Server on premise easy to to SQL Server in RDS. So thank you very much and please provide your feedback. If you have, you know, any thoughts of any videos that I should create, then do have them posted in the comments. I will try to, you know, have those videos created as soon as possible. So thank you so much and have a nice day. Bye bye. Hello all and welcome to this AWS tutorial on DynamoDB. In today's tutorial we will see how to create a DynamoDB table, how to insert items in a DynamoDB table, how to scan and query a DynamoDB table. Let us see what is DynamoDB. So DynamoDB is a fully managed, no SQL database service that provides fast and predictable performance with seamless scalability. DynamoDB allows you to create a database table that can store and retrieve any amount of data and serve any level of request traffic. Its flexible data model and reliable performance makes it a great fit for mobile-based application, web-based applications, gaming applications, at-tech applications, IoT applications. You could potentially use DynamoDB for any other application as well. For our tutorial today, we will be creating a DynamoDB table using its default read-write capacity units. The default read-write capacity units for DynamoDB is five. So we have five read capacity units and five write capacity units. Let us understand what is a read capacity unit. A read capacity unit represents one strongly consistent read per second or two eventually consistent reads per second for an item up to four KB in size. Item sizes for reads are rounded up to the next four KB multiple. For example, if I am reading a file which is of size 2.5 KB, it will consume the entire throughput of four KB. Even though it is lesser than four KB. And the reason behind it being is that one read capacity unit is of size four KB. And any other unit, let's say if you add more units to this as well, each unit is of size four KB. That means the first unit is of four KB, second unit is of four KB. Hence, if I have two read capacity units, I would have a total size of eight KB. You cannot reduce this size. Okay, so even though your item size is lesser, it will still consume the entire throughput of four KB. Let us now understand what is a write capacity unit. A write capacity unit represents one write per second for an item up to one KB in size. Item sizes for writes are rounded up to the next one KB multiple. So for example, if I am writing an item which is of size 500 bytes, it will consume the same throughput that an item of size one KB would consume, even though it is half the size of one KB. Again, just like the read capacity unit, the write capacity unit is provision in multiples of one KB. So let's say if I had an item which was of size two KB, then it would provision two write capacity units. But let's say if I had another item which was of size 2.5 KB, then it would provision three write capacity units of size three KB. Remember, it's in multiples of one KB. Although my item size is 2.5, it will still consume the entire throughput of three KB. I will be creating a separate video on how to calculate read and write capacity units for your DynamoDB table, depending upon your item size and the number of items that you read or write per second. Okay, at this point of time for this particular demo, we will just go ahead with the default read write capacity units. After we have created the table, then we will add records or items to our table. Finally, after we've added items, we will scan our table and then query our table. So let me quickly switch to my AWS console. So this is my AWS console right here. And I'm going to switch to DynamoDB. Okay, and let us click on create table. And I'm going to create a table over here called as orders. So the table name is orders, and I'm going to give it a primary key called as order ID. And I'm going to make it a numeric key. Now, as I mentioned earlier, we will be using the default settings of DynamoDB. So as you see, it has a minimum of five read capacity and five write capacity units. So let us go ahead and click on create. And as you see, our table is currently being created. So once our table is created, then we will go ahead and add items to our table. So as you see, our table is now created. This is our table name orders right over here. This is our primary partition key order ID, which is numeric. We have disabled point in time recovery encryption time to live. Our table status is active. The provisioned read capacity units is five. Same as the write capacity units again five. And currently there are no items and the storage size currently obviously is zero because there are no items. Okay, so let us go ahead and now add items to our table. So click on items and then click on create item over here. So as you see, our primary key is order ID. So I'm going to give it an order ID over here as one. And then I'm going to add a couple of more columns. Okay, I'll add another column over here, which is of type string and I'm going to call it as order, order, let's order name. Okay, let's say I'm going to say this is NAMS order. Okay, then we will add one more column to this. Okay, off type. I'll say list. Okay, so we'll say order items and we will add something over here off type. I'll say string. So I'm going to say I'll give us something like soap. Okay. Add one more item string and I'm going to say maybe. Okay, I'm just going to say toilet paper. Okay. And so we've added a couple of items over here. Okay, so I'm going to go ahead and now click on save. So as you see, our first item record was created over here. So let's go ahead and create one more item. I'm going to create an item with item order. Item IDS 34. And again, we will go ahead and append a string. So this time I'm going to give it order date. And yes, and when it comes to DynamoDB, you can have different kinds of columns. Okay. So we are going to give it an odd date as 11th November. 20th November. So we are going to give it an odd date as 11th November. 2018. Okay. And let's say I'm going to give this a value over here. So is it order status? Okay. So let's give it order status and the volume value is true. Okay. So I'm going to go ahead and save this. So as you see our columns for our first order and our second order are different. Okay. So let's create one more item. We are going to give this order number a 700. And I'm going to add the order name again. I'm going to say general order. And then I'm going to say again, let's say Boolean over here. Order status. And I'm going to give value as false. I'm going to go ahead and save this order. So we've created three orders over here as you see with order ID 1334 and 700. And these have different types of columns. So this is the beauty of DynamoDB. This is a new SQL table. So you could potentially have different kinds of columns over here and you could have different values in them as you see. So this is great. Okay. So let us now go ahead and scan our table. So in order to scan our table, remember the scan table actually scans the entire table. If you want, you can add filters. So let's say I'm going to go ahead and give say order name and it says it is string and let's say contains order. And now we're going to click on start search. So it will basically give us all the orders which have an order in its name. So let us click on start search. Okay. So there is nothing that matched. Okay. Let us say equals NAM. So let's see if this research sense. Okay. Nothing still NAMs order is looking for a specific match. Okay. Still nothing. Okay. There you go. So that was my mistake over there. It should have been I had the wrong column names. I'm going to go ahead and remove this and then go back to my original query. So order name. So yeah, it is case sense. It is basically if you have space or anything in between, it is sensitive to that. Okay. So now let us go and click on start search. So we got our two orders as expected over here. So NAMs order and general order. Okay. So couple of things to keep in mind guys, whatever order or the column name that you give over here, ensure that you have the same name on the top. If you give a space over here, it is not going to match it. Okay. So this was a scan. So we can pretty much give any kind of filter if you want to. If you don't want, then it is basically just going to go ahead and scan the entire table. So if you just click on start search, as you see scan the entire table and return as all the three records. Now let us go ahead and query the table. So if you click on query over here. So as you see, if you query a table, you have to provide a value for primary key. And that is because how the query table works. The query table basically goes and searches for records that match the primary key. It looks for that particular record compared to scan, which actually scans the entire table. So it basically picks up all the records in the table and then applies the filter expression on it. Versus query will only pick up records that will match your primary key value. So let's say if I'm going to give a value over here as 33 and start search. So we don't have any records for 33. Let's say if I give 34 and click on start search, then it will return me the record where the primary key value is 34. If I want, I can certainly add additional filters over here. So let's say attribute. I'm going to say order status is equal to false. Okay. So click on start search. Again, we don't have any records where in the order ID is 34 and order status is false. So if I change this to true, then our record should show up. So order status is true. Okay. So now it should show up. Okay. Why is it not showing up? Okay. So again, I had the wrong type over there. So click on start search and there you see our order status showed up. So this is our record right there. So keep this in mind, guys. You would need to ensure that the column name is the same as the column name over here. Now I don't have any spaces or anything of that sort. The column type also should match. So if you select this as string or number, it's not going to work. And again, the value should match accordingly. So this was our, you know, a little example over here to scan our table and to query our table. Now I have created a slide where in we will see we'll basically compare and contrast and understand what is the difference between scanning a table and clearing a table and versus what are the similarities as well. So let me switch back to my PowerPoint presentation right here. So this is a small table over here as I was talking. So scan operation, as we saw as well, reads every item in a table or a secondary index if you have a secondary index versus a query operation finds items based on a primary key value. Hence, if you remember, if I switch back over here for query, we have to provide a primary key value. If you don't provide, it's not going to go further. So let me remove this and less. If I go down and click on start search, you saw it gave me an error over here saying that this value is empty. I have to enter a value. So if you have to perform a query, then you have to provide a primary key value. It's mandatory. But let's say if I change this to scan, then this will disappear. And as we discussed, this will just scan the entire table. So again, by default, a scan operation returns all the data attributes of every item in a table or index. So if I just go ahead and click over here on start search, it is going to return everything. As you see, all attributes of all items are returned. Okay, now you can query any table on a secondary index or a composite primary key. It really doesn't matter. But when you're performing a query, it will basically only pick up records where the primary key value matches. And if you've provided filter expressions, then it will filter out that and it will provide you only what matches. After filtering everything. Okay, now you can use projection expression parameter so that the scan only returns some of the attributes rather than all of the attributes. So that is a possibility. But by default, it is just going to go ahead and return you all the attributes of all the items. Due to this above reason scan operation or performing a scan operation on any table in general is slow and it's especially slow for large tables. Okay, so this is very, very important. Keep this in mind, especially if you let's say if you go for an interview, they can ask you what is the difference between scanning a DynamoDB table versus performing a query on a DynamoDB table. And should you even perform scan operations on DynamoDB? I mean, you can perform if you really need to, but otherwise it is typically not recommended to perform scan operations on DynamoDB table. As we saw, it actually performs a scan on the entire table first and then it applies the filter expression to filter out what you have requested for. Vis-a-vis a query will only pick up items that will match your primary key value. Okay, so by default, the read consistency for both scanning a table and querying a table is eventual. If you want, you can set the consistent read parameter to basically true and that will change the read consistency to strongly consistent. And that is true for both when you perform a scan table and a query table. The default result set size is 1 MB for both scanning a table and querying a table. Again, as far as the filter expression is concerned, the filter expression is applied after a scan finishes. That means when it scans the entire table after that, the filter expression is applied. But before the results are returned to you, so it scans the table, then applies the filter expression and then it returns the result to the caller. Okay, so therefore a scan will consume the same amount of read capacity, regardless of whether a filter expression is present or not. A filter expression for query is applied after a query finishes. So let's say you had filter expressions in your query. Remember the query only picks up items based on the primary key value. So once it has picked up those items that matches the primary key, after that it applies the filter expression on those items. And then after applying the filter expression, then the results are returned to the user or the caller. Therefore, a query will consume the same amount of read capacity, regardless of whether the filter expression is present or not. That is because it first gets your items which match the primary key value and then it applies the filter expression on that. So hopefully guys, this was helpful. I tried to cover some basics for DynamoDB in this particular tutorial and some of these things are extremely important. So I ensure that you feel free to pause the video, read through this table in detail, especially if you're preparing for an interview. It will help you to answer some of the questions thoroughly well. Okay, so that's it from me guys for today. Hope you like this video. Please feel free to provide your comments and if you want me to create videos on any specific topic, have it posted in the comments and I will try my best to have those videos created for you. Thank you and have a nice day. Bye-bye. Hello all and welcome to this AWS tutorial on DynamoDB. In today's tutorial, we will learn how to calculate read and write capacity units for our DynamoDB table. Let us understand what we mean by read capacity unit. A read capacity unit represents one strongly consistent read per second or two eventually consistent reads per second for an item up to 4KV in size. So what this statement essentially means is that there are two read consistency types as far as DynamoDB is concerned. First is a strongly consistent read consistency and the other one is eventually consistent read consistency. So if we have provisioned one read capacity unit, we can perform one strongly consistent read per second. And with the same amount of read capacity units, we can perform two eventually consistent reads per second for an item up to 4KV in size. Item sizes for reads are rounded up to the next 4KV multiple. So what this means is that if you're performing a read, then the item size needs to be rounded up to the next 4KV multiple. That essentially means that one read capacity unit is of 4KV. Hence an item size, let's say if you have an item size of 2.5KV right over here, then you need to round it up to the next 4KV multiple. So in this case, the next 4KV multiple would be 4KV. Hence, if we are reading an item which has size of 2.5KV, it will consume the same throughput as much as an item which actually is of size 4KV. To calculate the number of read capacity units, take the item size and round it up to the next 4KV boundary. So when we say the next 4KV boundary is nothing else but multiples of 4. So it will be 4, 8, 12, 16, etc. So depending upon the item size, you need to get it to the nearest 4KV multiple or the 4KV boundary. So if the item size is 2.5, then it will be 4. If the item size is 6KV, then the nearest 4KV multiple would be 8. If you have specified a strongly consistent read, divide the roundup size by 4. This is the number of read capacity units required for strongly consistent read. For an eventually consistent read, which is the default option, take the strongly consistent read capacity units and divide it by 2. In case you have a decimal number, then you round it up to the nearest integer. So let us take a couple of examples and understand how read capacity units are calculated. So as you see, I have mentioned the read consistency type right over here. And keep in mind that one consistent read capacity unit is equal to 4KV, a read for 4KV. Okay, so in our first example, we will understand how to calculate read capacity units for strongly consistent reads. So let us consider that our item size is 10KV. In that case, and we are performing one read per second. Okay, the number of items that we are reading per second is just one. In that case, the roundup item size for 10KV would be 12KV. Okay, because that is the nearest 4KV multiple. Remember we discussed it is the multiples of 4. So the nearest 4KV multiple would be 12KV. Now we need to take up this roundup item size and divide it by 4. So after we divide 12 by 4, we get 3. Hence the number of read capacity units that is required to read an item size of 10KV for a strongly consistent read would be 3. Let us take another example over here. Let's consider that the item size is 7KV. And again, we are only reading one item per second over here. Then the roundup size for that would be 8KV right here. If we divide 8 by 4, we get 2. Hence we would need to read capacity units to read an item size of 7KV. Let us take another example. Let us consider that our item size is 14KV. In that case, the roundup item size would be 16KV. That is the nearest 4KV multiple. Again, if we divide 16 by 4, we get 4. Hence we would need 4 capacity units to read an item of size 14KV. Now after we have calculated the read capacity units for a strongly consistent type. Let's say if we change the type to eventually consistent and keeping the item sizes and the number of items to be read per second the same. What would be our read capacity units? So let us understand that. So considering that our item size is 10KV for an eventually consistent read. Again, the number of items read per second is 1. What we need to do is we need to consider the number of read capacity units for strongly consistent read. So as we calculated it over here right at the top, it was 3. So I have copied this number over here in this particular cell. Now what we will do is we will take this read capacity units and then divide it by 2. So essentially the eventual consistency would be half of the strong consistency. So you take 3 and divide it by 2 which will give you 1.5. Hence the number of read capacity units required for an eventually consistent read would be 2. Let us take the next example over here of item size 7KV. Again, we had calculated the strongly consistent read capacity units for this as 2. To calculate the eventually consistent read capacity units we will divide it by 2 that is take the half of it. And that would give us the read capacity units for eventually consistent read which would be 1 right here. Let us take another example 14KV. Again, the read capacity units for strongly consistent read is 4 dividing it by 2 and considering the half over here. So we would require 2 read capacity units for an eventually consistent read. So until now we had kept the number of items to be read per second as 1 and I had just kept it 1 to keep things simple. Let us say that if we increase the number of items to be read per second. Let us say if I increase it from 1 to 5 over here as you see for a strongly consistent read. So let us say if I am now reading 5 items per second of size 10KV then what would be the number of reads capacity units I would need. So let us calculate that. Remember over here on the top we discussed that for 1 the number of read capacity units for 1 item is 3. So I have copied that right here in this column. So if we have 5 items it is pretty simple it is a no brainer right it is 3 into 5 which would be 15 read capacity units. Similarly for 7 KV items if we are trying to read 5 items of size 7 KV the number of read capacity units for 1 item was 2. So 2 into 5 would give us 10. Similarly for 14 KV again if we are reading 5 items so it will be 4 into 5 which would give us 20 read capacity units would be required. To perform 5 item reads per second of size 14 KV. Now let us similarly calculate the same for an eventually consistent read. So let us say now we have the same item size as the same number of reads per second which is 5. We had calculated the read capacity units for 5 items over here in earlier. This is our number of read capacity units for 5 items per second. So I have copied that over here as you see. Okay now remember that eventual consistency reads are basically half of the strongly consistent read. So again we have 5 items of size 10 KV or the number of read capacity units for a strongly consistent read were calculated as 15. So for an eventually consistent read we would have to divide that number by 2. So as you see I have divided over here 15 by 2 which is equal to 7.5. Now if you remember I had mentioned in the slide that if you get a decimal number in this case which you have rounded up to the nearest integer. Hence you will see over here that to perform a read of 5 items of size 10 KV we would need 8 read capacity units for eventually consistent read consistency. Let us look at another example over here. Let's say the item size is 7 KV. Again we are reading 5 items. We had calculated the strongly consistent read capacity units as 10. Again we divided by 2 which is 5 and then we would have the read capacity units as 5 right here. And similarly for 14 read capacity units for 20 we divided by 2 to get 10 eventually consistent read capacity units. Okay so let us go back to our presentation and now let us understand what is a right capacity unit. So a right capacity unit represents 1 right per second for an item up to 1 KV in size. So unlike the read capacity the right capacity basically gets rounded up into 1 KV multiples. Okay so item sizes for writes are rounded up to the next 1 KV multiple. So for example if I have an item which is of size 500 bytes it will consume the same throughput as an item which is actually of size 1 KV. So even though it is half of 1 KV it will still consume the entire 1 KV throughput because one write capacity unit has the size of 1 KV. To calculate the number of write capacity units calculate the item size in KV. So let's say if your item size is 3 KV then that is the number of read capacity or write capacity units required. So for an item size which is of 3 KV the number of write capacity units would be 3. So let us take a couple of examples and look at this as well. So I have put in couple of examples over here. So remember that one write capacity unit is equal to 1 KV. So let us consider an item of size 3 KV. So our item size is 3 KV and the number of items to be written per second is 1. So in this case the round up item size would be 3 KV. This is the nearest 1 KV round up which is 3 KV which is pretty straightforward. So the write capacity units that you would need would be 3. Now let us consider that you had an item size of 500 bytes. In that case the round up item size would be 1 KV. As we remember each write capacity unit is 1 KV. Hence the number of write capacity units needed would be 1. Let us consider if the item size is 2.5 KV. And we are writing one item per second. In that case the round up item size would be 3 KV. And the number of write capacity units that you would need would be 3. Now let us increase the number of items to be written per second. So let's say we keep the item sizes the same. But we increase the number of items to be written per second to 5. In that case the round up item size would be, remember it was 3 over here so 3 into 5 which is equal to 15 KV. And hence the number of write capacity units that we will need would be 15. Similarly if we have a 500 byte item size item and we are writing 5 items per second. In that case remember the round up item size was 1 KV. So 1 into 5 is equal to 5 KV. Hence the number of write capacity units that would be needed would be 5. Let's consider if our item size is 2.5 KV. Remember the round up size over here was 3 KV. So we take the same round up size over here multiplied by 5 which will give us a total item size of 15 KV. And hence the number of write capacity units that would be needed would be 15. These are a couple of reference URLs in case you would like to read up more on read and write capacity units. All of these URLs do discuss these concepts in far more detail so feel free to read through or at least glance through them. But ensure that you understand this thoroughly especially if you are working on your AWS certification professional even associate. It doesn't hurt to understand how read and write capacity units for DynamoDB are calculated. So that's it from me guys for today. Do let me know your feedback. Please post your comments and if you would like me to create a video on any specific topic then have it posted in the comments and I can certainly have that video created at the earliest. So thank you so much and have a nice day. Bye bye.