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
?
?
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
