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.