SQL Server Magazine

Any programmer will tell you: There’s nothing like a sample bit of code to illustrate how something is done. Sample code helps jumpstart the learning and development process. Learning from examples is a great way to absorb new concepts and, arguably, is the standard used in almost every IT training class.

In the database world, sample databases, APIs, and code serve a similar purpose. Through its CodePlex website and other resources, Microsoft has made available a wealth of documents, webcasts, virtual labs, code libraries, and database samples to help with your SQL Server education. SQL Server 2005 introduced a new family of sample databases, called AdventureWorks, and changed the process for installing sample databases. You can still install the classic pubs and Northwind sample databases with the more recent versions of SQL Server, although you need to download them from the Microsoft Download Center. (For details, see the sidebar “Where in the World Are Pubs and Northwind?”)

CodePlex
Possibly Microsoft’s most useful resource is its host website for open-source projects, CodePlex (www.codeplex.com), which stores publically available code samples, as well as all of Microsoft’s sample databases. In this case, “open source” means the website has plenty of third-party contributions and wiki-like entries. It also means Microsoft doesn’t control the content, so contributed code might not be formally tested and validated. Microsoft hosts CodePlex as a storage service for the developer community. Data is gathered into “projects” based on subject matter. The last I checked, there were 5,289 projects listed in CodePlex—at least one for virtually every Microsoft product.

CodePlex includes a SQL Server Community page (www.codeplex.com/SqlServerSamples) that’s chockfull of samples—database samples, end-to-end multitechnology samples, SQL Server engine samples, SQL Server Integration Services samples, and SQL Server Reporting Services samples, as well as tools and utilities. A link to the latest SQL Server 2008 release candidate is at www.codeplex.com/MSFTDBProdSamples/ Release/ProjectReleases.aspx?ReleaseId=10901.

The multi-tabbed SQL Server Databases and Samples Overview wiki (www.codeplex.com/SqlServerSamples/Wiki/View.aspx ?title=SQLServerDatabasesandSamplesOverview) contains new and enhanced code for both SQL Server 2008 and SQL Server 2005. The home page explains how to remove previously installed versions of the AdventureWorks sample databases and how to properly re-install them, even in a multi-instance environment. The code listings are extensive, covering all that’s new in everything from ADO to XML. There’s also room for reader comments and feedback.

CodePlex’s Analysis Services page (www.codeplex.com/MSFTASProdSamples) contains explanations and code for building a SQL Server Analysis Services (SSAS) database for business intelligence (BI) scenarios. The newest SQL Server release (SQL Server 2008 Release Candidate 0—RC0) is listed in the Current Release section of the Home tab. You can click All Releases to expose the link to the SQL Server 2005 SP2a samples installation page. Like all pages on the CodePlex website, this page provides tabs for discussions, issue trackers, source code, stats, and who to contact on the Microsoft team. There’s so much on CodePlex that I simply cannot begin to cover it all. My suggestion is to go to CodePlex and start digging.

Sample Databases
SQL Server 2005 ships with two sample databases: AdventureWorks and AdventureWorksDW. These samples illustrate concepts of transactional database design and operation and of data warehousing and BI design and operation, respectively. The sample databases are optional features at install time; if you don’t install them with SQL Server, you can download and install them from CodePlex, as I explain later. Microsoft also makes available a “lite” version of AdventureWorks. And finally, a sample database called FoodMart from Microsoft’s earlier days remains available.

AdventureWorks. AdventureWorks, the transactional database, supports OLTP for a fictitious bicycle manufacturer. The AdventureWorks design maps to realworld enterprise scenarios, with schemas for Human Resources, Person, Production, Purchasing, and Sales. There are separate versions for SQL Server 2008 and SQL Server 2005; the SQL Server 2008 version doesn’t work with SQL Server 2005.

AdventureWorksDW. AdventureWorksDW illustrates by example how to design a data warehouse. A data warehouse is a repository for a company’s historical data. (For a high-level data-warehouse overview, see “Data Warehousing: The Foundation of BI,” July 2007, InstantDoc ID 96191.) The design techniques and table architecture of a data warehouse differ significantly from an OLTP database. At some point in a DBA’s career, his or her company will most likely embark on a data warehouse project, and if the DBA is lucky, he or she will be part of the process. AdventureWorksDW provides a leg up on how to design a data warehouse. It uses the sample AdventureWorks OLTP database as its data source, so you can see how transactional data can be transformed into a data warehouse.

AdventureWorksLT. The “lite” version of Adventure- Works, AdventureWorksLT, is a small, denormalized version of the AdventureWorks database that focuses on a product sales scenario and is not included in the SQL Server installation package. According to Microsoft, AdventureWorksLT “is helpful for those who are new to relational database technology.” However, this “lite” database contains too many violations of normalization and integrity. Additionally, the only available version of AdventureWorksLT is case-sensitive.

In my opinion as an educator, exposing entry-level DBAs to bad database design and touting it as acceptable isn’t a good idea. I’ve removed it from my hard drive and use pubs and Northwind instead when I need simple databases for testing or demonstrations. If you want to achieve simplicity in a test database, I recommend limiting the number of business functions supported by the database, but making sure the database design is correct. Should you decide to use AdventureWorksLT, you can find it at www.codeplex.com/MSFTDBProdSamples/ Wiki/View.aspx?title=AWLTDocs.

FoodMart. Beyond CodePlex, you can find other sample database offerings. For example, the Project- Distributor website (projectdistributor.net/Releases/Release.aspx?releaseId=331) lets you download a SSAS backup of the FoodMart sample database, which Microsoft used to distribute with SSAS. FoodMart is a Microsoft Access database that encompasses sales, employee relations, and inventory management. Like pubs, it's used for testing, training examples, and posting sample queries and newsgroup questions, but it's used for data warehousing and BI instead of for OLTP (transactional) databases. A Microsoft Access copy of the database (foodmart.mdb) is available on the Seneca College of Applied Arts & Technology Microsoft Windows shareware page at acs.senecac.on.ca/ftp/ms.

Download and Install
t’s a good idea to install the AdventureWorks databases, along with SQL Server 2008/2005, on your test and development servers. With all the complexity and capability in the two latest versions of SQL Server, you’ll need a controlled environment in which to learn and experiment. The AdventureWorks databases include a guest user account, so make sure that you aren’t violating company policy by installing the databases. You can always remove the guest user if it constitutes a violation of your IT and database security; check with your security administrator first.

Some folks believe the AdventureWorks databases should be installed on production servers, but I disagree. A production server is meant to be used for, well, production, and AdventureWorks is a sample database meant for testing and learning. Just as you wouldn’t use your production system for development, neither should you use it for testing.

Downloading and installing the sample databases is fairly straightforward. For instance, to download AdventureWorks for SQL Server 2005 SP2a, go to www.codeplex.com/MSFTDBProdSamples/ Release/ProjectReleases.aspx?ReleaseId=4004, decide which version you prefer (e.g., case-sensitive, case-insensitive, 32- or 64-bit), click the link, click I Agree when presented with Microsoft’s EULA, and download the file to your hard disk. It’s a Windows Installer (.msi) file, so just double-click the file, accept the license agreement, and follow the InstallShield Wizard’s prompts. The installer copies the database files (.mdb and .ldb) to the folder you’ve designated.

To use the database, you must attach it to an instance of SQL Server using SQL Server Management Studio (SSMS). Open SSMS and connect to your test server. Open the hierarchy so you can see the entry for databases, right-click Databases, then select Attach. Click Add in the Attach Databases dialog box and navigate to the folder in which the installer placed the downloaded files. You should see only the data file (i.e., Adventure- Works_data). Click OK, and then click OK again. The AdventureWorks database now should be in SSMS’s list of databases.

Continue to page 2

   Prev. page   [1] 2     next page