AWS : Data migration from SQL server to Aurora postgreSQL

hi guys welcome so today we are going to learn about the database migration in aws using aws aurora so uh i will tell you in detail but before going ahead let me show you the high level picture of what this activity has and in between please like and subscribe the channel if you have not done yet so what we are going to do is this migration will happen between my sequel basically uh it will be microsoft sql and then we will pick the aws aurora with post gre or post gray so this migration uh would have two stacks first thing is for example this will be our source database this will be our target database and before we move the data we have to move the schema now what schema is so i have table and then table has these rows right so this is the table and table has some data now the structure the way uh sql server sql server will assume things and the structure which postgre has is different right so first thing is whatever is the structure or the boxes we need to move then we will move the contents for that there is something which is called as the schema converter so first we will do the schema conversion and once schema conversion happens successfully then what we are going to do is we will replicate this for example this is our mysql sorry sql server we are going to create a read replica so that the actual traffic uh from the users will continue to fit this and then he will get the response but at the same time we will have a read replica so this will always have the current data live data whatever is happening and then we will spin up our post gray which will have the table structure ready only thing what we need to do is we have to pull up the data from here and we will update or fill up this postgre on the continuous basis so this will be the live migration in which there is no downtime required whatever the data would come here it would come here and then once entire migration is finished we will change the application endpoints instead of using the sql server we can update the connection string to use the post gr or postgre right so yeah this is what we are going to do so let me show you something real quick here i have this windows instance which is on cloud my sequel so it is the rds service basically if you go to console of aws you will find the let me actually take you there so if we are here and if we say create database you will get the option of sql server so once you spin up the sql server which i have already spinned up uh you can do the remote desktop and then you will get access to this box which has the uh installation of the sql server now there is a tool which is called as the schema converter this is the tool which is given by aws so schema conversion has a wizard so if you will follow the wizard without will give you all the steps once you do that the final thing is that you will get the source database schema here and then the postgre how would it look like what would be the table in some databases their table name cannot be in caps lock i have different data this is a different thing so the schema conversion will provide you from source to uh the target database uh schema matching and then it would say green and wherever there is a problem it would tell you that this is the issue either you can take the stack or you can ignore it like the way it is saying it here so maybe there are some time the data types or the field length several differences so based on that you can fix all those differences and then once you come here once you are satisfied with whatever are the changes you can just you know apply to database what it will do is whatever the conversion it has done for the schema basically if you say convert schema it would try it would start replacing this the processing which is happening so from the source database it is automatically converting this schema here now i have configured the uh basically the post gray uh connection string or credentials already so as soon as if i would go here and if i would say apply to database it would put those schema changes in my postwar let me show you my postgre as well over here so if i come to rds and then if i go to my databases you will see that this a same database rds labs which is my primary which is again replicated here so this is supposed to have that schema right now uh coming back to our this drawing again you have sql you have the schema you have converted the schema so my postgre has the schema now real task is database conversion will happen and remember i told you that we will first do the red uh like the replication so for that aws provides the uh data migration tasks basically or data migration uh service in which you can first replicate the instance so once you replicate the instance which i did i have already done you have to give name resources instance type whatever you want to attach to you can fill in all these details so let me show you what i have here so i have done the replication here this one is replication which which all the details are here now uh we have to con do the end points what do we mean by endpoint you could always watch the uh you know the cloud metrics a cloud watch matrix of how things are happening and then the migration task so i'll talk about migration test later but before that we have the end points so one end point is the source and one end point is the target so source is my my sequel engine which is nothing else but whatever the replication we did from the a sql server this is what we did here so it will read from here and then it would uh basically after reading from here it would write it to the target so that is what it is going to do now where did i get this from actually the server name is actual uh sql server so let me take you here and this is my rds database so i did that on ec2 instance actually i could always show you real quick here this one is a performance matrix though so let's jump on to ec2 to see our myc sql server sorry so let's go to e2 this one is ec2 now in this i have one running instance which is nothing else but my windows ec2 instance which is running the sql server so you can see here this is windows and then i have all the details configured over here this one is my private ipv4 dns which we have configured in the source anyway uh not getting the site not not getting sidetracked so there is a process of creating these endpoints and these endpoints are required for creating the database migration task so i have configured basically you have to configure the details and tags connectivity everything you can do this so if i have something in this vpc whatever the replication instance i have then i can run the test for the connectivity if i have configured all the things which are required same thing goes for the target as well you could always run these tests uh provided you have given all the needed information to this anyway so i'm skipping this for now to save you time and i'm jumping directly to the database tasks so here i have already configured the source which is sql server and the target which is push gray and in the post gray which post postgresql we already have the schema defined and waiting for the data to be moved up so we can create the task once we create the task it would ask for the identifier and then the replication thing target database endpoint all this would pre-populate from the previous step whatever we did before we can name it as anything demo migration so this would be our task right and then you can have the wizard you can always say target table preparation mode if you want to drop something on target do nothing you can say lob is basically large objects you can leave it as is the maximum size of this you can do you can enable cloud log watch so that during migration if there is some problem which happens you could easily see that in cloud watch logs after that you have the control table settings if you have to rename some of the schemas and things like that you could always do the full load tuning settings so it gives you a lot more option basically to give you a lot of control so after you are done with everything you can just create the task okay this is asking for the arm let's see we can pick this so this is the replica set we have this this this here then we have task setting create control table in target schema and then you could always have these rules basically for the migration if you want to you can always add what schema from where so you can enter the schema name or other information if you want to so in short without you know wasting much time we will jump into the already created task which i have uh basically and then aws provides the entire set of steps also which you are supposed to follow while doing these migrations so you can fill up each details by following the wizard and then the final state would be you will see the migration test now once you have the migration task ready then you can basically start the migration for me it is already running and now you can see the table statistics so it would say the schema name is this these are the table names and the load state is stable loading is completed then inserts deletes updates and ddls uh full uh load rows how many rows are there so these are uh roughly 5 million 5.57 million records basically so total rows has been loaded then you can have the validation state pending and lot of other information i did it today it's 11 19 20 20. so uh once everything is done you would say load completed replication ongoing what does that mean it means that if you will insert any data in my sequel sorry the sql server that data would be you know migrated or that data would also reflect on the post screen so if you are making changes here since it is ongoing migration it will go there if you will stop it then that thing will stop but if you will stop it then you have to first flash out whatever are the requests are coming here once this application or is down whatever is the source for this database then you can stop replication and then you can start basically from here so that user application would directly call here and you can remove this link to be precise let me try to erase this that this will all be gone basically in that case so yeah that is pretty much it that was uh the you know agenda for this video if you like to see more videos on data migrations or databases please leave a comment uh whatever you want to know more uh leave your questions in the comment and i have also published detailed database replication related videos on my youtube channel related to mongodb sharding and lot of other cool stuff so please feel free to check out that i have also uploaded a lot of programming tutorials on python and then also on golang on java different programming languages if you are interested in programming watch that and please wait i am going to put more cool videos on aws soon which would also cover the rest api micro services application aws lambda functions step functions and server less computing as well thanks for watching my name is pray messenger see you later you

Channel: Techie Prem Aseem

Views: 735

Rating: 5 out of 5

Keywords: arora, datamigration, data migration from sqlserver to postgre, postgresql

Id: xyr9DcZEdls

Length: 16min 38sec (998 seconds)

Published: Thu Nov 19 2020