How To Manage A Database Server 2012

Ross

_Mistry

With Shirmattie Seenarine and Kevin E. Kline

Microsoft

SQL

Server

2012

Management

and

Administration

Introduction 1

Part I: SQLServer 2012 Database

_Engine

Administration and

Management Topics

1

_Installing

or

Upgrading

the Database

Engine

to

SQLServer 2012 13

What's New for Installation with SQLServer2012? 14

Preplanningand _Preparing a SQLServer 2012

Server Database Engine Installation 16

Gathering

Additional Information

_Necessary

to Proceed 25

Installing a New Installation of_SQLServer 2012 31

Upgrading

the Database

_Engine

to SQLServer 2012 40

Finalizingthe _SQL Server 2012 Installation or _Upgrade 51

Installing SQLServer 2012 onWindows Server Core 53

Managing

SQL Server 2012 Installations 62

Summary 64

Best Practices 64

2 _{Administering}and Configuringthe Database

Engine Settings

67

What's New for DBAs When Administering the

Database

_Engine

on

SQL

Server 2012 68

Administering SQLServer 2012 Server Properties 68

Administering the SQL Server Database

_Engine

Folders 87

Administering Database _Properties 98

SQLServer Database

_{Engine Management}

Tasks 114

Administering

the SQL Server _Agent 119

Summary 123

Best Practices 124

3 _{Administering Storage, I/O,}and Partitioning 127 What's New for DBAs When_{Administering Storage}

onSQL Server 2012 128

vi Microsoft®SQLServer 2012 _Management and Administration

Designingand _{Administering Storage} on SQL

Server 2012 140

Designingfor BLOB Storage 158

Designing and

_{Administrating}

Partitionsin

SQL Server2012 164

Data_Compression in SQL Server 2012 172

Summary 175

Best Practices 175

4 _Creating

_Packages

and

_Transferring

Data On-Premise

andtothe Cloud 179

What's New in Integration Services forSQL Server2012 180

Optionsfor

_Creating

Packages 181

Integration

Services _Packages 182

Developing Packages 189

Enhancing

Packages

199

Deploying

and _{Running IntegrationServices}

_Packages

212

Transferring Data with

_Integration

Services 221

Moving

Data to the Cloud 224

Summary

228

BestPractices 228

5

_Managing

and _{Optimizing SQL}Server 2012 Indexes 229 What's New for Indexes with _SQL Server 2012 229

The Importance ofIndexes 230

How Indexes Work ₂₃₀

General Index Characteristics ₂₃₈ Index _Design and _{Strategy 242}

Administering Indexes 247

Configuring

Indexes for Maximum Performance 268

Summary

280

Best Practices ₂₈₀

6

_Backing

_Up and

_Restoring

SQLServer 2012 Databases 283 What's New forBackup and

_Recovery

with _SQL

Server 2012? ₂₈₃

The _Importance of_{Backups 285}

Backupand

_Recovery

_Impacton Other Database

Engine Features 324

Summary 330

Best Practices 330

Part II:

_Hardening,

Auditing,and

_Securing

SQLServer 2012

7 _Hardeningand AuditingaSQL Server 2012 Implementation 335 What's New for _Hardeningand_Auditing

SQL Server 2012 _{Implementation?} 336 Windows andSQL Server Authentication 337

Using Configuration Tools to Hardenthe Installation 343

Hardening SQL Server Service Accounts 350

Installing

Service Packsand Critical Fixes 355

Leveraging SQL Server_{Auditing Strategies} 357

Monitoring Events withSQL ServerAudit 359 Additional _SQL Server _Hardening Recommendations 373

Summary 379

Best Practices 380

8 Administering SQLServer 2012Securityand Authorization 383 What's New for_Securityand Authorization with

SQL Server 2012? 383

SQL

Server _Security 385

Security ManagementDDL 398

Administering

SQLServer _Security 404

Contained Database Authentication 421

Summary

427

Best Practices 427

9

_Encrypting

SQLServer 2012 Data andCommunications 429 What's New for Encryption with SQLServer 2012? 430

Encryption in SQL 431

Column _Encryption 432

Securing

the Data

_Storage

435

Extensible Key Management 444

Transparent Data

_Encryption

446

viii Microsoft®SQL Server 2012 ManagementandAdministration

Using

Certificates 456

SQLServerand BitLocker Drive

_Encryption

465

Configuring

BitLocker Drive Encryption on a

SQLServer

_System

469

Summary 478

Best Practices 478

Part III: SQLServer 2012_{AlwaysOn High-Availability} and Disaster

RecoveryAlternatives

10 _Implementingand _{Managing AlwaysOn Availability Groups} 483

SQL Server 2012AlwaysOn Availability GroupsOverview 484

Implementing AlwaysOn Availability

Groups 490

Managing AlwaysOn Availability Groups 509

Monitoringand _{Troubleshooting AlwaysOn}

Availability Groups 520

Summary

525

BestPractices 526

11 _Implementingand _{Managing AlwaysOn} Failover

Cluster Instances 527

SQL Server 2012 AlwaysOn FailoverCluster

Instances Overview 527

What's New for SQLServer 2012 _AlwaysOn

Failover Cluster Instances? 528 Additional Elementsof AlwaysOn Failover

Cluster Instances 537

Implementing

a

Single-Instance

SQL Server 2012

FailoverCluster ₅₄₄

Implementinga Multiple-Instance SQLServer 2012

Failover Cluster 564

Multi-subnet _SQLServer 2012 FailoverCluster Overview 568

ImplementingMulti-subnet

_SQL

Server 2012

Failover Cluster ₅₇₁

Managing

FailoverClusters from a

SQL

Server 2012

Perspective 574

Verifying

the Status ofClustered Service and

Applications, Nodes,

Storage,

and Networks 574

Managing Failover Clusters from aWindows Server

Summary

Best Practices

12 Implementing and _ManagingDatabase _Mirroring

SQL Server 2012 Database MirroringOverview

Combining

Database _Mirroring withOther

_SQL

Server 2012 _Technologies

Implementing

a Database MirroringSession

Managing a Database _MirroringSession

Monitoringand Troubleshootinga Database

Mirroring

Session

Summary

Best Practices

13 Implementingand Managing Replication

SQL

Server 2012

_Replication

Essentials

Implementing SQLServerReplication Managing SQLServer 2012 Replication

Monitoringand _{Troubleshooting SQL} Server _Replication

Summary

Best Practices

Part IV: Performance_Tuning,

_{Monitoring, Troubleshooting,}

and Maintenance

14 Performance_Tuningand _{Troubleshooting SQL}Server 2012 Platform

_{Troubleshooting}

and Optimization

Database _{Troubleshooting}and _Optimization

Application Optimization and

_{Troubleshooting}

IntroducingExtended Events to Troubleshoot SQL Server

Summary

Best Practices

15 Monitoring SQLServer 2012

What's New for _Monitoring in _SQLServer 2012?

Gaining Quick

Insight

into a SQL Server System

The Data Collector and the _Management Data Warehouse

X

Creating Operators and Sending Email Alerts 763

Usingthe Windows Server 2008 R2 Performance

Monitoring

Tools 769

Additional Tools to Monitor SQL Server 2012 776

Summary 777

Best Practices 777

16 SQL Server 2012 Maintenance Practices 779 What's New for Maintenance with SQL Server 2012? 779

Establishinga SQL Server Maintenance Plan 780

Creating a Maintenance Plan 792

Establishing Maintenance Schedules for_SQLServer 805

Summary 807

Best Practices 807

Part V: Multi-Instance Management, Consolidation,and Private Clouds

17 Implementingand Managing Policy-Based Management 811 Introductionto

_{Policy-Based Management}

811

Policy-Based Management Concepts 813

Implementing Policy-Based

Management

819

Monitoringand _Enforcing Best Practices _{by Using}

Policy-Based Management 830

Summary 836

Best Practices 836

18 _Managing Workloads withResource Governor 839 What's New forResource Governor in SQLServer2012 840 Overviewof Resource Governor

_Concepts,

_Workflow,and

Scenarios 841

Implementing

and _{Configuring Resource Governor 845}

Creatinga Workload Group 852

Understanding Default Transact-SQL Syntax with

Resource Governor ₈₅₈

Managing Resource Governor 860

Monitoring Resource Governor 862

Summary

867

19 Consolidation, Virtualization,and Private Clouds 869

Understanding

Consolidation, Virtualization,

and Private Cloud

_Strategies

870

PlanningYourImplementation ofHyper-V 878 Installation ofthe MicrosoftHyper-V Server Role 883

Becoming Familiar with the _Hyper-V Administrative

Console 886

Installing

a

SQL

Server 2012 Guest

Operating

System Session 889

Modifying

SQLServer 2012 Guest Session

Configuration

Settings

893

Launchinga Hyper-V Guest Session 898

Using

Snapshotsof GuestOperating System Sessions 900

Optimizing SQL Server 2012 for Private Cloud 903

Summary 905

Best Practices 905