Sample datasets are useful for many things, from quick proof of concepts to technical demos, however when you start searching for one to use on Azure it can be hard to know which to choose.
In this post I have reviewed previous sample databases along with a few other options.
For each database I created an Azure SQL database on the basic tier which should be more than enough to import the sample data.
Created many years ago (maybe 2005) the Adventure Works database is often the go-to sample. The Adventure Work LT database is built into Azure making it a great choice if you just need to get something up and running quickly. The sample data is a moderate size and a full schema is available.
The Adventure works sample is the simplest of the samples to setup and can be done directly inside the Azure portal. When you select to create a new Azure SQL database under the additional settings tab you can select to use a Sample as the data source. When selected this will populate the database with the AdventureWorksLT database ready to use.
In addition if you need a larger version of this database it is possible to download and restore one of the backups from the Microsoft docs. The provided files are in .bak format which are not supported by Azure, these must first be imported into a SQL server locally then deployed to Azure using SQL management studio.
Northwind Traders is the oldest of these samples and originally shipped with MS access and Excel 97, later being added to SQL Server 2000. Despite its age this database still works with Azure and can now be downloaded as a SQL script from github under the MIT licence.
You can easily run the SQL setup script directly from the Azure portal from the query editor. The only edit needed to make to the script in Azure is to remove the initial few lines down to
SET DATEFORMAT mdy As these create the database which has already been done.
The database itself is a similar complexity to Adventure Works however rather than being focused on people, businesses and contacts it focuses on the more traditional orders and products.
This database was originally shipped along with Northwind and it features a set of books and stores in a relational database. To set up this sample in Azure create a new Azure SQL DB and then run the script directly from inside the Azure portal query editor.
To run in Azure, take the SQL script from line 63 onwards as the database already exists. The last 10 lines just before
Use Master will also need to be removed for the script to execute successfully.
The pub database like previous two is a moderate size and contains all of the other items you expect to find in a standard SQL database. The theme of books and publishers can make a refreshing change from the standard themes.
Wide World Importers
Along with the release of SQL Server 2016 the Wide World Importers sample database was created. Being a modern database it is provided in
bacpac format which is fully compatible with Azure SQL. You will however need SQL server management studio to import it.
The full guide to running it in Azure can be found on the official documentation
Theme: A fictitious retail company with rewards system
Size: Rewards Database - 2 Tables 10 Customers, 11 Orders License: MIT
Database: External Link
Tailwind Traders is more than just a SQL database it’s a full modern application running on Azure. The team is actively working on it and contains Xamarin apps, a win forms application, a React JS front-end and various other components one of which is a small Azure SQL database.
If you’re looking for a sample SQL database you could always use the create tables and populate script of the rewards database that is part of this larger project. Much like the other databases it’s a case of creating an Azure SQL database and running the SQL script. The database itself is probably one of the smallest with only 2 tables and a handful of records. The main advantage of this sample is to see how it Azure SQL fits into the bigger application alongside CosmosDB and other technologies.
Theme: Online Q&A Site for Developers
Size: 10GB - 411GB
Schema: External Link
License: cc-by-sa 4.0 license
Stack Overflow as you probably already know is the worlds Q&A site for developers. What you might not know is that the database behind stack overflow is publicly available. As you can imagine the database itself is rather large at up to 411GB.
In comparison to the other sample databases this SQL database is complex and full of rich information. It can be ideal for larger samples and proof of concept where you need bigger volumes of data.
Brent Ozar has provided a small (1GB download, 10GB size), medium and large copy of the database along with full details on how to use the anonymised databases on his blog at brentozar.com
Bonus: Open Data Sets
In this post we covered a few sample databases, however there are other data sets that can be used. More companies are offering full open datasets which can be just as good for sample projects.
They may not slot into Azure as easily as some of the items mentioned above however there is a wide range of types of data. As a starting point you could look at the UKs open data sets at https://data.gov.uk/
In this post we have looked at many different sample databases from the original Northwind database from 1997 all the way through to the modern Azure based Tailwind Traders. We also looked at the stackoverflow database and pointed to open datasets which open up many other possibilities.
The great thing is there are many options and importing data into Azure SQL is a relatively straightforward process allowing you to get set up in a matter of minutes.
Title Photo by Tobias Fischer on Unsplash.