If you remember good ol’ NorthWind, you’ll know that having a sample SQL Server database can be great for sampling new technologies and performing RDBMS experiments and tests. As much as detail-oriented presenters and trainers like to use NorthWind, it is an old sample from SQL Server 2000 that doesn’t have many of the new features in SQL Server 2008.
I set out to get the sample databases, and I was amazed at the complexity of it. Not that it is in general a difficult task, but it could probably be made much more simple by Microsoft. Since I figured out which hoops you need to jump through, I thought I would summarize it for you here:
1) Read Up
If you want to have a successful installation of the sample databases, take 20-30 minutes to become familiar with the SQL Server Database Product Samples. Contrary to some of the complaints people have posted in the comments, there is lots of good documentation that helps you understand the contents and process.
Two important points you might miss are that you need to have full-text search installed and FILESTREAM enabled to ensure a problem free install of the samples. This can be done after the fact if you did not specify these settings during the initial installation.
It should also be noted that attempting to change the FILESTREAM setting through the SQL Server Configuration Manager did not seem to work even after a restart of the engine. Instead I had to run this SQL
|exec sp_configure ‘filestream_access_level’, ‘2’
2) Download & Run the Installer
Once you’ve familiarized yourself with the process and read the pre-requisites, download the .msi installer file:
For my Vista machine running SQL Server 2008, I picked this file: SQL2008.AdventureWorks_OLTP_DB_v2008.x86.msi
One interesting point is that there appears to be an option to have the installer restore the database for you. Unfortunately, when I attempted to use this option to restore it in my local SQLExpress instance, it bombed out on me requiring me to run the installer again without the option selected. The option is not selected by default which leads me to believe that Microsoft is aware this is a potential problem area.
3) Restore the DB Manually
This is the part where people with little or no DBA knowledge probably begin to curse. If you are running the same platform as me and accept all of the defaults, you would think this should be as simple as running this script in a new query window (based on step 6 in the instructions):
|RESTORE DATABASE AdventureWorks2008
FROM DISK ‘C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks2008.bak’
Unfortunately, this gave me a bunch of errors based on the way the RESTORE was attempting to use the default settings.
You can get around this by using the restore script (RestoreAdventureWorks2008.sql) in the same folder as the .bak file. If you look at how the path is being used, you’ll notice that you have to substitute the path as follows in the top section of the script:
SET @source_path = ‘C:\Program Files\Microsoft SQL Server\100\’
So after a bit of plugging away, I was able to the the sample successfully restored. Yea!
I truly empathize with Microsoft as streamlining this installation can be a daunting task. There are an endless number of settings that have to be accounted for, security, versions, etc. However, this installation process is much too difficult for the average developer in my view.
Now that I have some data to play around with, I expect to post some experiences about using the new 2008 features such as intellisense and the other goodies I expect to find in the newest version of this tool. All things considered, SQL Server is still my database platform of preference and I appreciate everything Microsoft does to make this free version available to the public.