professional microsoft sql server 2012 administration

Professional Microsoft® SQL Server ® 2012 Administration

Published by

John Wiley & Sons, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright © 2012 by John Wiley & Sons, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-1-118-10688-4

ISBN: 978-1-118-28684-5 (ebk)

ISBN: 978-1-118-28218-2 (ebk)

ISBN: 978-1-118-28388-2 (ebk)

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

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, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the

Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011,

fax (201) 748-6008, 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 specifi cally disclaim all warranties, including

without limitation warranties of fi tness 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 please contact our Customer Care Department within the

United States at (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with

standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media

such as a CD or DVD that is not included in the version you purchased, you may download this material at

http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Library of Congress Control Number: 2012933629

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Wrox Programmer to Programmer, and related trade dress

are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affi liates, in the United States and other

countries, and may not be used without written permission. Microsoft and SQL Server are registered trademarks of

Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc., is not

associated with any product or vendor mentioned in this book.

CONTENTS

INTRODUCTION xxxvii

CHAPTER 1: SQL SERVER 2012 ARCHITECTURE 1

SQL Server 2012 Ecosystem 1

New Important Features in 2012 2

Production DBA 2

Development DBA 2

Business Intelligence DBA and Developer 3

SQL Server Architecture 4

Database Files and Transaction Log 4

SQL Native Client 5

Standard System Databases 6

Schemas 8

Synonyms 8

Dynamic Management Objects 9

SQL Server 2012 Data Types 10

Editions of SQL Server 17

Edition Overview 17

Licensing 18

Summary 20

CHAPTER 2: INSTALLING SQL SERVER 2012 BEST PRACTICES 21

Planning the System 22

Hardware Options 22

Software and Install Options 27

Installing SQL Server 29

New Installs 30

Side-by-Side Installs 30

Upgrades 30

Unattended Installs 30

Attended Installations 36

Installing Analysis Services 40

Multidimensional and Data Mining Mode (UDM Mode) 41

Tabular Mode 42

Installing PowerPivot for SharePoint 43

Burning in the System 45

xx

CONTENTS

Post-Install Confi guration 45

Confi guring SQL Server Settings for Performance 46

tempdb 47

Confi guring SQL Server Setting for Security 49

Best Practices Analyzer (BPA) 50

SQL Server Confi guration Manager 50

Back It Up 51

Uninstalling SQL Server 51

Uninstalling Reporting Services 51

Uninstalling Analysis Services 51

Uninstalling the SQL Server Database Engine 52

Troubleshooting a Failed Install 52

Summary 52

CHAPTER 3: UPGRADING SQL SERVER 2012 BEST PRACTICES 53

Why Upgrade to SQL Server 2012? 53

Risk Mitigation — the Microsoft Contribution 54

Independent Software Vendors and SQL Community Contributions 54

Upgrading to SQL Server 2012 55

In-Place Upgrading 55

Side-by-Side Upgrade 57

In-Place Upgrade versus Side-By-Side Upgrade Considerations 58

Pre-Upgrade Steps and Tools 58

Pre-Upgrade Steps 58

Pre-Upgrade Tools 59

Backward Compatibility 67

Unsupported and Discontinued Features in SQL Server 2012 67

SQL Server 2012 Deprecated Database Features 67

Other SQL Server 2012 Changes Aff ecting Behavior 68

SQL Server Component Considerations 68

Upgrading Full-Text Catalog 68

Upgrading Reporting Services 68

Upgrading to 64-Bit 69

Post-Upgrade Checks 69

Poor Query Performance After Upgrade 69

Summary 70

CHAPTER 4: MANAGING AND TROUBLESHOOTING

THE DATABASE ENGINE 71

Confi guration and Administration Tools 71

SQL Server Confi guration Manager 72

Startup Parameters 73

xxi

CONTENTS

Startup Stored Procedures 77

Partially Contained Databases 78

Troubleshooting Tools 79

Dedicated Administrator Connection 79

Rebuilding the System Databases 81

Management Studio 82

Reports 82

Confi guring SQL Server in Management Studio 85

Filtering Objects 90

Error Logs 90

Activity Monitor 91

Monitoring Processes in T-SQL 96

sp_who and sp_who2 96

sys.dm_exec_connections 97

sys.dm_exec_sql_text 97

Multiserver Management 98

Central Management Servers and Server Groups 98

SQL Server Utility 99

Trace Flags 99

Getting Help from Support 101

SQLDumper.exe 101

SQLDiag.exe 102

Summary 104

CHAPTER 5: AUTOMATING SQL SERVER 105

Maintenance Plans 106

Maintenance Plan Wizard 106

Maintenance Plan Designer 109

Automating SQL Server with SQL Server Agent 111

Jobs 112

Schedules 117

Operators 118

Alerts 121

SQL Server Agent Security 126

Service Account 126

Access to SQL Agent 126

SQL Server Agent Proxies 127

Confi guring SQL Server Agent 130

General Properties 131

Advanced Properties 132

Alert System Properties 133

Job System Properties 133

xxii

CONTENTS

Connection Properties 134

History Properties 134

Database Mail 134

Architecture 135

Security 135

Confi guration 136

Archiving 140

Multiserver Administration 140

Using Token Replacement 140

Event Forwarding 143

Using WMI 143

Multiserver Administration — Using Master and Target Servers 145

Summary 146

CHAPTER 6: SERVICE BROKER IN SQL SERVER 2012 147

Asynchronous Messaging 147

SQL Service Broker Overview 148

SQL Server Service Broker Versus Other Message Queues 148

Confi guring SQL Server Service Broker 149

Enabling 149

Message Types 151

Contracts 151

Queues 152

Services 153

Routes 154

Priorities 156

Conversation Groups 156

Using SQL Server Service Broker 157

Sending Messages 157

Receiving Messages 160

Sending Messages Between Databases 161

Sending Messages Between Instances 162

External Activation 163

Summary 165

CHAPTER 7: SQL SERVER CLR INTEGRATION 167

Introduction to the CLR 167

SQL Server as a .NET Runtime Host 169

Application Domains 170

T-SQL versus CLR 170

Enabling CLR Integration 171

Creating CLR Assemblies 172

The Non-Visual Studio Way 172

xxiii

CONTENTS

Using Microsoft SQL Server Data Tools 174

Securing CLR 176

Performance Monitoring 177

Windows System Monitor 177

SQL Profi ler 178

Dynamic Management Views (DMVs) 179

CLR Integration Design Goals 180

Summary 180

CHAPTER 8: SECURING THE DATABASE INSTANCE 181

Authentication Types 181

SQL Authentication 182

Windows Authentication 183

SQL Versus Windows Authentication 183

Authorizing Securables 184

Server Securables 185

Database Securables 189

Permission Chains 190

Cross Database Permission Chains 191

Row Level Security 193

Summary 194

CHAPTER 9: CHANGE MANAGEMENT 197

Creating Solutions and Projects 198

Creating a Connection 199

Creating a Project Query 200

Policy-Based Management 200

Policy-Based Management Overview 201

Policy-Based Management Step by Step 202

Scripting Policy-Based Management 209

Policy-Based Management Implementation 210

DDL Trigger Syntax 212

Database Triggers 212

Server Triggers 217

Trigger Views 218

Scripting Overview 218

sqlcmd 219

PowerShell 223

Creating Change Scripts 225

Data-Tier Applications 225

SQL Server Data Tools 229

Version Tables 229

Summary 231

xxiv

CONTENTS

CHAPTER 10: CONFIGURING THE SERVER FOR OPTIMAL

PERFORMANCE 233

What Every DBA Needs to Know About Performance 234

The Performance Tuning Cycle 234

Defi ning Good Performance 235

Focus on What’s Most Important 236

What the Developer DBA Needs to Know About Performance 237

Users 237

SQL Statements 237

Data Usage Patterns 238

Robust Schema 238

What the Production DBA Needs to Know About Performance 238

Optimizing the Server 239

Hardware Management 241

CPU 241

x64 242

Cache 242

Hyper-threading 243

Multicore 244

System Architecture 246

Memory 248

Physical Memory 248

Physical Address Space 248

Virtual Memory Manager 249

The Page File 249

Page Faults 250

I/O 251

Network 252

Disks 252

Storage Considerations 255

Designing a Storage System 257

Large Storage System Considerations: SAN Systems 262

Server Confi guration 264

Fragmentation 269

Summary 271

CHAPTER 11: OPTIMIZING SQL SERVER 2012 273

Application Optimization 273

Defi ning a Workload 274

System Harmony Is the Goal 274

The Silent Killer: I/O Problems 274

xxv

CONTENTS

SQL Server I/O Process Model 275

Database File Placement 275

tempdb Considerations 276

Table and Index Partitioning 279

Why Consider Partitioning? 280

Creating a Partition Function 281

Creating Filegroups 284

Creating a Partition Scheme 284

Creating Tables and Indexes 285

Data Compression 290

Row Compression 290

Page Compression 291

Estimating Space Savings 293

Monitoring Data Compression 295

Data Compression Considerations 295

CPU Considerations 296

Cache Coherency 297

Affi nity Mask 297

Max Degree of Parallelism (MAXDOP) 300

Affi nity I/O Mask 301

Memory Considerations and Enhancements 302

Tuning SQL Server Memory 302

64-bit Versions of SQL Server 2012 305

Data Locality 306

Max Server Memory 307

Index Creation Memory Option 307

Minimum Memory per Query 308

Resource Governor 309

The Basic Elements of Resource Governor 309

Using Resource Governor from SQL Server 2012

Management Studio 313

Monitoring Resource Governor 314

Summary 315

CHAPTER 12: MONITORING YOUR SQL SERVER 317

The Goal of Monitoring 318

Determining Your Monitoring Objectives 318

Establishing a Baseline 318

Comparing Current Metrics to the Baseline 319

Choosing the Appropriate Monitoring Tools 319

Performance Monitor 321

CPU Resource Counters 322

xxvi

CONTENTS

Disk Activity 324

Memory Usage 330

Performance Monitoring Tools 333

Monitoring Events 335

The Default Trace 337

system_health Session 338

SQL Trace 338

Event Notifi cations 352

SQL Server Extended Events 355

Monitoring with Dynamic Management Views and Functions 376

What’s Going on Inside SQL Server? 377

Viewing the Locking Information 380

Viewing Blocking Information 380

Index Usage in a Database 381

Indexes Not Used in a Database 382

View Queries Waiting for Memory Grants 383

Connected User Information 384

Filegroup Free Space 384

Query Plan and Query Text for Currently Running Queries 385

Memory Usage 385

Buff er Pool Memory Usage 385

Monitoring Logs 386

Monitoring the SQL Server Error Log 386

Monitoring the Windows Event Logs 387

Management Data Warehouse 387

System Data Collection Sets 388

Viewing Data Collected by the System Data Collection Sets 388

Creating Your Own Data Collection Set 390

Examining the Data You Collected 392

SQL Server Standard Reports 393

System Center Management Pack 395

SQL Server Best Practice Analyzer 396

System Center Advisor 396

Summary 397

CHAPTER 13: PERFORMANCE TUNING T-SQL 399

Physical Query Processing Part One: Compilation

and Recompilation 399

Compilation 400

Recompilation 401

Tools and Commands for Recompilation Scenarios 408

xxvii

CONTENTS

Parser and Algebrizer 410

Optimization 412

Physical Query Processing Part Two: Execution 417

Database I/O Information 418

Working with the Query Plan 419

Estimated Execution Plan 420

Actual Execution Plan 424

Index Access Methods 427

Fragmentation 438

Statistics 439

Join Algorithms 440

Data Modifi cation Query Plan 443

Query Processing Enhancements on Partitioned Tables and Indexes 444

Gathering Query Plans for Analysis with SQL Trace 446

Summary 447

CHAPTER 14: INDEXING YOUR DATABASE 449

Noteworthy Index-Related Features in SQL Server 449

What’s New for Indexes in SQL Server 2012 450

Index Features from SQL Server 2008R2, SQL Server 2008,

and SQL Server 2005 452

Partitioned Tables and Indexes 455

Understanding Indexes 455

Creating Indexes 458

Why Use Both Partitioned Tables and Indexes? 459

Creating Partitioned Tables 460

Index Maintenance 461

Monitoring Index Fragmentation 462

Cleaning Up Indexes 462

Improving Query Performance with Indexes 464

Database Tuning Advisor 468

Too Many Indexes? 469

Summary 471

CHAPTER 15: REPLICATION 473

Replication Overview 473

Replication Components 474

Replication Types 476

Replication Enhancements in SQL Server 2012 478

Replication Models 478

Single Publisher, One or More Subscribers 478

xxviii

CONTENTS

Multiple Publishers, Single Subscriber 480

Multiple Publishers Also Subscribing 481

Updating Subscriber 482

Peer-to-Peer 483

Implementing Replication 484

Setting Up Snapshot Replication 484

Setting Up Distribution 484

Implementing Snapshot Replication 487

Implementing Transactional and Merge Replication 497

Peer-to-Peer Replication 498

Setting Up Peer-to-Peer Replication 498

Confi guring Peer-to-Peer Replication 499

Scripting Replication 502

Monitoring Replication 502

Replication Monitor 502

Performance Monitor 505

Replication DMVs 505

sp_replcounters 506

Summary 507

CHAPTER 16: CLUSTERING SQL SERVER 2012 509

Clustering and Your Organization 510

What Clustering Can Do 510

What Clustering Cannot Do 511

Choosing SQL Server 2012 Clustering for the Right Reasons 512

Alternatives to Clustering 512

Clustering: The Big Picture 514

How Clustering Works 515

Clustering Options 518

Upgrading SQL Server Clustering 520

Don’t Upgrade 520

Upgrading Your SQL Server 2012 Cluster In Place 520

Rebuilding Your Cluster 521

Back-Out Plan 523

Which Upgrade Option Is Best? 523

Getting Prepared for Clustering 523

Preparing the Infrastructure 523

Preparing the Hardware 524

Clustering Windows Server 2008 527

Before Installing Windows 2011 Clustering 527

Installing Windows Server 2008 Failover Clustering 528

Preparing Windows Server 2008 for Clustering 531

xxix

CONTENTS

Clustering Microsoft Distributed Transaction Coordinator 532

Clustering SQL Server 2012 534

Step by Step to Cluster SQL Server 534

Installing the Service Pack and Cumulative Updates 540

Test, Test, and Test Again 540

Managing and Monitoring the Cluster 542

Troubleshooting Cluster Problems 543

How to Approach Windows Failover Clustering

Troubleshooting 544

Doing It Right the First Time 544

Gathering Information 544

Resolving Problems 545

Working with Microsoft 545

Summary 546

CHAPTER 17: BACKUP AND RECOVERY 547

Types of Failure 548

Hardware Failure 548

Data Modifi cation Failure 548

Software Failure 550

Local Disasters 550

Making Plans 551

Backup/Recovery Plan 551

Disaster Recovery Planning 554

Creating the Disaster Recovery Plan 556

Maintaining the Plan 558

Overview of Backup and Restore 559

How Backup Works 559

Copying Databases 562

Backup Compression 570

Comparing Recovery Models 571

Choosing a Model 573

Switching Recovery Models 574

Backing Up History Tables 575

Permissions Required for Backup and Restore 576

Backing Up System Databases 577

Full-Text Backup 578

Verifying the Backup Images 578

How Restore Works 579

Preparing for Recovery 581

Recoverability Requirements 581

Data Usage Patterns 582

xxx

CONTENTS

Maintenance Time Window 583

Other High-Availability Solutions 584

Developing and Executing a Backup Plan 585

Using SQL Server Management Studio 585

Database Maintenance Plans 589

Using Transact-SQL Backup Commands 591

Managing Backups 593

Backup and Restore Performance 594

Performing Recovery 594

Restore Process 594

SQL Server Management Studio Restore 599

T-SQL Restore Command 602

Restoring System Databases 602

Archiving Data 604

SQL Server Table Partitioning 604

Partitioned View 605

Summary 606

CHAPTER 18: SQL SERVER 2012 LOG SHIPPING 607

Log Shipping Deployment Scenarios 608

Log Shipping to Create a Warm Standby Server 608

Log Shipping as a Disaster Recovery Solution 609

Log Shipping as a Report Database Solution 610

Log-Shipping Architecture 611

Primary Server 611

Secondary Server 611

Monitor Server 612

Log Shipping Process 612

System Requirements 613

Network 613

Identical Capacity Servers 613

Storage 614

Software 614

Deploying Log Shipping 614

Initial Confi guration 614

Deploying with Management Studio 616

Deploying with T-SQL Commands 624

Monitoring and Troubleshooting 624

Monitoring with Management Studio 625

Monitoring with Stored Procedures 626

Troubleshooting Approach 626

xxxi

CONTENTS

Managing Changing Roles 627

Synchronizing Dependencies 627

Switching Roles from the Primary to Secondary Servers 630

Switching Between Primary and Secondary Servers 632

Redirecting Clients to Connect to the Secondary Server 632

Database Backup Plan 633

Integrating Log Shipping with Other High-Availability Solutions 634

SQL Server 2012 Data Mirroring 634

Windows Failover Clustering 635

SQL Server 2012 Replication 635

Removing Log Shipping 636

Removing Log Shipping with Management Studio 636

Removing Log Shipping with T-SQL Commands 636

Log-Shipping Performance 637

Upgrading to SQL Server 2012 Log Shipping 638

Minimum Downtime Approach 638

With Downtime Approach 638

Deploy Log Shipping Approach 639

Summary 639

CHAPTER 19: DATABASE MIRRORING 641

Overview of Database Mirroring 641

Operating Modes of Database Mirroring 643

Database Mirroring In Action 645

Preparing the Endpoints 646

Preparing the Database for Mirroring 652

Initial Synchronization Between Principal and Mirror 653

Establishing the Mirroring Session 653

High-Safety Operating Mode Without Automatic Failover 655

High-Safety Operating Mode with Automatic Failover 655

High-Performance Operating Mode 657

Database Mirroring and SQL Server 2012 Editions 658

Database Mirroring Catalog Views 658

sys.database_mirroring 658

sys.database_mirroring_witnesses 660

sys.database_mirroring_endpoints 660

Database Mirroring Role Change 661

Automatic Failover 661

Manual Failover 664

Forced Failover 666

Database Availability Scenarios 667

Principal Is Lost 667