SQL Server 2008 Administration E-Book

Tom Carpenter

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch
• Veröffentlichungsjahr: 2010

Seitenzahl: 1144

Beschreibung

The ideal on-the-job reference guide for SQL Server 2008database administrators If you manage and administer SQL Server 2008 in the real world,you need this detailed guide at your desk. From planning todisaster recovery, this practical book explores tasks and scenariosthat a working SQL Server DBA faces regularly and shows you step bystep how to handle them. Topics include installation andconfiguration, creating databases and tables, optimizing thedatabase server, planning for high availability, and more. And, if you're preparing for MCTS or MCITP certification in SQLServer 2008 administration, this book is the perfect supplement toyour preparation, featuring a CD with practice exams, flashcards,and video walkthroughs of the more difficult administrativetasks * Delves into Microsoft's SQL Server 2008, a rich set ofenterprise-level database services for business-criticalapplications * Explores the skills you'll need on the job as a SQL Server 2008administrator * Shows you how to implement, maintain, and repair the SQL Serverdatabase, including bonus videos on the CD where the authors walksyou through the more difficult tasks * Covers database design, installation and configuration,creating databases and tables, security, backup and highavailability, and more * Supplements your preparation for MCTS and MCITP SQL Server 2008certification with in-depth coverage of the skill sets required forcertification, as defined by Microsoft * Uses hands-on exercises and real-world scenarios to keep whatyou're learning grounded in the reality of the workplace Make sure you're not only prepared for certification, but alsofor your job as a SQL Server 2008 administrator, with thispractical reference! Note: CD-ROM/DVD and other supplementary materials arenot included as part of eBook file.

Leseprobe

Table of Contents

Cover

Title Page

Credits and Copyright

Publisher's Note

Dedication

Acknowledgments

About the Author

Table of Exercises

Introduction

Who Should Read This Book

What Is Covered in This Book

How to Contact the Author

Part I: Introducing SQL Server 2008

Chapter 1: Understanding SQL Server’s Role

What Is Information Technology?

Introduction to Databases

Database Servers and Applications

SQL Server’s Role

Summary

Chapter Essentials

Chapter 2: Installing SQL Server 2008

Installation Planning

Installing a Default Instance

Installing Named Instances

Installing to a Cluster

Installing Extra Features

Upgrading from Previous Versions

Removing an Installation

Summary

Chapter Essentials

Chapter 3: Working with the Administration Tools

SQL Server Configuration Manager

SQL Server Management Studio (SSMS)

SQL Server Business Intelligence Development Studio

SQL Server Profiler

Books Online

Windows Server Administration for the DBA

Summary

Chapter Essentials

Chapter 4: SQL Server Command-Line Administration

Introducing the Command Prompt

General Commands

Batch Files

Mastering SQLCMD

Introducing Windows PowerShell

Using SQL Server PowerShell Extensions

Windows Scripting Host

Summary

Chapter Essentials

Part II: Designing Database Solutions

Chapter 5: Database Concepts and Terminology

Relational Database Theory

Database Design Processes

Project Management for the DBA

Summary

Chapter Essentials

Chapter 6: ERD and Capacity Planning

Planning a Database

Understanding Entity Relationship Diagramming

Building an ERD

Capacity Planning

Summary

Chapter Essentials

Chapter 7: Normalization: Enough Is Enough

Normalization Defined

Normal Forms

Normalizing a Database

Denormalizing a Database

Designing for Performance

Designing for Availability

Designing for Security

Summary

Chapter Essentials

Part III: Implementing Database Solutions

Chapter 8: Creating SQL Server Databases

SQL Server Databases

Database Storage

Database Options and Properties

Creating Databases in the GUI

Creating Databases with T-SQL

Creating Databases with PowerShell

Attaching and Detaching Databases

Database Snapshots

Summary

Chapter Essentials

Chapter 9: Creating Tables

Data Types

Collations

Creating Tables

Data Partitioning

Summary

Chapter Essentials

Chapter 10: Indexes and Views

Understanding Indexes

Creating Basic Indexes

Creating Advanced Indexes

Managing Indexes

Understanding Views

Creating Views

Summary

Chapter Essentials

Chapter 11: Triggers and Stored Procedures

Triggers Defined

Using Triggers

Creating Triggers

Understanding Stored Procedures

Creating Stored Procedures

Summary

Chapter Essentials

Chapter 12: Implementing Advanced Features

Understanding and Installing Analysis Services

Understanding Integration Services

Understanding and Installing Reporting Services

Implementing Database Mail

Configuring Full-Text Indexing

Summary

Chapter Essentials

Part IV: Administration and Maintenance

Chapter 13: Creating Jobs, Operators, and Alerts

Standardize, Automate, and Update

Understanding SQL Server Jobs

Creating T-SQL Jobs

Creating SSIS Jobs

Creating Windows Command Jobs

Creating and Using Operators

Creating and Using Alerts

Using WSUS for SQL Server 2008

Summary

Chapter Essentials

Chapter 14: Performance Monitoring and Tuning

Performance Tuning Principles

Performance and Troubleshooting Tools

Blocks, Locks, and Deadlocks

SQL Server Profiler

Database Engine Tuning Advisor

Performance Monitoring with System Monitor

Using the New Resource Governor

Performance Studio

Advanced Monitoring Tools

Summary

Chapter Essentials

Chapter 15: Policy-Based Management

Policy-Based Management (PBM)

Centralized Server Management

Standardizing with PBM and CMS

Summary

Chapter Essentials

Chapter 16: Backup and Restoration

Backing Up a Database

Back Up System Databases

Restore a Database

Back Up the Environment

Summary

Chapter Essentials

Part V: SQL Server Security

Chapter 17: Security Threats and Principles

Security Defined

Security Threats

Security Principles

Summary

Chapter Essentials

Chapter 18: Authentication and Encryption

Understanding Authentication

SQL Server Authentication Methods

Logins, Users, and Roles

Understanding Encryption

Summary

Chapter Essentials

Chapter 19: Security Best Practices

Establishing Baselines

Implementing Least Privilege

Auditing SQL Server Activity

Configuring the Surface Area

Understanding Common Criteria and C2

Summary

Chapter Essentials

Part VI: High Availability and Data Distribution

Chapter 20: SQL Server Failover Clustering

Understanding Windows Failover Clustering Service

Implementing a Windows Cluster

Installing SQL Server 2008 to a Cluster

Monitoring and Managing a SQL Server Cluster

Summary

Chapter Essentials

Chapter 21: Database Mirroring and Snapshots

RAID-Based Data Redundancy

Using Database Mirroring

Understanding Log Shipping

Implementing Database Snapshots

Summary

Chapter Essentials

Chapter 22: Implementing Replication

SQL Server Replication

Importing and Exporting Data

Summary

Chapter Essentials

Appendix A: Microsoft’s Certification Program

How Do You Become Certified on SQL Server 2008?

Certification Objectives Map

Appendix B: About the Companion CD

What’s on the CD

System Requirements

Using the CD

Troubleshooting

Glossary

Index

End-User License Agreement

Back Insert

Acquisitions Editor: Jeff Kellum

Development Editor: Denise Santoro Lincoln

Technical Editors: Acey Bunch and Mitchel Sellers

Production Editor: Elizabeth Ginns Britten

Copy Editor: Kathy Grider-Carlyle

Editorial Manager: Pete Gaughan

Production Manager: Tim Tate

Vice President and Executive Group Publisher: Richard Swadley

Vice President and Publisher: Neil Edde

Media Project Manager 1: Laura Moss-Hollister

Media Associate Producer: Josh Frank

Media Quality Assurance: Shawn Patrick

Book Designers: Judy Fung, Bill Gibson

Compositor: Craig Johnson, Happenstance Type-O-Rama

Proofreader: Word One, New York

Indexer: Ted Laux

Project Coordinator, Cover: Lynsey Stanford

Cover Designer: Ryan Sneed

Copyright © 2010 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-0-470-55420-3

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services or to obtain technical support, please contact our Customer Care Department within the U.S. at [phone], outside the U.S. at [phone], or fax [phone].

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

Library of Congress Cataloging-in-Publication Data

Carpenter, Tom.

SQL server 2008 administration : real world skills for MCITP certification and beyond / Tom Carpenter.—1st ed.

p. cm.

Includes bibliographical references and index.

ISBN 978-0-470-55420-3 (papen/cd-rom : alk. paper)

978-0-470-6494-4 (ebk); 978-0-470-6494-8 (ebk); 978-0-470-6494-1 (ebk)

1. Electronic data processing personnel—Certification. 2. Microsoft software—Examinations—Study guides. 3. SQL server. I. Title.

QA76.3.C34845 2010

005.4’476—dc22

2010004720

TRADEMARKS: Wiley, the Wiley logo, and the Sybex logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. SQL Server is a registered trademark of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.

10 9 8 7 6 5 4 3 2 1

Dear Reader,

Thank you for choosing SQL Server 2008 Administration: Real World Skills for MCITP Certification and Beyond. This book is part of a family of premium-quality Sybex books, all of which are written by outstanding authors who combine practical experience with a gift for teaching.

Sybex was founded in 1976. More than 30 years later, we’re still committed to producing consistently exceptional books. With each of our titles, we’re working hard to set a new standard for the industry. From the paper we print on to the authors we work with, our goal is to bring you the best books available.

I hope you see all of that reflected in these pages. I’d be very interested to hear your comments and get your feedback on how we’re doing. Feel free to let me know what you think about this or any other Sybex book by sending me an email at [email protected]. If you think you’ve found a technical error in this book, please visit http://sybex.custhelp.com. Customer feedback is critical to our efforts at Sybex.

Best regards,

Neil Edde

Vice President and Publisher

Sybex, an Imprint of Wiley

I dedicate this book to my family and God—the two most important relationships in my life. Thanks for all you do.

Acknowledgments

I would like to acknowledge the members of my amazing family, who continue to find the energy to support me during long writing projects. Tracy, I love you and appreciate the unending patience you’ve shown during the writing process. Faith, Rachel, Thomas, and Sarah, you are the most amazing children any father could hope to lead. I would also like to thank Denise Santoro Lincoln, one of the most proficient editors I’ve had the chance to work with. She is certain to make any book she touches better. Additionally, I’d like to thank Jeff Kellum for allowing me to write my first Sybex book. I’ve been an admirer of Sybex books for many years and am proud to have my name on the cover of one. Of course, I must acknowledge all of my training attendees and consulting clients. They have provided me with the greater depth of knowledge required to write a book like this.

About the Author

Tom Carpenter is a consultant and trainer based in Marysville, Ohio. He is the founder and current Senior Consultant for The Systems Education and Consulting Company (SysEdCo). SysEdCo provides training on Microsoft technologies, wireless networking, security, and IT professional development. Tom is the author of several books on topics ranging from wireless network administration to SQL Server database administration and optimization. Tom holds several certifications including MCITP: SQL Server 2008 Database Administrator, CWNA, CWSP, Project+, and several additional Microsoft certifications. He spends every spare moment he can with his amazing wife and children.

Table of Exercises

Exercise 2-1 Installing a Named Instance

Exercise 3-1 Starting and Stopping Services

Exercise 3-2 Configuring Service Properties

Exercise 3-3 Configuring Protocols

Exercise 3-4 Initial SSMS Configuration

Exercise 3-5 Opening a New Query Window and Viewing the Error List

Exercise 3-6 Viewing Predesigned Reports

Exercise 3-7 Administrative Task Scripts

Exercise 3-8 Create a New Project and a New Solution

Exercise 3-9 Create a New Project in an Existing Solution

Exercise 3-10 Delete a Project from a Solution

Exercise 3-11 Using the Import and Export Wizard

Exercise 3-12 Configuring IP Settings in the GUI

Exercise 3-13 Opening the Windows Command Prompt

Exercise 3-14 Configuring the Windows Firewall

Exercise 4-1 Preparing Your System for the Batch File

Exercise 4-2 Create the Batch File

Exercise 4-3 Running the Batch File

Exercise 6-1 Preparing the Visio Environment for Entity Relationship Diagramming

Exercise 6-2 Creating the Visio Entity Relationship Diagram

Exercise 6-3 Creating Relationships Between Entities

Exercise 6-4 Creating an ER File

Exercise 6-5 Creating the Entities

Exercise 6-6 Creating Relationships

Exercise 6-7 Estimating the Size of a Clustered Index

Exercise 6-8 Estimating the Size of a Clustered Index Table

Exercise 8-1 Creating Multiple Filegroups

Exercise 8-2 Creating the Books Database in the GUI

Exercise 8-3 Detaching the Books Database in the GUI

Exercise 8-4 Attaching the Books Database in the GUI

Exercise 9-1 Assigning Collations at the Column Level

Exercise 9-2 Launching the Table Designer

Exercise 9-3 Creating Columns

Exercise 9-4 Selecting a Primary Key

Exercise 9-5 Specifying Table Properties and Saving the Table

Exercise 9-6 Scripting the Table for Documentation

Exercise 10-1 Setting the Primary Key

Exercise 10-2 Creating the LastName Nonclustered Index

Exercise 10-3 Creating the City Nonclustered Index

Exercise 10-4 Creating the Covering Index

Exercise 10-5 Creating the Filtered Index

Exercise 10-6 Creating a View

Exercise 11-1 Creating a DML Trigger

Exercise 11-2 Creating a DDL Trigger

Exercise 12-1 Installing Analysis Services

Exercise 12-2 Installing Integration Services

Exercise 12-3 Creating a Basic Integration Services Package

Exercise 12-4 Troubleshooting an SSIS Package with Debug

Exercise 12-5 Saving and Scheduling Packages

Exercise 12-6 Installing and Configuring Reporting Services

Exercise 12-7 Configuring Database Mail

Exercise 12-8 Creating a Full-Text Index

Exercise 13-1 Creating a T-SQL Job

Exercise 13-2 Creating the SSIS Export Package

Exercise 13-3 Creating a SSIS Job

Exercise 13-4 Creating a Batch File for Information Gathering

Exercise 13-5 Creating a Windows Command Job

Exercise 13-6 Creating an Operator

Exercise 13-7 Creating a SQL Server Alert in SSMS

Exercise 13-8 Creating a Free Drive Space Alert

Exercise 14-1 Generating a Deadlock Scenario

Exercise 14-2 Creating a Trace with SQL Server Profiler

Exercise 14-3 Creating a DTA Workload File in SQL Server Profiler

Exercise 14-4 Analyzing the Workload File with the DTA Tool

Exercise 14-5 Applying the Saved DTA Recommendations

Exercise 14-6 Installing the System Monitor in a Custom MMC

Exercise 14-7 Viewing Live Performance Data on Windows Server

Exercise 14-8 Viewing Live Performance Data on Windows Server

Exercise 14-9 Creating a Performance Counter Log in Windows Server

Exercise 14-10 Creating a Data Collector Set in Windows Server

Exercise 14-11 Adding SQL Server Counters to a Data Collector Set

Exercise 14-12 Creating the MDW for Performance Studio

Exercise 14-13 Setting Up Data Collection Options

Exercise 14-14 Viewing Performance Studio Reports

Exercise 14-15 Launching the Resource Monitor Directly

Exercise 15-1 Determining Read-Only Properties of Facets

Exercise 15-2 Importing the Microsoft Sample Policies

Exercise 15-3 Creating Custom Categories for Policies

Exercise 15-4 Subscribing to a Category

Exercise 15-5 Creating the conDatabaseSize Condition

Exercise 15-6 Creating the conPasswordRules Condition

Exercise 15-7 Creating the conSurfaceArea Condition

Exercise 15-8 Creating the polDatabaseSizeScheduled Policy

Exercise 15-9 Creating the polPasswordRulesPrevent Policy

Exercise 15-10 Creating the polSurfaceAreaLog Policy

Exercise 15-11 Creating a CMS in SSMS

Exercise 15-12 Creating Server Groups in the CMS

Exercise 15-13 Creating Custom Categories for Policies

Exercise 15-14 Creating an Automated Event Log Monitoring Solution

Exercise 16-1 Setting the Recovery Model in SSMS

Exercise 16-2 Creating a Full Backup of the Database

Exercise 16-3 Creating a Backup Device That Points to a File

Exercise 16-4 Restoring to a Point in Time

Exercise 16-5 Backing Up the Tail Log After a Database File Storage Failure

Exercise 16-6 Starting the SQL Server Database Engine in Single-User Mode

Exercise 16-7 Installing Windows Server Backup

Exercise 17-1 Using the MBSA Utility from Microsoft

Exercise 17-2 Creating Strong Password Policies in Windows Domains

Exercise 18-1 Configuring the Authentication Mode in SSMS

Exercise 18-2 Creating a SQL Login

Exercise 18-3 Viewing Local Password Policies

Exercise 18-4 Creating Windows Logins

Exercise 18-5 Creating a Database Role with SSMS

Exercise 18-6 Creating a Database User

Exercise 19-1 Installing the GPO Accelerator

Exercise 19-2 Creating a Custom Security Template

Exercise 19-3 Analyzing Security with Templates

Exercise 19-4 Creating a Baseline from Current Settings with SCW

Exercise 19-5 Permission Management in SQL Server Management Studio

Exercise 19-6 Enabling SQL Server 2008 Audit

Exercise 19-7 Using sp_configure to Configure the Surface Area

Exercise 19-8 Configuring the Surface Area with PBM

Exercise 19-9 Enabling the C2 Audit Trace

Exercise 20-1 Installing Windows Failover Clustering

Exercise 20-2 Running the Validate a Configuration Wizard

Exercise 20-3 Creating a Failover Cluster

Exercise 21-1 Creating the Mirroring Endpoints

Exercise 21-2 Implementing Log Shipping

Exercise 21-3 Generating a Log Shipping Report

Exercise 22-1 Configuring the Publisher and Distributor

Exercise 22-2 Creating a Publication with a Single Table to Replicate the Production.Product Table

Exercise 22-3 Creating a Subscription

Exercise 22-4 Exporting Data with the Import/Export Wizard

Introduction

Administering SQL Server 2008 is no simple task. As database management systems go, SQL Server 2008 is one of the most complex solutions available today. Offering more than just straightforward database management, SQL Server 2008 also includes data management for data transfer and transformation; data distribution through replication; and high availability through database mirroring and server clustering. The modern database administrator (DBA) must understand all of these components and more to successfully administer an efficient and secure data facility, and this book has been written for just such a DBA.

This book was written from two perspectives. First, it covers the most important administrative tasks that the DBA in organizations of all sizes will need to perform. These are covered with a real-world focus on SQL Server 2008 administration. Second, it covers the MCITP: Database Administrator 2008 exam objectives, 70-432 and 70-450, through the written pages of the book and the videos and practice exams on the included DVD. Whether you’re preparing for these exams or preparing for life as a DBA, you’ll find this book a useful reference.

Who Should Read This Book

As you can probably tell by the title of this book, SQL Server 2008 Administration: Real World Skills for MCITP Certification and Beyond, this book is primarily aimed at two groups: those seeking real-world SQL Server database administration knowledge and those preparing for the MCITP Database Administrator exams. Yet a third group may benefit from reading this book as well. Following are some descriptions of those who will find this book useful:

DBAs looking for a reference for common administrative tasks. Everything from backing up your databases to securing them is covered in this book. You’ll find coverage of the extra SQL Server 2008 components such as SQL Server Integration Services and SQL Server Reporting Services as well.Exam candidates preparing to take the MCITP: Database Administrator 2008 exams: 70-432 TS: Microsoft SQL Server 2008, Implementation and Maintenance and/or 70-450 PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008. You’ll find that all of the objectives are covered when you use the complete training kit this book provides, which includes the book, the practice exams on the included DVD, and the video training. It’s important to note that what you hold in your hands is more than just a book. The DVD includes video training and memory-jogging flashcards, as well as practice exams and more, to help you master the objectives of both MCITP exams.Programmers will also find value in this book. This book does not contain programming guidance or detailed explanations of the T-SQL language or CLR code; however, it does provide the programmer with a reference to the SQL Server 2008 functionality and how to install and manage the SQL Server that may just be used as the backend for their data-driven applications.

As you can see, this book is useful to several groups. I have worked as a systems engineer creating applications, which access SQL Servers, so I know the value of a good administration book sitting on my shelf, and I’ve striven to write this book with that in mind. I’ve also taken the 70-432 and 70-450 exams, so I understand the stresses related to preparing for these challenges and the inside information needed to pass them. And although I could have provided the exam information in a series of bulleted lists, I wrote this book from a practical perspective instead, because I feel that this approach makes the information easier to remember and it certainly makes it more valuable for your real life outside of the exam.

What You Will Learn

As you progress through this book, you will go from understanding what SQL Server 2008 has to offer to your organization to implementing it with all the bells and whistles it provides. You’ll learn to select the appropriate hardware for your servers and then install SQL Server 2008 right the first time. Then you’ll move on to learn how to use the administration tools from both the graphical user interface (GUI) of Windows and the command-line interface (my favorite place to be).

Next, you’ll learn how to design and implement database design solutions for SQL Server 2008. During this process, you’ll learn all about normal forms and database optimization and the many terms you’ll need to understand to master database administration and design. You’ll learn to create databases with the SQL Server Management Studio and with T-SQL code. Of course, part of the optimization process will be to implement indexes, so I’ll make sure you really understand what they are and how they work to improve the performance of your database queries. You’ll also learn to enforce rules and data standards by using triggers, stored procedures, and other advanced administration solutions.

Once your database is in place, you’ll need to provide ongoing support for that database. One of the first things you must implement is a working backup and recovery plan. You’ll learn how to do this by first learning to create jobs, operators, and alerts. Then you’ll learn to perform performance analysis and optimization and how to take advantage of the new Declarative Management Framework (DMF), also known as Policy-Based Management (PBM). And, of course, you’ll learn to back up and restore your databases. The primary focus of this ongoing administration process will be to standardize, automate, and update so that your workload is reduced over the long haul.

Once you have the maintenance plans in place, it’s time to think seriously about security, which is a very important issue for databases, networks, and anything else of value. There are three chapters in this section. First, you’ll learn about security threats and vulnerabilities. You’ll then move on to learn about authentication and encryption in detail. Finally, I’ll provide you with several best practices for securing a SQL Server 2008 environment.

The final section of the book is three chapters long and addresses SQL Server 2008 high availability and data distribution. You’ll learn about failover clustering, database mirroring, database snapshots, and data replication in these chapters.

Throughout the book, you’ll find real-world exercises that walk you through the processes required to implement and support commonly used features of SQL Server 2008. You’ll also find notes and warnings scattered throughout the book to help you understand more detailed concepts. Additionally, real-world scenarios provide you with insights into the daily life of a DBA or database consultant.

This book was written to address the complete collection of tasks the DBA will be required to perform in the real world, while also covering all exam topics so readers can pass their MCITP exams. Each section offers real-world exercises so you can learn with hands-on tasks. I have also provided videos of some of these exercises as well. These are available on the book’s DVD.

Yet it’s also important that you remember what this book is not; this book is not a programming reference. My goal here is not to teach you everything about the T-SQL language. That would require a 700+ page volume itself and is well beyond the scope of this book. However, I have good news for you: If you are new to T-SQL you will find an introduction to the T-SQL language and some code examples in the demonstration and training videos on the DVD that is included with this book. Additionally, you can visit www.TomCarpenter.net to find blog posts related to SQL Server and other technologies. In these posts, I often cover T-SQL best practices and optimization techniques.

What You Need

The exercises in this book assume that you are running SQL Server 2008 on Windows Server 2008 or later. If you are using Windows Server 2003 R2 or previous versions, the exercises should work in most cases; however, they were only tested on Windows Server 2008.

If you do not have a Windows Server 2008 machine, you might want to create a virtual machine so that you can go through every exercise in the book. Here are your options:

You can download Microsoft’s Windows Virtual PC from www.microsoft.com/windows/virtual-pc.You can also download a trial version of Windows Server 2008 from http://www.microsoft.com/windowsserver2008/en/us/trial-software.aspx and install it as a virtual machine within Windows Virtual PC. I recommend a machine with 4GB of RAM to perform virtualization.If your machine does not meet the requirements of Windows Virtual PC, you may be able to use the VMWare Player 3.0 found at www.vmware.com.

You will also need the SQL Server 2008 media for installation. If you do not have a licensed copy of SQL Server 2008, you have two choices.

First, you can download a trial version from Microsoft’s website at www.microsoft.com/SQLserver/2008/en/us/trial-software.aspx.Second, you can purchase the Developer Edition of SQL Server 2008. It usually costs between $50 and $70 and is exactly the same as the Enterprise Edition except for the licensing. The Developer Edition license allows you to develop solutions but not deploy them. For example, you cannot implement a production database server for your users with the Developer Edition; however, you can work through every exercise in this book using it.

What Is Covered in This Book

SQL Server 2008 Administration: Real World Skills for MCITP Certification and Beyond is organized to provide you with the information you need to effectively administer your SQL Server 2008 instances. The following list provides an overview of the topics covered in each chapter.

Part I—Introducing SQL Server 2008

Chapter 1—Understanding SQL Server’s Role: In this chapter, you will learn about the role of a database server and the various roles SQL Server 2008 can play in your organization.

Chapter 2—Installing SQL Server 2008: Master the SQL Server installation process by actually doing it in this chapter. You will install a named instance and also learn how to install a default instance. Each step of the installation process is covered in detail.

Chapter 3—Working with the Administration Tools: Take a tour of the administration tools provided with SQL Server and Windows Server and learn to use them to keep your SQL Servers running smoothly.

Chapter 4—SQL Server Command-Line Administration: This chapter teaches you how to use the command line and Windows PowerShell for SQL Server 2008 administration. You’ll learn to use the SQLCMD command and also how to use the general command-line commands that ship with Windows itself.

Part II—Designing Database Solutions

Chapter 5—Database Concepts and Terminology: It’s time to begin learning the theory behind database systems. You’ll learn all the important terms and what they mean. This chapter lays a foundation for the following chapters of the book.

Chapter 6—ERD and Capacity Planning: Have you heard of entity relationship diagramming? In this chapter, you learn what it is and how to use it by using common tools and free tools available on the Internet. You’ll also learn to estimate the capacity needs for a given database specification.

Chapter 7—Normalization: Enough Is Enough: Normalization is an important process and this chapter teaches you how to use it to optimize your database designs.

Part III—Implementing Database Solutions

Chapter 8—Creating SQL Server Databases: You will learn to create databases using the SQL Server Management Studio as well as T-SQL code in this chapter. This is where the theory meets reality in the SQL Server database system.

Chapter 9—Creating Tables: In order to create well-performing tables, you must understand data types. This chapter provides a reference of data types in SQL Server 2008 and how to choose the best data type for any situation. You’ll also learn the difference between a heap and a clustered index.

Chapter 10—Indexes and Views: Trainers like to talk about them. DBAs like to implement them. Now, you will learn what they really are and how they improve the performance of your databases. What are they? Indexes, of course. You’ll also learn about views and the benefits they provide.

Chapter 11—Triggers and Stored Procedures: Triggers and stored procedures are often used to centralize business rules or business logic. This chapter introduces the concepts and provides examples of both.

Chapter 12—Implementing Advanced Features: SQL Server 2008 provides some advanced functionality right out-of-the-box, and this chapter introduces these capabilities with coverage of SQL Server Analysis Services, SQL Server Reporting Services, and SQL Server Integration Services.

Part IV—Administration and Maintenance

Chapter 13—Creating Jobs, Operators, and Alerts: Now that your databases are in place, it’s time to maintain them. In this chapter, I introduce the Standardize, Automate, and Update (SAU) model of administration and provide steps for creating jobs, operators, and alerts.

Chapter 14—Performance Monitoring and Tuning: This chapter introduces you to the performance maintenance tools available in Windows Server and SQL Server 2008. You’ll learn how to track down performance problems and improve the responsiveness of your servers.

Chapter 15—Policy-Based Management: Also known as the Declarative Management Framework. This chapter teaches you how to implement Policy-Based Management from the ground up.

Chapter 16—Backup and Restoration: This final general administration chapter will focus on the very important task of backing up and restoring your databases. You’ll learn about the different backup types and how to implement them. You’ll also learn about the importance of recovery testing and recovery procedures.

Part V—SQL Server Security

Chapter 17—Security Threats and Principles: When administering databases, you are often managing the most valuable asset in modern organizations. For this reason, I take a very serious approach to security when it comes to SQL Server 2008. This chapter begins the security journey by evaluating threats and vulnerabilities in a SQL Server 2008 networked environment.

Chapter 18—Authentication and Encryption: Continuing from Chapter 17, this chapter moves on to the topics of authentication and encryption. You’ll learn how authentication helps to protect your environment and about the authentication options SQL Server provides. You’ll also learn to implement and manage encryption in SQL Server databases.

Chapter 19—Security Best Practices: In this third and final chapter on security, you’ll learn several best practices to help you maintain the security of your environment. You’ll learn to perform surface area reduction and auditing in this chapter.

Part VI—High Availability and Data Distribution

Chapter 20—SQL Server Failover Clustering: SQL Server 2008 supports the failover clustering feature of Windows Server, and this chapter introduces you to the topic of clustering and how it is implemented in a SQL Server 2008 environment.

Chapter 21—Database Mirrors and Snapshots: Database mirroring was brand new in SQL Server 2005, and it has been enhanced in SQL Server 2008. This chapter provides instruction for implementing database mirroring and database snapshots for point-in-time data recovery and analysis.

Chapter 22—Implementing Replication: The final chapter in the book introduces the features of SQL Server replication. You’ll learn about the different replication types and how to implement them. You’ll also learn how to configure subscribers to receive the replicated data.

Appendixes

Appendix A: Appendix A provides an objectives map for exam 70-432 and exam 70-450. If you are studying for the exams, use this Appendix to find the portion of the book that covers the objectives you are studying currently.

Appendix B: Appendix B tells you all about the CD, including what’s on it, system requirements, how to use it, and troubleshooting tips.

Glossary: The final element of the book is the Glossary. You’ll find definitions of important terms related to SQL Server 2008 and the role of a DBA. If you’re preparing for the exams, be sure to read the Glossary on the morning of the exam. This action will ensure your understanding of the most important topics covered.

What’s on the CD

With this book, we are including quite an array of training resources. The CD offers sample videos, a PDF of the book, bonus exams, and flashcards to help you study for certification candidates. The CD’s resources are described here:

Sample Videos Throughout the book, I have included numerous hands-on exercises showing you how to perform a variety of tasks. For some of these tasks, I have also included Camtasia video-walkthrough on the CD. Look for the CD icon for exercises that include video walkthroughs.

The Sybex E-book Many people like the convenience of being able to carry their whole book on a CD. They also like being able to search the text via computer to find specific information quickly and easily. For these reasons, the entire contents of this book are supplied on the CD, in PDF. We’ve also included Adobe Acrobat Reader, which provides the interface for the PDF contents as well as the search capabilities.

The Sybex Test Engine Since this book is also a supplement for MCITP: SQL Server 2008 DBA candidates, we have also included two bonus exams, one practice exam for TS: Microsoft SQL Server 2008, Implementation and Maintenance (70-432) and one for IT Pro: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008 (70-450).

Sybex Flashcards The “flashcard” style of question offers an effective way to quickly and efficiently test your understanding of the fundamental concepts.

How to Contact the Author

If you have any questions on your certification or administration journey, please contact me. My email address is [email protected], and I always respond when I receive an email from a reader. More than a decade ago, I sent an email to a well-known author and he responded. I was shocked because I had never gotten a response from any other author I’d written. I told myself then that, if I ever had the chance to write a book, I would respond to any and all email messages that I received. When I respond to your email, just remember that you have Mark Minasi to thank, since he was the author who responded to me. If you don’t hear back within a few days, please email me again. You know how spam filters are! This is my seventh book, and I still love hearing from my readers.

Finally, if you ever get the chance to attend one of my seminars on SQL Server or any other topic, please let me know you’ve read my book. I always enjoy speaking with my readers face-to-face and learning how to improve the books as well as how they have helped the reader. My speaking schedule is posted at www.SysEdCo.com and I look forward to seeing you at a future event.

Part I: Introducing SQL Server 2008

Chapter 1: Understanding SQL Server’s Role

Topics Covered in This Chapter:

What Is Information Technology?An Introduction to DatabasesDatabase ServersDatabase ApplicationsNew Features in SQL Server 2005 and SQL Server 2008 Core SQL Server Features

Microsoft SQL Server 2008 is a database management system that provides enterprise-class features for organizations of all sizes. If you are tasked with administering a SQL Server, you need to understand the various roles it can play within an organization. This understanding comes best by studying from the foundation up, and this chapter provides that foundation. From this foundation, you will move through this book to learn how to administer the essential aspects of SQL Server 2008. In addition, the contents of exams 70-432 (Microsoft SQL Server 2008, Implementation and Maintenance) and 70-450 (PRO: Designing, Optimizing, and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008) are covered throughout the book.

The first major topics you’ll tackle in this chapter are the concepts of information technology and the role a database or database system plays within this concept. Next, you’ll look at databases in more detail and gain an understanding of fundamental concepts that apply to all databases, not just SQL Server databases. Once you’ve sufficiently covered the general database concepts, you’ll investigate database servers and applications. Finally, you’ll explore SQL Server’s features and the roles SQL Server can play in modern organizations.

What Is Information Technology?

Many organizations differentiate between Information Systems (IS) and Information Technology (IT). In general, IS deals with software and system development, and IT is concerned with technology management. Certainly, IT is the collection of technologies and resources used to manage information. Organizations place great value on their information, as they should, and they expect the IT group to manage this information well. It is essential that those of us who work in IT remember the “I” stands for and that our primary responsibilities are to collect, retain, distribute, protect, and when appropriate destroy that information. When a single group is responsible for these tasks, consistency is accomplished and security can be achieved.