AWS Tutorial - AWS Database Migration Service - MS SQL Server EC2 to MS SQL Server RDS

Ilan and welcome to this AWS tutorial in a tutorial today we will talk about AWS database migration service and discuss how we can migrate data from Microsoft sequel server installed on an ec2 instance to a Microsoft sequel server on an RDS instance before we go further ahead with a 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 VMs 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 pre-prep 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 those steps before we move further ahead the first thing that we need to do is we need to launch in Amazon Microsoft Windows Server 2016 instance with sequel 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 V PC or your custom V PC we will use this instance as our source database server for this particular tutorial I have used instance type as M file large now typically if you want to provision sequel servers 20:17 standard edition it generally requires a large instance or probably even sometimes an extra-large instead but for this particular demo I have used my large ensure that when you provision this instance check the cost associated with it okay because this instance is not free tier eligible after you have provisioned this ec2 instance ensure that inbound ports 1 4 3 3 + 3 3 8 9 are open in a security group once the instance is up and running RDP into this ec2 instance and download adventure 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 C colon wherever so just have this fight 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 sequel server standard edition on our RDS instance now again ensure that you launch this instance in your default V PC or your custom V PC in this case choose the use case as dev or test select the DB engine version as sequel server 2017 14.0 dot 3 0 3 5 or 2 dot version 1 the DB instance class choose that as DB dot r4 dot extra-large with 4 we see 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 a username password as my IDs 2018 now again this RDS instance is not free tier eligible so you will incur charges if you provision this instance ok and while provisioning this instance it will actually also show you the cost associated with it so ensure that you know the cost after your IDs instance has been provisioned go ahead and modify the IDS security group to provide inbound access to ec2 security group the security group that is associated to your ec2 instance and your VP sees default security group so that they can communicate with this RDS instance on four one four three three 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 steps to perform the actual migration from our sequel server on an ec2 instance to a sequel 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 a DB class and this is the end point of my RDS instance you want to keep make a note of this endpoint and again my ec2 security group as well as my VP sees default security group has access to this particular RDS instance and they are able to communicate over port 143 3 so let us quickly review that to ensure that we don't face any challenges later in the game ok so once this comes up ok so this is right here and inbound communication for port 143 3 for both of these security groups is is enabled ok the next thing that we need to do is to scroll down look at all the properties as you see all the properties are right here and this is launched in my default me PC and my available 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 143 3 and 3 3 8 9 for inbound communication ok and I have actually gone ahead and already peed into this instance right here so this is my instance and I have gone ahead and downloaded 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 ok 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 ec2 instance we need to launch our sequel server management studio and login using windows authentication ok so let me switch to my RDS RDP over here instance and let me start and launch my sequel server management studio so it should be somewhere right here let's see if I can find it sequel server tools sequel server management studio right there so go ahead and launch SSMS and once it is up and running we will continue with 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 login into sequel server as we need to change the authentication mode from Windows to mix mode ok 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 SA now you can choose the password of your choice I typically just keep SAS a is just easier for me but if you want to keep the password as sa and sure that you uncheck enforce password policy okay because otherwise it does not like the SA pass what is too weak and if the sa login is disabled we need to ensure that we enable the sa login and finally we need to restart the ms SQL so ok so let us see if our studio is up and running okay so our sequence of a management studio is up and running so when it comes to a server name you don't have to do anything you just have to type in a dot over here then you type in a dot that essentially means that it is the local server ok 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 connected to your sequence server on your ec2 instance as you see this is my sequel server on my ec2 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 sequel server and windows authentication mode and click on ok and as you see ya it tells you that this configuration changes will not take an effect under sequel server is restarted ok so if you want you can go ahead and restart the server now or you can restart the server later ok 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 sa and then click on properties and go ahead and reset the password for sa so I'm going to keep the password as sa right here and that's just easier for me but if you want to keep password as sa ensure that you uncheck enforce password policy and click on OK and as you see sa 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 s enabled and click on OK and click refresh okay so our sa login is now enabled and what we need to do is we need to restart this somewhat of go ahead and restart our sequel server so click on yes yes and it should stop and start the service shortly our sequel server comes up we need to go ahead and restore the adventureworks backup database backup on this particular sober okay so let us review their steps so after we restart the mssql 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 SI has DB owner access to this adventure works database now you can try to give the DB owner access to sa from the console itself if that does not work use these two statements in the query window and provide DB owner access to SA and after we have done restoring our adventure works database and providing access to sa we will go ahead and count the number of rows in our adventure works database and 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 sequel 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 fine 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 C colon right here so I'm going to go ahead and select it so this is my adventure works 2017 backup click on OK ok and click on OK and our adventure works database should be restored shortly so there it is our database was restore successfully so click on ok and then if you go ahead and open up databases you will see adventureworks 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 si 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 si just click on OK 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 ok so these are the two statements I'm just gonna copy these two and go back and paste it right here and say that using this adventureworks 2017 database change the permission for si so just select these two and hit f5 ok I had a special character over there so there you go so these two statements have been executed successfully ok so now let us go back to our person table right here and we will try and select top thousand rows first and as you see these are it has a lot of rows in this table ok so let us go ahead and select the account so select count from adventureworks person person schema person table and if you go ahead select the line hit f5 and you will notice that there are 19 thousand nine hundred and seventy two 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 adventureworks 2017 person that person and the number of rows that are there in this table is nineteen thousand nine hundred and seventy two okay so we have completed all the steps on this particular slide so I'm gonna move ahead now so after we have you know restored our adventure works database on a source server it is time to go ahead and prep our target so now a target server is also a sequel server and it's an RDS instance but we can use the same sequel server management studio that is there on our ec2 instance to connect to our RDS sequel 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 sequel authentication to login so let us go ahead and do that first so let me switch back to my ec2 instance console over here and as you see this is my sequel server on my ec2 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've copied my RDS instance endpoint right here so this is my ids instance endpoint and i'm gonna go ahead and copy that right here and instead of windows authentication go ahead and select sequel server authentication and my username password is my IDs 2018 my RDS 28 18 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 sequel server RDS instance ok 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 ok 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 ok and again we need to ensure that my RDS 2018 has DB owner access to this DMS demo DB that we will create ok 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 honors default and click on OK and this should create an NT database over here for us I think I made an error in the name so I'm gonna DMS demo BB 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 gonna create another database guys so if you happen to make such mistakes like me then I'm sure 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 ok so now the next thing that we need to do is let's go ahead and ensure that our my RDS 2018 user has a 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 your it is so if you see a 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 endpoints and the replication intend 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 DBT to medium and this is again not free tier eligible so you can potentially incur charges ok so let us go ahead and switch to our AWS console and I'm going to go to database migrations this right here and click on replication instances and create a replication instance you could give whatever name your likes I'm just gonna give easy to SQL to already s SQL wrap and I'm gonna give the same as a description I'm gonna leave that D the instance class as DM St to medium engine version also you can leave as default and sure that you provision this in the same VPC so my ec2 instance and RDS instance are in my default V PC so I'm going to go ahead and select default V PC my multi AC 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 gonna leave everything default over here except I will go ahead and change the V PC 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 end point for our ec2 my SQL and we will use the SE credentials to test the source endpoint so let me switch back over here click on endpoints create new endpoint select the endpoint type a source over here and we will give the endpoint identifier as ms SQL EC to EC to source and we select the source engine as sequel server and the server name is going to be the the DNS endpoint of our ec2 instance okay so if you go back over your 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 paste that in your database migration service server name right here the port is going to be one for three three sequel 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 adventureworks so type in adventureworks train 17 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 replication instances the one that which is provisioned and click on run tests 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 we're still taking some time when I'm going to wait for a couple of more seconds hopefully they should complete soon and I should test successfully so I believe it's a saying login field for user si so I need to go ahead and check my sequel server so it's right here I believe we had changed the authentication type - sorry - sequel server windows authentication okay let me restart the service once again just to ensure that it came into effect sometimes if you don't if the result is 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 so let us 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 mssql and we will use our my RDS 2018 credentials to log in 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 2080 and sequel server the endpoint identifier would be ms SQL RDS target and the target engine is going to be sequel server the server name I have it right here this is my sequel server RDS endpoint so I'm going to go ahead and paste that right here port is going to be 1 4 3 3 SSL mode is going to be none username is going to be my RDS 2018 same as the password and the day 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 gonna leave the advanced attribute attributes as default and further down select the V PC as default V PC and then click on run test now again testing this endpoint might take a while so given a - on the top and it does not like that so I'm gonna remove spaces let me see if less the - on the space again click on run test and cross the 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 tester 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 sequel server on a ec2 instance to are already a sequel server on our RDS instance and what we will do as a part of this task is we will select the schemas person and the table also has person and essentially basically migrate the data from our person table which is present on our ec2 sequel server to our RDS sequencer so remember we had about nineteen thousand nine hundred and seventy-two 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 application instance is the one that we just provisioned our source endpoint is going to be our MySQL are sorry a sequel server on ec2 and our target endpoint is going to be a sequel 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 let's say limited it will be more to about 32 KB if you want you can go ahead and enable logging that will help you to debug but remember that K will come with some 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 ec2 sequel server to our RDS sequel server so we are not going to add any transformation rules at this time so let us go ahead and click on create desc so after this task is created the 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 this task is a neighbor 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 scheme my name is person and the table name also asked 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 scheme my name is person table name is person the table load has been completed and it has gone ahead and loaded nineteen thousand nine hundred and seventy two records and this was a number that we were expecting as well so let us switch back to our our sequence of a 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 in this click on databases and you will see under DMS demo DB we should have a table over here called as 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 thousand rows in this table that just got copied over and as you see it has a lot of rows in this so 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 start and now as you see we have our entire query right here so we will select count star from DMS demo DB person dot person and this should be equal to nineteen thousand nine hundred and seventy two so as you see all our records have been copied over successfully from our sequel server on ur ec2 instance to our sequel 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 Westover on cream ice or let's see if they have done a lift and shift migration to AWS and now they want to use equal RDS so you can use this to migrate data either from your on-premise sequel server or even from your sequel server on your ec2 instance basically migrating from sequel server on-premise ec2 to sequel server in re s 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

Channel: NamrataHShah

Views: 7,367

Keywords: AWS Tutorial, AWS, Database Migration Service, DMS, MS SQL Server, SQL Server, EC2, RDS, RDS SQL Server, Data migration, migration, data, Certification, Associate, Professional

Id: 5VbKYnBn-jU

Length: 42min 24sec (2544 seconds)

Published: Thu Nov 15 2018