Basics of Amazon/AWS’s Relational Database Service (RDS) with Microsoft SQL Server and SSMS

hi friends thanks for tuning in in this video we're going to cover the basics of amazon's relational database service or rds and specifically how to use microsoft sql server with it though a lot of the same things will apply to the other database engines we'll go build the database in just a second but let me spend just a quick minute talking about theory here in the land of databases there are of course two big categories we've got relational where your data is stored in structured tables and then non-relational which have a more flexible structure these are sometimes called nosql databases we're going to focus on the left side here relational databases and in the world of aws that's going to be the relational database service or rds rds is a managed database service which basically means it's a database where amazon does all the underlying administrative work of setting up the actual server that it runs on doing the backups patching recovery and so on so rather than you setting up a new server and going and installing something like microsoft sql server directly and managing it all of that stuff is done for you now a quick distinction here rds is not the same as or another name for microsoft sql server or postgresql those are just two examples of database engines that are supported on rds the database engine of course is the underlying system that makes everything work the query language how you store and retrieve things from the database and so forth there's actually six different engines supported by rds shown here and microsoft sql server is just one of them all right but enough with the theory let's go create a database and see how to connect to it here on the console i've just navigated to rds if you weren't here already just type in rds and that'll bring you right here so let's start by creating a database there's two ways you can do this you can use a standard create which is where you get to go and select all of your options or you can do an easy create where amazon's automatically configure things for you based on best practices that's what we're going to do here so leave that one selected like we saw on the slides a second ago these are the different engines available for this video we're going to be working with microsoft sql server so i'll select that and then you need to select the instance size so this is the actual server or virtual server that's going to be running everything we want to go free of course the free tier will get a db t2 micro instance type and you'll see the additional details there and then down below some of this is filled out for us but we can obviously change things your db instance identifier i'll just leave that at database dash 2. the master username will go with admin as well and i'll specify the password here and confirm and then you can view all of the things that are basically chosen for you by going with that easy create because this is just a basics video i'm not going to get into too much detail just know that most of these things are editable after the database is created we are actually going to go in and change one of these things afterwards but this is going to take quite a while to create so let's get that started create database i do have one already up and running database one so the new one we're creating here is database two you'll be able to refresh and see the status here right now we're creating now this is going to take a little while when i was running this earlier for my practice session it took about 20 minutes so this is a good time to remind you if you're finding this helpful so far i'd really appreciate you hitting that like button so it can be shared with more people and also think about subscribing for more content like this also while that's running let's talk about how we're going to connect to the database once it's up and going if you've done any work with sql server in the past you might be familiar with sql server management studio or ssms i've got that open here and connected to another database this is free to download if you go to your favorite search engine just type in sql server management studio download and you'll be able to grab it from the microsoft site it's a free download right here i'm currently using version 18.10 another free tool you can use to work with rds is called sql workbench at sql-workbench.eu and as noted first thing on the page here this has no relation to my sql workbench which is an oracle tool but this is another free download and another way to work with the amazon rds database all right like i said we're going to be using sql server management studio or ssms but before you can do anything you need to make sure that your database is in an available state or status it can't be creating or configuring backup or anything like that so this is going to take a while like i said i'll pause the video and come back when it's done all right our database instance has been created everything is good to go we see the status is available to connect to it from sql server management studio we're going to need a few things so let me click into database 2 you'll see various details here about the instance what we need to connect though is the endpoint and the port and then on the configuration tab if you have forgotten it already we have the master username here of admin we're also going to need that so let me grab the endpoint i'll just copy this i'll remember the port is 1433 and now let's head over to sql server management studio and we'll say connect to a database engine so i already have one connection going this will be a new one and then for server name paste in that endpoint that you just copied out of the console and then very importantly you need to include the port at the end of this with a comma in between so comma and then port one four three three don't put any spaces there it'll just be the dot comma 1433 for authentication you want to go with sql server authentication like we have here and then i'll enter that password that i used when i created this and hit connect now we're going to let this do its thing for a second here i'll tell you though it's going to fail but because there's lots of walkthroughs out there that have you do exactly this i wanted to show you this error and then show you how to fix it but you'll get an error something like this cannot connect to this name that we entered we'll say okay let's go back to the console now when we did that easy setup before when we created the database instance one of the settings in there was publicly accessible and that is set to no you'll see that here under security which means that you can't access this outside of a virtual private cloud or vpc in aws and obviously we're not in the vpc we're just on our desktop trying to connect from the ssms client so we need to make a couple changes here if we scroll up i'm going to click on modify you'll remember when we first started this there was some settings that said they were modifiable after creating the database and this is one of them i'm going to scroll down here under connectivity and then additional configuration we'll want to make this publicly accessible this is going to give your instance a public ip address we'll leave everything else the same scroll down continue and then let's apply that modification immediately and modify db instance now sometimes this is successfully modified up here on the top but if you refresh your list the status is actually modifying so you'll need to make sure that finishes before you try to connect again and i'll refresh still going and there we go it's available now so let's go back to sql server management studio and try that again i'll hit connect and i'll tell you this is actually going to fail again apologies if this is a little tedious but this is a really common error to come across so let me show you how to fix this next issue it'll be the same kind of a failure error that we saw before but going back to the console i'll click into database 2 the next thing you want to check is the vpc security groups so right here you'll see vpc security groups default that is active and again that was all set up just by doing the easy setup we didn't choose that when we went to create this now vpcs and networking in aws that's definitely a whole different video or set of videos i don't want to dig too much into the details here but in short a security group is a set of rules for the firewall basically saying what ports are available to accept inbound and outbound traffic down here you'll see the inbound rules and outbound rules just give us a little bit more space here and for the issue that we're having where we can't connect to our database instance we need to add an inbound rule for ms sql so here with inbound rules selected i'll say edit inbound rules and we're going to add rule the type here will be ms sql you'll see it automatically fills in that port 1433 the one that we were working with earlier and then i'll just say this is from my ip that'll be detected by the browser and then save rules so this will basically allow us to connect from sql server management studio on my laptop on my ip address all right let's try that connection one more time that's the air from earlier we'll try one more time to connect once again we're going to leave everything the same up here we've got the server name with the comma 1433 for port connect and voila now it works now before we dig in more here in ssms let me give you a little bit more information about connecting and vpcs and so forth that last bit was probably a little bit hand wavy but if you need more information i've put a link to this page down in the description for the video so check this out there's different scenarios for accessing your database instance whether you're connecting from inside the same vpc or another vpc or so on so if you get stuck and you still have some issues on that connection check out this link all right back to ssms one more time i'll collapse this first one that i was connected to and this new one database dash two let me just expand databases here so this is connecting to the database up in the cloud you'll see that we've got the sql server standard built-in system databases master model msdb and tempdb and you can expand these nodes here this should look very familiar if you've done any database work or worked in ssms before you're also going to get a database called rds admin and amazon rds uses this to store objects that it uses to manage your database but from here you can create your own database just by saying new database or you could run scripts to do that running a query say new query do something like select add at version execute the query you'll see the version of sql server that we're on and so on so again if you've worked with a local sql server database before using sql server management studio the experience here is going to be exactly the same except you're connecting to the database that lives in aws now all right before we wrap things up very importantly let's make sure we're saving some money so back to the console we'll navigate back to rds we ended up in the ec2 console for our security groups and i'll click on databases over here on the left and you'll want to shut down the database that you created just select that say delete for what we're doing i'm not going to create a final snapshot and i will acknowledge this is basically going to wipe everything out that's okay delete me and i actually had a second one here that i'll delete while i'm at it you probably don't but it'll be the same steps and just make sure that everything gets deleted if you are following along so you don't have any surprise bills at the end of the month but that's it that's how to set up a sql server database using amazon rds and connect to it from sql server management studio hope you found it helpful thanks so much for watching

Channel: Tiny Technical Tutorials

Views: 21,802

Keywords: technical tutorials, technical training, technology, amazon web services, aws, amazon rds, amazon relational database service, rds microsoft sql server, rds sql server, rds ssms, rds sql server management studio, connecting to rds from ssms, cannot connect to database ssms

Id: vp_uulb5phM

Length: 12min 49sec (769 seconds)

Published: Mon Jan 10 2022