Azure for Beginners - Creating an Azure SQL Database

Nowadays, companies tend to not only collect lots of data, they also tend to collect a wide diversity of data. It's not unusual for a company to collect everything, from user data from their website to spreadsheets and everything in between. Not only does this data need to be stored somewhere safe and accessible, it also needs to be stored somewhere which grows along with the amount of data which is gathered. It's no surprise that many companies choose the Azure ecosystem for this, as it ticks all the boxes. A very popular option to store data is to use an Azure SQL Database. In this beginner's guide, we will walk you through the steps to create such an Azure SQL Database.

The data companies collect can broadly be divided into two categories: structured and unstructured data. Structured data fits neatly into tables with rows and columns (e.g., dates, numbers and such), while unstructured data doesn't (like videos and images). Depending on how the data is going to be used, generally, you'd want to store your structured data in something called a relational database. This is a database which stores and organizes the data into tables, containing rows and columns, based on a schema, which is like a blueprint for how a table is structured. The tables in a relational database can be linked to each other. For example, if you have one table containing data on orders placed by customers and another table containing customer data, you can create a combined view of these tables while keeping them separate. Azure SQL Database is such a relational database.

Note: The look of Azure tends to change regularly. Your screen might not look exactly like the images in this guide. Some features might have been moved to a different tab, but the general steps should be the same.

Note: please note that the services in Azure are all paid services. The cost is dependent on your usage and configuration. (More information can be found here.) It's advised to do this under the supervision of a database administrator or a data engineer.

After logging in, navigate to SQL Database. You can do so in various ways. One of the easiest ways would be to click on the 'Create a resource' button in the top left corner of the start screen.

Next, click on Databases in the bottom left corner and on Create below SQL Database.

If this is your first SQL Database, click on the blue button in the middle of the screen. If you already have an Azure SQL Database, click on the '+ Create' button in the top left corner.

Similar to creating an Azure storage account, the creation of an Azure SQL Database will lead us through several different steps, divided in multiple categories. These categories are reflected by the tabs on top. (Basics, advanced, networking, etc.) We will start with the Basics tab. 

Select your subscription and your resource group. If you don't have a resource group yet, create one through the link below it.

To be able to create and use an Azure SQL Database, we first have to create a server. A server is like a central administrative unit we can manage our database from (logins, firewall rules, etc.) If we want, we can run multiple databases on the same server, but for now we will stick with one. Click on 'Create new' to create a new server.

First, make up a new server name. Below it, you will have to choose the location for your new server. Similar to choosing a region for your Azure storage account, choosing a server location might not be as straightforward as choosing the location closest to you. For instance, if you are creating an Azure SQL database on behalf of your client, you might have to take into account your client's location. What if you're working with other teams, remote and otherwise, accessing the server from different locations? For our beginner guide, simply choose the location closest to you. If you have no idea what region to choose, you can use this tool to see which region would give you the highest speeds. (Lower latency is better.)

Next, we will have to choose an authentication method. If you opt for SQL authentication you will have to set both a server admin login

Setting an admin is as easy as looking up your own name in the search bar and selecting yourself in the search results.

When you're finished, click 'Ok', after which you will return to the Basics tab.

Seeing as we have just created a new server, we will now see it selected. Above it you will have to make up a name for your new database.

Below it, you will have to choose whether or not you want to use a SQL elastic pool.  When managing multiple databases, you might prefer to have them share resources, achievable through an elastic pool. Suppose you have two databases, each configured with 2 vCores. By placing these databases in an elastic pool, they can share a total of 4 vCores. If one database requires more computational power and needs 3 vCores at a certain time, it can utilize the additional vCore from the elastic pool, ensuring efficient resource usage without extra costs. (The same goes for a DTU based purchasing model.) However, since we are creating only one database in this guide, we will choose 'no' for using an elastic pool.

You will also have to choose between Development and Production workload environment. The workload environment setting is simply a way to change default settings during the creation of this database. Since it defaults to Development, we will leave it as such, because it select a less powerful compute/storage tier, which will be sufficient for this guide. If have chosen Production, have created the Azure SQL Database already, but want to up- or downgrade the compute/storage tier, simply navigate to your database > settings > Compute + storage blade.

Although we have chosen Development, we still want to take a look at the Compute + storage configuration.

A new screen will open, in which you have to choose a Service tier:

• General Purpose - This tier will suffice for most use cases, as it comes with a default SLA and storage latency between 5 ms and 10 ms. Out of all three options, this is the cheapest one, because the compute and storage are separated.

• Business Critical - This tier is great for when you expect high transaction rate workloads and have low-latency I/O requirements (1-2 ms on average), because compute and storage are tightly integrated through locally attached SSD storage as data storage.

• Hyperscale - Great for very large databases, as it allows for (separate) scaling of your compute and storage. It provides low-latency (1-2 ms on average) for the most frequently accessed part of your database.

Below it you will have to choose a Compute tier:

• Provisioned - With the Provisioned compute tier a specific amount of compute resources are continuously allocated to your database, whether you use them or not. This will cost you a predictable amount of money per month. 

• Serverless - The Serverless compute tier bills only for usage per second, and automatically scales based on workload demand. This means that you won't be billed for usage during inactive periods. (You will only be billed for your storage.) The serverless compute tier is  only available in the General Purpose service tier and the Hyperscale service tier.

Cost-wise, it's hard to say which compute tier will be the best fit for your situation, as it entirely depends on your usage. Either one might end up being more expensive than the other. For this beginner's guide we will opt for the Serverless compute tier.

Below it, depending on your business needs, you might also want to change the hardware configuration your database will be running on as well as the minimum and maximum amount of vCores it will use. For this guide, we will leave the default settings untouched.

If you have opted for the Serverless compute tier, depending on your Service tier (it's currently only available for General Purpose), you might also have to choose whether or not to enable auto-pause. Auto-pause delay is the period of time the database must be inactive before it is automatically paused. Since the Serverless compute tier is billed for usage per second, this is a very important setting. 

Below it, you will also have to choose a Data max size (in GB), as well as whether or not you want to make your database zone redundant. This makes your database redundant to a larger set of failures (including datacenter outages), which obviously has a price tag attached to it. After finishing your configuration, click 'Apply' and you will return to the Basics tab.

After returning to the Basics tab, you will have to choose the redundancy mechanism for your backup storage, which protects your data from planned and unplanned events (like transient hardware failure, network or power outages, or massive natural disasters) by storing multiple copies of it.

• Locally-redundant backup Storage (LRS) - Your data is copied synchronously three times within a single physical location in the primary region. This provides strong resiliency against hardware failures like server rack and drive failures, but if something happens to the data center itself, like a natural disaster, your data might be lost. It is not be the best option if you're requiring high data availability or durability. It is the cheapest option out of all redundancy options.

• Zone Redundant backup Storage (ZRS) - Your data is copied synchronously across three Azure availability zones in the primary region. Each availability zone is a data center in a separate physical location, having its own independent power, cooling, and networking. This offers strong protection within a single Azure region. This is recommended for high availability scenarios.

• Geo-redundant backup Storage (GRS) - Your data is copied synchronously three times within a single physical location in the primary region using LRS, after which your data is then copied asynchronously to a single physical location in a secondary region hundreds of miles away from the primary region. This is great for failover scenarios. It allows read-only access to data in the secondary region, which is great in cases of regional unavailability of the primary region. (E.g., natural disaster or power grid failure.)

If this option is greyed-out for you, it's done through your workload environment choice, as Development workload environment sets the Backup storage redundancy option to use Locally-redundant backup storage and Production workload environment sets it to Geo-redundant backup storage. For this beginner's guide, we will choose Locally-redundant backup storage.

Next, we will move on to the Networking tab, where we will need to configure network access. This is where we select how we want people to connect to the server we have made on the Basics tab.

We start off with Firewall rules, which arranges the access to your server. By default, the server prevents access to it until you add your current client IP address. You can do this either by flipping the switch to 'Yes', or you can do so later through the server settings. For our guide, we will do this later, as it will give us a chance to see where this can be done.

We move on to the Security tab where we can configure the security settings for our Azure SQL database.

We get the option to opt in to a Microsoft Defender for SQL trial, which is basically Microsoft Defender for the cloud. Similar to its Windows cousin, Microsoft Defender for SQL helps mitigate potential vulnerabilities (Vulnerability Assessment) and detect anomalous activities which might pose a threat to your SQL database (Advanced Threat Protection). For our beginner's guide, we will choose 'Not now'.

Below it, we have the option to add a ledger, which is a feature which acts like a secure and transparent record book by keeping a permanent history of all changes made to the data. So, if a row in a table is updated in the database, its previous value is maintained and protected in a history table. It's a record of all changes made to your database over time. By creating a ledger, you add tamper-evidence capabilities in your database. In case of audits or similar purposes, it lets you cryptographically attest that your data hasn't been tampered with. It will protect your data from attackers or high-privileged users, including database administrators (DBAs), system administrators, and cloud administrators. For our beginner's guide, we will not create a ledger.

The Always encrypted feature helps you protect the sensitive data stored in your Azure SQL database, such as credit card numbers, national/regional identification numbers. By encrypting this data on the client's side and not revealing the encryption keys to the Database Engine, there is a separation between data-owners, data-viewers, and data-managers. This reduces the theft of sensitive data by insiders. In essence, the Always encrypted feature automatically encrypts sensitive data before it is stored in the database and decrypts it when the data is retrieved, all without the application needing to know about or handle the encryption process. For this beginner's guide, we will leave this feature 'Off'.

The Additional Settings tab lets us configure collation and sample data.

It's also necessary to set a Maintenance window, which is a pre-defined time slot during which Azure can carry out planned maintenance tasks on your database. During these maintenance tasks, your database will still be fully available but it can be subject to short reconfigurations, which might result in short connection interruptions. By choosing a window outside of peak business hours (the default), it will minimize the impact on users and ensure the database remains reliable and secure. The maintenance window is free of charge. For our beginner's guide, we will leave it on System default (5pm to 8am).

The Tags tab lets you set your tags (if needed), which let you organize your resources by categorizing them. Tags are applied to the resource itself, in this case our SQL Database. If we are running multiple databases, with the help of these tags we can quickly and efficiently find all resources with specific tags. Tags are stored in key-value pairs. For example, if we were to enter 'Environment' in the Name field, and 'Development' in the Value field, they will form a key-value pair. Or how about, 'Department' as the Name and 'Finance' as the Value. If we would want to track costs for all development environments in the Finance department, through these tags we can now filter on 'Environment = Development' and 'Department = Finance'. For our beginner's guide, we will skip the tags and move on to the Review + create tab.

Finally we have arrived at the Review + create tab, which is an overview of all the choices we have made thusfar.

After having checked your answers, click 'Create' to finish. Azure will then start with the deployment of your database. (This usually takes around 3 or 4 minutes.) If it is successful, you will see something like the image below. Click on 'Go to resource'.

If you have been following along, we still have to give ourselves access. We can do so by clicking on 'Configure'.

On the Public access tab, select 'Selected networks'.

By selecting 'Selected networks', new fields will appear. Below Firewall rules click on the 'Add your client IPv4 address', and click on the 'Save' button after. This will automatically grant your IP address access to the Azure SQL Database.

That's it! You have now created an Azure SQL Database you can connect to through the database management system of your choice, or you can stay within the Microsoft ecosystem and use tools such as Azure Data Studio or SQL Server Management Studio (SSMS) to connect to your new database. Below, we will show you how to connect to your Azure SQL Database with the use of Azure Data Studio.

If you haven't already, you will need to install Azure Data Studio. This is a standalone program to be run on your computer. From the main screen of your Azure SQL Database, click on the blue 'Open Azure Data Studio' button. Alternatively, you can also click here to directly go to the Azure Data Studio download page.

On the next screen, click on the 'Download Azure Data Studio' button, which will bring you to the Azure Data Studio. (Leave Azure open in your browser, as we will need to use it in a moment.) Download the right version for your operating system. If you are unsure which version to download, you can do the following.

After you have installed Azure Data Studio on your computer, return to your browser. You will need to copy the server name, which can be found on the main screen of your Azure SQL Database. Copy the whole server name (including the .database.window.net part). 

Run Azure Data Studio on your computer the way you run any other program. You will be met by the following screen. Here you will need to click on 'Create a connection'.

A new screen will slide into view. Paste the server name you have just copied into the field next to Server. Click on the blue 'Connect' button below, after which Azure Data Studio will connect to your database, where you will be able to run SQL queries, create notebooks, etc.

You have now successfully created an Azure SQL Database and have connected to it through Azure Data Factory. Please note that if you have chosen Serverless compute during the setup of your Azure SQL database, connecting to your database and performing any operations that consume CPU and memory will initiate billing. When you're finished using Azure Data Studio, be sure to disconnect from the database by right-clicking on the database connection you want to close in the 'Servers' pane, and choose 'Disconnect' from the context menu.