Blog

The best way to improve your skills as a database professional is to practice. If you’re like me, you might even take that practice outside of the workplace and do a bit of homework on the side. You can’t take a copy of your production database home with you, so what can you do? Thankfully, the fine people Microsoft have put together a set of databases that you can download and play with.

Brightly colored sample databases for everyone!

Brightly colored sample databases for everyone!

Introducing the AdventureWorks OLTP Sample Database

AdventureWorks is a transactional database for a fictional bicycle and outdoor supply company. It’s not very exciting data and it’s not terribly big data, but it gives developers a highly normalized schema to work with for testing different querying techniques. How Microsoft managed to cram all of the relevant SQL Server features into a 215MB database is beyond me. All that aside, AdventureWorks is the first place that database professionals should go to get started practicing their skills.

There are a number of downloads available for AdventureWorks, but only a few that matter to the OLTP crowd. To get started, database professionals should download one of the following:

  • AdventureWorks 2012 Data File – this is just an MDF file. You’ll need to drop it into a folder and tell SSMS to create a log file.
  • AdventureWorks 2012 data and log file – there’s an MDF and an LDF zipped up in a file.
  • AdventureWorks 2012 OLTP Script – these scripts will create AdventureWorks from scratch.

For a local database, go with one of the first two options – just drop the files in their appropriate folders, attach the database, and then you’re off to the races. If you want something for Azure, use the script file to create the database and deploy data.

Protip: There’s usually a CS option for each of these. The CS lets you know it’s a case sensitive option. If you want to deal with international databases, pick the CS version.

A full list of AdventureWorks OLTP options is available at http://msftdbprodsamples.codeplex.com/. There are some light weight data warehouse options available, but they are still relatively small. If you’re just getting started, those are a good option (especially since there’s an SSAS tutorial available).

Introducing the Contoso Data Warehouse

Let’s say you want to do something a bit more adventurous and you want to branch out into the bigger BI world that Microsoft has to offer. The AdventureWorks data sets are a great starting place, but the data set size doesn’t pose many challenges.

The Contoso Retail DW data set is several times the size of AdventureWorks and comes as a pre-built star schema. While it’s not the biggest database (my copy is around 1.6GB), ContosoRetailDW provides a large enough data set where aspiring database professionals can really start to push the limits of a local SQL Server.

ContosoRetailDW is also a good place to try out Enterprise Edition features like table partitioning and ColumnStore indexes. The data set is large enough that it’s possible to make use of these features but it’s still small enough that you won’t need enterprise grade hardware to accomplish these things in a reasonable amount of time.

What Are You Waiting For?

Database professionals, this is your chance to start exploring the features and functionality that SQL Server has to offer. The sample databases from Microsoft give you plenty of opportunities to work with different techniques for both OLTP and data warehouse and even familiarize yourself with Azure without having to come up with your own data.

↑ Back to top
  1. Hi Jeremiah,

    So I’m a VMware and NetApp dude. In an effort to test out NetApp’s SnapManager for SQL on 2012 AlwaysOn Availability Groups, I used Brent’s SQL AAG guide on this very blog to set up a 3-node AAG in the lab (very cool, so thanks!) using the AdventureWorks DB. This is a great start, but in order to see the Snapshot deltas, I’d like to make changes to the DB, somehow.

    I don’t know any DBAs I can bribe to write me a script or application to do this, so I thought I’d reach out to a smart, good looking DBA such as yourself and ask if there’s some sort of script or application that makes automatic changes to the AdventureWorks DB over and over and over again so I can see how regular backups and Snapshots will work with a changing DB.

    All the best,

    Mike

    http://VirtuallyMikeBrown.com
    https://twitter.com/VirtuallyMikeB
    http://LinkedIn.com/in/michaelbbrown

  2. Hi Jeremiah,

    I’m literally getting started teaching myself SQL via the free SQL Server 2012 Express Edition plus Patrick LeBlanc’s Step by Step book. He uses the AdventureWorks database for demonstrations, but I’m finding that I need more “homework problems” to develop a deeper understanding.

    Instead of –type the query and execute– to merely view the result, starters like myself need word problems like –Using tables ‘x’ and ‘y’, write a query that will result in ‘z’.– with accompanying solution set.

    Do you know where SQL learners can access problems for the AdventureWorks database? I’ve searched without success. Something like a –Schaum’s Outline for SQL Server 2012 w/ AdventureWorks Database– would be awesome!

  3. My problem is that while I would “love” to practice outside the work place, I struggle with arbitrarily defined or thought up examples.

    I prefer to work on something real and with roots into actual problems meaning I mostly only get to play around with my skills and learn more about database (I’m actually a developer…. accidental DBA I think it’s called) which causes quite a delay between learning and solving something actually new versus just helping yet another co-worker optimizing an illwritten query or trying to explain why that unique key is a bad primary key when they then also add a unique product identifier and so on…..

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php