Problem
I want to learn how to write SQL queries and how the SQL Server database works. Most online resources use the free sample database from Microsoft SQL Server DBMS called AdventureWorks. However, they all install this database in an on-premises SQL Server. I want to learn how the cloud version, Azure SQL Database, works. Is it possible to have a sample online transaction processing (OLTP) database in the cloud?
Solution
In this tutorial, we’ll go over the required steps to create a SQL Server instance in the Azure cloud and how you can install the AdventureWorksLT sample database, a lightweight version of the original AdventureWorks database. If you’re interested in the on-premises version of the sample database, check out AdventureWorks Database Installation Steps.
To follow along with this tutorial, you need an Azure subscription. You can get a free trial here.
Install a New SQL Server Instance in Azure
Log into the Azure Portal using a browser. On the home page, click on “Create a resource”.
In the Marketplace, search for SQL Server. In the results, click on SQL Server (logical server).
This logical server is a container that will host your Azure SQL Databases. It also allows you to configure common properties, such as the firewall or an admin account from Azure Active Directory. In the overview screen, click Create.
Choose a subscription, a resource group, and a region for your server. A resource group is a logical container for one or more Azure resources and services. If you haven’t created one already, the wizard can create one. You also need to supply a name for the server, which must be globally unique.
For the authentication, you can choose between Azure Active Directory (AAD) authentication and/or SQL authentication.
In the case of AAD, you need to choose a user or group from Active Directory, while in the case of SQL authentication, you need to provide a username and a password.
In the Networking tab, allow Azure services and resources to access the server.
This doesn’t mean every Azure service (like Azure Data Factory, for example) can read your data. You still need to assign permissions. This firewall rule allows Azure services to gain access to the SQL Server over the network. If you don’t enable this, you may need to explicitly add IP addresses to the firewall later on.
Click on Review + create. You’ll get an overview of everything you configured.
As you can see from the review screen, there’s no charge for creating a logical SQL Server instance. You pay for the databases but not for the server. Click on Create at the bottom. After a while, the resource will be ready.
In the Overview screen, you can find the most important information about your server. Take note of the “server name,” as this is the one you need to enter in your connection strings or in tools like SQL Server Management Studio.
There’s one more thing we need to configure before we create our database. In the Networking section, add your own IP address to the firewall rules. This is important for when you want to log into your database later on. If you don’t, you’ll be refused access, even though you’re the admin. Don’t forget to hit the Save button!
Install AdventureWorks on the SQL Server Instance
Once the server has been created, we can start adding databases. In the overview pane, click on Create database.
Most information, such as the resource group and location, will already be filled in. You need to specify a name, of course.
For the other settings, don’t use an elastic pool, choose a development workload, and set the redundancy to locally-redundant backup storage (LRS, the cheapest option).
An Azure SQL database size can be configured in multiple ways. You can find a small overview of the available options in the tip Snowflake vs Azure SQL Database under the Scaling section. Click on Configure database to change the default sizing. For this tip, we will use the DTU-based purchasing model because it’s easy to understand and configure.
There are three options available:
- Basic – only use this for small scale test scenarios. You might choose this option if you are going to use your sample database only for writing some easy SQL queries.
- Standard – the middle-tier. For a sample database, a size between S0 and S2 will be sufficient.
- Premium – for production workloads.
The following screenshot shows a database configuration with the S0 tier and a maximum size of 10GB, corresponding to 12.41 EUR per month.
You can find a mapping between the service tiers and the number of DTUs in the Azure pricing calculator.
More info about DTUs can be found in the documentation. Once you’ve configured the size of the database, you can hit Apply and go to the Networking tab. If you haven’t already, you can add your IP address to the server firewall rules:
The Security tab can be skipped. And in the Additional settings, we can finally configure the sample database. Change the slider for “Use existing data” to Sample. This will create the database by restoring a backup of the sample AdventureWorksLT database.
Go to Review + Create and hit Create at the bottom.
This will start the deployment of your new resource, which might take a few minutes.
If you want to choose another pricing tier later on, you can do so from the overview pane.
Query the Data in the AdventureWorks Sample Database
You can query the data using T-SQL statements using a tool like Azure Data Studio or SQL Server Management Studio (SSMS). In this tip, we will use SSMS. In SSMS, connect to a new database instance and enter the server name of the server we created in the first section of this tip.
Depending on how you configured your server, you can connect using Azure AD or SQL authentication. Before you hit Connect, go to Options first. There, change the master database to the name of your sample database.
This is especially important for users that are not an administrator. Otherwise, they are trying to connect to the master database, and the connection will be refused. Click Connect. Once logged in, you can view all the sample database tables.
Now you can start querying the data!
Next Steps
- If you’re interested in other sample databases, check out the tips AdventureWorks Database Installation Steps and Download and Install SQL Server 2016 Sample Databases WideWorldImporters and WideWorldImportersDW
- To start writing SQL statements, check out SQL Server SELECT Examples and SQL Queries Tutorial.
- More Azure SQL Database tips can be found in this overview.
