Introduction

Database solutions are integral to every organization that needs to store, analyze, and report on data. Microsoft SQL Server 2005 provides a robust platform for implementing your database solution. SQL Server 2005 is packed with easy-to-use features that make it an ideal environment. In particular, this latest version of SQL Server is more secure, more scalable, and easier to use and manage than ever.

This book is for developers and database administrators who are already familiar with SQL Server and want to explore the latest features in SQL Server 2005. Developers and administrators with experience on other platforms will be able to use this book even if they are new to the topic of SQL Server. By following the step-by-step procedures in each chapter, you'll get a comprehensive hands-on introduction to the most important options for implementing your database solution using SQL Server 2005.

This book will cover the basics, including design, security, backups, and distribution. Along the way it will touch on related issues, such as migration from earlier versions of SQL Server. It gives extensive coverage to the question of retrieving and analyzing data, including data from remote sources, with an entire chapter devoted to accessing SQL Server through the Internet. You'll also find a thorough introduction to some of the features that make SQL Server such a comprehensive solution. These features provide for transactions, snapshots, auditing, reporting, and notification, as well as new features, such as enhanced support for XML.

Organization of This Book

Part 1 of this book describes the essentials for creating a database solution through SQL Server 2005: what to consider when designing your database, how to secure your data against unauthorized access, how to protect your data from unexpected loss, and how to distribute your data to other servers.

Part 2 explains how to retrieve the data in your database: using aggregates to turn data into information through summary and analysis, designing your database for the fastest possible retrieval of data, providing your users with flexibility in how they view the data, linking data from other sources, and connecting to SQL Server via the Internet.

Part 3 gives you a taste of the power of SQL Server 2005 with a discussion of additional features: using transactions to ensure the integrity of your data, keeping historical information for your environment, designing reports using Reporting Services, and updating applications when data changes through Notification Services.

After reading this book, you will have mastered all you need to know to implement your own database solution. You'll be armed with an overview of the capabilities of SQL Server 2005 and enough information to explore its full depth on your own.

Finding Your Best Starting Point in This Book

If you are:	Follow these steps:
New to SQL Server	Install the code samples as described in the "Code Samples" section of this Introduction. Work through Chapters 1 through 7. Complete Chapters 8 through 13 as your level of experience and interest dictates.
New to Database Administration	Install the code samples as described in the "Code Samples" section of this Introduction. Work through Chapters 1 through 4. Skim Chapters 5 through 13 for an overview of SQL Server 2005 features.
Migrating from Other Database Platforms	Install the code samples as described in the "Code Samples" section of this Introduction. Skim the first two chapters, then concentrate on Chapters 3 through 13.
Migrating from An Earlier Version of SQL Server	Install the code samples as described in the "Code Samples" section of this Introduction. Skim the first seven chapters, then concentrate on Chapters 8 through 13, which cover features that are new or have been updated in SQL Server 2005.
Referencing the Book after Working Through the Exercises	Use the index or the table of contents to find information about particular subjects. Read the Quick Reference sections at the end of each chapter to find a brief review of the syntax and techniques presented in the chapter.

Conventions and Features in This Book

This book presents information using conventions designed to make the information readable and easy to follow. Before you start the book, read the following list, which explains conventions you'll see throughout the book and points out helpful features in the book that you might want to use.

Conventions

• Each exercise is a series of tasks. Each task is presented as a series of numbered steps (1, 2, and so on).

• Notes labeled "tip" provide additional information or alternative methods for completing a step successfully.

• Notes labeled "important" alert you to information you need to check before continuing.

• Text that you type appears in italics.

• SQL keywords appear in uppercase.

• Visual Basic programming elements appear in italics.

• A series of menu commands are shown separated by the pipe character (|).

• A plus sign (+) between two key names means that you must press those keys at the same time. For example, "Press Alt+Tab" means that you hold down the Alt key while you press the Tab key.

Other Features

• Sidebars throughout the book provide more in-depth information about the topic. The sidebars might contain background information, design tips, or features related to the information being discussed.

• Each chapter ends with a Quick Reference section. The Quick Reference section contains quick reminders of how to perform the tasks you learned in the chapter.

System Requirements

You'll need the following hardware and software to complete the practice exercises in this book:

Note

The SQL Server 2005 software is not included with this book! The CD-ROM packaged in the back of this book contains the code samples need to complete the exercises. The SQL Server 2005 software must be purchased separately.

• Microsoft Windows XP with Service Pack 2, Microsoft Windows Server 2003 with Service Pack 1, or Microsoft Windows 2000 with Service Pack 4

• Microsoft Visual Studio 2005 Professional Edition

• Microsoft SQL Server 2005 Developer Edition

• 600 MHz Pentium or compatible processor (1 GHz Pentium recommended)

• 256 MB RAM (512 MB or more recommended)

• Video monitor (800 x 600 or higher resolution) with at least 256 colors (1024 x 768 high-color 16-bit recommended)

• CD-ROM or DVD-ROM drive

• Microsoft Mouse or compatible pointing device

Code Samples

The companion CD inside this book contains the code samples that you can use as you perform the exercises in the book. You can use the samples as they are to experiment on your own, or you can copy and paste code from the sample files as you build your own files while following the exercises. The files and the step-by-step instructions in the lessons let you learn by doing, which is an easy and effective way to acquire and remember new skills.

Installing the Code Samples

Follow these steps to install the code samples on your computer so that you can use them with the exercises in this book.

1.	Remove the companion CD from the package inside this book and insert it into your CD-ROM drive. Note An end user license agreement should open automatically. If this agreement does not appear, open My Computer on the desktop or Start menu, double-click the icon for your CD-ROM drive, and then double-click StartCD.exe.
2.	Review the end user license agreement. If you accept the terms, select the accept option and then click Next. A menu will appear with options related to the book.
3.	Click Install Code Samples.
4.	Follow the instructions that appear. The code samples are installed by default to the following location on your computer: My Documents\Microsoft Press\SQLAppliedTechSBS\

Using the Code Samples

Each chapter in this book explains when and how to use any code samples for that chapter. When it's time to use a code sample, the book will point you to the appropriate file or folder. The chapters are built around scenarios that simulate real programming projects, so you can easily apply the skills you learn to your own work. For SQL Script samples, you will typically open the file in SQL Server Management Studio, highlight the relevant portion of the script, and execute only that portion before proceeding. For Visual Studio projects, you will double-click the .sln file to open the project and navigate within Visual Studio to the relevant code.

Uninstalling the Code Samples

Follow these steps to remove the code samples from your computer.

1.	In Control Panel, open Add Or Remove Programs.
2.	From the list of Currently Installed Programs, select Microsoft SQL Server 2005 Applied Techniques Step by Step.
3.	Click Remove.
4.	Follow the instructions that appear to remove the code samples.

Configuring SQL Server 2005 Express Edition

This book makes frequent use of the AdventureWorks sample database that ships with SQL Server 2005. Make sure you have installed the sample database, following the instructions in the SQL Server installation program. Then follow these steps to grant access for this database to the user account that you will be using when performing the exercises in this book:

1.	Log onto Microsoft Windows on your computer using an account with administrator privileges.
2.	On the Windows Start menu, click All Programs, click Accessories, and then click Command Prompt to open a command prompt window.
3.	In the command prompt window, type the following case-sensitive command: sqlcmd -S YourServer\InstanceName -E Replace YourServer with the name of your computer, and replace InstanceName with the name of the SQL Server instance you will be using. You can find the name of your computer by running the hostname command in the command prompt window, before running the sqlcmd command.
4.	At the 1> prompt, type the following command, including the square brackets, and then press Enter: sp_grantlogin [YourServer\UserName] Replace YourServer with the name of your computer, and replace UserName with the name of the user account you will be using.
5.	At the 2> prompt, type the following command and then press Enter: GO If you see an error message, make sure you have typed the sp_grantlogin command correctly, including the square brackets.
6.	At the 1> prompt, type the following command, including the square brackets, and then press Enter: sp_addsrvrolemember [YourServer\UserName], dbcreator
7.	At the 2> prompt, type the following command and then press Enter: GO If you see an error message, make sure you have typed the sp_addsrvrolemember command correctly, including the square brackets.
8.	At the 1> prompt, type the following command and then press Enter: exit
9.	Close the command prompt window.
10.	Log out of the administrator account.

Support for This Book

Every effort has been made to ensure the accuracy of this book and the contents of the companion CD.

Microsoft Learning provides support for books and companion CDs at the following address:

http://www.microsoft.com/learning/support/search.asp

If you have comments, questions, or ideas regarding the book or the companion CD, or questions that are not answered by visiting the sites above, please send them to Microsoft Press via e-mail to:

[e-mail]

Or via postal mail to:

Microsoft Press
Attn: Microsoft SQL Server 2005 Step by Step Editor
One Microsoft Way
Redmond, WA 98052-6399

Please note that Microsoft software product support is not offered through the above addresses.