and welcome back to BackSpace Academy in this lab we're going to do something a 1
little bit more advanced, we're going to look at migrating an Oracle database 2
over to Amazon Aurora and we're going to use the AWS data 3
migration service to do that. Now the AWS data migration service allows us to 4
migrate data from a number of different databases and they can be on-premises 5
they could be on RDS we just need to have access from our replication 6
instance that will be doing the work through to that source database and that 7
could be an Oracle database, which is what we'll be doing today 8
and it could be a Microsoft SQL server and mySQL and all of the 9
variants of that, Amazon Aurora and MariaDB, PostgreSQL or MongoDB 10
a NoSQL database also the SAP adaptive server enterprise or ASE. 11
We can migrate that over to Amazon Redshift, Amazon s3 even as a dump or Amazon 12
DynamoDB. Now how this works is that we will launch our RDS instance for example 13
here we have our RDS Oracle source and we do our RDS Aurora as a target and 14
we have an instance of the AWS database migration service 15
that's like an ec2 instance and it will communicate between both of those 16
database endpoints to transfer data between the two databases. We do that 17
using the AWS console and we can also use the schema conversion tool 18
if there's any conversion that needs to be done of the schema prior to being 19
uploaded to the target database as well. We're going to connect in to the AWS 20
cloud using the management console and create an oracle DB database as our 21
source database and we're going to add a table to that source just to see that 22
something has been actually migrated over. We're going to create an 23
Aurora DB target cluster as well. To make it all happen we use the 24
data migration service and we create a replication instance that will 25
communicate between the source and the target to transfer that data over. 26
Now the first thing we need to do is that we need to create a security group 27
that's going to allow inbound access for our Oracle database but at the same time 28
it's also going to allow inbound access on our Aurora database as well and also 29
because we're using a replication instance that we'll be communicating 30
with both of these RDS instances, we need to also allow access for that to come in 31
and have it in the same security group. So we go to the ec2 console and go to 32
security groups and we create a security group. We'll give it a name and 33
we'll give it a description as well. Now the first rule we'll put in will be for our 34
source database our Oracle database, so we select Oracle RDS 35
and we do for "anywhere". We add another rule for our mySQL Aurora 36
and again we'll do that for "anywhere". Now we need to create this security group 37
and then go back into it to create another rule for the actual security 38
group to communicate. 39
So we entered this security group and we add another rule which will be for all 40
traffic and that will be for this security group, so any traffic in the 41
same security group will be allowed. We select our Oracle Aurora migration 42
and we save that. Now we can jump into the RDS console and we can start 43
making our instances so the first one we're going to launch will be an Oracle 44
instance. We make sure that free tier eligible is off. We select Oracle and 45
we'll do Oracle SE 2. We'll do it on the dev test environment. Our license 46
model would be license included. Our DB instant class will be T2 micro and we 47
won't be doing a multi AZ deployment. We will give our database an identifier and 48
we just call it source DB. Again we put in our admin username and password 49
or we set up our admin username and password. Ok once that's in we click next step 50
Now we need to select our security group. So we have here Oracle Aurora migration 51
and we'll leave the database name there. ORCL that will be fine. 52
We won't enable encryption and we'll change our backup retention period to zero days 53
so that we won't be doing any automated backups and we'll launch that DB instance. 54
Now while that's being launched we'll do another one for our Amazon Aurora 55
instance, our target. Now we're going to select the smallest we can there which 56
is a t2 small. We're not going to have multi AZ deployment and we'll give our 57
instance an identifier called target DB and again we put in admin for our 58
username and we'll create a password for that. Ok and we click next step. 59
Again we need to select that security group, our Oracle Aurora 60
migration security group. We'll give our cluster an identifier and we'll and 61
we'll also give our database a name of target DB and we're not going to enable 62
encryption so we'll just deselect that. We'll just do tier 0 of 63
failover priority. We will leave the backup retention period as it is. We won't 64
enable enhanced monitoring, we don't need that and the rest is OK for now. 65
So this is just really an educational exercise, so we're doing the minimum we can we 66
with Amazon Aurora to save costs. We launch our DB instance. Now when that 67
changes from creating to available then we can jump into the data database 68
migration service and start doing this task to migrate the data across. 69
Okay so the Aurora database and Oracle database are both up and running. So we just look 70
in here we can see we've got this cluster section and this is the time 71
where you would actually see something appearing in a cluster and that is the 72
Aurora database which is the way that it works. Normally you wouldn't see 73
anything in this cluster section so I just thought I'd show you that. So back 74
into instances we can see it's available. We'll jump into 75
the AWS database migration service and of course just get there from 76
services. Now if you click on getting started there is a way that you can go 77
through here and you can go through step by step and it will supposedly do that 78
for you. I thoroughly recommend you do not do it 79
this way it is very buggy it'll give you a lot of headaches until AWS sort this 80
side of it out and make sure that it actually works quite reliably don't use 81
it, it's far better to just go through and individually create 82
each each thing that you have to do and it doesn't take any more time either to 83
do that. The first thing we need to do is that we need to create a replication 84
instance. We go into replication instances and this is just basically an 85
ec2 instance that will communicate between these two databases and transfer 86
the information around. So create database instance or create replication 87
instance. We'll give it a name, so I call it Oracle Aurora migration use that for 88
the description as well. We're going to use the smallest we can possibly get 89
which is a t2 micro. We won't have multi AZ and we're going to advanced and we 90
need to select that security group and there it is. So you could select our 91
Oracle Aurora migration security group that we created previously, and 92
let's create that replication instance. Now when that is setup or when it is 93
actually running that instance, we can go in and start creating our 94
endpoints, when they're created we can then run a task. Ok so our replication 95
instances are up and running. We can now look at creating our endpoints for our 96
databases for the replication instance to communicate with. So create endpoint. 97
The first one we will do will be the source. We'll give that just a 98
name source DB and the source engine will be Oracle and the server name 99
we'll get that from the RDS console and that will be our Oracle endpoint. We copy 100
that over and we just take the 1521 off the end of that and the colon (:) 101
and we put the 1521 into our port. SSL mode will be none and then our username 102
was admin and we'll put our password in there and our SID was ORCL. 103
Okay so now we can just look at running a test on that to see whether the 104
replication instance can connect to that that endpoint. Okay and that's just come 105
through now. That is connecting through fine. I'll just save that 106
endpoint and let's create another endpoint for the target DB. We select 107
target and we'll put in target DB and the target engine will be Aurora and the 108
server name that will be again the RDS endpoint for our Aurora clusters 109
so I just copy that over and paste that in there and we need to remove the 110
3306 and the colon (:) off the end and put that into our port. Our SSL mode will be 111
none and our username again will be admin and that password that 112
you would have created. Now we can look at testing our endpoint 113
and that's created successfully. We'll just save that. Okay so I've now just 114
jumped into the Oracle SQL developer software because what I want to do is to 115
just create a table there and that way we can copy that table over so we 116
can actually see that the actual migration worked. So I'm just going to 117
create a connection to our database and put in the username and password and our 118
host name which will be our RDS endpoint. 119
Make sure we select the one for Oracle and not for Aurora and take the 120
1521 and the colin (:) off the end of that and the SID would be ORCL and 121
we'll just test that quickly and that's fine. So just connect in to that now. 122
What we need to do is we need to create a table in this database and that way we 123
can see that the data has actually migrated over to our Aurora instance. So 124
what I'm going to do is just in the middle here we've got find database 125
object I'm just going just select our connection Oracle Aurora and then I'm 126
just going to get this schemas section deselected and then expand it and then 127
we can see we've got a schema for admin. The way what Oracle works is that 128
when you create a user it creates a schema under that username. We're 129
going to actually put a table within that admin schema. I'm just going to 130
paste in a command then so that's just a SQL command it's just going to create 131
a table called sample with some fields in there. I'm just going to run 132
that and then we can see that table sample has been created for us. 133
Now we can jump back in we can do our tasks and we can migrate this data over and 134
see if it actually worked by connecting into our our Aurora instance and see 135
what happens. Now back in the database migration service I'm just going to jump 136
now into tasks and we're going to create a migration task to copy this data over. 137
We just give it a name doesn't matter what it is and we leave that as it is 138
It's selected the right replication instance. It's got our source endpoint 139
and our target endpoint there. Now all we need to do is to tell it what we are 140
going to be migrating across so the schema and 141
can put multiple rules in here but we just add rules that we can use. 142
First of all we are going to tell it what we're doing. So we're getting a schema 143
admin and we're just basically going to leave the wild-card in there because 144
it's just going to migrate all tables over. We could actually just put the 145
table name in there but I'll just leave it like that and it will just take it over anyway 146
the action there is to include it into 147
our target database. I'm just going to add that selection rule. Okay and let's 148
just create that task. So after a while it'll change its status and we can see 149
whether that table was copied over. Okay so that has 150
completed its now saying status load complete and we can see here that it 151
has one table that it has copied over. Now if we jump into the mySQL 152
workbench and connect into this RDS console we'll be able to see exactly 153
whether it did really work. Okay now we're in the mySQL workbench let's 154
create a connection for this Aurora database. We'll just give it any name we 155
want and our host name again we need to get that from our RDS console. 156
Make sure that you select the one for your Aurora instance and not your Oracle 157
instance. Select your Aurora and copy that 158
Now again we remove the 3306 from the end here, our username will be admin 159
again and let's just test that connection quickly 160
Put in our password. Okay so that has successfully connected so let's save that 161
ok we double click on this and connect into our Aurora instance or our Aurora 162
cluster 163
Okay so we've connected in and we can see here from our schemas we've got our 164
admin schema and we've got our tables and lo and behold we have a sample 165
table within an admin schema that wasn't there before. So that brings us to the 166
end of a pretty long lab this one and a very good one. I think that copying 167
databases over especially from Oracle in an enterprise environment over to Amazon Aurora 168
is something that you may someday get to do and it's good to be able 169
to have the experience of doing that. So I'll see you in the next lecture