Welcome back to Backspace Academy. In this lecture we'll have a look at the 2
different options that are available for running databases on AWS. We will then have 3
a look at a database example and finally we'll finish up with a hands-on lab 4
where we'll be creating a database server on AWS and then remotely 5
connecting in to that database. The relational database service or RDS for 6
short is a fully managed database service that makes it easy to launch 7
database servers in the AWS cloud and scale them when required. The RDS service 8
can launch servers for mySQL including variations of the mySQL 9
database engine with MariaDB and Amazon's own enterprise version of mySQL 10
Amazon Aurora. Standard Postgre SQL is also available and also available 11
as Amazon's Enterprise Aurora Postgre SQL. Microsoft SQL server and Oracle 12
are also available. Amazon DynamoDB is AWS's NoSQL database as a 13
service. It's a serverless service like Amazon S3 and as such you don't need to 14
worry about the underlying infrastructure behind it. 15
AWS takes care of everything for you and it provides high speed extremely low 16
latency performance. Amazon Redshift is a fast fully managed petabyte scale data 17
warehouse that is based upon the postgre SQL database engine. If you're 18
looking for a big data storage solution Redshift is perfect for this. Amazon 19
ElastiCache is an in-memory data store or cache in the cloud. It allows you to 20
retrieve information from fast fully managed in-memory caches instead of 21
relying on slower disk based databases. The AWS database migration service or DMS 22
orchestrates a migration of databases over to AWS easily and securely. 23
It can also migrate data from one database engine type to another totally 24
different database engine type, for example you can use it to migrate from 25
Oracle over to Amazon Aurora. Amazon Neptune is a fast reliable fully managed 26
graph database service it has a purpose-built 27
high performance graph database engine optimized for storing billions of 28
relationships and clearing the graph with millisecond latency. 29
Now let's have a look at how you might 30
use some of these database services in a real-life example. 31
On the right there, we've got our corporate data center and 32
inside of our corporate data center we've got an on-site 33
Oracle database. Now let's just say that Oracle database, 34
it's old, it's worn out, it's outgrown its capacity 35
and it needs to be replaced. Now you've done 36
a total cost of ownership analysis on the situation 37
and you've identified that it is far more cost effective 38
to host that database on the aws cloud. So the first thing that you are 39
going to want to do is to launch an RDS database 40
instance. Now let's say you want to further reduce your cost 41
by not having to pay for the Oracle licensing fee 42
and what you can do is that you can launch an Amazon 43
Aurora database instance and that will be running 44
either the Mysql or the Postgresql open source database engines and by doing 45
that you're not going to be paying for a licensing fee. The disadvantage of 46
that is that some of the fields of data that are 47
located in that Oracle database may not be compatible with the Amazon 48
Aurora Mysql or Postgresql fields and what you're going to need to 49
do is that when you take that data out of your 50
Oracle database, you're going to have to change it and 51
manipulate it to suit the amazon aurora database, 52
and that is where the aws database migration service comes in. 53
You can define a database migration workflow by specifying 54
the source database, the target database and any operations on that data that 55
need to occur during that migration of that data. 56
Once you've done that you can then run the database migration 57
job and that will look after everything for you. 58
It will migrate that data from that on-site oracle database 59
to your amazon aurora database and at the same time it will be giving you 60
feedback through the aws management console, 61
as a dashboard, on the performance of how that job is 62
actually going, because that job could take hours, it could take 63
days, it could take weeks, depending on how big your oracle database 64
is and how fast your connection to the aws 65
cloud is, and it will also give you feedback of any errors and alert you to 66
any problems that may occur. Once our RDS instance is up and running 67
and the data has been migrated over, then we can look at launching a web 68
server that can receive traffic and requests from the outside 69
world over the internet and then get that data that is required 70
from the RDS database and then return that back to the 71
requester. Now let's just say we're getting a lot 72
of requests from the outside world and a lot of that 73
is for the same data. What we can look at doing is getting all 74
of our regularly accessed content and putting it into an Elasticache 75
node and, because the Elasticache node is serving those requests from its 76
memory, not from a solid-state drive, as is the case of the rds service, it 77
will be returning that very quickly and it will be at a lower 78
cost. Now we need to take into consideration 79
that the costs of storing data in memory is more expensive 80
than storing that data on a solid state drive and so we need to 81
make sure that Elasticache node only contains 82
regularly accessed data. So the way that we would do that is that requests would 83
come in from the outside world to the web server, 84
the web server could then check to see whether that data 85
is in the Elasticache node. If it is or if it is in the Elasticache node it 86
will simply grab that data and then forward it back to that 87
requester. Let's just say a request comes in and 88
that data is not in the Elasticache node. So then the web server will go to 89
the RDS database instance, it will get that data if it's there and 90
after it has got that data, it will then write that data into the Elasticache 91
node, and at the same time it will define a time to live or a ttl 92
for that specific data, and once that ttl has expired, 93
if there are no further requests for that data within that ttl, 94
then that data will be removed automatically by the Elasticache 95
service from the Elasticache node, and by doing that, all of that data 96
that's in the Elasticache node will be 97
regularly accessed data that has been accessed within that time to live period. 98
Now let's just say a request comes into that web server 99
for either writing to or deleting from that RDS database. So the way that would 100
work is that the request would come into the web server 101
the web server would then either delete or write to 102
the RDS database. If it is deleting from the database 103
then it would also delete from the Elasticache node as well. 104
If it's writing to the database then it would also 105
write to the Elasticache node and again it would define a time to live period, 106
so that if that data is not requested within that time to live period then it 107
will automatically be removed from the Elasticache node 108
by the Elasticache service. Okay let's have a go at doing this 109
ourselves. In this lab we're going to be launching 110
an RDS database instance that will be using the 111
Mysql database engine that will be covered under the AWS free 112
tier so this lab is not going to cost us anything. 113
Now because we're launching that within our VPC which is our 114
own private space within the aws network, no access will be allowed 115
to that RDS instance unless we allow that to happen. 116
So that rds instance will have a default security group associated to it 117
and that security group is a firewall to the outside world 118
and it will have rules that will prevent access 119
to that database instance. So to allow access to that RDS database 120
instance, we are going to need to create a new 121
security group and within that security group we are 122
going to define a rule that will allow access 123
from our remote computer. Then when we are launching 124
our RDS instance we simply define and associate that security group 125
with that instance and that database will be launched 126
with that security group associated to it. 127
Once we've done that we can then install the Mysql workbench software on our 128
remote computer and then provided we've got the username 129
and password that we created for that database, we will be able to connect into 130
that database instance and manipulate that database. 131
Now before we start the lab you need to make sure that you 132
open up these introduction to AWS lab notes and follow along with me. 133
Very important that you follow the lab notes not the video when you're doing it 134
yourself. So certainly watch the video now but 135
when you go and do it yourself, make sure that you follow the lab notes. 136
The reason being is that the AWS management console, it continuously 137
changes. I can't remember a week gone by where they haven't 138
fiddled with this in some shape or form. So if you've got the lab notes you can 139
follow those and they will be updated whenever I'm aware of any changes to the 140
management console. With this lab you may run into trouble 141
when you're trying to connect into your RDS database. 142
What I've got there is in this creating a SQL database with RDS, that chapter 143
that we're going to be doing for this lab, there is 144
a troubleshooting connection issues section. If you click on that 145
it will go through all of the different scenarios that you may encounter 146
when you can't connect to this database instance. 147
So it could be there we see wrong user name password. We could put 148
the wrong endpoint in there, a whole heap of different things. It's normally 149
something very simple such as you haven't clicked on 150
public when you've created the database or, it could be 151
something a bit more fatal, such as you're trying to connect 152
in from your work environment and the port 3306 is being blocked by 153
by your work environment. If you run into those sorts of issues you're just going 154
to have to run the lab from at home and use your home internet 155
connection instead. So that will basically detail all of 156
those issues and how you need to solve those. 157
Now before we get started with launching our database 158
instance, we need to make sure that we create this security group that is going 159
to be or is going to allow inbound access 160
from the outside world. So we'll just create that. 161
So we'll go to services and i've just listed this by a to z. We'll go to vpc 162
we'll scroll down to security and security groups. So click on security 163
groups and we're going to create security group. 164
We'll give this security group a name and i've just copied from the lab notes 165
here and we'll give it a description. 166
Okay what we need to do now is that we need to add an 167
inbound rule that is going to allow that access. So we'll add 168
rule and the type of rule is going to be mysql/aurora 169
and the source is going to be from anywhere. 170
So although anyone can access our database, to get anything out of that 171
database they're going to need to have a username and password. 172
So we'll just scroll down. We don't need to have an outbound rule 173
and we'll create that security group. So there our security group has been 174
created. So when we launch our rds database we can associate this 175
security group to that database and then that database 176
will accept inbound traffic from the wider 177
internet. So we'll go to services now and we'll 178
now go to the RDS. 179
Okay so we'll scroll down to databases and then we're going to create a 180
database. So click on create database. 181
We're going to do a standard create and we're going to use 182
mysql and we'll scroll down here and we're going to use the free tier 183
because it's free why not. Now we need to give our db instance an 184
identifier. 185
So just give it a name there for your database or for your database instance. 186
Now we need to define a username and password. So the username, I'm just going 187
to use is, going to leave that as admin and put in a password there. 188
We'll scroll down here and we're just going to leave that as a t2 micro that's 189
a small instance that we're going to get on the free tier. 190
I'm not going to have any auto scaling for the storage because we're not going 191
to be putting anything in this database. We don't 192
have to worry about that, we're not going to be 193
launching this into multiple availability zones so that is all grayed 194
out that is fine. We're going to launch it into our 195
default vpc. Now we need to click on additional 196
connectivity configuration. 197
No we need this to be publicly accessible. We're going to access this 198
from outside of our virtual private cloud. So click on yes there. 199
Now we need to select our security group that is going to allow for that outside 200
access to our vpc. So we'll select that there 201
and it was called backspace and there it is backspace rds intro lab. 202
So we'll just click on that to select it and we can see there it's put it down 203
there. I'll just click outside of this. So there 204
we've added that, we've associated that backspace rds 205
intro lab security group to this instance. 206
We're going to leave it with password authentication so someone who needs to 207
log into this database will need to have a username and password 208
that we have defined up here. Which we have defined here is admin and 209
then our password. 210
We'll just click on additional configuration. 211
So we're going to give an initial database name and so when 212
this database instance or server or whatever has been 213
launched, that can create a database for us and then we can 214
automatically can straight away start playing around with that database. So i'm 215
just going to give it a name there we have test 216
that will be fine. We don't need to have any backups of this data that's fine. 217
I'll leave that it's just a lab nothing special. Scroll down a little bit 218
further. Okay the estimated monthly cost it's 219
going to be covered under the free tier we don't have to worry about any costs 220
there and we'll click on create database. 221
So there it has started to create this database and the status is creating so 222
after about five, ten minutes or so 223
this will change to being available. 224
So we'll just let that chug away. Okay so after a few minutes our status has 225
changed to available. So what we'll do now is 226
we'll click on this database 227
and then we can see some information about it. So we can see that we've got 228
our vpc security group, our backspace rds intro lab security group has been 229
associated with it, it has a or it is publicly 230
accessible and so that means that it's going to have a public ip address 231
and so we're going to be able to access that and it's going to have this end 232
point here for us. 233
Now if we have this endpoint we can now connect into our database 234
using the mysql workbench. Now the link to download that is in the 235
lab notes so make sure that you download that and install the MySQL 236
workbench it runs on on linux, it runs on windows, runs on mac, 237
it's pretty good and quite well supported. 238
So i'm just going to drag it into here and i'll just get rid of that connection. 239
okay so your MySQL workbench should look something like this. 240
We want to add a connection. So we just click on this plus next to 241
MySQL connection. Now the connection name, i'm just going to call it test 242
that can be anything you want. Now this host name is going to be 243
our endpoint, so we copy our endpoint here 244
and we'll paste that in here. 245
There's our port 3306. 246
Now our username is going to be that username and password that we defined. 247
So we actually did not use root we used admin 248
and that should be fine that's all we need. 249
What we can do now is we can test this connection. So down the bottom here a 250
test connection button we'll click on that. 251
Okay so it's started the connection process so it's gone to that 252
host name that endpoint and that's worked and now it's asking for the 253
password. So that is the password that we defined 254
when we created that that instance. We'll click on ok 255
and if all goes well we should have a success screen. There we go successfully 256
made the MySQL connection. The host was that 257
that end point, the port was 3306, our user was 258
admin. Now if you find that this doesn't work for you 259
remember there is always the troubleshooting connection 260
issues section in the lab notes. Make sure you follow those if you run into 261
problems here. So just click on ok that 262
and okay. So that has set up that connection so whenever we go into 263
the MySQL workbench that connection will be there 264
and all we need to do is double click on it and it will open up the SQL editor 265
for us. 266
That will take quite a bit of time, you'll have to connect in. 267
Okay there we go so we have successfully connected into 268
our database using that that username and password and we can see here we've 269
got our test database that has been automatically 270
created for us by the RDS service and we can click on that and we can see that 271
we've got no tables or anything in there yet 272
but we could add those if we wanted to. So that's 273
pretty well all we need to know about about this 274
lab we just created a database instance. We've connected into that successfully 275
and that's pretty good. When we get further into the course 276
we'll learn a lot more about this sort of stuff and how we can 277
do a lot more and how we can launch other instances such as Oracle 278
and microsoft sql server and all that sort of thing 279
but for now that is all we do for this lab. What we'll do now is it will clean 280
it all up. I'll just close that out there now 281
and we'll go to actions and we'll delete. We don't need to create a final snapshot 282
we just click on acknowledge that once we delete it 283
everything is gone and we just talked a lot we just type 284
delete me in here. 285
Okay so that will be deleting that so you can see up here a note saying 286
deleting db instance. Now the next thing that we 287
could do is we could clean up that that security group so we go to services 288
and then vpc 289
and if we go to security groups now we can find this so we'll just i just 290
sort this by security group name. So there we have that security group. Now 291
if we go to delete that now. If we click on delete security group, it 292
won't let us do it. So i'll just click on it now 293
and it won't let us do it because that that RDS 294
instance hasn't finished being terminated. So you need to wait until 295
that has terminated. You can come back here and 296
then you can delete that security group. If you forget about it, it's not a major 297
deal because you don't get charged for security groups, but 298
your rds instance you need to make sure you terminate that otherwise yes you 299
will get billed for that. Although it is under the free tier, if 300
you go to launch another one and you've got two of them running then you're 301
going to get billed for one of those. So just cancel that out. So that brings 302
us to the end of the lab. I hope you enjoyed it. Make sure that you 303
download the lab notes and do all that yourself and I look 304
forward to seeing you in the next one.