Blob Storage to Power BI part 3: Creating an Azure Database

Welcome back to my blog post and today we will go through and create the target system where the files in our Blob storage reside in will be sent to.

What we are going to do is go back into Azure portal and from there I will show you how to spin up two things:

1. Azure SQL Server
2. Azure SQL Database

We will have to spin up the SQL server first, and then we will create and attach a database to it. One thing to keep in mind is that Databases that are attached to a different SQL server will not be able to communicate with each other. So if you are going to be attaching other Databases that you want to keep separate, but in the future will want to share data between each other, then you will have to create them under one SQL Server. 

There is no right approach since it depends on your circumstances. For our purposes we will only be creating one SQL server and one SQL Database in Azure. So without further delay lets get started.

So first things first, let us go back to our favorite website, the Azure portal and search for SQL Server at the top of the page.

Pro tip: bookmark the portal page so as  to not go mad trying to retype it every time.

This will take you to the SQL Servers creation page. Click on "Add" in the top right corner to get started.

You will then be taken to a page that is similar to the one you saw during the Storage account creation. In this screen you will have to select your subscription group, resource group (or create a new one), then add a name to your SQL server. Just like in the storage account overview this has to be a unique name throughout Azure so best to be specific.

You will then have to create a user id and password which you will then use to log into your Database once we spin it up. This can be anything so make sure not to forget it. 

When finished click on "Review + create" to continue.

You will be taken to the review page just to make sure that everything looks correct. Another thing to point out is that Azure does not charge you for have a SQL server. You will start paying once you are using a Database which I will show you later in this post.

Make sure you wrote down your password!!

Click on Create and once the validation process finishes Azure will start creating one for you.

After a couple of minutes you should then see your SQL server appear in the earlier menu.

Click on the SQL server. This will bring up the window pane that will show everything that you can do with it, from configuration to adding a new Database or data warehouse.

Continuing on click on the "Add Database" button to add a Database to this SQL server.

This will take you to the SQL Database creation screen where you will have to create a name for your Database. Most of the settings can be left as default except the "Pricing tier". This is where we will be selecting how big the Database will be and how expensive it will be. After entering a name that Azure approves click on "Pricing tier".

This will take you to the configuration screen. There is a lot of info here so lets break it down. So first thing to keep in mind is that there are three tiers: Basic, Standard, Premium. You can choose either one for your Database size and performance. Naturally the bigger or more powerful the higher the price Azure will charge you for hosting this Database.

As you can see right off the bat if you keep the standard tier you will be limited to only 250GB. If you need to have a bigger size than that you will automatically need to go to premium. I do not have that ability because I am not using an enterprise level license, but a trial account.

The second item is DTUs. Now Microsoft does not like to have people compare them to CPUs, but that is how it helped me understand them. So the more DTUs you have the faster your Database will be able to perform. This is where the real cost of hosting an Azure DB comes from. For example if I move the max data size slider all the way to the left notice how the price (15$ a month) does not change at all.

They didn't even lower it by a dollar!!

And this is an important rule to keep in mind when thinking about cloud services. Most of the expense comes from compute and not from storage. It is much harder for Azure/AWS/GCP to give you the ability to process your workflows (since they have to spin up servers, VMs to do that for you) vs them just giving you a blob storage account or any kind of bucket to just to keep your data for you.

To see this in action lets move the DTU to 100 and see how big the price difference is.

I guess Azure does not trust that a person that is using a free trial has the 150$ a month to pay for a database.

As you can see the price tag jumped from 15$ all the way up to 150$ per month. Based on my subscription I can't even spin this up. Lucky for us we don't have to worry about bills in the hundreds of dollars. Although Azure puts you on the Standard tier right off the gate, there is actually a better (and much cheaper) plan called Basic.

Hey look they gave me a cent back!!

So right off the bat Basic comes with limitations. First, you can't have more than 5 DTUs. Second is that you are only allowed max 2GB of data. However, what comes with that limitation is the rock bottom price of 5$ a month (well actually 4.99$). This is usually my go to plan since I always recommend people thinking about using cloud server to start small and then expand as you need to. I will show in another post how to modify that Database, but suffice to say it will not be a painful process. For the purposes of our POC this is the Database that we will use.

Click on the "Apply" button to select this Database.

We are now back to the Database creation screen. Notice that now your tier is basic. So now click on "Okay" to have this Database be created.

After a few minutes the Azure Database will be created. To see it click on the SQL server page (or search for SQL Database). Then select "Overview" and scroll down. You should then see all the different Databases that are attached to this SQL server.

We have now successfully created a Database in Azure. In my next blog post I will then show how to create a table in that Azure Database through the portal and through a free tool offered by Microsoft called Azure Data Studio.