How to Install the AdventureWorks Sample Database in Azure SQL Database

By:   |   Updated: 2023-02-24   |   Comments   |   Related: > Azure SQL Database


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?


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".

create a new resource in Azure

In the Marketplace, search for SQL Server. In the results, click on SQL Server (logical server).

search for sql 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.

create new sql server instance

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.

configure sql server

For the authentication, you can choose between Azure Active Directory (AAD) authentication and/or SQL authentication.

configure 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.

allow azure services and resources access to this 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.

review screen

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.

deployment is complete

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.

overview screen of the sql server instance

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!

Graphical user interface, text, application, email

Description automatically generated

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.

create a new database

Most information, such as the resource group and location, will already be filled in. You need to specify a name, of course.

configure new database

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).

configuration continued

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.

Service and computer tier configuration

There are three options available:

  1. 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.
  2. Standard – the middle-tier. For a sample database, a size between S0 and S2 will be sufficient.
  3. 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.

database using S0 tier

You can find a mapping between the service tiers and the number of DTUs in the Azure pricing calculator.

azure pricing calc

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:

networking tab

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.

choose to use the sample database

Go to Review + Create and hit Create at the bottom.

review + create

This will start the deployment of your new resource, which might take a few minutes.

overview page database

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.

connect to server

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.

change default 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.

sample database in object explorer

Now you can start querying the data!

query results
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-02-24

Comments For This Article

get free sql tips
agree to terms