Pulling your hair out trying to get the SQL Server 2008 Sample AdventureWorks databases installed? If so, you've come to the right place; this article makes it easy(ier).
After the nine-hour download of the real SQL Server 2008 RTM from MSDN completed, I began the task of getting it installed on my new development system. It's an Intel Quad Core Duo with (only) 4GB of RAM, so you'll get an idea about how fast it is in comparison to yours when I start talking about timings.
I was initially surprised to find that the setup paradigm you've grown accustomed to in SQL Server 2000 and 2005 has been replaced (again). It's too early to pass judgment, but I can say that it's different but intuitive. However, before I got to the first SQL Server setup dialog, I found that the installation script wanted to install version 3.5 SP1 of the .NET Framework. This might already be in place if you have installed Visual Studio 2008 SP1. As you'll see later, it's a great idea to update Visual Studio before you install SQL Server 2008—it won't install if Visual Studio is not at SP1.
I started the .NET Framework 3.5 SP1 install and noticed that it said it was "downloading." I found this a bit distressing because I did not want to spend the next 30 minutes waiting for another download. Thankfully, the folks on campus tell me that that dialog is "incorrect;" it's not really downloading. Well, after 20 minutes of not downloading, the upgrade to the Framework was installed but then setup wanted to install version 4.5 of the Windows Installer (KB942288). Five minutes later, it was installed and I rebooted. This "benign" update pooched my video configuration and after several minutes of futzing around, I got it reset. Not an auspicious beginning.
|Once SQL Server 2008 was installed (it took about 45 minutes to install), it said I needed to reboot but did not use the expected reboot dialog or reminder that Vista shows when a reboot is required.|
I'm not going to focus this article on the new setup routines because they are mostly intuitive. Just note that you'll need to provide correct accounts for each of the services you install. Note that although SQL Server Reporting Services is included in the RTM drop, the Report Builder is not. You'll also find that none of the Express SKUs are there either. They're due out later this year.
Configuring the Server
I first looked for the tried and true SQL Server Surface Area Configuration Tool but discovered that it's been deprecated. I expect that many of us will miss it. Again, the folks on campus suggested that I learn how the new Policy Management paradigm works. Ah, I don't expect to be comfortable with that entirely new approach before the Federal budget is balanced. Perhaps we can convince Microsoft to provide the source code for the old tool so we can update it to manage SQL Server 2008?
I suggest you dig around in SQL Server Management Studio because there is a wealth of new features and replacements for the tools that I use. For example, there is a new Table explorer that either returns a RO rowset for the first 1000 rows or a RW dialog for the first 500 rows.
Installing Sample Databases
I often use the sample databases in my examples, articles, and seminars, so I'll walk through the process of getting these installed on the newly installed SQL Server 2008 instance (SS2K8) in detail. Unlike previous versions, this drop has no samples installed, so none of the demos we publish here will work. Note that both SQL Server 2005 and 2008 samples are now posted on the new CodePlex site.
- Start from the CodePlex site where Microsoft has exposed all of the sample databases:
|Of course, if you're working with 32-bit or IA64 systems, you'll need to run the appropriate MSI. Be sure to save the .MSI locally because when things go wrong, you won't want to repeat the 5-minute download—unless you're being paid by the hour.|
This page is a bit daunting at first—lots of options usually lead to lots of confusion as developers wonder which option is "best." In my case (where I'm targeting a 64-bit Vista development system/server), I wanted the 64-bit MSI version (SQL2008.AdventureWorks All DB Scripts.X64.msi). Note that you'll see a similar .ZIP download—don't be tempted unless you just want the data in .CSV form.
Figure 1: The .MSI script installer dialog.
Once the .MSI runs, you'll discover that you still have no sample databases—that's because you aren't nearly done. All the MSI does is copy the files (.SQL and other support files and the .CSV data files) to your local hard drive. These files are installed into:
C:\Program Files\Microsoft SQL Server\100\Tools\Samples\
- You'll need to navigate to this directory to complete the installation. But before you install any of the "2008" examples, you'll have to activate the FileStream option on the target server instance. This is a three-step process as shown in Figures 2 and 3. The final step (not shown) is to execute RECONFIGURE.
Click here for a larger image.
Figure 2: Using sp_configure to enable the FileStream option for non-OLTP examples.
- Next, you'll need to use SQL Server Configuration Manager to enable the FileStream option for the selected instance—it's disabled by default. To enable it, navigate to the specific SQL Server 2008 instance, right click, and choose "Properties." Note the new tab to control the FILESTREAM option. No, I doubt if you'll need to enable as much as I did, but let your DBA make that call.
Figure 3: Enabling the FileStream option.
- At this point, you need to decide (if you haven't already) which of the six sample AdventureWorks databases you want to install. The command batch you're about to run simply uses XCOPY to copy one (and only one) of the sample AdventureWorks directories to another target path and runs an install script from that folder. This script subsequently creates the chosen sample AdventureWorks database and loads the data to a selected SQL Server 2008 instance. Note that you'll have to repeat the BuildAdventureWorks.cmd batch for each of the databases you want to install. In the end, you'll end up with the data in at least two places—where the MSI wrote it (in the ..\Tools\Samples\ directory) and in the "common" DATA directory targeted by the Command batch.
|Note that SQL Server 2008 has changed the way that the instances are referenced in the Program Files tree. This makes sense because it now permits admins to clearly see the purpose of the directory and the version of SQL Server. In my case, where the instance name is SS2K8, the target path for the awdb files should be: C:\Program Files\Microsoft SQL Server\MSSQL10.SS2K8\MSSQL\DATA.|
- To keep this simple, I didn't follow the instructions given in the Command batch. If you do, you'll have to manually copy the files again because the install script it executes has the SQL Server 2008 instance common directory hard-coded.
I ran the BuildAdventureWorks.cmd batch from a Command prompt as follows:
C:\Program Files\Microsoft SQL Server\100\Tools\Samples>
buildadventureworks.cmd "C:\Program Files\
Microsoft SQL Server\MSSQL10.SS2K8" "betav1\ss2k8"
This installed the AdventureWorks2008 OLTP database.
- You need to pass three arguments:
- The path to the "common" data path for your newly installed SQL Server 2008 instance.
- The server\instance name to be used to open a connection.
- The name of the sample database to install from the batch file names shown in Figure 4.
In my case, I passed in the newly installed instance "common root path" and specified the instance name as shown above. No, I could not get the suggested ".\<instance name>" to work, but the explicit reference worked fine. Note that if you don't specify a sample database to install, the command batch installs the "AdventureWorks" (non-SQL Server 2008) OLTP example database.
- You might want to repeat the batch for each of the sample databases. Figure 4 shows the name you need to use to install each of the databases.
|Batch File Name
||File Path (files copied here)|
||General OLTP examples
||2008 OLTP examples
||Data warehouse examples
||2008 Data Warehouse...
||2008 Lightweight database
Figure 4: Mapping the sample databases to the file location.
For more instructions, go to http://www.codeplex.com/MSFTDBProdSamples/Wiki/ View.aspx?title=AW2008Details&referringTitle=Home and skip down to "AdventureWorks 2008" Installation.
|Incidentally, I'm told that some of the spatial data for the AdventureWorks database is a bit off. For some reason, some of the Texas stores are plotted out in the middle of the Pacific Ocean. Perhaps these are offshore oil rigs.|
If I were king, I would have had an interactive application that let developers choose which sample database(s) they wanted and just installed them. Gee, that might be a job of the setup tool—like it used to be. There was plenty of room left on the DVD.
I hope this helps get you started with SQL Server 2008. A lot has changed with this version. Although I'm disappointed that these revisions are coming so fast, perhaps there are those of you who are really looking forward to the extra functionality it provides. Of course, I don't expect the adoption rate for SQL Server 2008 to reach 50% for another few years—many folks are just now transitioning to SQL Server 2005.
About the Author
William (Bill) Vaughn is an industry-recognized author, mentor, and subject-matter expert on Visual Studio, SQL Server, Reporting Services, and data access interfaces. He's worked in the computer industry for over thirty-five years—working with mainframe, minicomputer, and personal computer systems as a developer, manager, architect, trainer, marketer, support specialist, writer, and publisher. In 2000, after 14 years at Microsoft, Bill stepped away to work on his books, mentoring, and independent training seminars. He's written seven editions of the Hitchhiker's Guide to Visual Basic and SQL Server and three editions of ADO.NET and ADO Examples and Best Practices for Visual Basic (and C#) Programmers. He and Peter Blackburn also wrote the critically acclaimed Hitchhiker's Guide to SQL Server 2000 Reporting Services.
Bill is a top-rated speaker and frequents conferences all over the world, including TechEd, Visual Studio/SQL Connections, DevTeach, and many others. He's also written a wealth of articles for magazines such as MSDN, SQL Server Magazine, Visual Basic Programmer's Journal, .NET Magazine, and many others as well as a regular editorial for Processor magazine. Bill spends considerable time answering questions on the public newsgroups and speaking at INETA user group meetings all over the country and at other speaking venues all over the world. He's available for consulting, mentoring, or custom training. See www.betav.com or www.betav.com/blog/billva for his current schedule and course catalog.