9781118859131

TERM	PRICE	DUE
SEMESTER	$33.62	Aug 02

Learn to take advantage of the opportunities offered by SQL Server 2014

Microsoft's SQL Server 2014 update means big changes for database administrators, and you need to get up to speed quickly because your methods, workflow, and favorite techniques will be different from here on out. The update's enhanced support of large-scale enterprise databases and significant price advantage mean that SQL Server 2014 will become even more widely adopted across the industry. The update includes new backup and recovery tools, new AlwaysOn features, and enhanced cloud capabilities. In-memory OLTP, Buffer Pool Extensions for SSDs, and a new Cardinality Estimator can improve functionality and smooth out the workflow, but only if you understand their full capabilities. Professional Microsoft SQL Server 2014 is your comprehensive guide to working with the new environment. Authors Adam Jorgensen, Bradley Ball, Ross LoForte, Steven Wort, and Brian Knight are the dream team of the SQL Server community, and they put their expertise to work guiding you through the changes.

Improve oversight with better management and monitoring
Protect your work with enhanced security features
Upgrade performance tuning, scaling, replication, and clustering
Learn new options for backup and recovery

Professional Microsoft SQL Server 2014 includes a companion website with sample code and efficient automation utilities, plus a host of tips, tricks, and workarounds that will make your job as a DBA or database architect much easier. Stop getting frustrated with administrative issues and start taking control. Professional Microsoft SQL Server 2014 is your roadmap to mastering the update and creating solutions that work.

Adam Jorgensen is the President of Pragmatic Works and the Executive Vice President of PASS with more than 15 years experience.

Bradley Ball is a MCITP 2005, 2008, and 2012 MCSE DBA with more than a decade of experience who is currently the Data Platform Management Lead with Pragmatic Works specializing in Data Platform solutions.

Steven Wort has been working with SQL Server since 1993, who joined Microsoft in 2000, and now works as an Architect in the CRM Service Engineering team.

Ross LoForte is a Technology Architect at the Microsoft Technology Center Chicago focused on Microsoft SQL Server solutions, with more than 20 years of business development, project management, and SQL experience.

Brian Knight is the founder of Pragmatic Works and co-founder of SQLServerCentral.com and JumpStartTV.com

Wrox Professional guides are planned and written by working programmers to meet the real-world needs of programmers, developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.

INTRODUCTION xxxvii

CHAPTER 1: SQL SERVER 2014 ARCHITECTURE 1

SQL Server 2014 Ecosystem 1

New Important Features in 2014 2

Production DBA 2

Development DBA 3

Business Intelligence DBA and Developer 4

SQL Server Architecture 5

Database Files and Transaction Log 5

SQL Server Native Client 6

Standard System Databases 7

Schemas 9

Synonyms 10

Dynamic Management Objects 10

SQL Server 2014 Data Types 12

Editions of SQL Server 18

Edition Overview 18

Licensing 21

Summary 22

CHAPTER 2: INSTALLING SQL SERVER 2014 BEST PRACTICES 23

Planning the System 24

Hardware Options 24

Software and Install Options 29

Installing SQL Server 32

New Installs 32

Side-by-Side Installs 32

Upgrades 33

Attended Installations 33

Unattended Installs 37

Installing Analysis Services 43

Multidimensional and Data Mining Mode (UDM Mode) 44

Tabular Mode 46

Installing PowerPivot for SharePoint 47

Burning in the System 48

Post-Install Configuration 49

Configuring SQL Server Settings for Performance 49

tempdb 51

Configuring SQL Server Settings for Security 52

SQL Server Configuration Manager 54

Back It Up 54

Uninstalling SQL Server 54

Uninstalling Reporting Services 55

Uninstalling Analysis Services 55

Uninstalling the SQL Server Database Engine 55

Troubleshooting a Failed Install 55

Summary 56

CHAPTER 3: UPGRADING SQL SERVER 2014 BEST PRACTICES 57

Why Upgrade to SQL Server 2014? 57

Risk Mitigation—the Microsoft Contribution 58

Independent Software Vendors and SQL Community Contributions 59

Upgrading to SQL Server 2014 59

In-Place Upgrading 60

Side-by-Side Upgrade 62

In-Place Upgrade versus Side-by-Side Upgrade Considerations 62

Pre-Upgrade Steps and Tools 63

Pre-Upgrade Steps 63

Pre-Upgrade Tools 64

Backward Compatibility 72

Unsupported and Discontinued Features in SQL Server 2014 72

SQL Server 2014 Deprecated Database Features 74

Other SQL Server 2014 Changes Affecting Behavior 74

SQL Server Component Considerations 75

Upgrading Full-Text Catalog 75

Upgrading Reporting Services 75

Upgrading to 64-Bit 76

Post-Upgrade Checks 76

Summary 77

CHAPTER 4: MANAGING AND TROUBLESHOOTING THE DATABASE ENGINE 79

Configuration and Administration Tools 80

SQL Server Configuration Manager 80

Startup Parameters 82

Startup Stored Procedures 84

Partially Contained Databases 86

Troubleshooting Tools 87

Dedicated Administrator Connection 87

Rebuilding the System Databases 89

SQL Server Management Studio 90

Reports 90

Configuring SQL Server in SQL Server Management Studio 93

Filtering Objects 97

Error Logs 97

Activity Monitor 98

Monitoring Processes in T-SQL 104

sp_who and sp_who2 104

sys.dm_exec_connections 105

sys.dm_exec_sql_text 105

Multiserver Management 106

Central Management Servers and Server Groups 106

Trace Flags 107

Getting Help from Support 109

SQLDumper.exe 109

SQLDiag.exe 110

Summary 112

CHAPTER 5: AUTOMATING SQL SERVER 113

Maintenance Plans 114

Maintenance Plan Wizard 114

Maintenance Plan Designer 119

Automating SQL Server with SQL Server Agent 122

Jobs 123

Schedules 128

Operators 129

Alerts 133

SQL Server Agent Security 138

Service Account 138

Access to SQL Agent 138

SQL Server Agent Proxies 139

Configuring SQL Server Agent 143

General Properties 143

Advanced Properties 144

Alert System Properties 145

Job System Properties 146

Connection Properties 147

History Properties 147

Multiserver Administration 147

Using Token Replacement 148

Event Forwarding 150

Using WMI 151

Multiserver Administration—Using Master and Target Servers 152

Summary 154

CHAPTER 6: SERVICE BROKER IN SQL SERVER 2014 155

Asynchronous Messaging 155

SQL Service Broker Overview 157

SQL Server Service Broker versus Other Message Queues 158

Configuring SQL Server Service Broker 159

Setting Broker State 159

Message Types 161

Contracts 162

Queues 163

Services 165

Routes 166

Priorities 167

Conversation Groups 168

Using SQL Server Service Broker 169

Sending Messages 169

Receiving Messages 172

Sending Messages between Instances 174

External Activation 176

Log User Example 177

Summary 184

CHAPTER 7: SQL SERVER CLR INTEGRATION 185

Introduction to CLR 185

SQL Server as a .NET Runtime Host 187

Application Domains 187

T-SQL versus CLR 187

Enabling CLR Integration 188

Creating CLR Assemblies 189

The Non–Visual Studio Way 189

Using Microsoft SQL Server Data Tools 191

CLR Integration Security 193

Performance Monitoring 194

Windows System Monitor 194

Extended Events 196

Dynamic Management Views (DMVs) 197

CLR Integration Design Goals 197

Summary 198

CHAPTER 8: SECURING THE DATABASE INSTANCE 199

Authentication Types 200

SQL Authentication 200

Windows Authentication 201

SQL versus Windows Authentication 202

Logins and Users 202

Authorizing Securables 202

Server Securables 203

Database Securables 209

Permission Chains 210

Cross-Database Permission Chains 211

Row-Level Security 213

Summary 214

CHAPTER 9: IN-MEMORY OLTP 215

Using and Implementing In-Memory OLTP 215

Enabling In-Memory OLTP 216

In-Memory OLTP Structures 218

Records 218

Indexes 219

CPU Considerations 224

Virtualization Considerations 224

Memory Considerations 225

Managing Memory with Resource Governor 227

Creating Natively Compiled Stored Procedures 230

Overview of the Analyze, Migrate, and Report (ARM) Tool 233

Summary 246

CHAPTER 10: CONFIGURING THE SERVER FOR OPTIMAL PERFORMANCE 247

What Every DBA Needs to Know About Performance 248

The Performance Tuning Cycle 248

Configuration 250

Power Configuration 250

Instant Database File Initialization 252

Trace Flags 256

Defining Good Performance 256

Focus on What’s Most Important 257

What the Developer DBA Needs to Know About Performance 258

Users 258

SQL Statements 259

Data Usage Patterns 259

Database Schema 259

What the Production DBA Needs to Know About Performance 260

Optimizing the Server’s Hardware 261

Hardware Management 262

CPU 263

x64 263

Cache 264

Hyper-Threading 264

Multicore Terminology 265

Memory 266

Physical Memory 266

Physical Address Space 267

Virtual Memory Manager 267

The Page File 267

Page Faults 268

I/O 269

Network 270

Magnetic Disks 271

Solid State/Flash Drives 274

Storage Considerations 274

Summary 278

CHAPTER 11: OPTIMIZING SQL SERVER 2014 281

Application Optimization 281

Defining a Workload 282

The Silent Killer: I/O Problems 282

SQL Server I/O Process Model 282

Database File Placement 283

tempdb Considerations 284

SQL Server Internals and File Allocations 288

Table and Index Partitioning 290

Why Consider Partitioning? 290

Creating a Partition Function 292

Creating Filegroups 294

Creating a Partition Scheme 295

Data Compression 296

Row Compression 296

Page Compression 299

Estimating Space Savings 300

Monitoring Data Compression 302

Data Compression Considerations 303

Understanding SQL Server and CPUs 304

NUMA and Hot Add CPUs 305

Cache Coherency 306

Affinity Mask 307

Max Degree of Parallelism (MAXDOP) 307

Cost Threshold for Parallelism 308

Memory Considerations and Enhancements 310

Buffer Pool Extensions 310

Tuning SQL Server Memory 312

Data Locality 314

Max Server Memory 315

Resource Governor 316

The Basic Elements of Resource Governor 317

Using Resource Governor from SQL Server 2014 Management Studio 321

Monitoring Resource Governor 322

Summary 323

CHAPTER 12: MONITORING YOUR SQL SERVER 325

The Goal of Monitoring 326

Determining Your Monitoring Objectives 326

Establishing a Baseline 327

Comparing Current Metrics to the Baseline 327

Choosing the Appropriate Monitoring Tools 328

Performance Monitor 329

CPU Resource Counters 331

Disk Activity 332

Memory Usage 338

Performance Monitoring Tools 341

Monitoring Events 343

The Default Trace 345

system_health Session 346

SQL Trace 347

Event Notifications 351

SQL Server Extended Events 354

Monitoring with Dynamic Management Views and Functions 375

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

Buffer Pool Memory Usage 385

Monitoring Logs 386

Monitoring the SQL Server Error Log 386

Monitoring the Windows Event Logs 387

SQL Server Standard Reports 387

System Center Advisor 387

Summary 390

CHAPTER 13: PERFORMANCE TUNING T-SQL 391

Overview of Query Processing 392

Generating Execution Plans 394

Statistics 394

SQL 2014 Query Optimization Improvements—The New Cardinality

Estimator 395

Using the New Cardinality Estimator 395

Identifying SQL Query Performance Tuning Issues 396

Monitoring Query Performance 396

What to Do When You Find a Slow-Performing Query 397

Generating the Query Plans 398

Reading the Query Plan 400

Data Access Operators in Query Plans 403

Table Scan 404

Clustered Index Scan 406

Nonclustered Index Scans 408

Connecting Access Operators 411

Join Operators 412

Nested Loop or Loop Join 412

Hash Join 413

Merge Join 415

Data Modification Query Plan 417

Query Processing on Partitioned Tables and Indexes 419

Partition-Aware Operations 419

Parallel Query Execution Strategy for Partitioned Objects 421

Analyzing Query Performance in a Production Environment 421

System Dynamic Management Views (DMVs) 422

Putting It All Together 423

Summary 424

CHAPTER 14: INDEXING YOUR DATABASE 425

What’s New for Indexes in SQL Server 2014 426

About Indexes and Partitioned Tables 430

Understanding Indexes 430

Creating Indexes 435

Using Partitioned Tables and Indexes 436

Index Maintenance 437

Monitoring Index Fragmentation 438

Cleaning Up Indexes 439

Improving Query Performance with Indexes 440

Database Tuning Advisor 445

Summary 448

CHAPTER 15: REPLICATION 449

Replication Overview 450

Replication Components 450

Replication Types 452

Replication Models 454

Single Publisher, One or More Subscribers 454

Multiple Publishers, Single Subscriber 454

Multiple Publishers Also Subscribing 456

Updating Subscriber 457

Peer-to-Peer 458

Implementing Replication 460

Setting Up Snapshot Replication 460

Setting Up Distribution 460

Implementing Snapshot Replication 464

Implementing Transactional and Merge Replication 479

Peer-to-Peer Replication 480

Setting Up Peer-to-Peer Replication 480

Configuring Peer-to-Peer Replication 481

Scripting Replication 484

Monitoring Replication 485

Replication Monitor 485

Performance Monitor 487

Replication DMVs 488

sp_replcounters 489

Summary 489

CHAPTER 16: CLUSTERING SQL SERVER 2014 491

Clustering and Your Organization 492

What Clustering Can Do 492

What Clustering Cannot Do 493

Choosing SQL Server 2014 Clustering for the Right Reasons 494

Alternatives to Clustering 494

Clustering: The Big Picture 497

How Clustering Works 497

Clustering Options 502

Upgrading SQL Server Clustering 504

Don’t Upgrade 504

Upgrading Your SQL Server 2014 Cluster In-Place 505

Rebuilding Your Cluster 505

Back-Out Plan 507

Which Upgrade Option Is Best? 507

Getting Prepared for Clustering 507

Preparing the Infrastructure 508

Preparing the Hardware 509

Clustering Windows Server 2012 R2 511

Before Installing Windows 2012 R2 Clustering 511

Installing Windows Server 2012 R2 Failover Clustering 512

Preparing Windows Server 2012 R2 for Clustering 516

Clustering Microsoft Distributed Transaction Coordinator 517

Clustering SQL Server 2014 518

Step-by-Step Instructions to Cluster SQL Server 519

Installing the Service Pack and Cumulative Updates 526

Test, Test, and Test Again 527

Managing and Monitoring the Cluster 529

Troubleshooting Cluster Problems 530

How to Approach Windows Failover Clustering Troubleshooting 530

Doing It Right the First Time 531

Gathering Information 531

Resolving Problems 532

Working with Microsoft 532

Summary 533

CHAPTER 17: BACKUP AND RECOVERY 535

Backup and Restore Enhancements 536

SQL Server Backup to URL 536

SQL Server Managed Backup to Windows Azure 536

Encryption for Backups 536

Overview of Backup and Restore 537

How Backup Works 537

Copying Databases 539

Backup Compression 553

Comparing Recovery Models 554

Choosing a Model 556

Switching Recovery Models 557

Backing Up History Tables 558

Permissions Required for Backup and Restore 559

Backing Up System Databases 559

Full-Text Backup 561

Verifying the Backup Images 561

How Restore Works 562

Preparing for Recovery 563

Recoverability Requirements 564

Data Usage Patterns 565

Maintenance Time Window 565

Amazon no longer offers textbook rentals. We do!

Professional Microsoft SQL Server 2014 Administration

Professional Microsoft SQL Server 2014 Administration

1118859138

Summary

Author Biography

Table of Contents

Supplemental Materials

Write a Review