DBA Tips Archive for SQL Server

  


Install AdventureWorks Sample Databases - (SQL Server 2008 R2)

by Jeff Hunter, Sr. Database Administrator

Contents

Introduction

To facilitate the SQL Server 2008 R2 articles and scripts on the iDevelopment.info website, all of the examples are based on the AdventureWorks 2008R2 sample databases. These sample databases are part of the AdventureWorks Community Sample Databases. This article presents the steps required to download and install all sample database found in the AdventureWorks Community Sample Databases package for SQL Server 2008 R2.

Starting with SQL Server 2005, the sample databases are not installed by default due to security concerns. Users are now required to download and manually install the sample databases after successfully setting up SQL Server. All sample databases can be downloaded from Microsoft's SQL Server sample website at http://msftdbprodsamples.codeplex.com/.

The following sample databases are available as part of the AdventureWorks Community Sample Databases for SQL Server 2008 R2:

About CodePlex

SQL Server code samples and sample databases are now hosted on CodePlex. CodePlex is the project hosting site for Microsoft SQL Server Samples and Community Projects. The portal page for SQL Server on Codeplex catalogs samples included with Product Releases, samples from the community, and independent SQL Server Community projects.

Visit the SQL Server Samples and Community Projects page on CodePlex at: http://codeplex.com/SqlServerSamples.

CodePlex is hosted by Microsoft. Microsoft does not control, review, revise, endorse or distribute the third party projects on the CodePlex site. Microsoft hosts the CodePlex site solely as a web storage site as a service to the developer community.

For more information, read the CodePlex Terms of Use.

Database Prerequisites

Before you install the AdventureWorks 2008 and 2008 R2 family of sample databases:

In order to be able to successfully install the AdventureWorks2008 or AdventureWorks2008R2 (OLTP) database, the following additional requirements must be met:

In order to successfully deploy the Analysis Services databases after installation, SQL Server Standard, SQL Server Enterprise, or SQL Server Developer is required. Note that this deployment must be done manually using Business Intelligence Development Studio after installation is complete.

How to Install the Prerequisites on an Existing Installation of SQL Server

Install Full-Text Search

 

If Full-Text Search is already installed, but the SQL Full-text Filter Daemon Launcher service is not running, the installation will fail with a message that the Full-Text Search feature is missing. Therefore, if you already have Full-Text Search installed, follow the procedure to enable the SQL Full-text Filter Daemon Launcher Service.

  1. To install Full-Text Search, you must run SQL Server Setup. When you do, select the New SQL Server stand-alone installation or add features to an existing installation installation option.

  2. When you get to the Installation Type page, select Add features to an existing instance of SQL Server 2008, select the instance name, and then click Next.

  3. When you get to the Feature Selection page, under the Instance Features/Database Engine Services node, select the Full-Text Search check box, click Next, and then complete the rest of the pages in the wizard.

Enable the SQL Full-text Filter Daemon Launcher Service

  1. Click Start, point to All Programs | Microsoft SQL Server 2008 R2 | Configuration Tools | SQL Server Configuration Manager.

  2. In the left pane, click SQL Server Services.

  3. In the list of services, right-click SQL Full-text Filter Daemon Launcher for the instance that you are configuring, and then click Properties.

  4. Click the Service tab.

  5. Next to Start Mode, click Disabled, and then click Automatic in the list that appears.

  6. Click OK.

  7. Right-click SQL Full-text Filter Daemon Launcher for the instance that you are configuring, and then click Start.

Enable FILESTREAM

The following steps describe how to enable FILESTREAM on an instance of the SQL Server Database Engine by using SQL Server Configuration Manager.

  1. On the Start menu, point to All Programs | Microsoft SQL Server 2008 R2 | Configuration Tools | SQL Server Configuration Manager.

  2. In the list of services, right-click SQL Server Services, and then click Open.

  3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.

  4. Right-click the instance, and then click Properties.

  5. In the SQL Server Properties dialog box, click the FILESTREAM tab.

  6. Select the Enable FILESTREAM for Transact-SQL access check box.

  7. (Optional) If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

    Note that this step is not required to install the sample databases. If you find that you need to enable FILESTREAM for File I/O Streaming access, you can enable this option later.

  8. (Optional) If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.

    Note that this step is not required to install the sample databases. If you find that you need to enable FILESTREAM for File I/O Streaming access, you can enable this option later.

  9. Click Apply.

  10. In SQL Server Management Studio, click New Query to display the Query Editor.

  11. In Query Editor, enter the following Transact-SQL code to enable FILESTREAM for both "Transact-SQL access" and for "File I/O Streaming access":


    EXEC sp_configure filestream_access_level, 2 RECONFIGURE

    Enabling FILESTREAM for "File I/O Streaming access" is not required to install the sample databases. The only requirement is to enable FILESTREAM for "Transact-SQL access".

    To enable FILESTREAM for "Transact-SQL access" only, issue the following statements:


    EXEC sp_configure filestream_access_level, 1 RECONFIGURE

    For more information about the access levels, see "filestream access level" in SQL Server Books Online.

  12. Click Execute.

How to install the Prerequisites on a New Installation of SQL Server

If you want to install the prerequisites at the same time that you install a new instance of SQL Server, note the following information:

  1. You must install Full-Text Search during SQL Server Setup. To do this, when you get to the Feature Selection page of the Setup wizard, under the Instance Features/Database Engine Services node, make sure that the Full-Text Search check box is selected.

  2. You can enable FILESTREAM during installation. To do this, follow these steps:

    1. When you get to the Database Engine Configuration page, after you configure account provisioning and data directories, click the FILESTREAM tab.

    2. Select the Enable FILESTREAM for Transact-SQL access check box, and then click Next. (Note that if you use a sample that requires file I/O streaming access, you can enable the Enable FILESTREAM for file I/O streaming access option later. Also note that if you enable FILESTREAM during SQL Server Setup, you do not have to run the sp_configure stored procedure with the filestream_access_level option to enable FILESTREAM.)

  3. After the installation, you must enable the SQL Full-text Filter Daemon Launcher service.

Download SQL Server 2008R2 Sample Databases

As mentioned at the beginning of this article, the sample databases for SQL Server 2005 and higher must be manually downloaded and installed. All sample databases can be downloaded from Microsoft's SQL Server sample website at http://msftdbprodsamples.codeplex.com/.

For the purpose of this article, download the following databases for SQL Server 2008 R2:

  Sample Databases for Microsoft SQL Server 2008R2 (SR1), 84280K, Dec 2, 2010

        Save the file AdventureWorks2008R2_SR1.exe to a temporary directory.

Install SQL Server 2008R2 Sample Databases

Install SQL Server Sample Databases

To install all the sample databases for SQL Server 2008 perform the following steps:

  1. Ensure all prerequisites are met. See Database Prerequisites for detailed prerequisite instructions. Note that database installation will not complete unless all prerequisites are met.

  2. Using the object explorer in SSMS delete any earlier version of the AdventureWorks sample databases you may have installed.

  3. Using the file explorer ensure all .MDF and .LDF files have been deleted from the data directory for all sample databases in the instance you will be installing the new sample databases. For the default instance, the path to the data directory for OLTP, LT, and DW sample databases is typically C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA.

  4. Respond to all the dialogs presented by the installer as detailed below.

Double-click the AdventureWorks2008R2_SR1.exe file.

Figure 1: Self Extracting ZIP File

Click the I agree license agreement check box, then click the Next button.

Figure 2: Microsoft Public License (Ms-PL)

Use the pull down selector to choose the instance in which to install the sample databases. For the purpose of this example, the only instance I have available is the Default instance.

While it is possible to change the directory where the scripts and data files will be installed, it is recommended that the default be used. If the directory is changed, be sure that the account the SQL Server service runs under has read access to this directory.

Un-check any databases you do not wish to install.

If there are warnings, click the More Information link(s) for additional information which can help you resolve the issues which prevent those databases from being installed. Note that this screen will display warnings for the Analysis Services (OLAP) sample databases. These warnings are normal and indicate that the database installer for SQL Server 2008 and SQL Server 2008R2 will not automatically deploy the Analysis Services database. After the install has successfully finished, follow the steps in the next section to manually deploy the analysis services database.

To copy the scripts and data files and install the selected databases, click the Install button.

Figure 3: Install Options

After clicking the Install button, all scripts and data files are copied to the directory specified in the previous dialog. Progress bars for the entire installation and progress for installing the current database are displayed along with status messages that change as the installation progresses.

More status information can be displayed by clicking Show Details. If the installation fails for any reason, troubleshooting information can be obtained by clicking the Show Details.

Note that any bugs filed should include all the details displayed which you can gather by typing Ctrl-A to select everything in the details text box, and Ctrl-C to copy all the lines to the clipboard. The details can then be pasted in a bug you can create on the Issue Tracker tab at http://msftdbprodsamples.codeplex.com/.

Figure 4: Install Progress

When the installation is complete, click the Finish button. Temporary files will be deleted by the self-extracting zip file. The databases which were installed should be ready to browse and access using either SQL Server Management Studio or the SQLCMD tool.

Figure 5: Install Successfully Completed

Manually Deploy the Analysis Services Database

The database installer for SQL Server 2008 and SQL Server 2008R2 does not automatically deploy the Analysis Services (OLAP) database. To deploy the analysis services database perform the following steps:

  1. Ensure that the SQL Server Analysis Services service account for the instance you will be deploying to has access to the instance where the AdventureWorksDW2008R2 sample database is stored, and also has access to that databases, and the account is at least a member of the db_datareader role for the AdventureWorksDW2008R2 sample database.

  2. Start Business Intelligence Development Studio by clicking Start / All Programs / Microsoft SQL Server 2008 R2 / SQL Server Business Intelligence Development Studio.

  3. Click File / Open / Project/Solution.

  4. Navigate to the appropriate folder containing the Analysis Services database solution. If the sample databases were installed to the default location:

    For SQL Server 2008

    1. C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project\standard\Adventure Works.sln will contain the solution for SQL Server Standard.

    2. C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project\enterprise\Adventure Works.sln will contain the solution for SQL Server Enterprise and SQL Server Developer.

    For SQL Server 2008 R2

    1. C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008R2 Analysis Services Project\standard\Adventure Works.sln will contain the solution for SQL Server Standard.

    2. C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008R2 Analysis Services Project\enterprise\Adventure Works.sln will contain the solution for SQL Server Enterprise and SQL Server Developer.

    Analysis Services databases are not supported on other editions of SQL Server.

  5. After opening the solution, in the Solution Explorer double click the "Adventure Works.ds" data source. Click the Edit... button just below the Connection String box. Supply your SQL Server name (and instance name if its a named instance). Supply log on credentials for the instance and then verify the correct AdventureWorks Data Warehouse sample database is selected.

    Figure 6: Data Source / Connection Manager

    Click the Test Connection Button. If the test succeeds, click OK and OK to save the changes.

    Figure 7: Test connection Succeeded

  6. Right click on the solution ('Adventure Works DW 2008') in Solution Explorer, choose Properties. On the Adventure Works DW 2008 Property Pages dialogue, choose the tree item for Deployment. Change the Target - Server property to your Analysis Services server name and instance name (if it is not a default instance).

    Figure 8: Adventure Works DW 2008 Property Pages

  7. In the solution explorer click right on the solution ('Adventure Works DW 2008') and click Deploy.

    Figure 9: Deploy Solution

  8. The deployment process will take several minutes to complete. After the deployment process has successfully completed, Exit from Business Intelligence Development Studio.

    Figure 10: Deployment Completed Successfully

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.



Copyright (c) 1998-2014 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Friday, 03-May-2013 09:17:12 EDT
Page Count: 93323