|
|
| |
 |
|
Install AdventureWorks Sample Databases - (SQL Server 2008 R2)
Expert Oracle Tips by Jeff Hunter
March 16, 2011
|
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.
The following sample databases are available as part of the
AdventureWorks Community Sample Databases for SQL Server 2008 R2:
- AdventureWorks OLTP 2008R2
- AdventureWorks Data Warehouse 2008R2
- AdventureWorks LT 2008R2
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 the
CodePlex website. 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:
- Some edition of SQL Server 2008 or SQL Server 2008 R2 must
be installed.
- If installing on Windows XP or Windows Server 2003, the SQL
Server service account must be something other than NT
Authority\Network Service (Local System will do).
In order to be able to successfully install the
AdventureWorks2008 or AdventureWorks2008R2 (OLTP) database, the
following additional requirements must be met:
- At least SQL Server Express with Advanced Services must be
installed. Ordinary SQL Server Express will not work. You can
download SQL Server 2008 Express with Advanced Services from the
Microsoft Download Center.
- FILESTREAM must be enabled and the SQL Full-text Filter Daemon
Launcher service must be running. See below for how to configure
these features.
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
Note that 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.
- 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.
- 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.
- 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
- Click Start, point to All Programs, point to
Microsoft SQL Server 2008 R2, point to Configuration
Tools, and then click SQL Server Configuration Manager.
- In the left pane, click SQL Server Services.
- In the list of services, right-click SQL Full-text Filter
Daemon Launcher for the instance that you are configuring,
and then click Properties.
- Click the Service tab.
- Next to Start Mode, click Disabled, and then
click Automatic in the list that appears.
- Click OK.
- 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.
- On the Start menu, point to All Programs, point
to Microsoft SQL Server 2008 R2, point to Configuration
Tools, and then click SQL Server Configuration Manager.
- In the list of services, right-click SQL Server Services,
and then click Open.
- In the SQL Server Configuration Manager snap-in, locate
the instance of SQL Server on which you want to enable FILESTREAM.
- Right-click the instance, and then click Properties.
- In the SQL Server Properties dialog box, click the
FILESTREAM tab.
- Select the Enable FILESTREAM for Transact-SQL access
check box.
- (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.
- (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.
- Click Apply.
- In SQL Server Management Studio, click New Query
to display the Query Editor.
- 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.
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:
- 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.
- You can enable FILESTREAM during installation. To do this,
follow these steps:
- When you get to the Database Engine Configuration
page, after you configure account provisioning and data
directories, click the FILESTREAM tab.
- 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.)
- 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 the CodePlex section.
For the purpose of this article, download the following databases
for SQL Server 2008 R2:
Sample Databases for Microsoft SQL Server 2008R2 (RTM), 84280K, May
25, 2010
Save the file AdventureWorks2008R2_RTM.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:
- Ensure all prerequisites are met. See Database Prerequisites
for detailed prerequisite instructions. Note that database
installation will not complete unless all prerequisites are met.
- Using the object explorer in SSMS delete any earlier version
of the AdventureWorks sample databases you may have installed.
- 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.
- Respond to all the dialogs presented by the installer as
detailed below.
Double-click the AdventureWorks2008R2_RTM.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 the CodePlex website.
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:
- 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.
- Start Business Intelligence Development Studio by clicking
Start / All Programs / Microsoft SQL Server 2008 R2 / SQL Server
Business Intelligence Development Studio.
- Click File / Open / Project/Solution.
- 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
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.
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
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.
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.
- 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 (database engine) server and instance name (if its
a named instance). Click the Test Connection Button. If the
test succeeds, click OK and OK to save the changes.
- 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).
- In the solution explorer click right on the solution ('Adventure
Works DW 2008') and click Deploy.
|
|
 |
|
Get the Complete
Oracle Tuning Details
The landmark book
"Oracle
Tuning: The Definitive Reference Second Edition" has been
updated with over 1,150 pages of expert performance tuning tips.
It's packed with scripts and tools to hypercharge Oracle 11g
performance and you can
buy it for 40% off directly from the publisher.
|
|
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
| |
| |
Copyright ©
1996 -2011 by Burleson Enterprises. All rights reserved.
Oracle® is the registered trademark of Oracle
Corporation. SQL Server® is the registered trademark of Microsoft
Corporation.
Many of the designations used by computer vendors to distinguish their
products are claimed as Trademarks
|
|
|
|
|
|
