Download and Install SQL Server 2016 Sample Databases WideWorldImporters and WideWorldImportersDW

By:   |   Comments (8)   |   Related: > SQL Server 2016


Problem

We have been using AdventureWorks and AdventureWorksDW sample SQL Server databases since SQL Server 2005 for both OLTP and OLAP environments. I am looking for the sample database where I can learn and play with the new features. Now with SQL Server 2016 Microsoft has come up with a new sample database, in this tip we will be exploring and see how useful these sample databases are for us.

Solution

Microsoft has taken a leap forward with SQL Server 2016 with its mission critical capabilities and features. There are new sample databases introduced, WideWorldImporters and WideWorldImportersDW for both OLTP and OLAP environments. This is applicable for both on-premises SQL Server instances and SQL Azure.

In SQL Server 2016, Microsoft has provided a version of the sample database according to the SQL Server edition. Let's get a basic understanding of these databases.  You can also download these databases by clicking on the database name below.

1. WideWorldImporters-Full.bak - This sample database is for SQL Server enterprise, developer and evaluation edition and can be useful for OLTP and real time analytics operations.

2. WideWorldImportersDW-Full.bak - This sample database is for Online Analytical Processing (OLAP) and is useful for SQL Server enterprise, developer and evaluation edition.

3. WideWorldImporters-Standard.bak - This sample database is for SQL Server 2016 standard edition and useful for OLTP environments.

4. WideWorldImportersDW-Standard - This sample database is for OLAP needs for SQL Server 2016 standard edition.

5. WideWorldImporters-Standrad.bacpac - This sample database is a BACPAC file format and can be used as a Azure OLTP sample database.

6. 2. WideWorldImportersDW-Standard.bacpac - This sample database is a BACPAC file format and can be used as an Azure OLAP sample database.

I am using SQL Server 2016 evaluation edition and have restored the WideWorldImporters-Full.bak and WideWorldImportersDW-Full.bak database. As a next step, let's review some sample code to complete the restore process.

Restore WideWorldImporters Sample Database in SQL Server 2016

USE [master]
GO
RESTORE DATABASE [WideWorldImporters] FROM  DISK = N'R:\SQLbackups\WideWorldImporters-Full.bak' 
WITH  FILE = 1,  
MOVE N'WWI_Primary' TO N'F:\SQL_Data\WideWorldImporters.mdf',  
MOVE N'WWI_UserData' TO N'F:\SQL_Data\WideWorldImporters_UserData.ndf', 
MOVE N'WWI_Log'  TO N'L:\SQL_Logs\SQL2016\WideWorldImporters.ldf',  
MOVE N'WWI_InMemory_Data_1' TO N'F:\SQL_Data\WideWorldImporters_InMemory_Data_1', 
NOUNLOAD,  
STATS = 5
GO

Restore WideWorldImportersDW Sample Database in SQL Server 2016

USE [master]
GO
RESTORE DATABASE [WideWorldImportersDW] FROM  DISK = N'R:\SQLbackups\WideWorldImportersDW-Full.bak' 
WITH  FILE = 1,  
MOVE N'WWI_Primary' TO N'F:\SQL_Data\WideWorldImportersDW.mdf',  
MOVE N'WWI_UserData' TO N'F:\SQL_Data\WideWorldImportersDW_UserData.ndf',  
MOVE N'WWI_Log' TO N'L:\SQL_Logs\SQL2016\WideWorldImportersDW.ldf', 
MOVE N'WWIDW_InMemory_Data_1' TO N'F:\SQL_Data\WideWorldImportersDW_InMemory_Data_1',  
NOUNLOAD,  
REPLACE,  
STATS = 5
GO

Features of the WideWorldImporters Sample Database in SQL Server 2016

As per MSDN, the WorldWideImporters database can be useful for testing new functionality available with SQL Server 2016 including:

  • Archive tables can be stretched to Azure for long-term retention, reducing storage cost and improving manageability.
  • Query Store is used to keep track of query performance.
  • Temporal tables are used to conveniently keep track of the history of reference data, as well as some of the main entities.
  • JSON is used to enable AJAX calls to some of the key tables, and also to extend the relational schema to record such things as application settings and user preferences.
  • Advanced security features like Always Encrypted, Row-Level Security and Dynamic Data Masking are used to secure data.
  • In-Memory OLTP is used to optimize the performance of table-valued parameters (TVPs) and to optimize ingestion of sensor data.
  • Clustered columnstore indexes are used to reduce the storage footprint of large tables with insert-only workload.
  • Partitioning is used to improve the manageability of large tables.

Features of the WideWorldImportersDW Sample Database in SQL Server 2016

WideWorldImportersDW is the main database for data warehousing and analytics (OLAP – Online Analytical Processing). The data in this database is derived from the transactional database WideWorldImporters, but it uses a schema that is specifically optimized for analytics.  Below are the features of the database:

  • Clustered columnstore indexes are used to reduce the storage footprint and improve query performance for the fact tables.
  • PolyBase is used to correlate data in the local database with a public data set in Azure Blog storage.
  • In-Memory OLTP is used to improve the performance of the ETL process.
  • Partitioning is used to improve manageability of the fact tables, which can grow very large in a data warehouse.

WideWorldImporters Database Properties

Below are some screen shots from the WideWorldImporters database:

WideWorldImporters General Database Properties

Query store enabled by default

WideWorldImporters Query Store Properties

Database with an in memory data file

WideWorldImporters In Memory Data File

Database tables overview- system-versioned temporal tables

WideWorldImporters System Versioned Temporal Tables

WideWorldImportersDW SQL Server Database Properties

WideWorldImportersDW General Database Properties

Query store enabled by default

WideWorldImportersDW Query Store Database Properties

Database with an in memory data file

WideWorldImportersDW In Memory OLTP File Properties

Data Generation in the WideworldImporters SQL Server Database

One of the good features of the WideWorldImporters and WideWorldImportersDW databases is that data can be generated to the latest date. Currently Microsoft has provided data from January 2013 to May 2013, but if we want to generate data to the current date it is possible with the below script:

EXECUTE DataLoadSimulation.PopulateDataToCurrentDate
        @AverageNumberOfCustomerOrdersPerDay = 60,
        @SaturdayPercentageOfNormalWorkDay = 50,
        @SundayPercentageOfNormalWorkDay = 0,
        @IsSilentMode = 1,
        @AreDatesPrinted = 1;

We can set the parameters based on the data we require and the data will be generated accordingly. I have modified the parameter to generate more records in the example below:

EXECUTE DataLoadSimulation.PopulateDataToCurrentDate
        @AverageNumberOfCustomerOrdersPerDay = 100,
        @SaturdayPercentageOfNormalWorkDay = 70,
        @SundayPercentageOfNormalWorkDay =20 ,
        @IsSilentMode = 1,
        @AreDatesPrinted = 1;
WideWorldImporters

Reseed the OLAP Database

Reseed the OLAP database by executing the query below:

EXECUTE [Application].Configuration_ReseedETL

Sample SQL Server Integration Services Package

Microsoft has also provided an SSIS Package (Daily.ETL.ISPAC) for ETL needs from the OLTP to the OLAP database.  We will be looking into package more in a future tip. Keep in mind this ETL package is common for both standard and enterprise editions.

SQL Server Sample and Examples

Microsoft has also provided a rich set of samples to explore the SQL Server 2016 features for Always Encrypted, Row Level Security, Operational Analytics, Polybase and In-Memory OLTP.  The samples can be download from here.

Workload

SQL Server 2016 Sample Workloads

SQL Server 2016 also includes sample workloads for the WideWorldImporters sample databases. These are Windows Form applications that will only run on Windows, this can be download from here.

This includes two application workloads:

1. Order-Insert: This application is used to provide an intensive order entry workload for the WideWorldImporters database.

Workload

2.Vehicle Location insert: This application is used to compare the insertion of rows into OnDisk and In Memory tables in the WideWorldImporters database.

Workload

I will be exploring more on these application workloads in future tips.

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 Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

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

View all my tips


Comments For This Article




Saturday, March 23, 2019 - 5:01:17 PM - Mike Spencer Back To Top (79387)

OK, the DB has been created from the BAK restore file.

I found this out the hard way (of course).

I had installed SQL Server Express before I installed SQL Server Developer Edition.

When trying to do the restore I was pointing to a SQLEXPRESS connection instance and not a SQL Server connection.

I have corrected this and it restored without issues.

Friday, March 22, 2019 - 1:02:17 PM - Mike Spencer Back To Top (79375)

The folder(s) has Full Control on all Group and user names

Friday, March 22, 2019 - 10:05:58 AM - Rajendra gupta Back To Top (79371)

 Please check the permission for the folder having database files 

Thursday, March 21, 2019 - 11:05:55 PM - Mike Spencer Back To Top (79365)

I installed the cumulative update.

Now all I get is this:

Msg 5120, Level 16, State 108, Line 14

Unable to open the physical file "E:\MSSQL\DATA\WideWorldImportersDW_InMemory_Data_1". Operating system error 0: "(null)".

Msg 3013, Level 16, State 1, Line 14

RESTORE DATABASE is terminating abnormally.

Thursday, March 21, 2019 - 6:27:59 PM - Greg Robidoux Back To Top (79364)

Hi Mike,

did you see this MS article: https://support.microsoft.com/en-us/help/4052987

-Greg

Thursday, March 21, 2019 - 6:13:27 PM - Mike Spencer Back To Top (79363)

I was able to adjust the restore query to my PC as follows:

USE [master]
RESTORE DATABASE [WideWorldImportersDW] 
FROM  DISK = N'E:\C# Pluralsight tutorials\sql-server-reporting-playbook\WideWorldImportersDW-Full\WideWorldImportersDW-Full.BAK' WITH  FILE = 1,  
MOVE N'WWI_Primary' TO N'E:\MSSQL\DATA\WideWorldImportersDW.mdf',  
MOVE N'WWI_UserData' TO N'E:\MSSQL\DATA\WideWorldImportersDW_UserData.ndf',  
MOVE N'WWI_Log' TO N'E:\MSSQL\SQL_Logs\SQL2016\WideWorldImportersDW.ldf', 
MOVE N'WWIDW_InMemory_Data_1' TO N'E:\MSSQL\DATA\WideWorldImportersDW_InMemory_Data_1',  
NOUNLOAD,  REPLACE,  STATS = 10
GO

But I get this in the Messages window:

10 percent processed.

Processed 1192 pages for database 'WideWorldImportersDW', file 'WWI_Primary' on file 1.

Processed 28184 pages for database 'WideWorldImportersDW', file 'WWI_UserData' on file 1.

Processed 26 pages for database 'WideWorldImportersDW', file 'WWI_Log' on file 1.

Processed 24 pages for database 'WideWorldImportersDW', file 'WWIDW_InMemory_Data_1' on file 1.

Msg 3013, Level 16, State 1, Line 14

RESTORE DATABASE is terminating abnormally.

When I look at the DB in SSMS or VS SQL Server Object Explorer the DB isn't available to do a query.

The files appear to be in the folders I set up.

What am I doing wrong?

Friday, July 7, 2017 - 3:31:56 PM - Derek Williams Back To Top (59023)

 

 Very helpful work. Well written article.

Thursday, August 4, 2016 - 1:59:05 PM - Timothy A Wiseman Back To Top (43055)

Excellent article.  The sample databases have been useful for testing and providing examples for a long time and it is good to see that they have been updated for the latest version of SQL.














get free sql tips
agree to terms