Professional Microsoft SQL Server 2012 Administration

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

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

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

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

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

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

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

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

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

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

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 

Using Data Connections in Excel 820

PerformancePoint Data Refresh 826

Visio Services Data Refresh 827

PowerPivot Data Refresh 829

Summary 836

CHAPTER 24: SQL AZURE ADMINISTRATION

AND CONFIGURATION 837

Introduction to SQL Azure 837

SQL Azure Architecture 838

Client Layer 838

Services Layer 838

Platform Layer 839

Infrastructure Layer 839

Confi guring SQL Azure 839

Server and Database Provisioning 840

Throttling and Load Balancing 844

Confi guring SQL Azure Firewalls 845

Connecting to SQL Azure 847

Administering SQL Azure 848

Creating Logins and Users 848

Assigning Access Rights 850

Working with SQL Azure 850

Backups with SQL Azure 852

Object Explorer for SQL Azure 852

What’s Missing in SQL Azure 854

Summary 855

CHAPTER 25: ALWAYSON AVAILABILITY GROUPS 857

Architecture 858

Availability Group Replicas and Roles 858

Availability Modes 859

Types of Failover Supported 859

Allowing Read-Only Access to Secondary Replicas 860

Availability Group Example 862

Confi gure a New Availability Group 862

Confi gure an Existing Availability Group 870

Availability Group Failover Operation 872

Suspend an Availability Database 873

Resume an Availability Database 874

Client Application Connections 874

Active Secondary for Secondary Read-Only 875

Read-Only Access Behavior 876

Secondary Replica Client Connectivity 876

Performance 878

Backup on the Secondary Replica 879

Evaluate Backup Replicas Metadata 880

AlwaysOn Group Dashboard 881

Monitoring and Troubleshooting 883

Summary 884

INDEX 885