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

Table of Contents:
Introduction    1 Who This Book Is For    2 What This Book Covers    2 Conventions Used in This Book    4 Good Luck!    6 Part I  Welcome to Microsoft SQL Server 1  SQL Server 2012 Overview    9 SQL Server Components and Features    9 The SQL Server Database Engine    10 SQL Server 2012 Administration and Management Tools    12 Replication    16 Merge Replication    17 Database Mirroring    17 SQL Server AlwaysOn Features    18 SQL Server Service Broker    19 Full-Text and Semantic Search    20 SQL Server Integration Services (SSIS)    21 SQL Server Analysis Services (SSAS)    22 SQL Server Reporting Services (SSRS)    24 Master Data Services    25 Data Quality Services    26 SQL Server 2012 Editions    26 SQL Server 2012 Standard Edition    26 SQL Server 2012 Enterprise Edition    27 Differences Between the Enterprise and Standard Editions of SQL Server    28 Other SQL Server 2012 Editions    29 SQL Server Licensing Models    32 Web Edition Licensing    33 Developer Edition Licensing    33 Express Edition Licensing    33 Choosing a Licensing Model    33 Mixing Licensing Models    34 Passive Server/Failover Licensing    34 Virtual Server Licensing    34 Summary    35 2  What’s New in SQL Server 2012    37 New SQL Server 2012 Features    37 New and Improved Storage Features    38 New Transact-SQL Constructs    39 New Scalability and Performance Features    40 New Security Features    40 New Availability Features    40 Statistical Semantic Search    41 Data Quality Services    41 SQL Server 2012 Enhancements    42 SQL Server Management Studio Enhancements    42 Resource Governor Enhancements    43 Spatial Data Enhancements    43 Integration Services Enhancements    43 Service Broker Enhancements    44 Full-Text Search Enhancements    44 Analysis Services Enhancements    45 Reporting Services Enhancements    45 Master Data Services Enhancements    46 Deprecated Features    46 Summary    47 3  Examples of SQL Server Implementations    49 Application Terms    50 OLTP Application Examples    51 OLTP ERP Example    51 OLTP Shopping Cart Example    53 DSS Application Examples    55 DSS Example One    55 DSS Example Two    56 DSS Example Three    57 Summary    59 Part II  SQL Server Tools and Utilities 4  SQL Server Management Studio    63 What’s New in SSMS    63 The Integrated Environment    64 Window Management    64 Integrated Help    67 Administration Tools    71 Registered Servers    71 Object Explorer    72 Activity Monitor    75 Log File Viewer    77 SQL Server Utility    78 Development Tools    84 The Query Editor    84 Managing Projects in SSMS    92 Integrating SSMS with Source Control    93 Using SSMS Templates    95 Using SSMS Snippets    99 T-SQL Debugging    100 Multiserver Queries    102 Summary    102 5  SQL Server Command-Line Utilities    105 What’s New in SQL Server Command-Line Utilities    106 The sqlcmd Command-Line Utility    107 Executing the sqlcmd Utility    108 Using Scripting Variables with sqlcmd    111 The dta Command-Line Utility    111 The tablediff Command-Line Utility    114 The bcp Command-Line Utility    117 The sqldiag Command-Line Utility    118 The sqlservr Command-Line Utility    120 The SqlLocalDB Command-Line Utility    121 Summary    123 6  SQL Server Profiler    125 What’s New with SQL Server Profiler    125 SQL Server Profiler Architecture    126 Creating Traces    127 Events    129 Data Columns    131 Filters    134 Executing Traces and Working with Trace Output    136 Saving and Exporting Traces    137 Saving Trace Output to a File    137 Saving Trace Output to a Table    137 Saving the Profiler GUI Output    138 Importing Trace Files    138 Importing a Trace File into a Trace Table    139 Analyzing Trace Output with the Database Engine Tuning Advisor    142 Replaying Trace Data    142 Defining Server-Side Traces    144 Monitoring Running Traces    155 Stopping Server-Side Traces    156 Profiler Usage Scenarios    158 Analyzing Slow Stored Procedures or Queries    158 Deadlocks    159 Identifying Ad Hoc Queries    161 Identifying Performance Bottlenecks    162 Monitoring Auto-Update Statistics    164 Monitoring Application Progress    164 Summary    166 Part III  SQL Server Administration 7  SQL Server System and Database Administration    169 What’s New in SQL Server System and Database Administration    169 System Administrator Responsibilities    170 System Databases    171 The master Database    171 The resource Database    172 The model Database    172 The msdb Database    172 The distribution Database    172 The tempdb Database    173 Maintaining System Databases    173 System Tables    174 System Views    175 Compatibility Views    176 Catalog Views    178 Information Schema Views    180 Dynamic Management Views    182 System Stored Procedures    185 Useful System Stored Procedures    186 Summary    187 8  Installing SQL Server 2012    189 What’s New in Installing SQL Server 2012    189 Installation Requirements    190 Hardware Requirements    190 Software Requirements    191 Installation Walkthrough    195 Install Screens, Step-by-Step    196 Installing SQL Server Documentation    216 Installing SQL Server Using a Configuration File    218 Running an Automated or Manual Install    224 Installing Service Packs and Cumulative Updates    225 Applying a Service Pack or Cumulative Update During a New Installation    225 Applying a Service Pack or Cumulative Update to an Existing Installation    227 Installing a Service Pack from the Command Line    231 Summary    232 9  Upgrading to SQL Server 2012    233 What’s New in Upgrading SQL Server    233 SQL Server 2012    233 The SQL Server 2012 Upgrade Matrix    234 Identifying Products and Features to be Upgraded    236 Using the SQL Server Upgrade Advisor (UA)    237 Getting Started with the UA    237 The Analysis Wizard    239 The Report Viewer    244 Destination: SQL Server 2012    245 Side-by-Side Upgrade    246 Upgrading In-Place    254 Upgrading the Database Engine    256 Installing Product Updates (Slipstreaming) During Upgrades    258 Upgrading Using a Configuration File    260 Upgrading from Pre-SQL Server 2005 Versions    261 Upgrading Other SQL Server Components    261 Upgrading Analysis Services    262 Upgrading SQL Server Analysis Services    262 Upgrading Reporting Services    262 Performing an In-Place Upgrade of Reporting Services    262 Migrating to Reporting Services 2012    264 Upgrading SSIS Packages    265 Migrating DTS Packages    266 Summary    267 10  Client Installation and Configuration    269 What’s New in Client Installation and Configuration    269 Client/Server Networking Considerations    270 Server Network Protocols    271 The Server Endpoint Layer    273 The Role of SQL Browser    276 Client Installation    277 Installing the Client Tools    277 Installing SNAC    278 Client Configuration    280 Client Configuration Using SSCM    280 Connection Encryption    283 Client Data Access Technologies    285 Provider Choices    286 Connecting Using the Various Providers and Drivers    287 General Networking Considerations and Troubleshooting    293 Summary    295 11  Database Backup and Restore    297 What’s New in Database Backup and Restore    297 Developing a Backup and Restore Plan    298 Types of Backups    299 Full Database Backups    300 Differential Database Backups    300 Partial Backups    301 Differential Partial Backups    301 File and Filegroup Backups    301 Copy-Only Backups    302 Transaction Log Backups    302 Recovery Models    302 Full Recovery    303 Bulk-Logged Recovery    304 Simple Recovery    305 Backup Devices    306 Disk Devices    306 Tape Devices    307 Network Shares    307 Media Sets and Families    307 Creating Backup Devices    308 Backing Up a Database    308 Creating Database Backups with SSMS    308 Creating Database Backups with T-SQL    311 Backing Up the Transaction Log    314 Creating Transaction Log Backups with SSMS    314 Creating Transaction Log Backups with T-SQL    315 Backup Scenarios    316 Full Database Backups Only    316 Full Database Backups with Transaction Log Backups    317 Differential Backups    318 Partial Backups    319 File/Filegroup Backups    321 Mirrored Backups    322 Copy-Only Backups    323 Compressed Backups    323 System Database Backups    324 Restoring Databases and Transaction Logs    324 Restores with T-SQL    325 Restoring by Using SSMS    329 Restore Information    333 Restore Scenarios    337 Restoring to a Different Database    337 Restoring a Snapshot    338 Restoring a Transaction Log    338 Restoring to the Point of Failure    339 Restoring to a Point in Time    342 Online Restores    342 Restoring the System Databases    344 Additional Backup Considerations    346 Frequency of Backups    346 Using a Standby Server    347 Snapshot Backups    348 Considerations for Very Large Databases    348 Maintenance Plans    348 Summary    349 12  Database Mail    351 What’s New in Database Mail    351 Setting Up Database Mail    352 Creating Mail Profiles and Accounts    353 Using T-SQL to Update and Delete Mail Objects    356 Setting System-Wide Mail Settings    357 Testing Your Setup    357 Sending and Receiving with Database Mail    358 The Service Broker Architecture    358 Sending Email    358 Receiving Email    364 Using SQL Server Agent Mail    364 Job Mail Notifications    365 Creating an Operator    365 Enabling SQL Agent Mail    365 Creating the Job    365 Testing the Job-Completion Notification    366 Alert Mail Notifications    367 Creating an Alert    367 Testing the Alert Notification    368 Related Views and Procedures    368 Viewing the Mail Configuration Objects    368 Viewing Mail Message Data    369 Summary    371 13  SQL Server Agent    373 What’s New in Scheduling and Notification    374 Configuring the SQL Server Agent    374 Configuring SQL Server Agent Properties    374 Configuring the SQL Server Agent Startup Account    376 Configuring Email Notification    378 SQL Server Agent Proxy Account    380 Viewing the SQL Server Agent Error Log    381 SQL Server Agent Security    382 Managing Operators    383 Managing Jobs    385 Defining Job Properties    385 Defining Job Steps    386 Defining Multiple Jobs Steps    389 Defining Job Schedules    390 Defining Job Notifications    391 Viewing Job History    392 Managing Alerts    393 Defining Alert Properties    393 Defining Alert Responses    397 Scripting Jobs and Alerts    399 Multiserver Job Management    400 Creating a Master Server    401 Enlisting Target Servers    401 Creating Multiserver Jobs    402 Event Forwarding    402 Summary    403 14  Administering SQL Server 2012 with PowerShell    405 What’s New with PowerShell    405 Overview of PowerShell    406 Start Using PowerShell Now    407 Common Terminology    407 Object-Based Functionality    408 SQL Server Management Objects    408 WMI    409 Installing PowerShell    409 PowerShell Console    409 Scriptable and Interactive    410 Default Security    411 Execution Policy    411 Profiles    411 Built-In Help Features    411 PowerShell Scripting Basics    414 A Few Basic Cmdlets    414 Creating a PowerShell Script    415 Adding Comments    415 Variables    416 Escaping Characters    417 Special Variable $_    418 Joining Variables and Strings    418 Passing Arguments    419 Using Param    419 Arrays    420 Operators    421 Conditional Statements    421 Functions    422 Looping Statements    423 Filtering Cmdlets    424 Formatting Cmdlets    425 Dealing with CSV Files    426 Dealing with Dates and Times    427 -WhatIf/-Confirm Parameters    428 PowerShell in SQL Server 2012    428 Adding PowerShell Support    428 Accessing SQL Server PowerShell    429 SQL Server PowerShell    430 SQL Provider    431 SQL Cmdlets    432 SQL Server Agent Support    432 Step-by-Step Examples    432 General Tasks    433 Scheduling Scripts    433 Common OS-Related Tasks    435 SQL Server-Specific Tasks    437 Using the Provider    438 Creating a Database Table    438 Performing a Database Backup    440 Checking Server Settings    441 Checking the Database Usage    442 Getting Table Properties    443 Cmdlet Example: Invoke-SqlCmd    443 Cmdlet Example: Invoke-PolicyEvaluation    444 Joining Columns    444 Retrieving an Entry    445 Summary    445 15  SQL Server Policy-Based Management    447 What’s New in Policy-Based Management    447 Introduction to Policy-Based Management    448 Policy-Based Management Concepts    449 Facets    449 Conditions    452 Policies    453 Categories    453 Targets    453 Execution Modes    453 Central Management Servers    454 Implementing Policy-Based Management    456 Creating a Condition Based on a Facet    456 Creating a Category    460 Evaluating Policies    462 Importing and Exporting Policies    462 Sample Templates and Real-World Examples    464 Sample Policy Templates    464 Evaluating Recovery Models    464 Implementing Surface Area Configuration Checks    464 Ensuring Object Naming Conventions    465 Checking Best Practices Compliance    465 Policy-Based Management Best Practices    465 Summary    466 Part IV  SQL Server Security Administration 16  Security and Compliance    469 Exposure and Risk    470 Across the Life Cycle    471 The Security Big Picture    472 Identity Access Management Components    474 Data Security Compliance and SQL Server    476 SQL Server Auditing    478 Setting Up Auditing via T-SQL    484 SQL Injection Is Easy to Do    485 Summary    487 17  Security and User Administration    489 What’s New in Security and User Administration    489 An Overview of SQL Server Security    490 Authentication Methods    493 Windows Authentication Mode    493 Mixed Authentication Mode    493 Setting the Authentication Mode    493 Managing Principals    494 Logins    494 SQL Server Security: Users    497 The dbo User    498 The guest User    498 The INFORMATION_SCHEMA User    499 The sys User    499 User/Schema Separation    500 Roles    501 Fixed Server Roles    501 Fixed Database Roles    502 The public Role    505 User-Defined Database Roles    506 User-Defined Server Roles    507 Application Roles    508 Managing Securables    508 Managing Permissions    509 Managing SQL Server Logins    511 Using SSMS to Manage Logins    512 Using T-SQL to Manage Logins    516 Managing SQL Server Users    517 Using SSMS to Manage Users    517 Using T-SQL to Manage Users    519 Managing Database Roles    520 Using SSMS to Manage Database Roles    520 Using T-SQL to Manage Database Roles    521 Managing Server Roles    522 Using SSMS to Manage Server Roles    522 Using T-SQL to Manage Server Roles    522 Managing SQL Server Permissions    524 Using SSMS to Manage Permissions    524 Using SSMS to Manage Permissions at the Server Level    524 Using SSMS to Manage Permissions at the Database Level    527 Using SSMS to Manage Permissions at the Object Level    529 Using T-SQL to Manage Permissions    531 The Execution Context    533 Explicit Context Switching    533 Implicit Context Switching    534 Summary    535 18  Data Encryption    537 What’s New in Data Encryption    538 An Overview of Data Security    538 An Overview of Data Encryption    539 SQL Server Key Management    541 Extensible Key Management    543 Column-Level Encryption    544 Encrypting Columns Using a Passphrase    545 Encrypting Columns Using a Certificate    547 Transparent Data Encryption    551 Implementing Transparent Data Encryption    552 Managing TDE in SSMS    554 Backing Up TDE Certificates and Keys    556 The Limitations of TDE    557 Column-Level Encryption Versus Transparent Data Encryption    557 Summary    558 Part V  Database Administration 19  Creating and Managing Databases    563 What’s New in Creating and Managing Databases    564 Data Storage in SQL Server    564 Database Files    565 Primary Files    566 Secondary Files    566 Using Filegroups    567 Using Partitions    570 Transaction Log Files    570 Creating Databases    572 Using SSMS to Create a Database    572 Using T-SQL to Create Databases    576 Setting Database Options    577 The Database Options    577 Using T-SQL to Set Database Options    580 Retrieving Option Information    581 Managing Databases    583 Managing File Growth    583 Expanding Databases    584 Shrinking Databases    585 Moving Databases    589 Restoring a Database to a New Location    590 Using ALTER DATABASE    590 Detaching and Attaching Databases    591 Contained Databases    594 Creating a Contained Database    594 Connecting to a Contained Database    597 Summary    598 20  Creating and Managing Tables    599 What’s New in SQL Server 2012    599 Creating Tables    600 Using Object Explorer to Create Tables    600 Using Database Diagrams to Create Tables    601 Using T-SQL to Create Tables    602 Defining Columns    605 Data Types    605 Column Properties    612 Column Sets    618 Working with Sparse Columns    619 Sparse Columns: Good or Bad?    623 Defining Sparse Columns in SSMS    623 Defining Table Location    623 Defining Table Constraints    625 Modifying Tables    627 Using T-SQL to Modify Tables    627 Using Object Explorer and the Table Designer to Modify Tables    630 Using Database Diagrams to Modify Tables    633 Dropping Tables    635 Using Partitioned Tables    636 Creating a Partition Function    637 Creating a Partition Scheme    640 Creating a Partitioned Table    641 Adding and Dropping Table Partitions    644 Switching Table Partitions    648 Using FILESTREAM Storage    652 Enabling FILESTREAM Storage    653 Setting Up a Database for FILESTREAM Storage    656 Using FILESTREAM Storage for Data Columns    657 Using FileTables    660 FileTable Prerequisites    660 Creating FileTables    661 Copying Files to the FileTable    662 Creating Temporary Tables    663 Summary    664 21  Creating and Managing Indexes    665 What’s New in Creating and Managing Indexes    665 Types of Indexes    666 Clustered Indexes    666 Nonclustered Indexes    668 Creating Indexes    669 Creating Indexes with T-SQL    670 Creating Indexes with SSMS    673 Managing Indexes    676 Managing Indexes with T-SQL    677 Managing Indexes with SSMS    680 Dropping Indexes    681 Online Indexing Operations    681 Indexes on Views    683 Summary    684 22  Implementing Data Integrity    685 What’s New in Data Integrity    685 Types of Data Integrity    686 Domain Integrity    686 Entity Integrity    686 Referential Integrity    686 Enforcing Data Integrity    686 Implementing Declarative Data Integrity    686 Implementing Procedural Data Integrity    687 Using Constraints    687 The PRIMARY KEY Constraint    687 The UNIQUE Constraint    689 The FOREIGN KEY Referential Integrity Constraint    690 The CHECK Constraint    694 Creating Constraints    695 Managing Constraints    700 Rules    704 Defaults    705 Declarative Defaults    705 Bound Defaults    707 When a Default Is Applied    707 Restrictions on Defaults    709 Summary    710 23  Creating and Managing Views    711 What’s New in Creating and Managing Views    711 Definition of Views    711 Using Views    712 Simplifying Data Manipulation    713 Focusing on Specific Data    714 Abstracting Data    715 Controlling Access to Data    716 Creating Views    718 Creating Views Using T-SQL    719 ENCRYPTION    721 Creating Views Using the View Designer    723 Managing Views    725 Altering Views with T-SQL    726 Dropping Views with T-SQL    726 Managing Views with SSMS    727 Data Modifications and Views    727 Partitioned Views    728 Modifying Data Through a Partitioned View    732 Distributed Partitioned Views    732 Indexed Views    734 Creating Indexed Views    734 Indexed Views and Performance    737 To Expand or Not to Expand    740 Summary    741 24  Creating and Managing Stored Procedures    743 What’s New in Creating and Managing Stored Procedures    743 Advantages of Stored Procedures    743 Creating Stored Procedures    745 Creating Procedures in SSMS    746 Executing Stored Procedures    753 Executing Procedures in SSMS    754 Execution Context and the EXECUTE AS Clause    755 Using the WITH RESULT SETS Clause    758 Deferred Name Resolution    761 Identifying Objects Referenced in Stored Procedures    763 Viewing Stored Procedures    765 Modifying Stored Procedures    769 Viewing and Modifying Stored Procedures with SSMS    769 Using Input Parameters    771 Setting Default Values for Parameters    772 Passing Object Names as Parameters    775 Using Wildcards in Parameters    777 Using Table-Valued Parameters    778 Using Output Parameters    780 Returning Procedure Status    781 Debugging Stored Procedures Using SQL Server Management Studio    783 Using System Stored Procedures    785 Startup Procedures    789 Summary    793 25  Creating and Managing User-Defined Functions    795 Why Use User-Defined Functions?    795 Types of User-Defined Functions    798 Scalar Functions    798 Table-Valued Functions    801 Creating and Managing User-Defined Functions    803 Creating User-Defined Functions    803 Viewing and Modifying User-Defined Functions    814 Managing User-Defined Function Permissions    823 Rewriting Stored Procedures as Functions    824 Creating and Using CLR Functions    825 Adding CLR Functions to a Database    825 Deciding Between Using T-SQL or CLR Functions    827 Summary    828 26  Creating and Managing Triggers    829 What’s New in Creating and Managing Triggers    830 Using DML Triggers    830 Creating DML Triggers    831 Using AFTER Triggers    833 Using inserted and deleted Tables    837 INSTEAD OF Triggers    841 Using DDL Triggers    849 Creating DDL Triggers    853 Managing DDL Triggers    857 Using CLR Triggers    859 Using Nested Triggers    862 Using Recursive Triggers    862 Summary    863 27  Transaction Management and the Transaction Log    865 What’s New in Transaction Management    865 What Is a Transaction?    865 How SQL Server Manages Transactions    866 Defining Transactions    867 AutoCommit Transactions    867 Explicit User-Defined Transactions    868 Implicit Transactions    874 Implicit Transactions Versus Explicit Transactions    877 Transactions and Batches    877 Transactions and Stored Procedures    879 Transactions and Triggers    884 Triggers and Transaction Nesting    885 Triggers and Multistatement Transactions    888 Using Savepoints in Triggers    889 Transactions and Locking    890 READ_COMMITTED_SNAPSHOT Isolation    892 Coding Effective Transactions    892 Transaction Logging and the Recovery Process    893 The Checkpoint Process    894 Automatic Checkpoints    896 Indirect Checkpoints    897 Manual Checkpoints    899 The Recovery Process    900 Managing the Transaction Log    902 Long-Running Transactions    906 Distributed Transactions    909 Summary    910 28  Database Snapshots    911 What’s New with Database Snapshots    912 What Are Database Snapshots?    912 Limitations and Restrictions of Database Snapshots    917 Copy-on-Write Technology    918 When to Use Database Snapshots    919 Reverting to a Snapshot for Recovery Purposes    919 Safeguarding a Database Prior to Making Mass Changes    921 Providing a Testing (or Quality Assurance) Starting Point (Baseline)    921 Providing a Point-in-Time Reporting Database    922 Providing a Highly Available and Offloaded Reporting Database from a Database Mirror    923 Setup and Breakdown of a Database Snapshot    924 Creating a Database Snapshot    925 Removing a Database Snapshot    930 Reverting to a Database Snapshot for Recovery    930 Reverting a Source Database from a Database Snapshot    930 Using Database Snapshots with Testing and QA    931 Database Snapshots Maintenance and Security Considerations    932 Security for Database Snapshots    932 Snapshot Sparse File Size Management    932 Number of Database Snapshots per Source Database    933 Summary    933 29  Database Maintenance    935 What’s New in Database Maintenance    936 The Maintenance Plan Wizard    936 Backing Up Databases    937 Checking Database Integrity    941 Shrinking Databases    942 Maintaining Indexes and Statistics    943 Scheduling a Maintenance Plan    947 Managing Maintenance Plans Without the Wizard    950 Executing a Maintenance Plan    954 Maintenance Without a Maintenance Plan    955 Database Maintenance Policies    956 Summary    956 Part VI  SQL Server Performance and Optimization 30  Data Structures, Indexes, and Performance    959 What’s New for Data Structures, Indexes, and Performance    960 Understanding Data Structures    960 Database Files and Filegroups    961 Primary Data File    962 Secondary Data Files    963 The Log File    963 File Management    963 Using Filegroups    965 FILESTREAM Filegroups    967 Database Pages    969 Page Types    970 Data Pages    971 Row-Overflow Pages    977 LOB Data Pages    978 Index Pages    980 Space Allocation Structures    981 Extents    981 Global and Shared Global Allocation Map Pages    982 Page Free Space Pages    983 Index Allocation Map Pages    983 Differential Changed Map Pages    984 Bulk Changed Map Pages    984 Data Compression    985 Row-Level Compression    985 Page-Level Compression    988 The CI Record    990 Implementing Page Compression    990 Evaluating Page Compression    991 Managing Data Compression with SSMS    994 Understanding Table Structures    995 Heap Tables    997 Clustered Tables    999 Understanding Index Structures    1000 Clustered Indexes    1001 Nonclustered Indexes    1004 Columnstore Indexes    1008 Data Modification and Performance    1013 Inserting Data    1013 Deleting Rows    1016 Updating Rows    1017 Index Utilization    1018 Index Selection    1020 Evaluating Index Usefulness    1021 Index Statistics    1024 The Statistics Histogram    1026 How the Statistics Histogram Is Used    1028 Index Densities    1029 Estimating Rows Using Index Statistics    1030 Generating and Maintaining Index and Column Statistics    1031 SQL Server Index Maintenance    1040 Setting the Fill Factor    1050 Reapplying the Fill Factor    1052 Disabling Indexes    1052 Managing Indexes with SSMS    1054 Index Design Guidelines    1054 Clustered Index Indications    1055 Nonclustered Index Indications    1057 Index Covering    1059 Included Columns    1061 Wide Indexes Versus Multiple Indexes    1062 Indexed Views    1062 Indexes on Computed Columns    1064 Filtered Indexes and Statistics    1065 Creating and Using Filtered Indexes    1067 Creating and Using Filtered Statistics    1069 Choosing Indexes: Query Versus Update Performance    1070 Identifying Missing Indexes    1071 The Database Engine Tuning Advisor    1072 Missing Index Dynamic Management Objects    1072 Missing Index Feature Versus Database Engine Tuning Advisor    1074 Identifying Unused Indexes    1075 Summary    1078 31  Understanding Query Optimization    1079 What’s New in Query Optimization    1080 What Is the Query Optimizer?    1080 Query Compilation and Optimization    1081 Compiling DML Statements    1081 Optimization Steps    1082 Query Analysis    1083 Identifying Search Arguments    1083 Identifying OR Clauses    1083 Identifying Join Clauses    1084 Row Estimation and Index Selection    1085 Evaluating SARG and Join Selectivity    1085 Estimating Access Path Cost    1090 Using Multiple Indexes    1097 Optimizing with Indexed Views    1104 Optimizing with Filtered Indexes    1107 Join Selection    1109 Join Processing Strategies    1109 Determining the Optimal Join Order    1114 Subquery Processing    1115 Execution Plan Selection    1118 Query Plan Caching    1121 Query Plan Reuse    1121 Query Plan Aging    1123 Recompiling Query Plans    1124 Monitoring the Plan Cache    1125 Other Query Processing Strategies    1133 Predicate Transitivity    1133 Group by Optimization    1134 Queries with DISTINCT    1134 Queries with UNION    1135 Queries Using Columnstore Indexes    1136 Parallel Query Processing    1137 Parallel Query Configuration Options    1138 Identifying Parallel Queries    1139 Common Query Optimization Problems    1140 Out-of-Date or Insufficient Statistics    1140 Poor Index Design    1141 Search Argument Problems    1141 Large Complex Queries    1143 Triggers    1143 Managing the Optimizer    1144 Optimizer Hints    1145 Forced Parameterization    1151 Using the USE PLAN Query Hint    1153 Using Plan Guides    1155 Limiting Query Plan Execution with the Query Governor    1163 Summary    1166 32  Query Analysis    1167 What’s New in Query Analysis    1168 Query Analysis in SSMS    1168 Execution Plan ToolTips    1169 Logical and Physical Operator Icons    1173 Analyzing Stored Procedures    1181 Saving and Viewing Graphical Execution Plans    1181 Displaying Execution Plan XML    1183 Missing Index Hints    1184 SSMS Client Statistics    1188 Using the SET SHOWPLAN Options    1190 SHOWPLAN_TEXT    1190 SHOWPLAN_ALL    1192 SHOWPLAN_XML    1193 Using sys.dm_exec_query_plan    1194 Query Statistics    1196 STATISTICS IO    1196 STATISTICS TIME    1199 Using datediff() to Measure Runtime    1202 STATISTICS PROFILE    1203 STATISTICS XML    1203 Query Analysis with SQL Server Profiler    1204 Summary    1206 33  Locking and Performance    1207 What’s New in Locking and Performance    1207 The Need for Locking    1207 Transaction Isolation Levels in SQL Server    1208 Read Uncommitted Isolation    1209 Read Committed Isolation    1210 Read Committed Snapshot Isolation    1211 Repeatable Read Isolation    1212 Serializable Read Isolation    1213 Snapshot Isolation    1213 The Lock Manager    1216 Monitoring Lock Activity in SQL Server    1217 Querying the sys.dm_tran_locks View    1217 Viewing Locking Activity with SQL Server Profiler    1221 Monitoring Locks with Performance Monitor    1223 SQL Server Lock Types    1225 Shared Locks    1226 Update Locks    1227 Exclusive Locks    1228 Intent Locks    1228 Schema Locks    1229 Bulk Update Locks    1230 SQL Server Lock Granularity    1230 Serialization and Key-Range Locking    1232 Using Application Locks    1236 Index Locking    1239 Row-Level Versus Page-Level Locking    1240 Lock Escalation    1241 Lock Compatibility    1243 Locking Contention and Deadlocks    1243 Identifying Locking Contention    1244 Setting the Lock Timeout Interval    1247 Minimizing Locking Contention    1248 Deadlocks    1249 Table Hints for Locking    1263 Transaction Isolation-Level Hints    1264 Lock Granularity Hints    1266 Lock Type Hints    1266 Optimistic Locking    1266 Optimistic Locking Using the rowversion Data Type    1267 Optimistic Locking with Snapshot Isolation    1269 Summary    1272 34  Database Design and Performance    1273 What’s New in Database Design and Performance    1273 Basic Tenets of Designing for Performance    1274 Logical Database Design Issues    1275 Normalization Conditions    1275 Normalization Forms    1275 Benefits of Normalization    1277 Drawbacks of Normalization    1277 Denormalizing a Database    1277 Denormalization Guidelines    1278 Essential Denormalization Techniques    1279 Database Filegroups and Performance    1285 RAID Technology    1287 RAID Level 0    1287 RAID Level 1    1289 RAID Level 10    1290 RAID Level 5    1290 SQL Server and SAN Technology    1292 What Is a SAN?    1292 SAN Considerations for SQL Server    1293 Summary    1294 35  Monitoring SQL Server Performance    1295 What’s New in Monitoring SQL Server Performance    1296 Performance Monitoring Tools    1297 The Data Collector and the MDW    1297 SQL Server Utility    1319 SQL Server Extended Events    1323 Windows Performance Monitor    1359 A Performance Monitoring Approach    1371 Monitoring the Network Interface    1372 Monitoring the Processors    1374 Monitoring Memory    1379 Monitoring the Disk System    1382 Monitoring SQL Server’s Disk Activity    1384 Monitoring Other SQL Server Performance Items    1385 Summary    1386 36  SQL Server Database Engine Tuning Advisor    1387 What’s New in SQL Server Database Engine Tuning Advisor    1387 SQL Server Instance Architecture    1388 Database Engine Tuning Advisor    1389 The Database Engine Tuning Advisor GUI    1390 The Database Engine Tuning Advisor Command Line    1397 Summary    1405 37  Managing Workloads with the Resource Governor    1407 What’s New for Resource Governor    1408 Overview of Resource Governor    1408 Resource Governor Components    1410 Classification    1410 Resource Pools    1410 Workload Groups    1412 Configuring Resource Governor    1413 Enabling Resource Governor    1413 Defining Resource Pools    1415 Defining Workload Groups    1417 Creating a Classifier Function    1421 Monitoring Resource Usage    1424 Modifying Your Resource Governor Configuration    1428 Deleting Workload Groups    1429 Deleting Resource Pools    1430 Modifying a Classifier Function    1431 Summary    1431 38  A Performance and Tuning Methodology    1433 The Full Architectural Landscape    1434 Primary Performance and Tuning Handles    1435 A Performance and Tuning Methodology    1436 Designing In Performance and Tuning from the Start    1437 Code and Test    1440 Performance and Tuning for an Existing Implementation    1442 Performance and Tuning Design Guidelines    1448 Hardware and Operating System Guidelines    1448 SQL Server Instance Guidelines    1450 Database-Level Guidelines    1451 Table Design Guidelines    1451 Indexing Guidelines    1453 View Design Guidelines    1455 Transact-SQL Guidelines    1455 Application Design Guidelines    1459 Distributed Data Guidelines    1460 High-Availability Guidelines    1460 Tools of the Performance and Tuning Trade    1461 Microsoft Out-of-the-Box    1461 Third-Party Performance and Tuning Tools    1462 Summary    1464 Part VII  SQL Server High Availability 39  SQL Server High Availability Fundamentals    1467 What’s New in High Availability    1468 What Is High Availability?    1469 The Fundamentals of HA    1471 Hardware Factors    1471 Backup Considerations    1471 Operating System Upgrades    1472 Vendor Agreements Followed    1472 Training Kept Up-to-Date    1472 Quality Assurance Done Well    1472 Standards/Procedures Followed    1472 Server Instance Isolation    1472 Building Solutions with One or More HA Options    1474 Failover Cluster Services (FCS)    1475 SQL Clustering    1476 AlwaysOn Failover Clustering Instance (FCI)    1478 AlwaysOn Availability Groups    1479 Data Replication    1480 Change Data Capture    1482 Log Shipping    1482 Database Mirroring    1484 Combining Failover with Scale-Out Options    1485 Other HA Techniques That Yield Great Results    1486 High Availability from the Windows Server Family Side    1489 Microsoft Virtual Machines and Hyper-V    1489 Summary    1490 40  Data Replication    1493 What’s New in Data Replication    1494 What Is Replication?    1495 The Publisher, Distributor, and Subscriber Magazine Metaphor    1497 Publications and Articles    1498 Filtering Articles    1498 Replication Scenarios    1503 The Central Publisher Replication Model    1503 The Central Publisher with Remote Distributor Replication Model    1504 The Publishing Subscriber Replication Model    1506 The Central Subscriber Replication Model    1507 The Multiple Publishers with Multiple Subscribers Replication Model    1507 The Updating Subscribers Replication Model    1509 The Peer-to-Peer Replication Model    1511 Subscriptions    1511 Anonymous Subscriptions (Pull Subscriptions)    1513 The Distribution Database    1513 Replication Agents    1515 The Snapshot Agent    1516 The Log Reader Agent    1518 The Distribution Agent    1519 The Merge Agent    1520 Other Specialized Agents    1520 Planning for SQL Server Data Replication    1521 Autonomy, Timing, and Latency of Data    1522 Methods of Data Distribution    1522 SQL Server Replication Types    1523 Snapshot Replication    1523 Transactional Replication    1524 Merge Replication    1525 Basing the Replication Design on User Requirements    1526 Data Characteristics    1528 Setting Up Replication    1529 Creating a Distributor and Enabling Publishing    1529 Creating a Publication    1535 Horizontal and Vertical Filtering    1542 Creating Subscriptions    1544 Scripting Replication    1550 Monitoring Replication    1555 Replication Monitoring SQL Statements    1555 Monitoring Replication within SQL Server Management Studio    1557 Troubleshooting Replication Failures    1559 Peer-to-Peer Replication    1560 The Performance Monitor    1566 Replication in Heterogeneous Environments    1566 Backup and Recovery in a Replication Configuration    1567 Some Thoughts on Performance    1568 Log Shipping    1569 Data Replication and Database Mirroring or AlwaysOn Features for Fault Tolerance and High Availability    1569 Change Data Capture    1570 The Change Data Capture Tables    1571 Enabling CDC for a Database    1572 Enabling CDC for a Table    1573 Querying the CDC Tables    1575 CDC and DDL Changes to Source Tables    1581 CDC and AlwaysOn Availability Groups    1582 Change Tracking    1582 Implementing Change Tracking    1583 Identifying Tracked Changes    1585 Identifying Changed Columns    1589 Change Tracking Overhead    1590 Summary    1591 41  Database Mirroring    1593 What’s New in Database Mirroring    1594 What Is Database Mirroring?    1594 Copy-on-Write Technology    1596 When to Use Database Mirroring    1597 Roles of the Database Mirroring Configuration    1597 Playing Roles and Switching Roles    1598 Database Mirroring Operating Modes    1598 Setting Up and Configuring Database Mirroring    1599 Getting Ready to Mirror a Database    1600 Creating the Endpoints    1603 Granting Permissions    1605 Creating the Database on the Mirror Server    1605 Identifying the Other Endpoints for Database Mirroring    1607 Configuring Database Mirroring by Using the Wizard    1609 Monitoring a Mirrored Database Environment    1616 Removing Mirroring    1619 Testing Failover from the Principal to the Mirror    1621 Client Setup and Configuration for Database Mirroring    1623 Using Replication and Database Mirroring Together    1624 Using Database Snapshots from a Mirror for Reporting    1625 Summary    1627 42  SQL Server Failover Clustering    1629 What’s New in SQL Server Failover Clustering    1630 How Microsoft SQL Server Failover Clustering Works    1630 Understanding WSFC    1632 Extending WSFC with NLB    1636 How WSFC Sets the Stage for SQL Server Clustering    1637 Installing SQL Server Failover Clustering    1638 Configuring SQL Server Database Disks    1639 Installing Network Interfaces    1641 Installing WSFC    1641 Cluster Events    1642 Installing SQL Server within WSFC    1642 Fail Over to Another Node    1655 The Client Connection Impact of a Failover    1655 Potential Problems to Watch Out for with SQL Server Failover Clustering    1658 Summary    1659 43  SQL Server AlwaysOn and Availability Groups    1661 SQL Server AlwaysOn and Availability Groups    1661 Windows Failover Cluster Services    1662 AlwaysOn Failover Clustering Instances    1662 AlwaysOn Availability Groups    1663 Combining Failover with Scale-Out Options    1666 Building a Multinode AlwaysOn Configuration    1666 Adding Replicas    1681 Dashboard and Monitoring    1681 Summary    1683 Part VIII  SQL Server Application Development 44  What’s New for Transact-SQL in SQL Server 2012    1687 THROW Statement    1687 The SEQUENCE Object    1693 SEQUENCE Objects Versus Identity Columns    1701 New Conversion Functions    1702 The PARSE Function    1702 The TRY_PARSE Function    1703 The TRY_CONVERT Function    1704 New Logical Functions    1705 The CHOOSE Function    1705 The IIF Function    1706 New String Functions    1706 CONCAT    1706 FORMAT    1708 New date and time Functions    1714 Some Recently Added Date and Time Features You May Have Missed    1717 Date and Time Conversions    1720 SQL Server 2012 Enhancements to Windowing Functions    1722 The OVER Clause    1722 Ranking Functions    1723 Calculating Aggregates with the OVER Clause    1729 SQL Server 2012 Enhancements to Windowing Functions    1730 Ad Hoc Query Paging    1744 New Features and Enhancements to Spatial Data    1747 The Spatial Data Types    1747 Spatial Instance Types    1748 Other SQL Server 2012 Enhancements    1749 Representing Spatial Data in SQL Server 2012    1750 Working with Geometry Data    1751 Working with Geography Data    1754 Spatial Data Support in SSMS    1756 Spatial Data Types: Where to Go from Here?    1759 Summary    1759 45  Transact-SQL Programming Guidelines, Tips, and Tricks    1761 General T-SQL Coding Recommendations    1762 Provide Explicit Column Lists    1762 Qualify Object Names with a Schema Name    1764 Avoid SQL Injection Attacks When Using Dynamic SQL    1768 Comment Your T-SQL Code    1778 General T-SQL Performance Recommendations    1779 UNION Versus UNION ALL Performance    1779 Use IF EXISTS Instead of SELECT COUNT(*)    1779 Avoid Unnecessary ORDER BY or DISTINCT Clauses    1779 Temp Tables Versus Table Variables Versus Common Table Expressions    1780 Avoid Unnecessary Function Executions    1781 Cursors and Performance    1782 Variable Assignment in UPDATE Statements    1784 In Case You Missed It: Recently Added T-SQL Features    1788 TOP Enhancements    1788 The OUTPUT Clause    1793 MERGE Statement    1798 MERGE Statement Best Practices and Guidelines    1804 Insert over DML    1805 Common Table Expressions    1807 Recursive Queries with CTEs    1809 Using the Hierarchyid Data Type    1818 Creating a Hierarchy    1818 Populating the Hierarchy    1819 Querying the Hierarchy    1821 Modifying the Hierarchy    1825 Using Row Numbers for Paging Results    1830 De-Duping Data with Ranking Functions    1834 PIVOT and UNPIVOT    1836 The APPLY Operator    1841 CROSS APPLY    1841 OUTER APPLY    1843 The TABLESAMPLE Clause    1843 Variable Assignment in DECLARE Statements    1847 Compound Assignment Operators    1848 GROUP BY Clause Enhancements    1848 GROUPING SETS    1849 The grouping_id() Function    1852 General T-SQL Tips and Tricks    1854 Date Calculations    1854 Using CONTEXT_INFO    1857 Working with Outer Joins    1859 Generating T-SQL Statements with T-SQL    1869 Summary    1869 46  Advanced Stored Procedure Programming and Optimization    1871 T-SQL Stored Procedure Coding Guidelines    1871 Calling Stored Procedures from Transactions    1874 Handling Errors in Stored Procedures    1877 Using Cursors in Stored Procedures    1881 Using CURSOR Variables in Stored Procedures    1886 Nested Stored Procedures    1892 Recursive Stored Procedures    1893 Using Temporary Tables in Stored Procedures    1897 Temporary Table Performance Tips    1899 Using the table Data Type    1901 Stored Procedure Performance    1903 Query Plan Caching    1904 The SQL Server Plan Cache    1904 Shared Query Plans    1905 Automatic Query Plan Recompilation    1905 Forcing Recompilation of Query Plans    1908 Using Dynamic SQL in Stored Procedures    1912 Using sp_executesql    1915 Installing and Using .NET CLR Stored Procedures    1918 Adding CLR Stored Procedures to a Database    1919 T-SQL or CLR Stored Procedures?    1920 Using xp_cmdshell    1921 Summary    1922 47  SQL Server and the .NET Framework    1925 What’s New in SQL Server 2012 and the .NET Framework    1925 Getting Comfortable with ADO.NET 4.5 and SQL Server 2012    1926 Essential ADO.NET Development Techniques    1926 Developing with LINQ to SQL    1933 Getting Started with LINQ to SQL    1933 Going Deeper    1935 Uncovering LINQ to SQL with LINQPad    1938 Introducing the Entity Framework    1942 Getting Started    1943 Data Operations with EF Entities    1946 Using WCF Data Services    1951 Getting Set Up    1951 Essentials    1951 Building Your Data Service    1951 CRUD Operations    1958 Summary    1963 48  SQLCLR: Developing SQL Server Objects in .NET    1965 What’s New for SQLCLR in SQL Server 2012    1965 Developing Custom Managed Database Objects    1968 An Introduction to Custom Managed Database Objects    1968 Managed Object Permissions    1969 Developing Managed Objects with Visual Studio 2012    1971 Developing Managed Stored Procedures    1972 Developing Managed User-Defined Functions (UDFs)    1977 Developing Managed User-Defined Types (UDTs)    1987 Developing Managed User-Defined Aggregates (UDAs)    1995 Developing Managed Triggers    1998 Using Transactions    2004 Using the Related System Catalogs    2006 Summary    2007 49  Using XML in SQL Server 2012    2009 What’s New in Using XML in SQL Server 2012    2009 Understanding XML    2011 Relational Data as XML: The FOR XML Modes    2011 RAW Mode    2012 AUTO Mode    2018 EXPLICIT Mode    2022 PATH Mode    2026 FOR XML and the xml Data Type    2029 XML as Relational Data: Using OPENXML    2031 Using the xml Data Type    2035 Defining and Using xml Columns    2036 Using XML Schema Collections    2039 The Built-in xml Data Type Methods    2045 Indexing and Full-Text Indexing of xml Columns    2063 Indexing xml Columns    2064 Full-Text Indexing xml Columns    2077 Summary    2078 50  SQL Server Service Broker    2079 What’s New in Service Broker    2079 Understanding Distributed Messaging    2080 The Basics of Service Broker    2080 Example System Design    2084 Understanding Service Broker Constructs    2085 Defining Messages and Choosing a Message Type    2085 Setting Up Contracts for Communication    2090 Creating Queues for Message Storage    2091 Defining Services to Send and Receive Messages    2093 Planning Conversations Between Services    2094 Service Broker Routing and Security    2105 Using Certificates for Conversation Encryption    2105 A Final Note on the Sample System    2113 Troubleshooting SSB Applications with ssbdiagnose.exe    2113 AlwaysOn Availability Group Support    2114 Related System Catalogs    2115 Summary    2117 51  SQL Server Full-Text Search    2119 What’s New in SQL Server 2012 Full-Text Search    2120 Installing SQL FTS    2121 Upgrade Options in SQL Server 2012    2123 How SQL Server FTS Works    2124 Indexing    2125 Searching    2131 Implementing SQL Server 2012 Full-Text Catalogs    2131 Setting Up a Full-Text Index    2134 Using T-SQL Commands to Build Full-Text Indexes    2134 Using the Full-Text Indexing Wizard to Build Full-Text Indexes and Catalogs    2152 Full-Text Searches    2155 CONTAINS and CONTAINSTABLE    2155 FREETEXT and FREETEXTTABLE    2162 Semantic Search    2164 Full-Text Search Maintenance    2166 Full-Text Search Performance    2167 Full-Text Search Troubleshooting    2167 Summary    2169 52  Working with SQL Azure    2171 Setting Up Subscriptions, Servers, and Databases    2171 Setting Up Your Windows Azure Subscription    2172 Creating a Logical Server    2174 Managing Your Server    2176 Configuring Your Firewall    2176 Using SQL Server Management Studio    2177 Using Management Portal    2179 Working with Databases    2179 Understanding SQL Database Editions    2179 Managing Databases Using T-SQL    2183 Migrating Data into SQL Database    2184 Copying Databases    2186 Backing Up and Restoring Databases    2187 Using Database Copies for Backup and Restore    2187 Using BACPAC Files for Backup and Restore    2187 Managing Logins, Users, and Roles    2189 Understanding Roles    2190 Managing Logins and Users    2190 Using SQL Database with ADO.NET    2192 Connecting to SQL Database Using a Custom Windows Application    2192 Connectivity Limitations    2196 Understanding SQL Database Billing    2203 Baseline Billing    2203 Tracking Your Usage    2204 Understanding SQL Database Limitations    2206 Unsupported and Partially Supported Functionality    2206 References    2209 Summary    2209 Part IX  SQL Server Business Intelligence Features 53  SQL Server 2012 Analysis Services    2213 What’s New in SSAS    2213 Understanding SSAS and OLAP    2215 Understanding the SSAS Environment Wizards    2217 OLAP Versus OLTP    2221 An Analytics Design Methodology    2223 An Analytics Mini-Methodology    2223 An OLAP Requirements Example: CompSales International    2225 CompSales International Requirements    2225 OLAP Cube Creation    2227 Using SQL Server SSDT    2228 Creating an OLAP Database    2229 Defining Dimensions and Hierarchies    2235 Creating the Other Dimensions    2244 Creating the Cube    2246 Building and Deploying the Cube    2249 Populating the Cube with Data    2251 Aggregating Data Within the Cube    2252 Browsing Data in the Cube    2257 Delivering Data to Users    2261 ADO MD    2263 Query Analysis and Optimization    2264 Generating a Relational Database    2266 Limitations of a Relational Database    2267 Cube Perspectives    2267 Data Mining    2268 Security and Roles    2278 Tabular Models and SSAS    2279 OLAP Performance    2280 Summary    2281 54  SQL Server Integration Services    2283 What’s New with SSIS    2284 SSIS Basics    2285 SSIS Architecture and Concepts    2290 SSIS Tools and Utilities    2293 A Data Transformation Requirement    2298 Running the SSIS Import and Export Wizard    2300 The SSIS Designer    2309 The Package Execution Utility    2318 The dtexec Utility    2320 Running Packages    2321 Running Package Examples    2325 The dtutil Utility    2326 dtutil Examples    2328 Change Data Capture and SSIS    2333 Using bcp    2333 Fundamentals of Exporting and Importing Data    2335 File Data Types    2338 Format Files    2339 Using Views    2348 Logged and Nonlogged Operations    2348 Batches    2349 Parallel Loading    2349 Supplying Hints to bcp    2350 Summary    2352 55  SQL Server 2012 Reporting Services    2353 What’s New in SSRS 2012    2353 Discontinued Functionality and Breaking Changes    2354 Reporting Services Architecture    2355 HTTP Architecture    2358 Installing and Configuring SSRS    2358 The Installation Sequence    2359 SSRS Configuration Using RSCM    2362 Developing Reports    2367 Tools of the Trade    2367 Report Basics    2368 Overview of the Report Development Process    2369 Data Planning and Preparation    2369 Using Shared Data Sources    2370 Using Datasets    2370 Using Shared Datasets    2371 Developing Reports Using SSDT    2371 Working with the Tablix    2379 Understanding Expressions    2380 Report Design Fundamentals    2383 Designing Reports Using Report Builder    2394 Report Builder and Report Model Security    2409 Enabling Report Builder    2410 Management and Security    2410 Securing Reports    2410 Subscriptions    2412 Report Execution Options    2414 Performance and Monitoring    2416 SSRS Trace Log    2416 Execution Log    2416 Windows Event Log    2417 Performance Counters    2417 Summary    2417 56  Master Data Services and Data Quality Services    2419 Master Data Services    2419 Data Quality Services    2420 What’s New in MDS and DQS    2420 Master Data Management    2421 Master Data Services Terms and Concepts    2423 Master Data Services    2426 Data Quality Services    2446 Summary    2460 57  Parallel Data Warehouse    2463 What’s New in Parallel Data Warehouse    2463 Understanding MPP and PDW    2464 MPP Architecture    2465 The PDW    2466 Data on a PDW    2468 PDW and Big Data (Hadoop)    2471 xVelocity Columnstore Indexes    2471 Columnstore Indexes    2472 Summary    2477 Part X  Bonus Chapters 58  Managing Linked Servers    2481 What’s New in Managing Linked Servers    2482 Linked Servers    2483 Distributed Queries    2483 Distributed Transactions    2484 Adding, Dropping, and Configuring Linked Servers    2485 sp_addlinkedserver    2485 sp_linkedservers    2492 sp_dropserver    2494 sp_serveroption    2494 Mapping Local Logins to Logins on Linked Servers    2495 sp_addlinkedsrvlogin    2496 sp_droplinkedsrvlogin    2497 sp_helplinkedsrvlogin    2498 Obtaining General Information About Linked Servers    2499 Executing a Stored Procedure via a Linked Server    2500 Setting Up Linked Servers Using SQL Server Management Studio    2501 Summary    2505 59  SQL Server Disaster Recovery Planning    2507 What’s New in SQL Server Disaster Recovery Planning    2508 How to Approach Disaster Recovery    2508 Disaster Recovery Patterns    2510 Recovery Objectives    2514 A Data-Centric Approach to Disaster Recovery    2516 Microsoft SQL Server Options for Disaster Recovery    2516 Data Replication    2517 Log Shipping    2519 Database Mirroring and Snapshots    2519 Change Data Capture    2521 AlwaysOn Availability Groups    2521 The Overall Disaster Recovery Process    2522 The Focus of Disaster Recovery    2523 Planning and Executing a Disaster Recovery    2530 Have You Detached a Database Recently?    2530 Third-Party Disaster Recovery Alternatives    2530 Summary    2531 60  SQL Server Configuration Options    2533 What’s New in Configuring, Tuning, and Optimizing SQL Server Options    2533 SQL Server Instance Architecture    2534 Configuration Options    2535 Fixing an Incorrect Option Setting    2542 Setting Configuration Options with SSMS    2543 Obsolete Configuration Options    2543 Configuration Options and Performance    2544 access check cache bucket count    2544 access check cache quota    2544 ad hoc distributed queries    2545 affinity I/O mask    2545 affinity mask    2547 Agent XP    2548 backup compression default    2549 blocked process threshold    2550 c2 audit mode    2550 clr enabled    2550 common criteria compliance enabled    2551 contained database authentication    2551 cost threshold for parallelism    2552 cross db ownership chaining    2553 cursor threshold    2553 Database Mail XPs    2554 default full-text language    2554 default language    2556 default trace enabled    2558 disallow results from triggers    2558 EKM provider enabled    2559 filestream_access_level    2559 fill factor    2559 index create memory    2560 in-doubt xact resolution    2560 lightweight pooling    2561 locks    2561 max degree of parallelism    2562 max server memory and min server memory    2562 max text repl size    2564 max worker threads    2565 media retention    2566 min memory per query    2566 nested triggers    2567 network packet size    2567 Ole Automation Procedures    2568 optimize for ad hoc workloads    2568 PH_timeout    2569 priority boost    2569 query governor cost limit    2570 query wait    2571 recovery interval    2571 remote access    2572 remote admin connections    2572 remote login timeout    2573 remote proc trans    2573 remote query timeout    2573 scan for startup procs    2575 show advanced options    2575 user connections    2575 user options    2577 XP-Related Configuration Options    2577 Summary    2578 Index    2579