Time Required: 45 minutes
Objective: Download and install the Adventure Works 2012 sample database.
Description: In this activity, you will download and install the Online Transaction Processing (OLTP) variant of the Adventure Works 2012 sample database from the Microsoft CodePlex site.
CodePlex is Microsoft’s open source project hosting site where you can find useful sample code for a wide range of Microsoft products. Online Transaction Processing (OLTP) is a method of processing data that is geared toward transaction-oriented applications that have a substantial write component in their interaction with the database. An OLTP database is a database defined by a high volume of individual read and write transactions. This sample database will be used in several of the activities later in this chapter and in the remaining chapters of the book.
1. If necessary, start your computer and log on using an Administrator account.
2. Click the Start button, point to All Programs, and then click Internet Explorer.
3. Click in the Address bar and type the following URL: msftdbprodsamples.codeplex.com to access the SQL Server Database Product Samples at CodePlex page. Click the SQL Server 2012 OLTP icon to open the Adventure Works for SQL Server 2012 page. See Figure 3-4.
Figure 3-4 Microsoft CodePlex download page for the Adventure Works for SQL Server 2012 sample database
Microsoft Internet Explorer
3
4. Scroll down the page, and click the AdventureWorks 2012 OLTP Script link.
Depending upon your settings, the following warning message may appear in the Internet Explorer Information bar: “To help protect your security, Internet Explorer blocked this site from downloading files to your computer. Click here for more options.” Click the Internet Explorer Information bar, and then click Download File.
5. In the Save As dialog box, keep the default location C:\Downloads\
AdventureWorks_2012_OLTP_Script.zip, and click the Save button. See Figure 3-5.
Figure 3-5 Save As dialog box for the Adventure Works 2012 sample database
Microsoft Windows Server 2008 R2
6. To launch SQL Server Management Studio, click the Start button, point to All Programs, click Microsoft SQL Server 2012, and then click SQL Server Management Studio. See Figure 3-6.
Figure 3-6 Launch SQL Server Management Studio
Microsoft Windows Server 2008 R2
7. In the Connect to Server dialog box, keep the default values for the Server type and Authentication modes. In the Server name text box, type your computer name and instance name in the format <ComputerName>\<InstanceName>. Click Connect. See Figure 3-7.
8. In SQL Management Studio, click File on the menu bar, point to Open, and then click File. See Figure 3-8.
Figure 3-8 Open a file from SQL Server Management Studio
Microsoft SQL Server 2012
Figure 3-7 Connect to named instance SQLSERVERUA
Microsoft SQL Server 2012
3
9. In the Open File dialog box, navigate to the folder location of the downloaded zIP file containing the Adventure Works sample database C:\Downloads\. Double-click the zIP file AdventureWorks_2012_OLTP_Script.zip. Click the Extract All files but-ton on the menu bar to extract the files in the zipped folder. Use the default location C:\Downloads\AdventureWorks_2012_OLTP_Script\ and click Extract. Open the subfolder AdventureWorks 2012 OLTP Script that contains the extracted files. You will see a number of CSV files that contain the data and a SQL script called instawdb.
sql. Double-click instawdb.sql to open the script in SQL Server Management Studio.
10. You need to run the script in SQLCMD mode. In SQL Server Management Studio, click Query on the menu bar, and then click SQLCMD Mode. See Figure 3-9.
Figure 3-9 Switch to SQLCMD mode
Microsoft SQL Server 2012
11. The instawdb.sql script will create the database objects on your SQL Server and use a procedure called BULK INSERT to load data into the various tables from the individual CSV files. Read the comments at the start of the script, and note that during the installation process of SQLSERVERUA we specified that the full text search feature should be switched on. Before executing the script, you must change the variables that store the location of the source data files and the path to the database that will be created. Change the variable SqlSamplesDatabasePath to match the location of the data files of your instance: C:\Program Files\Microsoft SQL Server
\MSSQL11.SQLSERVERUA\MSSQL\Data\. Next, change the variable SqlSamplesSourceDataPath to match your unzipped download location:
C\Downloads\ AdventureWorks_2012_OLTP_Script\AdventureWorks 2012 OLTP Script\. Figure 3-10 shows the script with the corrected variables.
12. Run the script by clicking the Execute button on the menu bar or by pressing the shortcut key F5. See Figure 3-11.
The script first drops any existing databases named AdventureWorks2012. Next, it creates the new AdventureWorks2012 database, adds the database schema objects, and then loads the sample data. Once the query has finished running, you will see a success message at the bottom of the window below the results grid. This grid contains a summary of the physical files that were created during installation of the database.
See Figure 3-12. The Messages tab in the output pane at the bottom of the window provides a summary of changes. If the query fails, you will need to diagnose the cause that is likely to be due to an incorrect entry when setting the variable in Step 11.
Figure 3-11 Execute SQL query
Microsoft SQL Server 2012
Figure 3-12 SQL query results grid
Microsoft SQL Server 2012
Figure 3-10 Modify variables in SQL script
Microsoft SQL Server 2012
13. Click the Refresh icon in Object Explorer, and expand the folders by clicking the + symbol to the left of the Databases folder. You should see an AdventureWorks2012 user database with the various schema object types listed beneath. See Figure 3-13.
3
14. Close the SQL Server Management Studio. If prompted, do not save changes.