Desperately Seeking Samples

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

It’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.

AdventureWorks Cycles Storefront 2000

A database without an end-user application is like a house without furniture: It shelters you from the elements, but sleeping on the floor gets to be uncomfortable. At the Microsoft Download Center (www.microsoft.com/downloads/details.aspx? FamilyID=15D6473F-1441-4269-84EA-14B04A0DAE09) you’ll find sample code that demonstrates how to build an e-commerce website for the fictitious AdventureWorks bicycle manufacturing company. In addition to illustrating how to access code from SQL Server and navigate hierarchical data using a tree structure, the code illustrates how to encrypt passwords and other sensitive data. The code was written for SQL Server 2000 and Visual Studio .NET 7.0, but it can be modified for more recent product releases.

AdventureWorks Cycles Business Scenarios

OK, you’ve got the back-end sample databases and the front-end sample web applications, and now you’re probably asking, “How do they work together?” The SQL Server 2005 Books Online (BOL), September 2007 edition, defines the major tables in the AdventureWorks OLTP database and describes how to use them, complete with T-SQL queries to generate information. The BOL sales and marketing page is at msdn2.microsoft.com/en-us/library/ms124824.aspx; the product page is at msdn2.microsoft.com/en-us/library/ms124670.aspx; manufacturing is at msdn2.microsoft.com/en-us/library/ms124499.aspx; and purchasing and vendors is at msdn2.microsoft.com/en-us/library/ms124785.aspx.

If you’re familiar with the Northwind database and are curious about the differences, go to the AdventureWorks to Northwind Table Comparison at msdn2.microsoft.com/en-us/library/ms124680.aspx. Are you a pubs lover? Then take a look at the AdventureWorks to pubs Table Comparison at msdn2.microsoft.com/ en-us/library/ms124583.aspx for information about how those two databases compare. At a high level, the biggest difference between AdventureWorks and Northwind or pubs is that AdventureWorks takes advantage of schemas and incorporates them into its design.

Schemas

SQL Server 2005 introduced the concept of a schema as separate from a user. Database objects such as tables, views, and procedures now belong to a schema rather than to a user. In earlier versions, removing a user’s account from the database compromised access to objects owned by that user. Now, since a schema owns the objects, access to them remains unchanged regardless of what happens to user accounts. AdventureWorks contains five schemas: Human Resources, Person, Production, Purchasing, and Sales. You can read about them and how to access them on the Schemas in AdventureWorks page (msdn2.microsoft.com/en-us/library/ms124894.aspx).

If you’d like a visual guide to help you grasp the database schema, check out the schema diagrams page at www.codeplex.com/MSFTDBProdSamples/Wiki/View.aspx?title= AWSchemaDiag&referringTitle= Home. This page provides diagrams for Adventure- Works, AdventureWorksDW, and AdventureWorksLT.

Online Libraries

When you need a technical library at your fingertips, check out the SQL Server section of the TechNet Library at technet.microsoft.com/en-us/library/bb545450.aspx. It offers BOL, white papers, links to SQL Server Express Edition, the SQL Server 2005 Virtual Labs, and the Data Platform Developer Center for SQL Server 2008, 2005, 2000, and 7.0. Tabs on the TechNet website take you to a list of learning resources, downloads, and support of all kinds, from the Knowledge Base to community forums and blogs, webcasts, wikis, and newsgroups. There’s so much information available for the SQL Server platforms that it’s hard to cover it all.

If you’re offline, you can always use your local copy of BOL, which, even though it’s an optional component, should have been installed as part of client services. Open BOL 2008, 2005, or 2000, and type in samples to produce a list of locally available samples for everything from sample databases and T-SQL code to utility programs and procedural code.

Resource Kits

Resource kits are another valuable addition to the DBA’s library. The typical resource kit is filled with chapter after chapter of how-to material and real-world lessons, plus utilities, explanations, and code to help you do your job better. The SQL Server 2000 Resource Kit, which at one time was available as a separate purchase, is out of print, but you can still find it at Amazon.com, often with the accompanying CD. A free text version of the kit is available at www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/default.mspx.

Starting with SQL Server 2005, Microsoft has apparently changed the resource kit model. Previously presented as a bound collection of information, the SQL Server 2005 resource kit provides distributed information and learning opportunities, anchored by CodePlex and the MSDN Library (msdn2.microsoft.com/en-us/sqlserver/default.aspx). I found a few obscure references to a “SQL Server 2005 Resource Kit” in online literature, but the resource kit itself seems to have vanished. I found a book in print titled Microsoft SQL Server 2005 Administrator’s Companion (Eric Whalen et al., Microsoft Press, 2006) that seems to fit the model of a resource kit, and it might be the closest volume on the market. But there are both print and online books concerning every facet of SQL Server 2005.

If you’re a Microsoft Dynamics NAV 5.0 customer, you can get a CD that’s essentially a SQL Server 2005 Technical Kit for this environment. Dynamics NAV is an end-to-end, customizable package that provides a framework of business units (e.g., financials, manufacturing, sales, marketing) on which you can hang your own custom solutions. According to a Waldo’s Blog entry (dynamicsuser.net/blogs/waldo/archive/2007/12/13/sqlserver-technical-kit-for-microsoft-dynamics-nav.aspx), this CD contains everything that the rest of us would want in a resource kit. However, if you aren’t a registered NAV partner or customer, you’re not eligible to receive this disk. Pity.

Where to Start

When you’re working with a new product or building a system, there’s nothing like learning by example. And there’s so much information available on the four current versions of SQL Server that it’s hard to decide where to start! I suggest you take stock of what you’re doing with SQL Server, assess where you are on your personal learning curve, and then seek out documents and learning materials that will support and advance your progress. And, of course, don’t forget to download and install the sample databases—pubs, Northwind, and AdventureWorks—so you’ll have something to practice on.