Tuesday, August 26, 2008

SQL Server Sample Install Epiphany

(Warning - this post has turned into a long drawn-out rant. If you want to skip to just the useful stuff, scroll down to the third and final bulleted list way down there at the bottom. I won't mind, I promise.)

One "new feature" of SQL Server 2008 that has always seemed of dubious value (at best) to me is the way that the product samples have been removed from the actual SQL Server installer. If my memory serves me correctly[1] the history of SQL Server samples (namely the sample databases) has gone something like this:

  • SQL Server 2000 and earlier: Sample databases automatically installed with the RDBMS. Users must manually delete them post-install if they're not wanted.
  • SQL Server 2005: Sample databases part of RDBMS install, but are not installed by default - they must be manually selected by the user.
  • SQL Server 2008: Sample databases not included with the RDBMS installer at all. Users must wade through dozens of poorly-documented downloads on the CodePlex web site, hope they get the installers that include the databases that they need, install them, then struggle to find out what files the installers put where.

Ok, so perhaps that last bullet isn't particularly fair[2] but it does sum up my personal experiences with getting samples working with SQL Server 2008. If you go to the Releases page for the SQL Server 2008 samples project on CodePlex you'll see 28 (twenty eight!!) different MSI installers that you can download. And when you install any one of them, it's pretty much a mystery what files are installed and where you can find them. In my book this is quite a big step backward.

To be completely open, I realize and admit that I'm not the typical SQL Server user. I do a lot of training, presenting and writing on SQL Server topics, and the samples are a big part of these activities - because without them I'd have to build samples of my own. And of course once the SQL Server samples are installed, they're great - it's hard to find anything bad to say about the content itself.

Anyway, today I've been spending some time preparing for a few presentations that I have on my schedule in the next month or so, and have needed to go back out to the CodePlex web site to download (again) the SQL Server 2008 samples. When I was faced (again) with the 28 (twenty eight!) different installers, I groaned and hung my head. "Why?" I moaned, "Why can't they just give us the expletive expletive SQL scripts and source code instead of these accursed MSI files?!?!?"


Oh yeah.

Oh yeah, one of the installers is named "SQL2008.AdventureWorks_All_DB_Scripts.x86.msi" - that sounds useful. How could I have missed this?

In fact, I've found that to get to where I need to be, there are really only two things I need from CodePlex:

  • That SQL2008.AdventureWorks_All_DB_Scripts.x86.msi file, which you can get here.
  • The "All Microsoft Product Samples in a Box" download, which includes "all Microsoft SQL Server product samples (except for the sample databases, due to size constraints) and does NOT include any community projects" and which you can get here.

The nice thing about this second download is that you can choose to download it as a zip file, which means you can extract it to wherever you want to put it. And that, for me, is key.

The DB installer MSI is another matter entirely. When you install it, there is no indication of where it's putting the DB scripts, nor does it give you the option to choose a destination directory. I looked in the C:\Program Files\Microsoft SQL Server\100\Samples folder - that makes sense, right? Wrong. There's nothing there but a license, a readme file which references the C:\Program Files\Microsoft SQL Server\100\Samples folder where the samples aren't located, and a shortcut to the CodePlex project.[3] Ugh.

Instead, the samples are installed in the C:\Program Files\Microsoft SQL Server\100\Tools\Samples folder (note the inclusion of Tools in the folder path) where, if you're like me, you'll never think to look.

Ok, this post has turned into a rant, which was really not my intent. Please let me summarize:

To get the complete samples for SQL Server 2008, perform the following steps:

Hopefully this will help someone out there avoid the frustration I've felt from time to time when working with the "decoupled" samples...

[1] If you say this in the voice of Chairman Kaga it sounds like a cool pop culture reference, instead of just an admission that I have trouble remembering things that happened before I started typing this blog post - try it out and you'll see!

[2] Especially seeing the huge improvements that the samples owner David Reed has made over the last few months leading up to SQL 2008 RTM.

[3] No, I have not yet filed a Connect item on this readme file. Typing up this rambling blog post took so long that I didn't have time to actually file a useful bug as well...


Darren Gosbell said...

Amen brother.

All I wanted was a copy of the Adventure Works SSAS project and I got the following error http://geekswithblogs.net/darrengosbell/archive/2008/08/20/error-2738-while-trying-to-install-adventure-works-bi-sample.aspx because of some VBScript that the msi was trying to use! A zip file is all that was needed.

Matthew Roche said...

Oh thank goodness. Every time I post something like this I always have that "am I just exposing my stupidity (again)?" moment as I press the Publish button. It's nicce to know it's not just me...

Matthew Roche said...

I feel compelled to add just one more bit about your comment, Darren. Every time I look at it, my mind tries to re-shape the words into "All I wanted was a Pepsi. Just one Pepsi. And SQL Server 2008 wouldn't give it to me!"

And so far my mind is winning...