Topic: SQL Server

Professional Microsoft SQL Server 2014 Administration (1118859138) cover image

Professional Microsoft SQL Server 2014 Administration

Adam Jorgensen, Bradley Ball, Steven Wort, Ross LoForte, Brian Knight
ISBN: 978-1-118-85913-1
Paperback
936 pages
September 2014
Other Available Formats: Wiley E-Text
If you are an instructor, you may request an evaluation copy for this title.
Paperback Version: US $54.99 Add to Cart

About This Title  |  Download Code  |  Errata  |  P2P Forum for This Title

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

Other High-Availability Solutions 566

Developing and Executing a Backup Plan 567

Using SQL Server Management Studio 568

Database Maintenance Plans 572

Using Transact-SQL Backup Commands 576

Managing Backups 578

Backup and Restore Performance 578

Performing Recovery 579

Restore Process 579

SQL Server Management Studio Restore 583

T-SQL Restore Command 588

Restoring System Databases 588

Archiving Data 590

SQL Server Table Partitioning 590

Partitioned View 591

Summary 592

CHAPTER 18: SQL SERVER 2014 LOG SHIPPING 593

Log-Shipping Deployment Scenarios 593

Log Shipping to Create a Warm Standby Server 594

Log Shipping as a Disaster-Recovery Solution 595

Log Shipping as a Report Database Solution 596

Log-Shipping Architecture 597

Primary Server 598

Secondary Server 598

Monitor Server 598

Log-Shipping Process 599

System Requirements 599

Network 600

Identical Capacity Servers 600

Storage 600

Software 600

Deploying Log Shipping 601

Initial Configuration 601

Deploying with Management Studio 602

Deploying with T-SQL Commands 610

Monitoring and Troubleshooting 611

Monitoring with SQL 2014 Management Studio 612

Monitoring with Stored Procedures 613

Troubleshooting Approach 613

Managing Changing Roles 614

Synchronizing Dependencies 614

Switching Roles from the Primary to Secondary Servers 617

Switching Between Primary and Secondary Servers 619

Redirecting Clients to Connect to the Secondary Server 620

Database Backup Plan 621

Integrating Log Shipping with Other High-Availability Solutions 622

SQL Server 2014 Data Mirroring 622

Windows Failover Clustering 622

SQL Server 2014 Replication 623

Removing Log Shipping 624

Removing Log Shipping with Management Studio 624

Removing Log Shipping with T-SQL Commands 624

Log-Shipping Performance 625

Upgrading to SQL Server 2014 Log Shipping 626

Minimum Downtime Approach 626

With Downtime Approach 626

Deploy Log-Shipping Approach 627

Summary 627

CHAPTER 19: DATABASE MIRRORING 629

Overview of Database Mirroring 630

Operating Modes of Database Mirroring 631

Database Mirroring In Action 634

Preparing the Endpoints 635

Preparing the Database for Mirroring 640

Initial Synchronization between Principal and Mirror 641

Establishing the Mirroring Session 642

High-Safety Operating Mode Without Automatic Failover 643

High-Safety Operating Mode with Automatic Failover 643

High-Performance Operating Mode 645

Database Mirroring and SQL Server 2014 Editions 646

Database Mirroring Role Change 646

Automatic Failover 647

Manual Failover 650

Forced Failover 651

Monitoring Using Database Mirroring Monitor 652

Setting Thresholds on Counters and Sending Alerts 656

Preparing the Mirror Server for Failover 658

Hardware, Software, and Server Configuration 658

Database Availability During Planned Downtime 660

SQL Job Configuration on the Mirror 661

Client Redirection to the Mirror 662

Database Mirroring and Other High-Availability Solutions 663

Database Mirroring versus Clustering 663

Database Mirroring versus Transactional Replication 664

Database Mirroring versus Log Shipping 664

Database Mirroring versus Availability Groups 664

Database Snapshots 665

Summary 666

CHAPTER 20: INTEGRATION SERVICES ADMINISTRATION AND PERFORMANCE TUNING 667

A Tour of Integration Services 668

Integration Services Uses 668

The Main Parts of Integration Services 669

Project Management and Change Control 671

Administration of the Integration Services Service 671

An Overview of the Integration Services Service 672

Configuration 672

Event Logs 675

Monitoring Activity 676

Administration of Integration Services Packages in Package Deployment Model 677

Using Management Studio for Package Management 677

Deployment 679

Administration of Integration Services Packages in Project Deployment Model 683

Configuring the SSIS Catalog 683

Deploying Packages 685

Configuring Packages 687

Execution and Scheduling 690

Running Packages in SQL Server Data Tools 691

Running Packages with the SQL Server Import and Export Wizard 691

Running Packages with DTExec 691

Running Packages with DTExecUI (Package Deployment Model) 692

Running Packages with the Execute Package Tool (Project Deployment Model) 693

Scheduling Execution with SQL Server Agent 694

Running Packages with T-SQL 696

Applying Security to Integration Services 696

An Overview of Integration Services Security 696

Securing Packages in Package Deployment Model 697

Summary 699

CHAPTER 21: ANALYSIS SERVICES ADMINISTRATION AND PERFORMANCE TUNING 701

Tour of Analysis Services 702

MOLAP Components 703

Tabular Model Components 704

Analysis Services Architectural Components 704

Administering Analysis Services Server 705

Server Properties 706

Required Services 707

Analysis Services Scripting Language 708

Administering Analysis Services Databases 709

Deploying Analysis Services Databases 710

Processing Analysis Services Objects 712

Backing Up and Restoring Analysis Services Databases 717

Synchronizing Analysis Services Databases 721

Analysis Services Performance Monitoring and Tuning 722

Monitoring Analysis Services Events 722

Using Flight Recorder for After-the-Fact Analysis 723

Summary 723

CHAPTER 22: SQL SERVER REPORTING SERVICES ADMINISTRATION 725

SQL Server Reporting Services

Configuration Manager 726

The Service Account 728

The Web Service URL 729

Reporting Services Databases 731

The Report Manager URL 733

E-mail Settings 734

Execution Account 734

Encryption Keys 736

Scale-Out Deployment 737

The Report Execution Log 739

Report Manager 740

Managing Report Manager 740

Managing Reports 747

Summary 761

CHAPTER 23: SQL SERVER 2014 SHAREPOINT 2013 INTEGRATION 763

Components of Integration 763

PowerPivot 764

Reporting Services 766

Power View 768

Data Refresh 771

Using Data Connections in Excel 771

PerformancePoint Data Connections 776

Visio Services Data Refresh 778

PowerPivot Data Refresh 780

Summary 788

CHAPTER 24: SQL DATABASE ADMINISTRATION AND CONFIGURATION 789

Getting to Know Windows Azure SQL Database 789

SQL Database Architecture 790

Client Layer 791

Services Layer 791

Platform Layer 791

Infrastructure Layer 791

Understanding the Differences 791

Configuring SQL Database 792

Server and Database Provisioning 792

Throttling and Load Balancing 800

Configuring SQL Database Firewalls 801

Connecting to SQL Database 802

Administering SQL Database 804

Creating Logins and Users 805

Assigning Access Rights 807

Working with SQL Database 807

Backups with SQL Database 807

Object Explorer for SQL Database 809

What’s Missing in SQL Database 810

Summary 811

CHAPTER 25: ALWAYSON AVAILABILITY GROUPS 813

Architecture 814

Availability Group Replicas and Roles 815

Availability Modes 815

Types of Failover Supported 816

Allowing Read-Only Access to Secondary Replicas 817

Availability Group Example 818

Configure a New Availability Group 819

Configure an Existing Availability Group 829

Availability Group Failover Operation 833

Suspend an Availability Database 834

Resume an Availability Database 835

Client Application Connections 836

Active Secondary for Secondary Read-Only 837

Read-Only Access Behavior 837

Secondary Replica Client Connectivity 838

Performance 839

Backup on the Secondary Replica 841

Evaluate Backup Replicas Metadata 842

AlwaysOn Group Dashboard 843

Monitoring and Troubleshooting 845

Summary 847

INDEX 849