Background
We have all learned to love and hate the trusty Bike shop database. Almost every demo or presentation pertaining to SQL Server we do, we use the AdventureWorks sample database. Almost every code sample on books online references AdventureWorks for illustrations and practical explanations of a feature. When SQL Server 2005 was released Microsoft replaced the old Pubs & Northwind Sample databases with a more complete and more feature rich database called AdventureWorks. Ever since we have been using this new sample database for almost everything we want to test. Microsoft has now given us an even better sample database called WorldWideImporters, which utilises almost every SQL Server feature I can think of from Temporal Tables to In-Memory table.
Discussion
When SQL Server 2016 was still in the CTP phases I wanted to play with all the new features and see what I can use it for in the “real world”. I know SQL Server always has this awesome new features, but I do not always have the resources or opportunities to play with this in a “real world” scenario. A good example of this is In-Memory tables, I know of them and I have created a few In-Memory tables in SIT to demonstrate the speed enhancements but it always gets turned down due to a concern of excessive memory usage.
The new WorldWideImporters database offers us the ability to really test and play with all the new features as well as use it for simulations. The new sample database has and is still being built to be able to run new workloads. In other words, you can build a DataMart from WorlWideImporters and then simulate new workloads in the sample database which you can run daily. This means our data in our sample database is no longer static and can be changed whenever we need new data. This is exciting stuff because now I can brush up on my ASP skills and built web front ends and see how data changes would affect the performance of my application.
I have added below an image of the database diagram for the new WorldWideImportes, as this is an ongoing project I am sure this will change in the near future. As you can see it is a nice complex database that we can use for development and testing.
Considerations
I have found that running the initial data load script on my laptop as it stands in the git repository that I run out of memory on my laptop and the script fails, so I had to break it up into 2 parts.
Prerequisites
Create a git account and download GitHub for desktop
SQL Server 2016 or Azure SQL Database v12+
Objective
In this article, we will first fork and clone the SQL-server-samples repository and once this has been setup we will create and setup the WorldWideImporters sample database.
Solution
Go to GitHub and create a new user account if you do not have one yet.
Once your user account is ready go and download GitHub for desktop.
Once the download is complete you can then just install the application by clicking next the whole time. You will be prompt to enter your user account details you have created earlier.
Let’s start off with forking and cloning the SQL-server-samples repository. Go to SQL Server Samples Repository and click on “fork” on the top right-hand corner.
This will fork the repository and create a copy on your user profile, you can now copy the new URL in your browser. It should look something like https://github.com/Username/sql-server-samples .
Now open the Git Shell that was installed with Github Desktop and enter the following command
git clone https://github.com/Username/sql-server-samples
(use your URL as this one is only for demonstration purposes). If everything is working according to plan you should see the following in the shell while it is busy cloning it to your local machine.
Once this is completed we now have all the code we need to continue with creating and setting up the WorldWideImporters sample database.
Now it is time to open our trusty SSMS and start creating the database. All of the scripts that we are going to use for this exercise is located now in your development directory that you specified in GitHub Desktop, by default it is in your “My Documents” ~\sql-server-samples\samples\databases\wide-world-importers\wwi-database-scripts . There are 9 scripts that we will use to create the Sample database.
The first script that we need to run is “1-wwi-metadata-population.sql” this prepares the metadata for the sample database and just assist with the final creations and recreation of the database. Microsoft has provided us with a script to do this straight of the bat, but we will have to change a few thing in the script for it to run and thus we will not be going this route in this article.
We can just run this script without changing anything, this will create the WWI_Preparation database that contains all of the metadata for WorldWideImporters everything from schemas to indexes.
Once the preparation database is completed we can now run “2-wwi-construct-database-from-metadata-tables” script. This will generate an sql script that you can use to create or re-create the WorldWideImporters database.
We can just copy the output into a new window and execute, this will then create the Sample database for use. You can re-run this anytime you want to start fresh.
Now that we have our sample database up and running, we need to configure some of the required objects for the workloads. Luckily Microsoft has provided us with a script that does this for us “4-wwi-configure-required-database-objects.sql”. We can just open this script and press execute.
We now have the schema ready and can now start with loading some data into the sample database, we can do this by running the following script “5-wwi-load-seed-data.sql”. I have found that on my laptop I had to break the script up into smaller chunks for it to execute successfully as my laptop did not enjoy the large transactions.
Once we have our data ready we can run a data simulation from the following script “6-wwi-data-simulation.sql”
Please note this does take some time, so now is a good time to go and refill your coffee cup.
We do have a script that will enable the full features “7-wwi-enable-full-features.sql”, but take note that you can only run this if you are using SQL Server 2016 Enterprise or Developer Edition.
If you are happy, you can now create a backup of our new sample database, by either doing this manual or just running one of the scripts Microsoft has provided us” 8-wwi-backup.sql”.
We are now done, and can start to convert our demos to the new Sample database
Final Thoughts
I am truly impressed with the flexibility or the new sample database Microsoft has released. I found that you can enable Enterprise functionality later when you want to test more of the SQL Server 2016 functionality. I will write more articles on how to run the sample workloads and really put SQL Server and its new functions to the test. I know from the repository you can create and load an Analytics database (DataMart), but I think it would be more fun to try and create on for yourself.
I also believe that Microsoft will start changing the book online code samples to reference the new sample database for better demonstration purposes.
Jean-Pierre speaks at the Johannesburg SQL User Group, trying to give back to the SQL community as much as possible. He loves to tinker with SQL Server and see how he can approach a problem with a different angle.
View all posts by Jean-Pierre Voogt
- Running with running totals in SQL Server - July 29, 2016
- The new SQL Server 2016 sample database - July 22, 2016
- Storing Twitter feeds with Microsoft Flow in Azure SQL Database - June 29, 2016