Microsoft SQL Server 2008 For Dummies E-Book

Mike Chapple

• Herausgeber: John Wiley & Sons
• Kategorie: Fachliteratur
• Sprache: Englisch

Seitenzahl: 391

Veröffentlichungsjahr: 2009

Beschreibung

If you're a database administrator, you know Microsoft SQL Server 2008 is revolutionizing database development. Get up to speed on SQL Server 2008, impress your boss, and improve your company's data management -- read Microsoft SQL Server 2008 For Dummies! SQL Server 2008 lets you build powerful databases and create database queries that give your organization the information it needs to excel. Microsoft SQL Server 2008 For Dummies helps you build the skills you need to set up, administer, and troubleshoot SQL Server 2008. You'll be able to: * Develop and maintain a SQL Server system * Design databases with integrity and efficiency * Turn data into information with SQL Server Reporting Services * Organize query results, summarizing data with aggregate functions and formatting output * Import large quantities of data with SSIS * Keep your server running smoothly * Protect data from prying eyes * Develop and implement a disaster recovery plan * Improve performance with database snapshots * Automate SQL Server 2008 administration Microsoft SQL Server 2008 For Dummies is a great first step toward becoming a SQL Server 2008 pro!

Leseprobe

Microsoft® SQL Server® 2008 For Dummies®

Table of Contents

Introduction

About This Book

Conventions Used in This Book

What You Are Not to Read

Foolish Assumptions

How This Book Is Organized

Icons Used in This Book

Where to Go from Here

Part I: Welcome to SQL Server 2008

Chapter 1: Introducing SQL Server 2008

Starting Off on the Right Foot

Examining SQL Server editions

Checking system requirements

Understanding the Basic Components of SQL Server

SQL and Transact-SQL

SQL Server components

Implementing Databases with SQL Server 2008

Setting up your server

Stocking it with databases

Accessing and updating your data

Managing and protecting what you’ve built

What’s New in SQL Server 2008

Declarative Management Framework

Encryption and Auditing

Resource conservation

Date/Time Data Types

Chapter 2: Building Your SQL Server

Installing SQL Server 2008

Choosing between default and named instances

Selecting an authentication mode

Choosing service accounts

Selecting the collation

Performing the installation

Upgrading an Existing SQL Server Installation

Preparing for an upgrade with Upgrade Advisor

Upgrading Your Installation

Configuring Database Mail

Using SQL Server’s Built-In Databases

Master database

The msdb database

The model database

The tempdb database

Chapter 3: Working with SQL Server Tools

Using SQL Server Configuration Manager

Launching SQL Server Configuration Manager

Starting and stopping services

Changing service accounts

Changing service start modes

Modifying networking settings

Managing Your Server with SQL Server Management Studio

Starting SSMS and connecting to an instance

Exploring the SSMS Interface

Issuing Transact-SQL queries

Working from the Command Line

Part II: Building SQL Server 2008 Databases

Chapter 4: Planning Your Database

Introducing Database Design Concepts

Understanding the Elements of a Database

Database servers

Organizing a Database

Defining your database objectives

Grouping data into tables

Selecting primary keys

Linking related tables

Diagramming Your Database

Staying Fit and Trim with Normalization

First normal form

Second and third normal forms

Choosing Data Types for Your Tables

Numeric data types

Date and time data types

Character string data types

Binary data types

Other data types

Working with NULL Values

Chapter 5: Creating Databases and Tables

Creating a Database

Altering database properties

Deleting or renaming a database

Specifying Files and Filegroups

SQL Server files

Using filegroups

Creating a Table

Getting started

Adding columns

Selecting a primary key

Modifying tables

Deleting tables

Chapter 6: Imposing Constraints and Relationships

Introducing Constraints

Controlling Database Contents Using Constraints

Filling in empty values with DEFAULT constraints

Enforcing Database Integrity

Enforcing uniqueness

Enforcing referential integrity with FOREIGN KEY constraints

Part III: Retrieving Data from Databases

Chapter 7: Constructing Simple Database Queries

Retrieving Data with SELECT Statements

The SELECT. . .FROM clause

The WHERE clause

Organizing Query Results

Sorting output

Summarizing data with aggregate functions

Grouping results

Renaming columns in your output

Chapter 8: Joins and Other Advanced Queries

Joining Data from Multiple Tables

Matching records with INNER JOINs

Including nonmatching records with OUTER JOINs

Joining a table with itself

Taking SELECT to the Next Level

Computing values

Managing complexity with subqueries

Dealing with different cases

Using Database Views

Creating a view

Modifying a view

Deleting a view

Chapter 9: Turning Data into Information with SQL Server Reporting Services

Setting up SQL Server Reporting Services

Creating an SSRS Report with Report Builder

Installing and starting Report Builder 2.0

Choosing a data source and data set

Laying out the report

Publishing the report

Working with Deployed

(Published) Reports

Viewing reports

Configuring report security

Part IV: Inserting and Manipulating Your Data

Chapter 10: Inserting, Updating, and Deleting Data

Inserting Small Quantities of Data

Understanding simple data entry

Writing INSERT statements

Modifying and Deleting Data

Modifying data with UPDATE

Removing data from a database

Importing Large Quantities of Data

Inserting query results

Copying bulk data with BULK INSERT

Performing blk operations from the command line with bcp

Working with SQL Server Integration Services

Chapter 11: Saving Time with Functions, Stored Procedures, and Triggers

Reusing Logic with Functions

Understanding types of functions

Leveraging SQL Server’s built-in functions

Calling built-in functions

Obtaining a list of built-in functions

Creating Your Own Functions

Reusing SQL Code with Stored Procedures

Saving time with system stored procedures

Writing your own stored procedures

Updating Data Automatically with Triggers

Creating a trigger

Disabling a trigger

Modifying and Deleting Functions, Stored Procedures, and Triggers

Modifying objects

Deleting objects

Part V: SQL Server Administration

Chapter 12: Keeping Your SQL Server Running Smoothly

Indexing Data to Improve Query Performance

Using clustered indexes

Creating nonclustered indexes

Optimizing index performance

Improving Performance with Partitions

Creating a partition function

Creating a partition scheme

Creating a partitioned table

Updating Database Statistics

Automatically updating statistics

Manually updating statistics

Managing File Sizes

Automatically shrinking database files

Manually shrinking a single database file

Manually shrinking all files associated with a database

Checking Database Integrity

Running DBCC CHECKDB

Correcting integrity errors

Governing Resource Consumption

Creating resource pools

Creating workload groups

Creating classifier functions

Activating and deactivating Resource Governor

Chapter 13: Automating SQL Server 2008 Administration

Scheduling Tasks with SQL Server Agent

Starting SQL Server Agent

Creating a SQL Server Agent job

Adding job steps to a SQL Server Agent job

Scheduling a SQL Server Agent job

Notifying someone when the job completes

Implementing Database Maintenance Plans

Identifying the tasks to include in a maintenance plan

Creating a maintenance plan

Alerting Administrators about Database Events

Configuring database operators

Creating SQL Server alerts

Chapter 14: Troubleshooting SQL Server 2008 Problems

Understanding the Inner Workings of SQL Server Queries

Creating a trace with SQL Server Profiler

Reviewing trace results

Reviewing Log Records

SQL Server error log

Windows Application Log

SQL Server Management Studio Log File Viewer

Monitoring Your Server with Performance Studio

Configuring Performance Studio

Reviewing performance data

Tuning Your Database with Database Engine Tuning Advisor

Chapter 15: Replicating Data across Multiple Servers

Understanding Replication

Server roles

Articles and publications

Replication types

Publishing Data with Snapshot Replication

Creating a distributor

Creating a publication

Subscribing to a Publication

Monitoring Replication

Part VI: Protecting Your Data

Chapter 16: Protecting Your Data from Prying Eyes

Creating and Managing Logins

Creating server logins

Removing database logins

Adding Database Users

Managing Rights with Roles

Understanding fixed server roles

Understanding fixed database roles

Creating database roles

Assigning users to database roles

Preserving Confidentiality with Encryption

Encrypting database connections

Encrypting stored data

Auditing SQL Server Activity

Enabling and configuring auditing

Reviewing audit records

Chapter 17: Preserving the Integrity of Your Transactions

Preserving Transaction Integrity with the ACID Model

Atomicity

Consistency

Isolation

Durability

Creating SQL Server Transactions

COMMIT or ROLLBACK?

Testing Transact-SQL statements with transactions

Changing the Transaction Isolation Level

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

SNAPSHOT

Handling Errors

Chapter 18: Preparing for Disaster

Backing Up Your Data

Backing up databases

Saving time with differential backups

Saving space with backup compression

Backing up the transaction log

Specifying Disaster Recovery Requirements with Recovery Models

Choosing a recovery model

Changing recovery models

Restoring Your Data after a Disaster

Using Database Snapshots

Creating a database snapshot

Accessing a database snapshot

Reverting to a database snapshot

Chapter 19: Staying Alive: High Availability in SQL Server 2008

Creating Redundancy with Database Mirroring

Choosing an operating mode

Configuring mirroring

Monitoring Database Mirroring

Failing over a mirrored database

Synchronizing Databases with Log Shipping

Configuring log shipping

Failing over to a log shipping secondary instance

Chapter 20: Implementing Policy-Based Management with the Declarative Management Framework

Coming to Terms with DMF

Creating DMF Policies

Creating a condition

Creating a policy

Using On Demand Evaluation Mode

Verifying policy compliance

Enforcing a policy manually

Automated Policy Enforcement

Viewing Policies Affecting a Target

Part VII: The Part of Tens

Chapter 21: Ten Ways to Keep Your SQL Server 2008 Databases Humming

Monitor Query Performance

Back Up Your Data Routinely

Verify Database Integrity Often

Tune the Physical Structure of Your Databases

Conserve Transaction Log Disk Space

Monitor Database Logs

Automate Administrative Alerts

Manage Multiple Servers

Simplify User Rights Administration with Roles

Perform Security Reviews

Chapter 22: Ten Database Design Tips

Plan Ahead

Draw Before You Click

Choose Primary Keys Carefully

Select Data Types with Space Efficiency in Mind

Make Sure Your Fields Are Single Purpose

Remember the Meaning of NULL

Normalize when Possible

Manage Your Relationships

Use Descriptive Names

Document Your Design

Microsoft® SQL Server® 2008 For Dummies®

by Mike Chapple

Microsoft® SQL Server® 2008 For Dummies®

Published byWiley Publishing, Inc.111 River St.Hoboken, NJ 07030-5774www.wiley.com

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

Published simultaneously in Canada

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.

Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, Making Everything Easier, and related trade dress 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. 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.

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 Website 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 Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites 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, please contact our Customer Care Department within the U.S. at 800-762-2974, outside the U.S. at 317-572-3993, or fax [phone].

For technical support, please visit www.wiley.com/techsupport.

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 Control Number: 2008933745

ISBN: 978-0-470-22465-6

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

About the Author

Mike Chapple, MCDBA, CISA, CISSP is an IT professional with over ten years’ experience with SQL Server. He currently serves as an IT professional with the University of Notre Dame, where he also teaches an undergraduate computer applications course. Mike actively participates as a subject matter expert in the SQL Server community and writes extensively on SQL Server at the About.com Guide to Databases. He also serves on the Center for Internet Security SQL Server security standard development team.

Mike is a technical editor for Information Security Magazine and is author of several books, including Information Security Illuminated and the CISSP Prep Guide. Mike holds a BS in computer science from the University of Notre Dame, an MS in computer science from the University of Idaho, and an MBA from Auburn University.

Dedication

To my family: Renee, Richard, Matthew, and Christopher who lovingly put up with me during the hours I spent buried in my laptop writing this book.

Author’s Acknowledgments

I would like to thank Kyle Looper and Susan Christophersen, my editors at Wiley, who provided me with invaluable assistance throughout the book development process. I also owe a debt of gratitude to my literary agent, Carole Jelen of Waterside Productions. Doug Couch served as technical editor for this title and was a great source of advice as we worked through some of the more difficult portions of the book. I’d also like to thank the many people who participated in the production of this book but I never had the chance to meet: the graphics team, production staff, and all those involved in bringing this book to press.

Publisher’s Acknowledgments

We’re proud of this book; please send us your comments through our online registration form located at www.dummies.com/register/.

Some of the people who helped bring this book to market include the following:

Acquisitions and Editorial

Project Editor: Susan Christophersen

Acquisitions Editor: Kyle Looper

Copy Editor: Susan Christophersen

Technical Editor: Doug Couch

Editorial Manager: Jodi Jensen

Editorial Assistant: Amanda Foxworth

Sr. Editorial Assistant: Cherie Case

Cartoons: Rich Tennant (www.the5thwave.com)

Composition Services

Project Coordinator: Katherine Key

Layout and Graphics: Carl Byers, Reuben W. Davis

Proofreader: Toni Settle

Indexer: Broccoli Information Management

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Andy Cummings, Vice President and Publisher

Mary Bednarek, Executive Acquisitions Director

Mary C. Corder, Editorial Director

Publishing for Consumer Dummies

Diane Graves Steele, Vice President and Publisher

Joyce Pepple, Acquisitions Director

Composition Services

Gerry Fahey, Vice President of Production Services

Debbie Stailey, Director of Composition Services

Introduction

I’ve been using SQL Server for longer than I care to admit. Let’s just say that I remember the days when Microsoft first released its own version of SQL Server after obtaining the rights to it from Sybase Corporation. That was a long time ago!

Why have I been using SQL Server for such a long time? Quite simply, I believe in its power as a user- and business-friendly database platform that’s readily accessible to users in most modern enterprises. It’s much more powerful than desktop databases such as Microsoft Access, and it’s rapidly gaining market share over the industry leader, Oracle.

SQL Server is unique in that it easily accommodates users with a wide range of experience. If you’re upgrading from Microsoft Access, you’ll find many of SQL Server’s graphical user interfaces friendly and familiar. On the other hand, if you’re a database professional moving from another platform, you’ll find that the ability to directly issue commands to the database accelerates your learning curve.

About This Book

This book provides you with an introduction to many of the commonly used features of SQL Server 2008. You’ll find that it’s an excellent starting point for anyone beginning to use SQL Server and offers a great foundation for your database career. Some of the important issues I cover in this book include:

Choosing the appropriate edition of SQL Server for your needs

Orienting yourself to the SQL Server database management tools

Installing and configuring your first SQL Server 2008 database server

Designing your first database

Creating databases and tables in SQL Server 2008

Imposing constraints on database tables and creating inter-table relationships

Retrieving data from your database with simple and advanced Transact-SQL queries

Creating basic reports with SQL Server Reporting Services

Inserting data into your database via manual or bulk insertion

Using stored procedures, functions and triggers to automate database tasks

Keeping your database server running smoothly with indexes and partitions

Limiting resource consumption with SQL Server 2008’s new Resource Governor

Automating database administration with SQL Server Agent and Maintenance Plans

Troubleshooting and tuning SQL Server databases

Protecting your database with security controls, backups, and transactions

Creating high-availability database solutions for critical IT environments

Using the Declarative Management Framework to create policies covering multiple SQL Server installations

SQL Server 2008 is the most powerful database product ever released by Microsoft. In this book, I scratch the surface of this product’s powerful capabilities by providing you with the information you need to get up and running quickly.

Conventions Used in This Book

Throughout the book, I apply the following typography conventions to help guide you through some of the information I present:

Text that appears in this special font is certain to be a URL (Web address), e-mail address, filename, folder name, or code.

When I use a term that I think you might not be familiar with, I apply italics to that term to let you know that I go on to define it next.

When I tell you to choose menu commands, I do it like this: Choose File⇒Save, which means choose the File command and then choose the Save command.

When I want you to type a specific item, I put it in bold text.

What You Are Not to Read

There’s quite a bit of material in this book. Some of it will be more important to you than others, depending on the way you use SQL Server and your role within your organization. If you’re looking for a broad-based introduction to SQL Server, feel free to start reading at Chapter 1 and continue through the end of the book. Otherwise, I wrote each chapter with the intention that it stands on its own merit. Feel free to flip through the Table of Contents and skip directly to the chapters of most interest to you.

If you’re not involved in designing or modifying database structures, you can skip Chapters 4, 5, and 6.

If you’re not responsible for day-to-day administration of SQL Server, bypass Chapters 12, 13, 14, and 15.

Foolish Assumptions

I’ve made a few assumptions about you when writing this book. Here’s what I guessed:

You’re already comfortable using a computer and with basic use of the Windows operating system. You should feel comfortable starting programs and opening files.

You’re familiar with the Internet and know how to locate specific information using a search engine.

You’re familiar with the use of a simple spreadsheet, such as Microsoft Excel, to organize information. You may not know all the advanced features of such software, but you’re able to create a simple Excel spreadsheet.

If these assumptions don’t describe you, you might be starting with the wrong book. I suggest going out and picking up a copy of PCs For Dummies or Windows Vista For Dummies to help you get started.

How This Book Is Organized

This book is made up of seven parts that introduce you to Microsoft SQL Server 2008:

Part I: Welcome to SQL Server 2008 provides you with an overview of SQL Server 2008. You find out about the differences between SQL Server’s Express, Workgroup, Standard, and Enterprise editions so that you can select the one most appropriate for your needs. You also discover the decisions you need to make and actions you need to take to get your first SQL Server installation up and running.

Part II: Building Databases walks you through the process of creating your first database in a SQL Server environment. I explain the planning process you should follow to build your database according to accepted design principles and walk you through the process of diagramming your database on paper before implementing it for real. I then describe the process to create your database, design tables, and enforce relationships between tables.

Part III: Retrieving Data from Databases describes how to retrieve information from a SQL Server database. I introduce the Structured Query Language (SQL) and explain how you can use it to pull the exact information you need out of your database. I also describe some advanced database queries that allow you to combine information from multiple tables and take different actions based on the results of database queries.

Part IV: Inserting and Manipulating Your Data takes you beyond simple retrieval of data and describes how you get new data into a database and modify information that exists within a database table. I describe the use of SQL statements and bulk import tools to add information to database tables. You also discover how stored procedures, functions, and triggers can help you automate tedious database tasks.

Part V: SQL Server Administration is for those of you who have responsibility for administering SQL Server databases. You discover tips and tricks to help you keep your database operating in an optimal fashion by tuning performance parameters and governing resource utilization. I also provide you with advice on using SQL Server’s administration tools to make the server do the routine work for you. I conclude this section with chapters dedicated to troubleshooting SQL Server problems and administering multiple servers in the same environment.

Part VI: Protecting Your Data covers the basics you need to know to protect your SQL Server data from unwanted intruders and natural or technical disasters. You see how to implement access controls to limit the rights of database users and how to use encryption to protect your information from unauthorized access. I spend an entire chapter introducing the concept of transactions and explaining how they can protect the integrity of data stored within your database. Finally, you find out about techniques for backing up your database so that you can restore your data in the event of a disaster.

Part VI: The Part of Tens is in every regular For Dummies book that you will ever pick up. In the first chapter in this part, I describe ten ways you can keep your database operating efficiently. In the second chapter, I provide you with ten tips for properly designing new SQL Server databases.

Icons Used in This Book

Icons are little pictures in the margins of the book that emphasize a point to remember, a warning to be aware of, or a tip that I think you might find helpful. Here are the ones I use in this book:

These are bits of information that I want to draw your attention to.

This icon means that I’m alerting you to something critical or I want you to think long and carefully about any action you might be about to take.

The information that shows up next to this icon might be more than you need (or want) to know, so you can skip it if you want, or come back to it when you have more time.

Here’s a nugget of information that’s worth storing in your memory because you’ll need it from time to time.

Where to Go from Here

If you’re looking for a broad introduction to SQL Server, just start reading at Chapter 1 and don’t put the book down until you fall asleep or can’t bear to read my writing any longer!

On the other hand, if you’re looking for specific information about one aspect of SQL Server, feel free to pick and choose. Flip through the Table of Contents and select the chapters that interest you most. As I mentioned earlier, I wrote each chapter with the intention of making it a stand-alone chunk of information. Good luck in your SQL Server 2008 adventures!

Part I

Welcome to SQL Server 2008

In this part . . .

In this first part, I give you an overview of SQL Server 2008. I point out the differences between SQL Server’s various editions to help you figure out which one best suits your purposes. Here is where you also find out how to get your first SQL Server installation up and running.

Chapter 1

Introducing SQL Server 2008

In This Chapter

Understanding database basics

Choosing a SQL Server 2008 edition

Using SQL Server components

Implementing SQL Server databases

Finding additional information in SQL Server references

SQL Server 2008 is Microsoft’s enterprise-class database server, designed to compete with products such as Oracle and IBM’s DB2. According to a Gartner study, SQL Server is rapidly gaining momentum, possessing more than 17 percent of the worldwide database market in 2006.