This tutorial demonstrates how to use Amazon Web Services (AWS) and set up a Relational Database Service (RDS), enabling seamless access to an SQL database from anywhere with an internet connection. You will learn how to navigate AWS RDS services and familiarize yourself with hosting a database in the cloud. By the end of this guide, you'll know how to configure and connect to an RDS instance securely, providing global access to your database. This tutorial will also show you how to connect to the AWS database using SQL Server Management Studio (SSMS), Microsoft's SQL database platform.
Go to the AWS RDS webpage and create an account or sign in. For ease of use, a hyperlink to the webpage is included in this tutorial. Next, go to the search bar and search for RDS.
Once on Amazon RDS, click Create Database. On the new webpage, Click on Easy create, so that best practices will be used and the DB will be overall simpler to set up. Next, for the Engine Options select Microsoft SQL Server. After choosing the Engine Option, we will be selecting which DB instance size to use, for this example I will be using the Dev/Test. Choose the correct DB instance for what you will be using the DB. Next is the DB instance identifier, which is the name of the DB, this can be the default "database-1". The Master username is the login ID name for the admin of the DB, set it as something that you can remember such as "admin_s". Next is Credentials management, which gives you options on how to handle the master password. In this tutorial we will create a Master password. Set this password to something you can remember. After all the changes have been made, click on Create database, and wait approximately 10-15 minutes for the database to be created.
After our database state is shown as Available, we will now make the database accessible publicly. Select the database and click on Modify. Once on the new page, scroll down till you see the subtitle Connectivity. Then click on Additional configuration and select Publicly accessible. After such actions, click on Continue and within the Schedule modifications select Apply Immediately and select Modify DB instance.
Next we will configure Security Groups (the set of rules for the firewall). To begin, click on the DB identifier. Next, make sure you are on the Connectivity & security section. Once there, click on the link under the VPC security groups . In the Security Groups click on the security group, once clicking on the group a tab will open on the bottom of the page where you will click on Inbound rules. Next, click on Edit inbound rules. Once there, select Add rule, a new row will pop up where in the type section, select MYSQL. Within the Source you will have the option to either input your computers IP address, so that only your computer will be able to access the DB, or selecting Anywhere-IPv4 so that when using a hotspot or when using another ISP you are able to access the DB. Finally, select Save rules .
After these changes, what is next is to download SMSS if you have not already. I have added the link to Microsoft's website to download the application. Once downloaded, open the application. Then go back to the amazon RDS website and click on the database instance again and select Connectivity & security, once there copy the Endpoint & port information. Next you need to locate your Master username and Master password to sign in, if you forgot you can go to the Configuration to see your Master Username. After having all this information, go back to SMSS and make sure you are on the Login tab. once there you will input your endpoint name and port after using a , in the Server name section, for example database-2.************.*********.rds.amazonaws.com,1433. For authentication, you will select SQL Server Authentication. For the Login section you will input your Master password that you have created, as well as your Master password within the Password section. Make sure Trust server certificate is checkmarked; otherwise, you will not be able to access the database. Select Connect and wait for the database to connect. Once connected successfully, you will be able to see all the folders within the database.
To create a new database within the AWS RDS Instance, right click on Databases and select New Database.... A new window will appear, type in the name of the database in Database name: and select OK. Next, click on your new database to select it and then click on New Query to begin modifying the DB using SQL commands. Now with the Query tab open, you are now able to create tables. For this example, I will create a table called Employees with columns: EmployeeID, FirstName, LastName, Department, & HireDate. Here is the following code for the Employee table. CREATE TABLE Employees (EmployeeID INT IDENTITY(1,1) PRIMARY KEY, FirstName NVARCHAR(60) NOT NULL, LastName NVARCHAR(60) NOT NULL, Department VarChar(50), HireDate DATE NOT NULL );. The EmployeeID is set as the PRIMARY KEY, as this column is going to have the values that uniquely identify the table. This column is to only have data type INT or Integer values (...-2,-1,0,1,2...), as well as IDENTITY(1,1), this allows that each employee has an automatic EmployeeID, starting with 1 and increasing by 1. The FirstName column, as well as the LastName & Department, use the NVARCHAR data type. This datatype is a variable character datatype, which means the column can be filled with up to the amount of characters within the argument, so NVARCHAR(60) allows up to 60 characters in the column. Lastly, for HireDate, the data type is DATE, which means the data in this column is in the data format YYYY-MM-DD. Also, the NOT NULL constraint means that the column MUST be filled when inserting or making changes to the table. After typing in the code, click on Execute. Once executed, a tab will pop up showing whether the commands were completed successfully. To insert values into the database, you can use the INSERT INTO command, where you type the table you want to insert the values, the columns you want to insert, in order of how you created the table. Here is an example of inserting values INSERT INTO Employees (FirstName, LastName, Department, HireDate) VALUES ('Josh', 'Doe', 'Marketing', '2023-01-15'), ( 'Jane', 'Smith', 'Sales', '2023-03-23');. To check the tables values you can use SELECT * FROM Employees;, where the * is a wildcard and basically means to select ALL from the table Employees, this shows you the table at the bottom of the app. Since I accidentally added copies of the employees, I will use the command DELETE FROM Employees WHERE EmployeeID IN (3, 4); which will remove the typos. After this command you can use SELECT * FROM Employees; to show the table again.
