Microsoft SQL Server 2012 Unleashed

Ray Rankins is owner and president of Gotham Consulting Services, Inc. (http://www.gothamconsulting.com), near Saratoga Springs, New York. Ray has been working with Sybase and Microsoft SQL Server for more than 24 years and has experience in database administration, database design, project management, application development, consulting, courseware development, and training. He has worked in a variety of industries, including financial, manufacturing, health care, retail, insurance, communications, public utilities, and state and federal government. His expertise is in database performance and tuning, query analysis, advanced SQL programming and stored procedure development, database design, data architecture, and database application design and development. Ray’s presentations on these topics at user group conferences have been very well received. Ray is coauthor of Microsoft SQL Server 2008 R2 Unleashed, Microsoft SQL Server 2005 Unleashed , Microsoft SQL Server 2000 Unleashed , Microsoft SQL Server 6.5 Unleashed , Sybase SQL Server 11 Unleashed , and Sybase SQL Server 11 DBA Survival Guide, all published by Sams Publishing. As an instructor, Ray brings his real-world experience into the classroom, teaching courses on SQL, advanced SQL programming and optimization, database design, database administration, and database performance and tuning. Ray can be reached at rrankins@gothamconsulting.com.   Paul Bertucci is the founder of Data by Design, LLC (www.dataxdesign.com–formerly Database Architechs), a global database consulting firm with offices in the United States and Paris, France. He is also the Chief Architect and runs a global Shared Services team for Autodesk, Inc. (teams: BI/DW/ODS, Big Data, Identity Management, SOA, Integration (EAI & ETL), MDM, Collaboration/Social, SaaS application platforms, and Enterprise Architecture). He was also the former Chief Data Architect at Symantec for many years. Paul has more than 30 years of experience with database design, data architecture, big data, data replication, performance and tuning, master data management (MDM), data provenance/DataDNA, distributed data systems, data integration, high-availability, enterprise architect, identity management, SOA, SaaS, and systems integration for numerous Fortune 500 companies, including Intel, Coca-Cola, Apple, Toshiba, Lockheed, Wells Fargo, Safeway, Sony, Charles Schwab, Cisco Systems, Sybase, Symantec, Veritas, and Honda, to name a few. He has authored numerous database articles, data standards, and high-profile database courses, such as Sybase’s “Performance and Tuning” and “Physical Database Design” courses. Other Sams Publishing books that he has authored include the highly popular Microsoft SQL Server 2000 Unleashed, ADO.NET in 24 Hours, Microsoft SQL Server High Availability, Microsoft SQL Server 2005 Unleashed, and Microsoft SQL Server 2008 R2 Unleashed. Mr. Bertucci is a frequent speaker at industry conferences such as Informatica World, Oracle World, MDM Summits, and Microsoft-oriented conferences such as SQL Saturday’s, Silicon Valley Code-Camp, PASS conferences, Tech Ed’s, and SQL Server User Groups. He has deployed numerous systems with Microsoft SQL Server, Sybase, DB2, Postgres, MySQL, NoSQL, Paraccel, Hadoop and Oracle database engines, and he has designed/architected several commercially available tools in the database, data modeling, performance and tuning, data integration, digital DNA, and multidimensional planning spaces. Paul received his formal education in computer science and electrical engineering from UC Berkeley (Go Bears!). He lives in the great Pacific Northwest (Oregon) with his five children, Donny, Juliana, Nina, Marissa, and Paul Jr. Paul can be reached at pbertucci@dataxdesign.com or Bertucci@Alum.CalBerkeley.Org.   Chris Gallelli is the president of CGAL Consulting Services, Inc. His company focuses on consulting services in the areas of database administration, database tuning, advanced stored procedure development, Integration Services, Powershell development and database programming using Microsoft Visual Studio. He has provided SQL Server based solutions for a variety of customers including several Fortune 500 companies. Chris has more than 20 years of experience with SQL Server or Sybase and more than 25 years in the field of information technology. He has a bachelor’s degree in electrical engineering and a master’s degree in business administration from Union College. Chris currently lives near Albany, New York, with his lovely wife, Laura, and two beautiful daughters, Rachael and Kayla. Other Sams Publishing books that he has coauthored include Microsoft SQL Server 2000 Unleashed, Microsoft SQL Server 2005 Unleashed, and Microsoft SQL Server 2008 R2 Unleashed. Chris can be reached at cgallelli@gmail.com.   Alex T. Silverstein is owner and chief information officer of Unified Digital Group, LLC (http://unifieddigital.com), a custom database administration and software development firm headquartered near Saratoga Springs, New York. He specializes in designing high availability software systems using the principles of agile development. Alex has more than 15 years of experience providing application development, database administration, and training services worldwide to a variety of industries. He was also a coauthor for Microsoft SQL Server 2005 Unleashed and Microsoft SQL Server 2008 R2 Unleashed and a contributing author for Microsoft SQL Server 2000 Unleashed, all published by Sams Publishing. You can reach Alex anytime via email at alex@unifieddigital.com.   Hilary Cotter is a SQL Server MVP with more than 20 years of IT experience working for Fortune 500 clients. He is the author of a book on SQL Server Replication and coauthor of Microsoft SQL Server 2008 Management and Administration from Sams Publishing. Hilary has also written numerous white papers and articles on SQL Server and databases.  

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