Creating a table in Azure Database using SQL Server Management Studio

What is SQL Server Management Studio (SSMS)

In our previous blog post we looked at how to easily create a table using Azure Data Studio. After a quick installation and set up, we were able to run SQL queries against our Azure SQL server and create the Staging, Archive, and View in our SQL Database.

If you missed that post please follow this link to review it: Creating a table in Azure Database using Azure Data Studio

In this blog post I will show you how to quickly set up SQL Server Management Studio (I will refer to it from now on as SSMS), configure it to point it to our Azure SQL server, and then run the CREATE TABLE and CREATE VIEW scripts that we did through Azure Data Studio.

While the past two blog posts have not been too exciting I still think it's useful to find a tool that you like using from all the options that are available.

Installing SSMS

To install SSMS follow the link to the Microsoft docs site: Download SQL Server Management Studio (SSMS)

Then click on "Download SQL Server Management Studio (SSMS)". As of this writing version 18.4 is the most current one.

As we did for Azure Data Studio, follow the instructions and install it on your local machine. The installation process is fairly straight forward. The file itself will be much larger than Azure Data Studio as SSMS contains a lot more features than Data Studio.

Once the installation is done you will need to restart your computer. Once you have done so click on SSMS and you should see the following screen:

So let's now configure this to connect to our Azure SQL server.

Configuring SSMS to connect to Azure SQL Server

So first step is to enter our SQL server name. This can be found in our Azure Portal. If you have been following my other blog posts you should already have the Azure Portal bookmarked and have "SQL Server" copied and pasted into the search function.

Just in case below is the picture of where you can find the SQL server name.

So after filling in the SQL server name change the Authentication method to SQL Server Authentication and then add the userid and password that you set up for the SQL Server.

After all is said and done you should have something similar to this:

Click on "Connect" and give it a couple of seconds to set up a connection.

If this is the first time you are logging into Azure through this you might get the following prompt:

Not to worry. Essentially you will just need to click on "Sign In" and then select "Add my client IP address".  This will most likely happen if you are using a different laptop or if you are using a VPN service. If you are using your work network, you should not run into this issue.

After you click "Sign In" you will see a pop up window that will ask for your Azure portal credentials.

Nothing bad ever happened to people who share their email address.

Once signed in you will then be able to add the client IP address to the SQL Server. Click on "OK" and then continue.

After a couple of seconds you should see the SQL server, databases, and views in the top left corner.

Success!!

Running SQL in SSMS

Now that we configured SSMS we are not going to be able to run our TABLE CREATE scripts right from SSMS.

To do that, right click on the SQL server that you just connected to and select "New Query". In this case it would be the dataengineerslife SQL Server that I created.

You will see the query screen open up on the right side of the screen:

Now we are going to first run the following SQL code to drop our POC_Stage table. This way we can then recreate it in SSMS:

Now we re-enter the SQL script that we used to create that table.

Same process will be done to drop and create the "POC_View":

Next Steps

Now that we have seen all the different ways we can create tables, in my next post I will get into developing an Azure Data Factory pipeline and all the necessary configurations.