0
1
00:00:00,140 --> 00:00:05,490
and welcome back to BackSpace Academy in
this lab we're going to do something a
1

2
00:00:05,490 --> 00:00:09,769
little bit more advanced, we're going to
look at migrating an Oracle database
2

3
00:00:09,769 --> 00:00:14,160
over to Amazon Aurora
and we're going to use the AWS data
3

4
00:00:14,160 --> 00:00:20,880
migration service to do that. Now the AWS
data migration service allows us to
4

5
00:00:20,880 --> 00:00:25,710
migrate data from a number of different
databases and they can be on-premises
5

6
00:00:25,710 --> 00:00:31,349
they could be on RDS we just need to
have access from our replication
6

7
00:00:31,349 --> 00:00:35,100
instance that will be doing the work
through to that source database and that
7

8
00:00:35,100 --> 00:00:38,969
could be an Oracle database, which is
what we'll be doing today
8

9
00:00:38,969 --> 00:00:42,840
and it could be a Microsoft SQL
server and mySQL and all of the
9

10
00:00:42,840 --> 00:00:49,200
variants of that, Amazon Aurora and
MariaDB, PostgreSQL or MongoDB 
10

11
00:00:49,200 --> 00:00:55,079
a NoSQL database also the SAP adaptive
server enterprise or ASE. 
11

12
00:00:55,079 --> 00:01:01,350
We can migrate that over to Amazon Redshift,
Amazon s3 even as a dump or Amazon
12

13
00:01:01,350 --> 00:01:10,140
DynamoDB. Now how this works is that we
will launch our RDS instance for example
13

14
00:01:10,140 --> 00:01:17,729
here we have our RDS Oracle source
and we do our RDS Aurora as a target and
14

15
00:01:17,729 --> 00:01:23,909
we have an instance of the AWS database migration service 
15

16
00:01:23,909 --> 00:01:27,630
that's like an ec2 instance and it will
communicate between both of those
16

17
00:01:27,630 --> 00:01:34,110
database endpoints to transfer data
between the two databases. We do that
17

18
00:01:34,110 --> 00:01:38,850
using the AWS console and we can also
use the schema conversion tool
18

19
00:01:38,850 --> 00:01:44,850
if there's any conversion that needs to be
done of the schema prior to being
19

20
00:01:44,850 --> 00:01:52,619
uploaded to the target database as well. We're going to connect in to the AWS
20

21
00:01:52,619 --> 00:01:57,869
cloud using the management console and
create an oracle DB database as our
21

22
00:01:57,869 --> 00:02:02,130
source database and we're going to add a
table to that source just to see that
22

23
00:02:02,130 --> 00:02:06,960
something has been actually migrated
over. We're going to create an
23

24
00:02:06,960 --> 00:02:13,850
Aurora DB target cluster as well.
To make it all happen we use the
24

25
00:02:13,850 --> 00:02:17,750
data migration service and we create a
replication instance that will
25

26
00:02:17,750 --> 00:02:24,070
communicate between the source and the
target to transfer that data over.
26

27
00:02:24,070 --> 00:02:28,220
Now the first thing we need to do is
that we need to create a security group
27

28
00:02:28,220 --> 00:02:33,650
that's going to allow inbound access for
our Oracle database but at the same time
28

29
00:02:33,650 --> 00:02:40,160
it's also going to allow inbound access
on our Aurora database as well and also
29

30
00:02:40,160 --> 00:02:43,940
because we're using a replication
instance that we'll be communicating
30

31
00:02:43,940 --> 00:02:49,940
with both of these RDS instances, we need
to also allow access for that to come in
31

32
00:02:49,940 --> 00:02:55,690
and have it in the same security group.
So we go to the ec2 console and go to
32

33
00:02:55,690 --> 00:03:08,150
security groups and we create a security
group. We'll give it a name and 
33

34
00:03:08,150 --> 00:03:14,390
we'll give it a description as well. Now the
first rule we'll put in will be for our
34

35
00:03:14,390 --> 00:03:18,380
source database our Oracle database, so
we select Oracle RDS
35

36
00:03:18,380 --> 00:03:26,720
and we do for "anywhere". We add
another rule for our mySQL Aurora
36

37
00:03:26,720 --> 00:03:33,080
and again we'll do that for "anywhere". Now
we need to create this security group
37

38
00:03:33,080 --> 00:03:37,370
and then go back into it to create
another rule for the actual security
38

39
00:03:37,370 --> 00:03:40,450
group to communicate.
39

40
00:03:45,380 --> 00:03:51,470
So we entered this security group and we
add another rule which will be for all
40

41
00:03:51,470 --> 00:03:56,660
traffic and that will be for this
security group, so any traffic in the
41

42
00:03:56,660 --> 00:04:01,640
same security group will be allowed.
We select our Oracle Aurora migration
42

43
00:04:01,640 --> 00:04:07,310
and we save that. Now we can jump
into the RDS console and we can start
43

44
00:04:07,310 --> 00:04:11,000
making our instances so the first one
we're going to launch will be an Oracle
44

45
00:04:11,000 --> 00:04:17,630
instance. We make sure that free tier
eligible is off. We select Oracle and
45

46
00:04:17,630 --> 00:04:24,080
we'll do Oracle SE 2. We'll do it on the
dev test environment. Our license
46

47
00:04:24,080 --> 00:04:30,620
model would be license included. Our DB
instant class will be T2 micro and we
47

48
00:04:30,620 --> 00:04:37,850
won't be doing a multi AZ deployment.
We will give our database an identifier and
48

49
00:04:37,850 --> 00:04:47,690
we just call it source DB. Again we
put in our admin username and password
49

50
00:04:47,690 --> 00:04:54,380
or we set up our admin username and
password. Ok once that's in we click next step
50

51
00:04:54,380 --> 00:05:04,250
Now we need to select our security
group. So we have here Oracle Aurora migration
51

52
00:05:04,250 --> 00:05:12,410
and we'll leave the database
name there. ORCL that will be fine.
52

53
00:05:12,410 --> 00:05:17,210
We won't enable encryption and we'll change
our backup retention period to zero days
53

54
00:05:17,210 --> 00:05:24,700
so that we won't be doing any automated
backups and we'll launch that DB instance.
54

55
00:05:26,140 --> 00:05:32,120
Now while that's being launched we'll do
another one for our Amazon Aurora
55

56
00:05:32,120 --> 00:05:38,960
instance, our target. Now we're going to
select the smallest we can there which
56

57
00:05:38,960 --> 00:05:44,890
is a t2 small. We're not going to have
multi AZ deployment and we'll give our
57

58
00:05:44,890 --> 00:05:52,880
instance an identifier called target DB
and again we put in admin for our
58

59
00:05:52,880 --> 00:05:59,979
username and we'll create a password for
that. Ok and we click next step.
59

60
00:06:00,880 --> 00:06:05,690
Again we need to select that
security group, our Oracle Aurora
60

61
00:06:05,690 --> 00:06:10,550
migration security group. We'll give
our cluster an identifier and we'll and
61

62
00:06:10,550 --> 00:06:24,020
we'll also give our database a name of
target DB and we're not going to enable
62

63
00:06:24,020 --> 00:06:29,920
encryption so we'll just deselect that.
We'll just do tier 0 of
63

64
00:06:29,920 --> 00:06:35,300
failover priority. We will leave the backup
retention period as it is. We won't
64

65
00:06:35,300 --> 00:06:42,830
enable enhanced monitoring, we don't need
that and the rest is OK for now. 
65

66
00:06:42,830 --> 00:06:48,260
So this is just really an educational exercise,
so we're doing the minimum we can we
66

67
00:06:48,260 --> 00:06:58,340
with Amazon Aurora to save costs. We
launch our DB instance. Now when that
67

68
00:06:58,340 --> 00:07:02,840
changes from creating to available then
we can jump into the data database
68

69
00:07:02,840 --> 00:07:10,730
migration service and start doing this
task to migrate the data across. 
69

70
00:07:10,730 --> 00:07:16,850
Okay so the Aurora database and Oracle database
are both up and running. So we just look
70

71
00:07:16,850 --> 00:07:19,730
in here we can see we've got this
cluster section and this is the time
71

72
00:07:19,730 --> 00:07:24,470
where you would actually see something
appearing in a cluster and that is the
72

73
00:07:24,470 --> 00:07:28,610
Aurora database which is the way that
it works. Normally you wouldn't see
73

74
00:07:28,610 --> 00:07:32,660
anything in this cluster section so I
just thought I'd show you that. So back
74

75
00:07:32,660 --> 00:07:36,140
into instances we can see it's
available. We'll jump into
75

76
00:07:36,140 --> 00:07:41,510
the AWS database migration service
and of course just get there from
76

77
00:07:41,510 --> 00:07:48,380
services. Now if you click on getting
started there is a way that you can go
77

78
00:07:48,380 --> 00:07:53,630
through here and you can go through step
by step and it will supposedly do that
78

79
00:07:53,630 --> 00:07:57,920
for you.
I thoroughly recommend you do not do it
79

80
00:07:57,920 --> 00:08:03,830
this way it is very buggy it'll give you
a lot of headaches until AWS sort this
80

81
00:08:03,830 --> 00:08:08,600
side of it out and make sure that it
actually works quite reliably don't use
81

82
00:08:08,600 --> 00:08:12,380
it, it's far better to just go through
and individually create
82

83
00:08:12,380 --> 00:08:16,700
each each thing that you have to do and
it doesn't take any more time either to
83

84
00:08:16,700 --> 00:08:20,840
do that. The first thing we need to do
is that we need to create a replication
84

85
00:08:20,840 --> 00:08:25,280
instance. We go into replication
instances and this is just basically an
85

86
00:08:25,280 --> 00:08:29,180
ec2 instance that will communicate
between these two databases and transfer
86

87
00:08:29,180 --> 00:08:34,160
the information around. So create
database instance or create replication
87

88
00:08:34,160 --> 00:08:40,190
instance. We'll give it a name, so I call
it Oracle Aurora migration use that for
88

89
00:08:40,190 --> 00:08:44,750
the description as well. We're going to
use the smallest we can possibly get
89

90
00:08:44,750 --> 00:08:52,910
which is a t2 micro. We won't have multi
AZ and we're going to advanced and we
90

91
00:08:52,910 --> 00:09:00,590
need to select that security group and
there it is. So you could select our
91

92
00:09:00,590 --> 00:09:04,760
Oracle Aurora migration security group
that we created previously, and 
92

93
00:09:04,760 --> 00:09:11,030
let's create that replication instance. Now
when that is setup or when it is
93

94
00:09:11,030 --> 00:09:14,750
actually running that instance, we
can go in and start creating our
94

95
00:09:14,750 --> 00:09:21,590
endpoints, when they're created we can
then run a task. Ok so our replication
95

96
00:09:21,590 --> 00:09:26,300
instances are up and running. We can now
look at creating our endpoints for our
96

97
00:09:26,300 --> 00:09:31,250
databases for the replication instance
to communicate with. So create endpoint. 
97

98
00:09:31,250 --> 00:09:35,240
The first one we will do will
be the source. We'll give that just a
98

99
00:09:35,240 --> 00:09:45,440
name source DB and the source engine
will be Oracle and the server name 
99

100
00:09:45,440 --> 00:09:51,650
we'll get that from the RDS console and that
will be our Oracle endpoint. We copy
100

101
00:09:51,650 --> 00:09:59,680
that over and we just take the 1521 off the end of that and the colon (:)
101

102
00:09:59,680 --> 00:10:07,040
and we put the 1521 into our port. SSL
mode will be none and then our username
102

103
00:10:07,040 --> 00:10:20,260
was admin and we'll put our password in
there and our SID was ORCL.
103

104
00:10:27,660 --> 00:10:32,040
Okay so now we can just look at running
a test on that to see whether the
104

105
00:10:32,040 --> 00:10:42,160
replication instance can connect to that
that endpoint. Okay and that's just come
105

106
00:10:42,160 --> 00:10:45,970
through now. That is connecting
through fine. I'll just save that
106

107
00:10:45,970 --> 00:10:52,000
endpoint and let's create another
endpoint for the target DB. We select
107

108
00:10:52,000 --> 00:11:01,360
target and we'll put in target DB and
the target engine will be Aurora and the
108

109
00:11:01,360 --> 00:11:06,100
server name that will be again the RDS
endpoint for our Aurora clusters
109

110
00:11:06,100 --> 00:11:13,450
so I just copy that over and paste that in
there and we need to remove the 
110

111
00:11:13,450 --> 00:11:20,410
3306 and the colon (:) off the end and put
that into our port. Our SSL mode will be
111

112
00:11:20,410 --> 00:11:26,140
none and our username again will be admin
and that password that 
112

113
00:11:26,140 --> 00:11:36,720
you would have created. Now we can look
at testing our endpoint
113

114
00:11:40,770 --> 00:11:50,130
and that's created successfully. We'll
just save that. Okay so I've now just
114

115
00:11:50,130 --> 00:11:55,290
jumped into the Oracle SQL developer
software because what I want to do is to
115

116
00:11:55,290 --> 00:12:01,589
just create a table there and that
way we can copy that table over so we
116

117
00:12:01,589 --> 00:12:04,800
can actually see that the actual
migration worked. So I'm just going to
117

118
00:12:04,800 --> 00:12:21,360
create a connection to our database and
put in the username and password and our
118

119
00:12:21,360 --> 00:12:25,730
host name which will be our RDS endpoint.
119

120
00:12:30,759 --> 00:12:40,910
Make sure we select the one for Oracle
and not for Aurora and take the 
120

121
00:12:40,910 --> 00:12:47,809
1521 and the colin (:) off the end of
that and the SID would be ORCL and
121

122
00:12:47,809 --> 00:13:01,399
we'll just test that quickly and that's
fine. So just connect in to that now. 
122

123
00:13:01,399 --> 00:13:07,939
What we need to do is we need to create a
table in this database and that way we
123

124
00:13:07,939 --> 00:13:13,579
can see that the data has actually
migrated over to our Aurora instance. So
124

125
00:13:13,579 --> 00:13:16,339
what I'm going to do is just in the
middle here we've got find database
125

126
00:13:16,339 --> 00:13:20,629
object I'm just going just select our
connection Oracle Aurora and then I'm
126

127
00:13:20,629 --> 00:13:26,449
just going to get this schemas section
deselected and then expand it and then
127

128
00:13:26,449 --> 00:13:31,819
we can see we've got a schema for admin.
The way what Oracle works is that
128

129
00:13:31,819 --> 00:13:37,279
when you create a user it creates a
schema under that username. We're
129

130
00:13:37,279 --> 00:13:42,800
going to actually put a table within
that admin schema. I'm just going to
130

131
00:13:42,800 --> 00:13:50,240
paste in a command then so that's just a
SQL command it's just going to create
131

132
00:13:50,240 --> 00:13:55,309
a table called sample with some
fields in there. I'm just going to run
132

133
00:13:55,309 --> 00:14:04,040
that and then we can see that 
table sample has been created for us. 
133

134
00:14:04,040 --> 00:14:08,360
Now we can jump back in we can do our tasks
and we can migrate this data over and
134

135
00:14:08,360 --> 00:14:13,370
see if it actually worked by connecting
into our our Aurora instance and see
135

136
00:14:13,370 --> 00:14:19,309
what happens. Now back in the database
migration service I'm just going to jump
136

137
00:14:19,309 --> 00:14:25,120
now into tasks and we're going to create
a migration task to copy this data over.
137

138
00:14:25,120 --> 00:14:31,490
We just give it a name doesn't matter
what it is and we leave that as it is
138

139
00:14:31,490 --> 00:14:35,179
It's selected the right replication
instance. It's got our source endpoint
139

140
00:14:35,179 --> 00:14:41,600
and our target endpoint there. Now all we
need to do is to tell it what we are
140

141
00:14:41,600 --> 00:14:44,630
going to be migrating across so the
schema and
141

142
00:14:44,630 --> 00:14:48,860
can put multiple rules in here but we
just add rules that we can use. 
142

143
00:14:48,860 --> 00:14:53,420
First of all we are going to tell it what
we're doing. So we're getting a schema
143

144
00:14:53,420 --> 00:14:59,630
admin and we're just basically going to
leave the wild-card in there because
144

145
00:14:59,630 --> 00:15:03,020
it's just going to migrate all tables
over. We could actually just put the
145

146
00:15:03,020 --> 00:15:06,770
table name in there but I'll just leave
it like that and it will just take it over anyway
146

147
00:15:06,770 --> 00:15:09,380
the action there is to include it into
147

148
00:15:09,380 --> 00:15:15,410
our target database. I'm just going to
add that selection rule. Okay and let's
148

149
00:15:15,410 --> 00:15:22,940
just create that task. So after a while
it'll change its status and we can see
149

150
00:15:22,940 --> 00:15:30,140
whether that table
was copied over. Okay so that has
150

151
00:15:30,140 --> 00:15:35,270
completed its now saying status load
complete and we can see here that it
151

152
00:15:35,270 --> 00:15:42,260
has one table that it has copied over. Now if we jump into the mySQL
152

153
00:15:42,260 --> 00:15:46,040
workbench and connect into this RDS
console we'll be able to see exactly
153

154
00:15:46,040 --> 00:15:54,170
whether it did really work. Okay now
we're in the mySQL workbench let's
154

155
00:15:54,170 --> 00:15:58,370
create a connection for this Aurora
database. We'll just give it any name we
155

156
00:15:58,370 --> 00:16:06,650
want and our host name again we need to
get that from our RDS console. 
156

157
00:16:06,650 --> 00:16:13,370
Make sure that you select the one for your
Aurora instance and not your Oracle
157

158
00:16:13,370 --> 00:16:21,310
instance. Select your Aurora and copy
that
158

159
00:16:26,060 --> 00:16:35,490
Now again we remove the 3306 from
the end here, our username will be admin
159

160
00:16:35,490 --> 00:16:39,780
again
and let's just test that connection quickly 
160

161
00:16:39,780 --> 00:16:56,030
Put in our password. Okay so that has
successfully connected so let's save that
161

162
00:16:56,030 --> 00:17:03,420
ok we double click on this and connect
into our Aurora instance or our Aurora
162

163
00:17:03,420 --> 00:17:06,110
cluster
163

164
00:17:10,699 --> 00:17:15,810
Okay so we've connected in and we can
see here from our schemas we've got our
164

165
00:17:15,810 --> 00:17:23,699
admin schema and we've got our tables
and lo and behold we have a sample
165

166
00:17:23,699 --> 00:17:29,520
table within an admin schema that wasn't
there before. So that brings us to the
166

167
00:17:29,520 --> 00:17:36,200
end of a pretty long lab this one and a
very good one. I think that copying
167

168
00:17:36,200 --> 00:17:41,040
databases over especially from Oracle in
an enterprise environment over to Amazon Aurora 
168

169
00:17:41,040 --> 00:17:45,720
is something that you may 
someday get to do and it's good to be able 
169

170
00:17:45,720 --> 00:17:53,030
to have the experience of doing that.
So I'll see you in the next lecture