Azure SQL Setup

Now that we have our site being authenticated we need to create the rest of the login process for our new users.  When our users are logged in the first time we want to send them to the login page so that we can collect some additional customer information about them.   We will use the id that is passed back to us by Facebook to lookup our new user in the database we are going to create.    If we don’t find the Id we will redirect our user to the new login page.  

Our first step will be to layout our database.  We have a lot options for databases that can be hosted in the cloud.   On one end of the spectrum we could create a VM to host our database.  This would require us to manage the VM as well as the database.  We would also need to setup the storage and manage backups directly.   This gives a fine level of control over the environment.   The downside of this approach is that you have taken many limits of on prem implementations with you into the cloud.  This implementation requires your database management team to patch servers, manage uptime and plan for scalability.

On the other end of the spectrum are cloud native databases. They abstract away many aspects of database management and limit our control over the environment the tradeoff is that we don’t have to dedicate resources to maintenance and since our uptime is guaranteed we can focus other higher value tasks for the company.   Additionally, cloud native databases can be scaled on demand.   

Because our application is cloud native we will use a cloud native database.   Azure Sql is Microsoft’s recommended cloud native SQL database.   It’s is intended to handle most database workloads.  There may be specific high demand applications that require access to sql server internals that would not be a good fit for Azure Sql,  but these applications would be rare.  In those cases it would be best to either leave the database on prem or create a VM in the cloud to host the database.    You can read a good summary of Azure SQL here 

Some key advantages to using Azure SQL is that developers can utilize database tools with which they are already familiar.   We can use SQL Server Management Studio to work with our SQL Server Database. There are a few minor setup steps to enable your SSMS to talk to azure you can find the steps here .

Once we are authenticated we can work on our database from within SSMS. 

Let’s  briefly go through the process of setting up a new database in the Azure Portal.   Below is an image of the create database dialog as seen in the azure portal.  The creation process is similar to other resource creation processes in Azure.   The first significant item to address is the selection of a  resource group to manage your database.  Remember that resource groups are not just to group resources but also define security around a group of resources.  For this reason consider how you want to allow access to your db as you lay out your resource groups.

 The next setup item is the size of the server.  In order to determine the size of the server you need review DTU documentation here.    

DTU’s are unique to azure.  They are a measure of overall performance capalities.    DTUs are based on a blended measure of CPU, memory, reads, and writes. As DTUs increase, the power offered by the performance level increases. You can find a useful DTU calculator here .  The calculator is only a starting point to give you a rough idea of your DTU requirements.  You will need to fine tune your DTU settings once the db is under load.  As expected Microsoft gives you several tools to measure the performance of your db.  

I recommend adding appropriate tags for your resource.  Tags are a useful tool for managing your resource.  For more on tags see my earlier post. 

Once we are done entering create your database and server.    Next we will setup SSMS to work with our cloud database.  We will then be ready to layout our initial tables and setup our db access.