Microsoft SQL Server 2012 Unleashed

Description

• Copyright 2014
• Dimensions: 7" x 9-1/8"
• Edition: 1st

• eBook
• ISBN-10: 0-13-340854-X
• ISBN-13: 978-0-13-340854-6

Buy the print version of¿Microsoft SQL Server 2012 Unleashed and get the eBook version for free! eBook version includes chapters 44-60 not included in the print. See inside the book for access code and details.

With up-to-the-minute content, this is the industry’s most complete, useful guide to SQL Server 2012.

You’ll find start-to-finish coverage of SQL Server’s core database server and management capabilities: all the real-world information, tips, guidelines, and samples you’ll need to create and manage complex database solutions. The additional online chapters add extensive coverage of SQL Server Integration Services, Reporting Services, Analysis Services, T-SQL programming, .NET Framework integration, and much more.

Authored by four expert SQL Server administrators, designers, developers, architects, and consultants, this book reflects immense experience with SQL Server in production environments. Intended for intermediate-to-advanced-level SQL Server professionals, it focuses on the product’s most complex and powerful capabilities, and its newest tools and features.

• Understand SQL Server 2012’s newest features, licensing changes, and capabilities of each edition
• Manage SQL Server 2012 more effectively with SQL Server Management Studio, the SQLCMD command-line query tool, and Powershell
• Use Policy-Based Management to centrally configure and operate SQL Server
• Utilize the new Extended Events trace capabilities within SSMS
• Maximize performance by optimizing design, queries, analysis, and workload management
• Implement new best practices for SQL Server high availability
• Deploy AlwaysOn Availability Groups and Failover Cluster Instances to achieve enterprise-class availability and disaster recovery
• Leverage new business intelligence improvements, including Master Data Services, Data Quality Services and Parallel Data Warehouse
• Deliver better full-text search with SQL Server 2012’s new Semantic Search
• Improve reporting with new SQL Server 2012 Reporting Services features

Download the following from informit.com/title/9780672336928: Sample databases and code examples



Sample Content

Table of Contents

Introduction    1

Who This Book Is For    2

What This Book Covers    2

Conventions Used in This Book    4

Good Luck!    6

Part I  Welcome to Microsoft SQL Server

1  SQL Server 2012 Overview    9

SQL Server Components and Features    9

The SQL Server Database Engine    10

SQL Server 2012 Administration and Management Tools    12

Replication    16

Merge Replication    17

Database Mirroring    17

SQL Server AlwaysOn Features    18

SQL Server Service Broker    19

Full-Text and Semantic Search    20

SQL Server Integration Services (SSIS)    21

SQL Server Analysis Services (SSAS)    22

SQL Server Reporting Services (SSRS)    24

Master Data Services    25

Data Quality Services    26

SQL Server 2012 Editions    26

SQL Server 2012 Standard Edition    26

SQL Server 2012 Enterprise Edition    27

Differences Between the Enterprise and Standard Editions of SQL Server    28

Other SQL Server 2012 Editions    29

SQL Server Licensing Models    32

Web Edition Licensing    33

Developer Edition Licensing    33

Express Edition Licensing    33

Choosing a Licensing Model    33

Mixing Licensing Models    34

Passive Server/Failover Licensing    34

Virtual Server Licensing    34

Summary    35

2  What’s New in SQL Server 2012    37

New SQL Server 2012 Features    37

New and Improved Storage Features    38

New Transact-SQL Constructs    39

New Scalability and Performance Features    40

New Security Features    40

New Availability Features    40

Statistical Semantic Search    41

Data Quality Services    41

SQL Server 2012 Enhancements    42

SQL Server Management Studio Enhancements    42

Resource Governor Enhancements    43

Spatial Data Enhancements    43

Integration Services Enhancements    43

Service Broker Enhancements    44

Full-Text Search Enhancements    44

Analysis Services Enhancements    45

Reporting Services Enhancements    45

Master Data Services Enhancements    46

Deprecated Features    46

Summary    47

3  Examples of SQL Server Implementations    49

Application Terms    50

OLTP Application Examples    51

OLTP ERP Example    51

OLTP Shopping Cart Example    53

DSS Application Examples    55

DSS Example One    55

DSS Example Two    56

DSS Example Three    57

Summary    59

Part II  SQL Server Tools and Utilities

4  SQL Server Management Studio    63

What’s New in SSMS    63

The Integrated Environment    64

Window Management    64

Integrated Help    67

Administration Tools    71

Registered Servers    71

Object Explorer    72

Activity Monitor    75

Log File Viewer    77

SQL Server Utility    78

Development Tools    84

The Query Editor    84

Managing Projects in SSMS    92

Integrating SSMS with Source Control    93

Using SSMS Templates    95

Using SSMS Snippets    99

T-SQL Debugging    100

Multiserver Queries    102

Summary    102

5  SQL Server Command-Line Utilities    105

What’s New in SQL Server Command-Line Utilities    106

The sqlcmd Command-Line Utility    107

Executing the sqlcmd Utility    108

Using Scripting Variables with sqlcmd    111

The dta Command-Line Utility    111

The tablediff Command-Line Utility    114

The bcp Command-Line Utility    117

The sqldiag Command-Line Utility    118

The sqlservr Command-Line Utility    120

The SqlLocalDB Command-Line Utility    121

Summary    123

6  SQL Server Profiler    125

What’s New with SQL Server Profiler    125

SQL Server Profiler Architecture    126

Creating Traces    127

Events    129

Data Columns    131

Filters    134

Executing Traces and Working with Trace Output    136

Saving and Exporting Traces    137

Saving Trace Output to a File    137

Saving Trace Output to a Table    137

Saving the Profiler GUI Output    138

Importing Trace Files    138

Importing a Trace File into a Trace Table    139

Analyzing Trace Output with the Database Engine

Tuning Advisor    142

Replaying Trace Data    142

Defining Server-Side Traces    144

Monitoring Running Traces    155

Stopping Server-Side Traces    156

Profiler Usage Scenarios    158

Analyzing Slow Stored Procedures or Queries    158

Deadlocks    159

Identifying Ad Hoc Queries    161

Identifying Performance Bottlenecks    162

Monitoring Auto-Update Statistics    164

Monitoring Application Progress    164

Summary    166

Part III  SQL Server Administration

7  SQL Server System and Database Administration    169

What’s New in SQL Server System and Database Administration    169

System Administrator Responsibilities    170

System Databases    171

The master Database    171

The resource Database    172

The model Database    172

The msdb Database    172

The distribution Database    172

The tempdb Database    173

Maintaining System Databases    173

System Tables    174

System Views    175

Compatibility Views    176

Catalog Views    178

Information Schema Views    180

Dynamic Management Views    182

System Stored Procedures    185

Useful System Stored Procedures    186

Summary    187

8  Installing SQL Server 2012    189

What’s New in Installing SQL Server 2012    189

Installation Requirements    190

Hardware Requirements    190

Software Requirements    191

Installation Walkthrough    195

Install Screens, Step-by-Step    196

Installing SQL Server Documentation    216

Installing SQL Server Using a Configuration File    218

Running an Automated or Manual Install    224

Installing Service Packs and Cumulative Updates    225

Applying a Service Pack or Cumulative Update During

a New Installation    225

Applying a Service Pack or Cumulative Update to an

Existing Installation    227

Installing a Service Pack from the Command Line    231

Summary    232

9  Upgrading to SQL Server 2012    233

What’s New in Upgrading SQL Server    233

SQL Server 2012    233

The SQL Server 2012 Upgrade Matrix    234

Identifying Products and Features to be Upgraded    236

Using the SQL Server Upgrade Advisor (UA)    237

Getting Started with the UA    237

The Analysis Wizard    239

The Report Viewer    244

Destination: SQL Server 2012    245

Side-by-Side Upgrade    246

Upgrading In-Place    254

Upgrading the Database Engine    256

Installing Product Updates (Slipstreaming)

During Upgrades    258

Upgrading Using a Configuration File    260

Upgrading from Pre-SQL Server 2005 Versions    261

Upgrading Other SQL Server Components    261

Upgrading Analysis Services    262

Upgrading SQL Server Analysis Services    262

Upgrading Reporting Services    262

Performing an In-Place Upgrade of Reporting Services    262

Migrating to Reporting Services 2012    264

Upgrading SSIS Packages    265

Migrating DTS Packages    266

Summary    267

10  Client Installation and Configuration    269

What’s New in Client Installation and Configuration    269

Client/Server Networking Considerations    270

Server Network Protocols    271

The Server Endpoint Layer    273

The Role of SQL Browser    276

Client Installation    277

Installing the Client Tools    277

Installing SNAC    278

Client Configuration    280

Client Configuration Using SSCM    280

Connection Encryption    283

Client Data Access Technologies    285

Provider Choices    286

Connecting Using the Various Providers and Drivers    287

General Networking Considerations and Troubleshooting    293

Summary    295

11  Database Backup and Restore    297

What’s New in Database Backup and Restore    297

Developing a Backup and Restore Plan    298

Types of Backups    299

Full Database Backups    300

Differential Database Backups    300

Partial Backups    301

Differential Partial Backups    301

File and Filegroup Backups    301

Copy-Only Backups    302

Transaction Log Backups    302

Recovery Models    302

Full Recovery    303

Bulk-Logged Recovery    304

Simple Recovery    305

Backup Devices    306

Disk Devices    306

Tape Devices    307

Network Shares    307

Media Sets and Families    307

Creating Backup Devices    308

Backing Up a Database    308

Creating Database Backups with SSMS    308

Creating Database Backups with T-SQL    311

Backing Up the Transaction Log    314

Creating Transaction Log Backups with SSMS    314

Creating Transaction Log Backups with T-SQL    315

Backup Scenarios    316

Full Database Backups Only    316

Full Database Backups with Transaction Log Backups    317

Differential Backups    318

Partial Backups    319

File/Filegroup Backups    321

Mirrored Backups    322

Copy-Only Backups    323

Compressed Backups    323

System Database Backups    324

Restoring Databases and Transaction Logs    324

Restores with T-SQL    325

Restoring by Using SSMS    329

Restore Information    333

Restore Scenarios    337

Restoring to a Different Database    337

Restoring a Snapshot    338

Restoring a Transaction Log    338

Restoring to the Point of Failure    339

Restoring to a Point in Time    342

Online Restores    342

Restoring the System Databases    344

Additional Backup Considerations    346

Frequency of Backups    346

Using a Standby Server    347

Snapshot Backups    348

Considerations for Very Large Databases    348

Maintenance Plans    348

Summary    349

12  Database Mail    351

What’s New in Database Mail    351

Setting Up Database Mail    352

Creating Mail Profiles and Accounts    353

Using T-SQL to Update and Delete Mail Objects    356

Setting System-Wide Mail Settings    357

Testing Your Setup    357

Sending and Receiving with Database Mail    358

The Service Broker Architecture    358

Sending Email    358

Receiving Email    364

Using SQL Server Agent Mail    364

Job Mail Notifications    365

Creating an Operator    365

Enabling SQL Agent Mail    365

Creating the Job    365

Testing the Job-Completion Notification    366

Alert Mail Notifications    367

Creating an Alert    367

Testing the Alert Notification    368

Related Views and Procedures    368

Viewing the Mail Configuration Objects    368

Viewing Mail Message Data    369

Summary    371

13  SQL Server Agent    373

What’s New in Scheduling and Notification    374

Configuring the SQL Server Agent    374

Configuring SQL Server Agent Properties    374

Configuring the SQL Server Agent Startup Account    376

Configuring Email Notification    378

SQL Server Agent Proxy Account    380

Viewing the SQL Server Agent Error Log    381

SQL Server Agent Security    382

Managing Operators    383

Managing Jobs    385

Defining Job Properties    385

Defining Job Steps    386

Defining Multiple Jobs Steps    389

Defining Job Schedules    390

Defining Job Notifications    391

Viewing Job History    392

Managing Alerts    393

Defining Alert Properties    393

Defining Alert Responses    397

Scripting Jobs and Alerts    399

Multiserver Job Management    400

Creating a Master Server    401

Enlisting Target Servers    401

Creating Multiserver Jobs    402

Event Forwarding    402

Summary    403

14  Administering SQL Server 2012 with PowerShell    405

What’s New with PowerShell    405

Overview of PowerShell    406

Start Using PowerShell Now    407

Common Terminology    407

Object-Based Functionality    408

SQL Server Management Objects    408

WMI    409

Installing PowerShell    409

PowerShell Console    409

Scriptable and Interactive    410

Default Security    411

Execution Policy    411

Profiles    411

Built-In Help Features    411

PowerShell Scripting Basics    414

A Few Basic Cmdlets    414

Creating a PowerShell Script    415

Adding Comments    415

Variables    416

Escaping Characters    417

Special Variable $_    418

Joining Variables and Strings    418

Passing Arguments    419

Using Param    419

Arrays    420

Operators    421

Conditional Statements    421

Functions    422

Looping Statements    423

Filtering Cmdlets    424

Formatting Cmdlets    425

Dealing with CSV Files    426

Dealing with Dates and Times    427

-WhatIf/-Confirm Parameters    428

PowerShell in SQL Server 2012    428

Adding PowerShell Support    428

Accessing SQL Server PowerShell    429

SQL Server PowerShell    430

SQL Provider    431

SQL Cmdlets    432

SQL Server Agent Support    432

Step-by-Step Examples    432

General Tasks    433

Scheduling Scripts    433

Common OS-Related Tasks    435

SQL Server-Specific Tasks    437

Using the Provider    438

Creating a Database Table    438

Performing a Database Backup    440

Checking Server Settings    441

Checking the Database Usage    442

Getting Table Properties    443

Cmdlet Example: Invoke-SqlCmd    443

Cmdlet Example: Invoke-PolicyEvaluation    444

Joining Columns    444

Retrieving an Entry    445

Summary    445

15  SQL Server Policy-Based Management    447

What’s New in Policy-Based Management    447

Introduction to Policy-Based Management    448

Policy-Based Management Concepts    449

Facets    449

Conditions    452

Policies    453

Categories    453

Targets    453

Execution Modes    453

Central Management Servers    454

Implementing Policy-Based Management    456

Creating a Condition Based on a Facet    456

Creating a Category    460

Evaluating Policies    462

Importing and Exporting Policies    462

Sample Templates and Real-World Examples    464

Sample Policy Templates    464

Evaluating Recovery Models    464

Implementing Surface Area Configuration Checks    464

Ensuring Object Naming Conventions    465

Checking Best Practices Compliance    465

Policy-Based Management Best Practices    465

Summary    466

Part IV  SQL Server Security Administration

16  Security and Compliance    469

Exposure and Risk    470

Across the Life Cycle    471

The Security Big Picture    472

Identity Access Management Components    474

Data Security Compliance and SQL Server    476

SQL Server Auditing    478

Setting Up Auditing via T-SQL    484

SQL Injection Is Easy to Do    485

Summary    487

17  Security and User Administration    489

What’s New in Security and User Administration    489

An Overview of SQL Server Security    490

Authentication Methods    493

Windows Authentication Mode    493

Mixed Authentication Mode    493

Setting the Authentication Mode    493

Managing Principals    494

Logins    494

SQL Server Security: Users    497

The dbo User    498

The guest User    498

The INFORMATION_SCHEMA User    499

The sys User    499

User/Schema Separation    500

Roles    501

Fixed Server Roles    501

Fixed Database Roles    502

The public Role    505

User-Defined Database Roles    506

User-Defined Server Roles    507

Application Roles    508

Managing Securables    508

Managing Permissions    509

Managing SQL Server Logins    511

Using SSMS to Manage Logins    512

Using T-SQL to Manage Logins    516

Managing SQL Server Users    517

Using SSMS to Manage Users    517

Using T-SQL to Manage Users    519

Managing Database Roles    520

Using SSMS to Manage Database Roles    520

Using T-SQL to Manage Database Roles    521

Managing Server Roles    522

Using SSMS to Manage Server Roles    522

Using T-SQL to Manage Server Roles    522

Managing SQL Server Permissions    524

Using SSMS to Manage Permissions    524

Using SSMS to Manage Permissions at the Server Level    524

Using SSMS to Manage Permissions at the Database Level    527

Using SSMS to Manage Permissions at the Object Level    529

Using T-SQL to Manage Permissions    531

The Execution Context    533

Explicit Context Switching    533

Implicit Context Switching    534

Summary    535

18  Data Encryption    537

What’s New in Data Encryption    538

An Overview of Data Security    538

An Overview of Data Encryption    539

SQL Server Key Management    541

Extensible Key Management    543

Column-Level Encryption    544

Encrypting Columns Using a Passphrase    545

Encrypting Columns Using a Certificate    547

Transparent Data Encryption    551

Implementing Transparent Data Encryption    552

Managing TDE in SSMS    554

Backing Up TDE Certificates and Keys    556

The Limitations of TDE    557

Column-Level Encryption Versus Transparent

Data Encryption    557

Summary    558

Part V  Database Administration

19  Creating and Managing Databases    563

What’s New in Creating and Managing Databases    564

Data Storage in SQL Server    564

Database Files    565

Primary Files    566

Secondary Files    566

Using Filegroups    567

Using Partitions    570

Transaction Log Files    570

Creating Databases    572

Using SSMS to Create a Database    572

Using T-SQL to Create Databases    576

Setting Database Options    577

The Database Options    577

Using T-SQL to Set Database Options    580

Retrieving Option Information    581

Managing Databases    583

Managing File Growth    583

Expanding Databases    584

Shrinking Databases    585

Moving Databases    589

Restoring a Database to a New Location    590

Using ALTER DATABASE    590

Detaching and Attaching Databases    591

Contained Databases    594

Creating a Contained Database    594

Connecting to a Contained Database    597

Summary    598

20  Creating and Managing Tables    599

What’s New in SQL Server 2012    599

Creating Tables    600

Using Object Explorer to Create Tables    600

Using Database Diagrams to Create Tables    601

Using T-SQL to Create Tables    602

Defining Columns    605

Data Types    605

Column Properties    612

Column Sets    618

Working with Sparse Columns    619

Sparse Columns: Good or Bad?    623

Defining Sparse Columns in SSMS    623

Defining Table Location    623

Defining Table Constraints    625

Modifying Tables    627

Using T-SQL to Modify Tables    627

Using Object Explorer and the Table Designer to Modify Tables    630

Using Database Diagrams to Modify Tables    633

Dropping Tables    635

Using Partitioned Tables    636

Creating a Partition Function    637

Creating a Partition Scheme    640

Creating a Partitioned Table    641

Adding and Dropping Table Partitions    644

Switching Table Partitions    648

Using FILESTREAM Storage    652

Enabling FILESTREAM Storage    653

Setting Up a Database for FILESTREAM Storage    656

Using FILESTREAM Storage for Data Columns    657

Using FileTables    660

FileTable Prerequisites    660

Creating FileTables    661

Copying Files to the FileTable    662

Creating Temporary Tables    663

Summary    664

21  Creating and Managing Indexes    665

What’s New in Creating and Managing Indexes    665

Types of Indexes    666

Clustered Indexes    666

Nonclustered Indexes    668

Creating Indexes    669

Creating Indexes with T-SQL    670

Creating Indexes with SSMS    673

Managing Indexes    676

Managing Indexes with T-SQL    677

Managing Indexes with SSMS    680

Dropping Indexes    681

Online Indexing Operations    681

Indexes on Views    683

Summary    684

22  Implementing Data Integrity    685

What’s New in Data Integrity    685

Types of Data Integrity    686

Domain Integrity    686

Entity Integrity    686

Referential Integrity    686

Enforcing Data Integrity    686

Implementing Declarative Data Integrity    686

Implementing Procedural Data Integrity    687

Using Constraints    687

The PRIMARY KEY Constraint    687

The UNIQUE Constraint    689

The FOREIGN KEY Referential Integrity Constraint    690

The CHECK Constraint    694

Creating Constraints    695

Managing Constraints    700

Rules    704

Defaults    705

Declarative Defaults    705

Bound Defaults    707

When a Default Is Applied    707

Restrictions on Defaults    709

Summary    710

23  Creating and Managing Views    711

What’s New in Creating and Managing Views    711

Definition of Views    711

Using Views    712

Simplifying Data Manipulation    713

Focusing on Specific Data    714

Abstracting Data    715

Controlling Access to Data    716

Creating Views    718

Creating Views Using T-SQL    719

ENCRYPTION    721

Creating Views Using the View Designer    723

Managing Views    725

Altering Views with T-SQL    726

Dropping Views with T-SQL    726

Managing Views with SSMS    727

Data Modifications and Views    727

Partitioned Views    728

Modifying Data Through a Partitioned View    732

Distributed Partitioned Views    732

Indexed Views    734

Creating Indexed Views    734

Indexed Views and Performance    737

To Expand or Not to Expand    740

Summary    741

24  Creating and Managing Stored Procedures    743

What’s New in Creating and Managing Stored Procedures    743

Advantages of Stored Procedures    743

Creating Stored Procedures    745

Creating Procedures in SSMS    746

Executing Stored Procedures    753

Executing Procedures in SSMS    754

Execution Context and the EXECUTE AS Clause    755

Using the WITH RESULT SETS Clause    758

Deferred Name Resolution    761

Identifying Objects Referenced in Stored Procedures    763

Viewing Stored Procedures    765

Modifying Stored Procedures    769

Viewing and Modifying Stored Procedures with SSMS    769

Using Input Parameters    771

Setting Default Values for Parameters    772

Passing Object Names as Parameters    775

Using Wildcards in Parameters    777

Using Table-Valued Parameters    778

Using Output Parameters    780

Returning Procedure Status    781

Debugging Stored Procedures Using SQL Server Management Studio    783

Using System Stored Procedures    785

Startup Procedures    789

Summary    793

25  Creating and Managing User-Defined Functions    795

Why Use User-Defined Functions?    795

Types of User-Defined Functions    798

Scalar Functions    798

Table-Valued Functions    801

Creating and Managing User-Defined Functions    803

Creating User-Defined Functions    803

Viewing and Modifying User-Defined Functions    814

Managing User-Defined Function Permissions    823

Rewriting Stored Procedures as Functions    824

Creating and Using CLR Functions    825

Adding CLR Functions to a Database    825

Deciding Between Using T-SQL or CLR Functions    827

Summary    828

26  Creating and Managing Triggers    829

What’s New in Creating and Managing Triggers    830

Using DML Triggers    830

Creating DML Triggers    831

Using AFTER Triggers    833

Using inserted and deleted Tables    837

INSTEAD OF Triggers    841

Using DDL Triggers    849

Creating DDL Triggers    853

Managing DDL Triggers    857

Using CLR Triggers    859

Using Nested Triggers    862

Using Recursive Triggers    862

Summary    863

27  Transaction Management and the Transaction Log    865

What’s New in Transaction Management    865

What Is a Transaction?    865

How SQL Server Manages Transactions    866

Defining Transactions    867

AutoCommit Transactions    867

Explicit User-Defined Transactions    868

Implicit Transactions    874

Implicit Transactions Versus Explicit Transactions    877

Transactions and Batches    877

Transactions and Stored Procedures    879

Transactions and Triggers    884

Triggers and Transaction Nesting    885

Triggers and Multistatement Transactions    888

Using Savepoints in Triggers    889

Transactions and Locking    890

READ_COMMITTED_SNAPSHOT Isolation    892

Coding Effective Transactions    892

Transaction Logging and the Recovery Process    893

The Checkpoint Process    894

Automatic Checkpoints    896

Indirect Checkpoints    897

Manual Checkpoints    899

The Recovery Process    900

Managing the Transaction Log    902

Long-Running Transactions    906

Distributed Transactions    909

Summary    910

28  Database Snapshots    911

What’s New with Database Snapshots    912

What Are Database Snapshots?    912

Limitations and Restrictions of Database Snapshots    917

Copy-on-Write Technology    918

When to Use Database Snapshots    919

Reverting to a Snapshot for Recovery Purposes    919

Safeguarding a Database Prior to Making Mass Changes    921

Providing a Testing (or Quality Assurance) Starting

Point (Baseline)    921

Providing a Point-in-Time Reporting Database    922

Providing a Highly Available and Offloaded Reporting

Database from a Database Mirror    923

Setup and Breakdown of a Database Snapshot    924

Creating a Database Snapshot    925

Removing a Database Snapshot    930

Reverting to a Database Snapshot for Recovery    930

Reverting a Source Database from a Database Snapshot    930

Using Database Snapshots with Testing and QA    931

Database Snapshots Maintenance and Security Considerations    932

Security for Database Snapshots    932

Snapshot Sparse File Size Management    932

Number of Database Snapshots per Source Database    933

Summary    933

29  Database Maintenance    935

What’s New in Database Maintenance    936

The Maintenance Plan Wizard    936

Backing Up Databases    937

Checking Database Integrity    941

Shrinking Databases    942

Maintaining Indexes and Statistics    943

Scheduling a Maintenance Plan    947

Managing Maintenance Plans Without the Wizard    950

Executing a Maintenance Plan    954

Maintenance Without a Maintenance Plan    955

Database Maintenance Policies    956

Summary    956

Part VI  SQL Server Performance and Optimization

30  Data Structures, Indexes, and Performance    959

What’s New for Data Structures, Indexes, and Performance    960

Understanding Data Structures    960

Database Files and Filegroups    961

Primary Data File    962

Secondary Data Files    963

The Log File    963

File Management    963

Using Filegroups    965

FILESTREAM Filegroups    967

Database Pages    969

Page Types    970

Data Pages    971

Row-Overflow Pages    977

LOB Data Pages    978

Index Pages    980

Space Allocation Structures    981

Extents    981

Global and Shared Global Allocation Map Pages    982

Page Free Space Pages    983

Index Allocation Map Pages    983

Differential Changed Map Pages    984

Bulk Changed Map Pages    984

Data Compression    985

Row-Level Compression    985

Page-Level Compression    988

The CI Record    990

Implementing Page Compression    990

Evaluating Page Compression    991

Managing Data Compression with SSMS    994

Understanding Table Structures    995

Heap Tables    997

Clustered Tables    999

Understanding Index Structures    1000

Clustered Indexes    1001

Nonclustered Indexes    1004

Columnstore Indexes    1008

Data Modification and Performance    1013

Inserting Data    1013

Deleting Rows    1016

Updating Rows    1017

Index Utilization    1018

Index Selection    1020

Evaluating Index Usefulness    1021

Index Statistics    1024

The Statistics Histogram    1026

How the Statistics Histogram Is Used    1028

Index Densities    1029

Estimating Rows Using Index Statistics    1030

Generating and Maintaining Index and Column Statistics    1031

SQL Server Index Maintenance    1040

Setting the Fill Factor    1050

Reapplying the Fill Factor    1052

Disabling Indexes    1052

Managing Indexes with SSMS    1054

Index Design Guidelines    1054

Clustered Index Indications    1055

Nonclustered Index Indications    1057

Index Covering    1059

Included Columns    1061

Wide Indexes Versus Multiple Indexes    1062

Indexed Views    1062

Indexes on Computed Columns    1064

Filtered Indexes and Statistics    1065

Creating and Using Filtered Indexes    1067

Creating and Using Filtered Statistics    1069

Choosing Indexes: Query Versus Update Performance    1070

Identifying Missing Indexes    1071

The Database Engine Tuning Advisor    1072

Missing Index Dynamic Management Objects    1072

Missing Index Feature Versus Database Engine

Tuning Advisor    1074

Identifying Unused Indexes    1075

Summary    1078

31  Understanding Query Optimization    1079

What’s New in Query Optimization    1080

What Is the Query Optimizer?    1080

Query Compilation and Optimization    1081

Compiling DML Statements    1081

Optimization Steps    1082

Query Analysis    1083

Identifying Search Arguments    1083

Identifying OR Clauses    1083

Identifying Join Clauses    1084

Row Estimation and Index Selection    1085

Evaluating SARG and Join Selectivity    1085

Estimating Access Path Cost    1090

Using Multiple Indexes    1097

Optimizing with Indexed Views    1104

Optimizing with Filtered Indexes    1107

Join Selection    1109

Join Processing Strategies    1109

Determining the Optimal Join Order    1114

Subquery Processing    1115

Execution Plan Selection    1118

Query Plan Caching    1121

Query Plan Reuse    1121

Query Plan Aging    1123

Recompiling Query Plans    1124

Monitoring the Plan Cache    1125

Other Query Processing Strategies    1133

Predicate Transitivity    1133

Group by Optimization    1134

Queries with DISTINCT    1134

Queries with UNION    1135

Queries Using Columnstore Indexes    1136

Parallel Query Processing    1137

Parallel Query Configuration Options    1138

Identifying Parallel Queries    1139

Common Query Optimization Problems    1140

Out-of-Date or Insufficient Statistics    1140

Poor Index Design    1141

Search Argument Problems    1141

Large Complex Queries    1143

Triggers    1143

Managing the Optimizer    1144

Optimizer Hints    1145

Forced Parameterization    1151

Using the USE PLAN Query Hint    1153

Using Plan Guides    1155

Limiting Query Plan Execution with the Query Governor    1163

Summary    1166

32  Query Analysis    1167

What’s New in Query Analysis    1168

Query Analysis in SSMS    1168

Execution Plan ToolTips    1169

Logical and Physical Operator Icons    1173

Analyzing Stored Procedures    1181

Saving and Viewing Graphical Execution Plans    1181

Displaying Execution Plan XML    1183

Missing Index Hints    1184

SSMS Client Statistics    1188

Using the SET SHOWPLAN Options    1190

SHOWPLAN_TEXT    1190

SHOWPLAN_ALL    1192

SHOWPLAN_XML    1193

Using sys.dm_exec_query_plan    1194

Query Statistics    1196

STATISTICS IO    1196

STATISTICS TIME    1199

Using datediff() to Measure Runtime    1202

STATISTICS PROFILE    1203

STATISTICS XML    1203

Query Analysis with SQL Server Profiler    1204

Summary    1206

33  Locking and Performance    1207

What’s New in Locking and Performance    1207

The Need for Locking    1207

Transaction Isolation Levels in SQL Server    1208

Read Uncommitted Isolation    1209

Read Committed Isolation    1210

Read Committed Snapshot Isolation    1211

Repeatable Read Isolation    1212

Serializable Read Isolation    1213

Snapshot Isolation    1213

The Lock Manager    1216

Monitoring Lock Activity in SQL Server    1217

Querying the sys.dm_tran_locks View    1217

Viewing Locking Activity with SQL Server Profiler    1221

Monitoring Locks with Performance Monitor    1223

SQL Server Lock Types    1225

Shared Locks    1226

Update Locks    1227

Exclusive Locks    1228

Intent Locks    1228

Schema Locks    1229

Bulk Update Locks    1230

SQL Server Lock Granularity    1230

Serialization and Key-Range Locking    1232

Using Application Locks    1236

Index Locking    1239

Row-Level Versus Page-Level Locking    1240

Lock Escalation    1241

Lock Compatibility    1243

Locking Contention and Deadlocks    1243

Identifying Locking Contention    1244

Setting the Lock Timeout Interval    1247

Minimizing Locking Contention    1248

Deadlocks    1249

Table Hints for Locking    1263

Transaction Isolation-Level Hints    1264

Lock Granularity Hints    1266

Lock Type Hints    1266

Optimistic Locking    1266

Optimistic Locking Using the rowversion Data Type    1267

Optimistic Locking with Snapshot Isolation    1269

Summary    1272

34  Database Design and Performance    1273

What’s New in Database Design and Performance    1273

Basic Tenets of Designing for Performance    1274

Logical Database Design Issues    1275

Normalization Conditions    1275

Normalization Forms    1275

Benefits of Normalization    1277

Drawbacks of Normalization    1277

Denormalizing a Database    1277

Denormalization Guidelines    1278

Essential Denormalization Techniques    1279

Database Filegroups and Performance    1285

RAID Technology    1287

RAID Level 0    1287

RAID Level 1    1289

RAID Level 10    1290

RAID Level 5    1290

SQL Server and SAN Technology    1292

What Is a SAN?    1292

SAN Considerations for SQL Server    1293

Summary    1294

35  Monitoring SQL Server Performance    1295

What’s New in Monitoring SQL Server Performance    1296

Performance Monitoring Tools    1297

The Data Collector and the MDW    1297

SQL Server Utility    1319

SQL Server Extended Events    1323

Windows Performance Monitor    1359

A Performance Monitoring Approach    1371

Monitoring the Network Interface    1372

Monitoring the Processors    1374

Monitoring Memory    1379

Monitoring the Disk System    1382

Monitoring SQL Server’s Disk Activity    1384

Monitoring Other SQL Server Performance Items    1385

Summary    1386

36  SQL Server Database Engine Tuning Advisor    1387

What’s New in SQL Server Database Engine Tuning Advisor    1387

SQL Server Instance Architecture    1388

Database Engine Tuning Advisor    1389

The Database Engine Tuning Advisor GUI    1390

The Database Engine Tuning Advisor Command Line    1397

Summary    1405

37  Managing Workloads with the Resource Governor    1407

What’s New for Resource Governor    1408

Overview of Resource Governor    1408

Resource Governor Components    1410

Classification    1410

Resource Pools    1410

Workload Groups    1412

Configuring Resource Governor    1413

Enabling Resource Governor    1413

Defining Resource Pools    1415

Defining Workload Groups    1417

Creating a Classifier Function    1421

Monitoring Resource Usage    1424

Modifying Your Resource Governor Configuration    1428

Deleting Workload Groups    1429

Deleting Resource Pools    1430

Modifying a Classifier Function    1431

Summary    1431

38  A Performance and Tuning Methodology    1433

The Full Architectural Landscape    1434

Primary Performance and Tuning Handles    1435

A Performance and Tuning Methodology    1436

Designing In Performance and Tuning from the Start    1437

Code and Test    1440

Performance and Tuning for an Existing Implementation    1442

Performance and Tuning Design Guidelines    1448

Hardware and Operating System Guidelines    1448

SQL Server Instance Guidelines    1450

Database-Level Guidelines    1451

Table Design Guidelines    1451

Indexing Guidelines    1453

View Design Guidelines    1455

Transact-SQL Guidelines    1455

Application Design Guidelines    1459

Distributed Data Guidelines    1460

High-Availability Guidelines    1460

Tools of the Performance and Tuning Trade    1461

Microsoft Out-of-the-Box    1461

Third-Party Performance and Tuning Tools    1462

Summary    1464

Part VII  SQL Server High Availability

39  SQL Server High Availability Fundamentals    1467

What’s New in High Availability    1468

What Is High Availability?    1469

The Fundamentals of HA    1471

Hardware Factors    1471

Backup Considerations    1471

Operating System Upgrades    1472

Vendor Agreements Followed    1472

Training Kept Up-to-Date    1472

Quality Assurance Done Well    1472

Standards/Procedures Followed    1472

Server Instance Isolation    1472

Building Solutions with One or More HA Options    1474

Failover Cluster Services (FCS)    1475

SQL Clustering    1476

AlwaysOn Failover Clustering Instance (FCI)    1478

AlwaysOn Availability Groups    1479

Data Replication    1480

Change Data Capture    1482

Log Shipping    1482

Database Mirroring    1484

Combining Failover with Scale-Out Options    1485

Other HA Techniques That Yield Great Results    1486

High Availability from the Windows Server Family Side    1489

Microsoft Virtual Machines and Hyper-V    1489

Summary    1490

40  Data Replication    1493

What’s New in Data Replication    1494

What Is Replication?    1495

The Publisher, Distributor, and Subscriber

Magazine Metaphor    1497

Publications and Articles    1498

Filtering Articles    1498

Replication Scenarios    1503

The Central Publisher Replication Model    1503

The Central Publisher with Remote Distributor

Replication Model    1504

The Publishing Subscriber Replication Model    1506

The Central Subscriber Replication Model    1507

The Multiple Publishers with Multiple Subscribers

Replication Model    1507

The Updating Subscribers Replication Model    1509

The Peer-to-Peer Replication Model    1511

Subscriptions    1511

Anonymous Subscriptions (Pull Subscriptions)    1513

The Distribution Database    1513

Replication Agents    1515

The Snapshot Agent    1516

The Log Reader Agent    1518

The Distribution Agent    1519

The Merge Agent    1520

Other Specialized Agents    1520

Planning for SQL Server Data Replication    1521

Autonomy, Timing, and Latency of Data    1522

Methods of Data Distribution    1522

SQL Server Replication Types    1523

Snapshot Replication    1523

Transactional Replication    1524

Merge Replication    1525

Basing the Replication Design on User Requirements    1526

Data Characteristics    1528

Setting Up Replication    1529

Creating a Distributor and Enabling Publishing    1529

Creating a Publication    1535

Horizontal and Vertical Filtering    1542

Creating Subscriptions    1544

Scripting Replication    1550

Monitoring Replication    1555

Replication Monitoring SQL Statements    1555

Monitoring Replication within SQL Server Management

Studio    1557

Troubleshooting Replication Failures    1559

Peer-to-Peer Replication    1560

The Performance Monitor    1566

Replication in Heterogeneous Environments    1566

Backup and Recovery in a Replication Configuration    1567

Some Thoughts on Performance    1568

Log Shipping    1569

Data Replication and Database Mirroring or AlwaysOn

Features for Fault Tolerance and High Availability    1569

Change Data Capture    1570

The Change Data Capture Tables    1571

Enabling CDC for a Database    1572

Enabling CDC for a Table    1573

Querying the CDC Tables    1575

CDC and DDL Changes to Source Tables    1581

CDC and AlwaysOn Availability Groups    1582

Change Tracking    1582

Implementing Change Tracking    1583

Identifying Tracked Changes    1585

Identifying Changed Columns    1589

Change Tracking Overhead    1590

Summary    1591

41  Database Mirroring    1593

What’s New in Database Mirroring    1594

What Is Database Mirroring?    1594

Copy-on-Write Technology    1596

When to Use Database Mirroring    1597

Roles of the Database Mirroring Configuration    1597

Playing Roles and Switching Roles    1598

Database Mirroring Operating Modes    1598

Setting Up and Configuring Database Mirroring    1599

Getting Ready to Mirror a Database    1600

Creating the Endpoints    1603

Granting Permissions    1605

Creating the Database on the Mirror Server    1605

Identifying the Other Endpoints for Database Mirroring    1607

Configuring Database Mirroring by Using the Wizard    1609

Monitoring a Mirrored Database Environment    1616

Removing Mirroring    1619

Testing Failover from the Principal to the Mirror    1621

Client Setup and Configuration for Database Mirroring    1623

Using Replication and Database Mirroring Together    1624

Using Database Snapshots from a Mirror for Reporting    1625

Summary    1627

42  SQL Server Failover Clustering    1629

What’s New in SQL Server Failover Clustering    1630

How Microsoft SQL Server Failover Clustering Works    1630

Understanding WSFC    1632

Extending WSFC with NLB    1636

How WSFC Sets the Stage for SQL Server Clustering    1637

Installing SQL Server Failover Clustering    1638

Configuring SQL Server Database Disks    1639

Installing Network Interfaces    1641

Installing WSFC    1641

Cluster Events    1642

Installing SQL Server within WSFC    1642

Fail Over to Another Node    1655

The Client Connection Impact of a Failover    1655

Potential Problems to Watch Out for with SQL Server

Failover Clustering    1658

Summary    1659

43  SQL Server AlwaysOn and Availability Groups    1661

SQL Server AlwaysOn and Availability Groups    1661

Windows Failover Cluster Services    1662

AlwaysOn Failover Clustering Instances    1662

AlwaysOn Availability Groups    1663

Combining Failover with Scale-Out Options    1666

Building a Multinode AlwaysOn Configuration    1666

Adding Replicas    1681

Dashboard and Monitoring    1681

Summary    1683

Part VIII  SQL Server Application Development

44  What’s New for Transact-SQL in SQL Server 2012    1687

THROW Statement    1687

The SEQUENCE Object    1693

SEQUENCE Objects Versus Identity Columns    1701

New Conversion Functions    1702

The PARSE Function    1702

The TRY_PARSE Function    1703

The TRY_CONVERT Function    1704

New Logical Functions    1705

The CHOOSE Function    1705

The IIF Function    1706

New String Functions    1706

CONCAT    1706

FORMAT    1708

New date and time Functions    1714

Some Recently Added Date and Time Features You

May Have Missed    1717

Date and Time Conversions    1720

SQL Server 2012 Enhancements to Windowing Functions    1722

The OVER Clause    1722

Ranking Functions    1723

Calculating Aggregates with the OVER Clause    1729

SQL Server 2012 Enhancements to Windowing Functions    1730

Ad Hoc Query Paging    1744

New Features and Enhancements to Spatial Data    1747

The Spatial Data Types    1747

Spatial Instance Types    1748

Other SQL Server 2012 Enhancements    1749

Representing Spatial Data in SQL Server 2012    1750

Working with Geometry Data    1751

Working with Geography Data    1754

Spatial Data Support in SSMS    1756

Spatial Data Types: Where to Go from Here?    1759

Summary    1759

45  Transact-SQL Programming Guidelines, Tips, and Tricks    1761

General T-SQL Coding Recommendations    1762

Provide Explicit Column Lists    1762

Qualify Object Names with a Schema Name    1764

Avoid SQL Injection Attacks When Using Dynamic SQL    1768

Comment Your T-SQL Code    1778

General T-SQL Performance Recommendations    1779

UNION Versus UNION ALL Performance    1779

Use IF EXISTS Instead of SELECT COUNT(*)    1779

Avoid Unnecessary ORDER BY or DISTINCT Clauses    1779

Temp Tables Versus Table Variables Versus Common Table Expressions    1780

Avoid Unnecessary Function Executions    1781

Cursors and Performance    1782

Variable Assignment in UPDATE Statements    1784

In Case You Missed It: Recently Added T-SQL Features    1788

TOP Enhancements    1788

The OUTPUT Clause    1793

MERGE Statement    1798

MERGE Statement Best Practices and Guidelines    1804

Insert over DML    1805

Common Table Expressions    1807

Recursive Queries with CTEs    1809

Using the Hierarchyid Data Type    1818

Creating a Hierarchy    1818

Populating the Hierarchy    1819

Querying the Hierarchy    1821

Modifying the Hierarchy    1825

Using Row Numbers for Paging Results    1830

De-Duping Data with Ranking Functions    1834

PIVOT and UNPIVOT    1836

The APPLY Operator    1841

CROSS APPLY    1841

OUTER APPLY    1843

The TABLESAMPLE Clause    1843

Variable Assignment in DECLARE Statements    1847

Compound Assignment Operators    1848

GROUP BY Clause Enhancements    1848

GROUPING SETS    1849

The grouping_id() Function    1852

General T-SQL Tips and Tricks    1854

Date Calculations    1854

Using CONTEXT_INFO    1857

Working with Outer Joins    1859

Generating T-SQL Statements with T-SQL    1869

Summary    1869

46  Advanced Stored Procedure Programming and Optimization    1871

T-SQL Stored Procedure Coding Guidelines    1871

Calling Stored Procedures from Transactions    1874

Handling Errors in Stored Procedures    1877

Using Cursors in Stored Procedures    1881

Using CURSOR Variables in Stored Procedures    1886

Nested Stored Procedures    1892

Recursive Stored Procedures    1893

Using Temporary Tables in Stored Procedures    1897

Temporary Table Performance Tips    1899

Using the table Data Type    1901

Stored Procedure Performance    1903

Query Plan Caching    1904

The SQL Server Plan Cache    1904

Shared Query Plans    1905

Automatic Query Plan Recompilation    1905

Forcing Recompilation of Query Plans    1908

Using Dynamic SQL in Stored Procedures    1912

Using sp_executesql    1915

Installing and Using .NET CLR Stored Procedures    1918

Adding CLR Stored Procedures to a Database    1919

T-SQL or CLR Stored Procedures?    1920

Using xp_cmdshell    1921

Summary    1922

47  SQL Server and the .NET Framework    1925

What’s New in SQL Server 2012 and the .NET Framework    1925

Getting Comfortable with ADO.NET 4.5 and SQL Server 2012    1926

Essential ADO.NET Development Techniques    1926

Developing with LINQ to SQL    1933

Getting Started with LINQ to SQL    1933

Going Deeper    1935

Uncovering LINQ to SQL with LINQPad    1938

Introducing the Entity Framework    1942

Getting Started    1943

Data Operations with EF Entities    1946

Using WCF Data Services    1951

Getting Set Up    1951

Essentials    1951

Building Your Data Service    1951

CRUD Operations    1958

Summary    1963

48  SQLCLR: Developing SQL Server Objects in .NET    1965

What’s New for SQLCLR in SQL Server 2012    1965

Developing Custom Managed Database Objects    1968

An Introduction to Custom Managed Database Objects    1968

Managed Object Permissions    1969

Developing Managed Objects with Visual Studio 2012    1971

Developing Managed Stored Procedures    1972

Developing Managed User-Defined Functions (UDFs)    1977

Developing Managed User-Defined Types (UDTs)    1987

Developing Managed User-Defined Aggregates (UDAs)    1995

Developing Managed Triggers    1998

Using Transactions    2004

Using the Related System Catalogs    2006

Summary    2007

49  Using XML in SQL Server 2012    2009

What’s New in Using XML in SQL Server 2012    2009

Understanding XML    2011

Relational Data as XML: The FOR XML Modes    2011

RAW Mode    2012

AUTO Mode    2018

EXPLICIT Mode    2022

PATH Mode    2026

FOR XML and the xml Data Type    2029

XML as Relational Data: Using OPENXML    2031

Using the xml Data Type    2035

Defining and Using xml Columns    2036

Using XML Schema Collections    2039

The Built-in xml Data Type Methods    2045

Indexing and Full-Text Indexing of xml Columns    2063

Indexing xml Columns    2064

Full-Text Indexing xml Columns    2077

Summary    2078

50  SQL Server Service Broker    2079

What’s New in Service Broker    2079

Understanding Distributed Messaging    2080

The Basics of Service Broker    2080

Example System Design    2084

Understanding Service Broker Constructs    2085

Defining Messages and Choosing a Message Type    2085

Setting Up Contracts for Communication    2090

Creating Queues for Message Storage    2091

Defining Services to Send and Receive Messages    2093

Planning Conversations Between Services    2094

Service Broker Routing and Security    2105

Using Certificates for Conversation Encryption    2105

A Final Note on the Sample System    2113

Troubleshooting SSB Applications with ssbdiagnose.exe    2113

AlwaysOn Availability Group Support    2114

Related System Catalogs    2115

Summary    2117

51  SQL Server Full-Text Search    2119

What’s New in SQL Server 2012 Full-Text Search    2120

Installing SQL FTS    2121

Upgrade Options in SQL Server 2012    2123

How SQL Server FTS Works    2124

Indexing    2125

Searching    2131

Implementing SQL Server 2012 Full-Text Catalogs    2131

Setting Up a Full-Text Index    2134

Using T-SQL Commands to Build Full-Text Indexes    2134

Using the Full-Text Indexing Wizard to Build Full-Text

Indexes and Catalogs    2152

Full-Text Searches    2155

CONTAINS and CONTAINSTABLE    2155

FREETEXT and FREETEXTTABLE    2162

Semantic Search    2164

Full-Text Search Maintenance    2166

Full-Text Search Performance    2167

Full-Text Search Troubleshooting    2167

Summary    2169

52  Working with SQL Azure    2171

Setting Up Subscriptions, Servers, and Databases    2171

Setting Up Your Windows Azure Subscription    2172

Creating a Logical Server    2174

Managing Your Server    2176

Configuring Your Firewall    2176

Using SQL Server Management Studio    2177

Using Management Portal    2179

Working with Databases    2179

Understanding SQL Database Editions    2179

Managing Databases Using T-SQL    2183

Migrating Data into SQL Database    2184

Copying Databases    2186

Backing Up and Restoring Databases    2187

Using Database Copies for Backup and Restore    2187

Using BACPAC Files for Backup and Restore    2187

Managing Logins, Users, and Roles    2189

Understanding Roles    2190

Managing Logins and Users    2190

Using SQL Database with ADO.NET    2192

Connecting to SQL Database Using a Custom

Windows Application    2192

Connectivity Limitations    2196

Understanding SQL Database Billing    2203

Baseline Billing    2203

Tracking Your Usage    2204

Understanding SQL Database Limitations    2206

Unsupported and Partially Supported Functionality    2206

References    2209

Summary    2209

Part IX  SQL Server Business Intelligence Features

53  SQL Server 2012 Analysis Services    2213

What’s New in SSAS    2213

Understanding SSAS and OLAP    2215

Understanding the SSAS Environment Wizards    2217

OLAP Versus OLTP    2221

An Analytics Design Methodology    2223

An Analytics Mini-Methodology    2223

An OLAP Requirements Example: CompSales International    2225

CompSales International Requirements    2225

OLAP Cube Creation    2227

Using SQL Server SSDT    2228

Creating an OLAP Database    2229

Defining Dimensions and Hierarchies    2235

Creating the Other Dimensions    2244

Creating the Cube    2246

Building and Deploying the Cube    2249

Populating the Cube with Data    2251

Aggregating Data Within the Cube    2252

Browsing Data in the Cube    2257

Delivering Data to Users    2261

ADO MD    2263

Query Analysis and Optimization    2264

Generating a Relational Database    2266

Limitations of a Relational Database    2267

Cube Perspectives    2267

Data Mining    2268

Security and Roles    2278

Tabular Models and SSAS    2279

OLAP Performance    2280

Summary    2281

54  SQL Server Integration Services    2283

What’s New with SSIS    2284

SSIS Basics    2285

SSIS Architecture and Concepts    2290

SSIS Tools and Utilities    2293

A Data Transformation Requirement    2298

Running the SSIS Import and Export Wizard    2300

The SSIS Designer    2309

The Package Execution Utility    2318

The dtexec Utility    2320

Running Packages    2321

Running Package Examples    2325

The dtutil Utility    2326

dtutil Examples    2328

Change Data Capture and SSIS    2333

Using bcp    2333

Fundamentals of Exporting and Importing Data    2335

File Data Types    2338

Format Files    2339

Using Views    2348

Logged and Nonlogged Operations    2348

Batches    2349

Parallel Loading    2349

Supplying Hints to bcp    2350

Summary    2352

55  SQL Server 2012 Reporting Services    2353

What’s New in SSRS 2012    2353

Discontinued Functionality and Breaking Changes    2354

Reporting Services Architecture    2355

HTTP Architecture    2358

Installing and Configuring SSRS    2358

The Installation Sequence    2359

SSRS Configuration Using RSCM    2362

Developing Reports    2367

Tools of the Trade    2367

Report Basics    2368

Overview of the Report Development Process    2369

Data Planning and Preparation    2369

Using Shared Data Sources    2370

Using Datasets    2370

Using Shared Datasets    2371

Developing Reports Using SSDT    2371

Working with the Tablix    2379

Understanding Expressions    2380

Report Design Fundamentals    2383

Designing Reports Using Report Builder    2394

Report Builder and Report Model Security    2409

Enabling Report Builder    2410

Management and Security    2410

Securing Reports    2410

Subscriptions    2412

Report Execution Options    2414

Performance and Monitoring    2416

SSRS Trace Log    2416

Execution Log    2416

Windows Event Log    2417

Performance Counters    2417

Summary    2417

56  Master Data Services and Data Quality Services    2419

Master Data Services    2419

Data Quality Services    2420

What’s New in MDS and DQS    2420

Master Data Management    2421

Master Data Services Terms and Concepts    2423

Master Data Services    2426

Data Quality Services    2446

Summary    2460

57  Parallel Data Warehouse    2463

What’s New in Parallel Data Warehouse    2463

Understanding MPP and PDW    2464

MPP Architecture    2465

The PDW    2466

Data on a PDW    2468

PDW and Big Data (Hadoop)    2471

xVelocity Columnstore Indexes    2471

Columnstore Indexes    2472

Summary    2477

Part X  Bonus Chapters

58  Managing Linked Servers    2481

What’s New in Managing Linked Servers    2482

Linked Servers    2483

Distributed Queries    2483

Distributed Transactions    2484

Adding, Dropping, and Configuring Linked Servers    2485

sp_addlinkedserver    2485

sp_linkedservers    2492

sp_dropserver    2494

sp_serveroption    2494

Mapping Local Logins to Logins on Linked Servers    2495

sp_addlinkedsrvlogin    2496

sp_droplinkedsrvlogin    2497

sp_helplinkedsrvlogin    2498

Obtaining General Information About Linked Servers    2499

Executing a Stored Procedure via a Linked Server    2500

Setting Up Linked Servers Using SQL Server Management Studio    2501

Summary    2505

59  SQL Server Disaster Recovery Planning    2507

What’s New in SQL Server Disaster

Recovery Planning    2508

How to Approach Disaster Recovery    2508

Disaster Recovery Patterns    2510

Recovery Objectives    2514

A Data-Centric Approach to Disaster Recovery    2516

Microsoft SQL Server Options for Disaster Recovery    2516

Data Replication    2517

Log Shipping    2519

Database Mirroring and Snapshots    2519

Change Data Capture    2521

AlwaysOn Availability Groups    2521

The Overall Disaster Recovery Process    2522

The Focus of Disaster Recovery    2523

Planning and Executing a Disaster Recovery    2530

Have You Detached a Database Recently?    2530

Third-Party Disaster Recovery Alternatives    2530

Summary    2531

60  SQL Server Configuration Options    2533

What’s New in Configuring, Tuning, and Optimizing

SQL Server Options    2533

SQL Server Instance Architecture    2534

Configuration Options    2535

Fixing an Incorrect Option Setting    2542

Setting Configuration Options with SSMS    2543

Obsolete Configuration Options    2543

Configuration Options and Performance    2544

access check cache bucket count    2544

access check cache quota    2544

ad hoc distributed queries    2545

affinity I/O mask    2545

affinity mask    2547

Agent XP    2548

backup compression default    2549

blocked process threshold    2550

c2 audit mode    2550

clr enabled    2550

common criteria compliance enabled    2551

contained database authentication    2551

cost threshold for parallelism    2552

cross db ownership chaining    2553

cursor threshold    2553

Database Mail XPs    2554

default full-text language    2554

default language    2556

default trace enabled    2558

disallow results from triggers    2558

EKM provider enabled    2559

filestream_access_level    2559

fill factor    2559

index create memory    2560

in-doubt xact resolution    2560

lightweight pooling    2561

locks    2561

max degree of parallelism    2562

max server memory and min server memory    2562

max text repl size    2564

max worker threads    2565

media retention    2566

min memory per query    2566

nested triggers    2567

network packet size    2567

Ole Automation Procedures    2568

optimize for ad hoc workloads    2568

PH_timeout    2569

priority boost    2569

query governor cost limit    2570

query wait    2571

recovery interval    2571

remote access    2572

remote admin connections    2572

remote login timeout    2573

remote proc trans    2573

remote query timeout    2573

scan for startup procs    2575

show advanced options    2575

user connections    2575

user options    2577

XP-Related Configuration Options    2577

Summary    2578

Index    2579



Updates

Submit Errata

