Beginning SQL Server 2008 Administration

Contents at a Glance

Table of Contents

About the Authors

About the Technical Reviewer

Acknowledgments

Introduction

Who This Book Is For

How This Book Is Structured

Getting the Most from This Book

Errata

Contacting the Authors

Chapter 1: The Database Administration Profession

Why Do We Need a DBA? Access Runs Great!

What Exactly Does a DBA Do Anyway?

Database Maintenance

Security and Regulatory Compliance

Disaster Recovery

Design and Performance Improvement

Documentation

Salary Information

Words from Real-World DBAs

Grant Fritchey, Principal DBA

Roman Rehak, Principal Database Architect

Summary

Chapter 2: Planning and Installing SQL Server 2008

Understanding the Editions

Demystifying 32-Bit and 64-Bit Architectures

Server Editions

Enterprise Edition (x86, x64, and IA64)

Standard Edition (x86 and x64)

Specialized Editions

Developer Edition (x86, x64, and IA64)

Workgroup (x86 and x64)

Web (x86 and x64)

Express (x86 and x64)

Compact (x86 Only)

Evaluation (x86, x64, and IA64)

SQL Server Terminology

Planning Your Installation

Assessing the Environment

Choosing the Right Edition

Validating Hardware and Software Requirements

Installing SQL Server Evaluation Edition

Step 1: Downloading SQL Server Evaluation Edition

Step 2: Understanding the SQL Server Installation Center

Planning Tab

Installation Tab

Maintenance Tab

Tools Tab

Resources Tab

Advanced Tab

Options Tab

Step 3: Installing an Instance of SQL Server

Feature Selection Page

Instance Configuration Page

Disk Space Requirements Page

Server Configuration Page

Database Engine Configuration Page

Error and Usage Reporting Page

Installation Rules Page

Ready to Install Page

Upgrading SQL Server

A Word from the SQL Server Setup Team

Summary

Chapter 3: What’s in the Toolbox?

SQL Server Management Studio

Connecting to SQL Server

Issuing Queries Using SSMS

Object Explorer

Writing the Query

Document Windows

Results Pane

Managing Multiple Servers

Registered Servers

Queries Against Multiple Servers

Policy-Based Management

Monitoring Server Activity

Activity Monitor

Performance Data Collector

Reports

Other Tools from the Start Menu

Analysis Services Folder

Configuration Tools Folder

SQL Server Installation Center

SQL Server Error and Usage Reporting

Reporting Services Configuration Manager

SQL Server Configuration Manager

Documentation and Tutorials Folder

Microsoft SQL Server Samples Overview

SQL Server Books Online

SQL Server Tutorials

Integration Services Folder

Data Profiler Viewer

Execute Package Utility

Performance Tools Folder

Database Engine Tuning Advisor

SQL Server Profiler

Import and Export Data Wizard

Business Intelligence Development Studio (BIDS)

Command-Line Tools

SQLCMD

Connecting to SQL Server

Passing Variables

PowerShell Provider for SQL Server

Summary

Chapter 4: Creating Tables and Other Objects

Navigating the Object Explorer Tree

Scripting the Actions of an SSMS Dialog Box

Transact-SQL (T-SQL) Primer

Data Definition Language (DDL)

Data Manipulation Language (DML)

Data Control Language (DCL)

Creating Tables

Creating Tables from the Table Designer

Issuing the CREATE TABLE Statement

Altering Tables

Adding Constraints

NULL Constraints

CHECK Constraints

Primary Key and Unique Constraints

Foreign Key Constraints

Dropping Tables

Creating Indexes

Summary

Chapter 5: Transact-SQL

The VetClinic Sample Database Revisited

Data Types

Unicode vs. ANSI

Living with NULL

Data Manipulation Language

SELECT

INSERT

UPDATE

DELETE

Transactions

Execution

Transaction Isolation

Deadlocks

Stored Procedures

Creating Stored Procedures Using Templates

Modifying Stored Procedures

System Stored Procedures

Functions

Creating a Function

Invoking a Function

System-Defined Functions

Triggers

Summary

Chapter 6: SQL Server Internals

Databases

Master Database

Tempdb Database

Model Database

MSDB Database

Resource Database

Repairing Corrupt System Databases

Writing Data to Disk

SQL Server Services

Single-User Mode

Placing an Already-Started Database into Single-User Mode

Starting SQL Server in Single-User Mode

Summary

Chapter 7: Storage Management Strategies

Storage Systems

Storage System Interfaces

IDE ATA

SATA

SCSI

Serial Attached SCSI

Fibre Channel Direct Attached Storage

Fibre Channel Storage Area Network

iSCSI

InfiniBand

Storage System Types

Direct Attached Storage

Storage Area Network

Logical Unit Numbers (LUNs)

Network Attached Storage

Figure 7-3. NAS architecture

Disk Configuration: RAID

RAID 0 (Striping)

RAID 1 (Mirroring and Duplexing)

RAID 10 (Stripe of Mirrors)

RAID 5 (Striping with Parity)

Hardware and Software RAID

Selecting a Storage System for SQL Server 2008

I/O Performance

Redundancy Is Critical

Configuring Your Database

Data Compression

Disk Volume Alignment

Summary

Chapter 8: Database Backup Strategies

Defining the Types of Backups

Full Backups

Full Backup Using SQL Server Management Studio

Full Backup Using T-SQL

Differential Backups

File and Filegroup Backups

Backing Up the Logs

Initiating Fully Recovery Mode

Log Backup Using Management Studio

Log Backup Using T-SQL

Backing Up a Copy of the Database

Automating the Backup Process

Encrypting Backup Files for Security

Compressing Backups to Save Space

Designing a Backup Plan

Summary

Chapter 9: Database Restore Strategies

Restoring a Database from the Management Studio GUI

Specifying Restore Options from the GUI

Restoring a Database Using T-SQL

Executing a Simple Restore

Creating a Copy of a Database

Retrieving Logical and Physical File Names

Displaying General Information About a Backup

Cleaning Up

Restoring Differential Backups

Restoring Log Backups

Restoring File Backups

Testing Backups

Developing a Full Disaster Recovery Plan

Gathering Information About the Business

Establishing the Disaster Plan

Scenario 1: Large Commercial Insurance Company

Scenario 2: Small Online Retail Business

Testing the Disaster Plan

Summary

Chapter 10: Common Database Maintenance Tasks

Backing Up and Restoring

Checking the Database Integrity

Shrinking the Database

Reorganizing and Rebuilding Indexes

Detecting Undue Fragmentation

Rebuilding an Index

Reorganizing an Index

Getting Updated Statistics

Checking the Status of Automatic Statistics Gathering

Manually Updating Statistics

Monitoring Logs

Creating SQL Server Agent Jobs

Connecting to SQL Server

Creating an Agent Job

Defining Alerts

Summary

Chapter 11: Automation Through SQLCMD and PowerShell

Scripting in SQLCMD

Executing Commands Interactively

Executing Script in Batch Mode

Testing SQLCMD Scripts

Using SQLCMD to Back Up a Database

Generating Scripts from Management Studio

Scripting in PowerShell

Configuring PowerShell for Use With SQL Server

Understanding the Components

Starting PowerShell in Interactive Mode

Specifying an Execution Policy

Executing a PowerShell Script Interactively

Running PowerShell Scripts in Batch Mode

Running PowerShell from SQL Server Agent

Running PowerShell Scripts from Maintenance Plans

Using the PowerShell Integrated Scripting Environment

Summary

Chapter 12: Database Maintenance Plans

Understanding the Fundamentals

Creating a Maintenance Plan

Starting the Maintenance Plan Wizard

Specifying Plan Properties

Specifying Job Schedule Properties

Selecting Maintenance Tasks

Selecting Maintenance Task Order

Configuring Individual Tasks

Check Database Integrity

Shrink Database

Reorganize Index

Rebuild Index

Update Statistics

History Cleanup

Back Up Database (Full)

Maintenance Cleanup

Select Report Options

Completing the Maintenance Plan Wizard

Modifying an Existing Maintenance Plan

Summary

Chapter 13: Performance Tuning and Optimization

Measuring SQL Server Performance

Performance Counters

Performance Monitor

Dynamic Management Views

Data Collector

Setting Up the Data Collector

Viewing the Data Collector Data

Server Activity History

Disk Usage Summary

Query Statistics History

Tuning Queries

Understanding Execution Plans

Gathering Query Information with Profiler

Working with Trace Data

Using Trace to Capture Execution Plans

Using the Database Engine Tuning Advisor

Managing Resources

Limiting Resource Use

Leveraging Data Compression

Summary

Chapter 14: SQL Server Security

Terminology

Authentication

Authentication Mode

Authorization

Server Instance vs. the Database

SQL Server Instance Security

Creating a SQL Server Login

Server Roles

Server Permissions

Endpoints

Database Security

Database Users

Schemas

An Example of the “Wrong” Way

The “Right” Way

Four-Part Naming Convention

Default Schema

Reassigning Schema Ownership

Fixed Database Roles

Database Permissions

Flexible Database Roles

Security Functions

fn_my_permissions() Function

HAS_PERMS_BY_NAME Function

Summary

Chapter 15: Auditing, Encryption, and Compliance

Auditing in SQL Server 2008

Auditing Objects

Server Audit Object

Server Audit Specification Object

Database Audit Specification Object

Encryption

Encryption Primer

Password-Based Encryption

Certificate-Based Encryption

Transparent Data Encryption

Validating Server Configuration

The Need for a Policy

Create Policy on a Local Server

Evaluate the Policy

Using the Central Management Server

Summary

Chapter 16: SQL Server in the Enterprise

Systems Center Operations Manager 2007

Installing the SQL Server SCOM Management Pack

Getting Familiar with the SQL Server Management Pack

Finding and Resolving an Issue

Using System Center Data Protection Manager

Setting Up a Protection Group

Executing DPM Reports

Recovering a Databases

Summary

Chapter 17: Where to Go Next?

Visit SQL Server Books Online

Keep Up with Public SQL Server Websites

Attend a Conference

Find a Local Users Group

Create a Lab System and Practice

Get Certified!

Index