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.
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.
10 Comments. Leave new
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
I believe you’ve confused me with Brent.
Ha, do you mean Brent’s the smart, good looking one or he’s the one that come up with a script?
Brent’s a DBA.
Hi Mike. I’ve been meaning to blog about something like that for quite a while, and I’ll add it to my queue for this week. Keep your eyes peeled!
That’s fantastic! I’ll definitely keep an eye out. Thanks!
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!
Glad to hear that you’re curious and hungry for more!
When I started out, I looked at AdventureWorks and asked myself “If I owned a bicycle shop, what kind of reports would I want to see?” This led me to build out reports for sales performance, sales person performance, stock levels, and any number of other things that I’ve completely forgotten about since then.
T-SQL Fundamentals 2012 and T-SQL Fundamentals 2008 have a series of exercises and solutions in every chapter.
If you’re feeling more adventurous, you could check out Joe Celko’s SQL Puzzles and Answers.
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…..
I feel ya, Allen.
There are a lot of people struggling to get good SQL Server help. Or even any SQL Server help. They post questions on twitter using the #sqlhelp hash tag and they’re on http://stackoverflow.com and http://dba.stackexchange.com. Those are both great places to help solve real problems and gain real world experience.
Just be warned – a lot of people use AdventureWorks to create repeatable query problems.