M07 - Part 2: Database Project - Milestone 3 - Populating the Database
For this Milestone we will be building the actual database on the MS SQL Server you installed during milestone 2. In order to complete this milestone you will need to be able to transfer files from your local PC to your AWS instance. You should have already downloaded and install WinSCP from the directions in IvyLearn. Now you will need to configure WinSCP to connect to your server.
Configuring WinSCP
First launch WinSCP and configure the Login page as indicated below
File Protocol: SCP
Host Name: Your Amazon Instance name
Username: Ubuntu
Then you need to configure WinSCP to use your keypair to authenticate. You do this by clickin on the Advanced button and selecting Authentication under the SSH heading as shown below
From this window browse and select the private key you converted and have been using with PuTTY for your terminal connection. Once you have located that .ppk key file click OK. You are now ready to click login.
You should end up with a window that looks similar to the one below. In the right hand pane you have you local PC in the right your Linux Server in AWS.
Transferring the files
Once you are connected to your Linux box you will want to browse in the left hand pane to the location where you downloaded and extracted the .sql files from the Module Assignment page. Then drag the two files to the right hand pane to upload them to your Linux Instance. When the upload has completed you will see the files in the right hand pane. You can now close WinSCP.
Creating the Database
With the script files uploaded it is now time to connect via PuTTY to your AWS instance and create the database. The files you uploaded will be in your home directory which can be accessed at the path of /home/Ubuntu
In order to create the database we will use the sqlcmd utility. Use the command below to enter into the sqlcmd utility
/opt/mssql-tools/bin/sqlcmd –S localhost –U SA –P YOURPASSWORD
This should bring you to a prompt that looks like the image below
From here you are ready to build your database using the command below
CREATE DATABASE sample
Once you have typed the command hit enter to get a new line. Then type GO and press enter again. Include a screenshot of the result, then type Exit to leave SQLCMD
Build the Tables in the database
In order to create the tables in the database we will need to execute the sample_model.sql file. This file contains a series or SQL commands that will build the tables. We will again be using SQLCMD but this time we will be passing the it SQL Script file that you uploaded earlier in the lab. The command below should be executed in your PuTTY window while you are in your home directory. If you need to navigate to your home directory you can do so with the command cd ~
/opt/mssql-tools/bin/sqlcmd –S localhost –U SA –P YourPassword –I sample-model.sql
Once this command has completed include a screenshot below of the output.
Load the Data into the Tables
Now that your database has tables in it we need to load data into the tables. The sample data we are going to use is in the sample-data.sql file that you uploaded to your server with WinSCP. In order to execute this script we will use the same command as when we built the tables and simply replace the sample-model.sql filename with the filename sample-data.sql.
Once the script has finished include a screenshot of the output here.
Test Query
Congratulations, if all of your scripts ran as expected you now have a working database server complete with 2 populated tables. The last step for this milestone is to execute a simple query against of the tables to show that it contains data. For that you will need to log into SQLCMD again with the command below
/opt/mssql-tools/bin/sqlcmd –S localhost –U SA –P YourPassword
Once you are at the prompt type in the following command
Select * from product
Go
Take a screenshot of the results and paste it here. Don’t forget to upload this document for your instructor to review.