Introduction 1 Who This Book Is For 2What This Book Covers 2Conventions Used in This Book 4Good Luck! 5 Part I Welcome to Microsoft SQL Server 1 SQL Server 2014 Overview 9 SQL Server Components and Features 9The SQL Server Database Engine 10SQL Server 2014 Administration and Management Tools 12Replication 15Merge Replication 16SQL Server AlwaysOn Features 17SQL Server Service Broker 18Full-Text and Semantic Search 18SQL Server Integration Services (SSIS) 20SQL Server Analysis Services (SSAS) 21SQL Server Reporting Services (SSRS) 23Master Data Services 23Data Quality Services 24SQL Server 2014 Editions 24SQL Server 2014 Standard Edition 25SQL Server 2014 Enterprise Edition 26Differences Between the Enterprise and Standard Editions of SQL Server 26Other SQL Server 2014 Editions 28SQL Server Licensing 30Web Edition Licensing 31Developer Edition Licensing 32Express Edition Licensing 32Choosing a Licensing Model 32Mixing Licensing Models 32Licensing SQL Server of High Availability 32Licensing SQL Server in a Virtual Environment 34Summary 35 2 What''s New in SQL Server 2014 37 New SQL Server 2014 Features 37Memory-Optimized Tables/In-Memory OLTP 38New Cardinality Estimation Logic 38Delayed Durability for Transactions 38Buffer Pool Extension 38SQL Server Data Tools for Business Intelligence 39SQL Server 2014 Enhancements 39Resource Governor Enhancements 39Security Enhancements 39Backup and Restore Enhancements 40Indexing Enhancements 40Monitoring Enhancements 41SQL Server AlwaysOn and Availability Groups Enhancements 42New Transact-SQL Enhancements 42Deprecated and Discontinued Features 42Summary 45 Part II SQL Server Tools and Utilities 3 SQL Server Management Studio 49 What''s New in SSMS 50The Integrated Environment 50Window Management 50Integrated Help 53Administration Tools 56Registered Servers 56Object Explorer 58Activity Monitor 60Log File Viewer 62SQL Server Utility 64Development Tools 69The Query Editor 69Managing Projects in SSMS 77Integrating SSMS with Source Control 78Using SSMS Templates 80Using SSMS Snippets 84T-SQL Debugging 85Multiserver Queries 86Summary 87 4 SQL Server Command-Line Utilities 89 What''s New in SQL Server Command-Line Utilities 90The sqlcmd Command-Line Utility 91Executing the sqlcmd Utility 93Using Scripting Variables with sqlcmd 95The dta Command-Line Utility 96The tablediff Command-Line Utility 99The bcp Command-Line Utility 102The sqldiag Command-Line Utility 103The sqlservr Command-Line Utility 105The sqlLocalDB Command-Line Utility 106Summary 108 5 SQL Server Profiler 111 What''s New with SQL Server Profiler 111SQL Server Profiler Architecture 112Creating Traces 113Events 115Data Columns 117Filters 120Executing Traces and Working with Trace Output 122Saving and Exporting Traces 123Saving Trace Output to a File 123Saving Trace Output to a Table 124Saving the Profiler GUI Output 124Importing Trace Files 125Importing a Trace File into a Trace Table 125Analyzing Trace Output with the Database Engine Tuning Advisor 128Replaying Trace Data 128Defining Server-Side Traces 131Monitoring Running Traces 141Stopping Server-Side Traces 143Profiler Usage Scenarios 145Analyzing Slow Stored Procedures or Queries 145Deadlocks 146Identifying Ad Hoc Queries 148Identifying Performance Bottlenecks 148Monitoring Auto-Update Statistics 150Monitoring Application Progress 151Summary 153 6 SQL Distributed Replay 155 What''s New for Distributed Replay 155Overview of Distributed Replay 155Distributed Replay Components 156Distributed Replay Administrative Tool 157Distributed Replay Controller 157Distributed Replay Clients 158Target Server 158Configuring Distributed Replay 158Controller Configuration File 159Client Configuration File 159Preprocess Configuration File 160Replay Configuration File 161Replay the Trace Data 163Configure Permissions and Security 163Capture the Workload 165Preprocess the Trace File 166Apply the Workload 167Summary 169 Part III SQL Server Administration 7 SQL Server System and Database Administration 173 What''s New in SQL Server System and Database Administration 173System Administrator Responsibilities 174System Databases 174The master Database 175The resource Database 176The model Database 176The msdb Database 176The distribution Database 176The tempdb Database 177Maintaining System Databases 177System Tables 178System Views 179Compatibility Views 180Catalog Views 182Information Schema Views 184Dynamic Management Views 186System Stored Procedures 189Useful System Stored Procedures 189Summary 191 8 Installing SQL Server 2014 193 What''s New in Installing SQL Server 2014 193Installation Requirements 193Hardware Requirements 194Software Requirements 195Installation Walkthrough 198Install Screens, Step-by-Step 198Installing SQL Server Documentation 217Installing SQL Server Using a Configuration File 219Running an Automated or Manual Install 224Installing SQL Server Using Sysprep 226Preparing a SQL Server Sysprep Image 226Completing a SQL Server Sysprep Image 229Modifying a SQL Server Sysprep Image 231Common Uses of SQL Server Sysprep Images 232Installing Service Packs and Cumulative Updates 233Applying a Service Pack or Cumulative Update During a New Installation 233Summary 236 9 Upgrading to SQL Server 2014 237 What''s New in Upgrading SQL Server 237The SQL Server 2014 Upgrade Matrix 237Identifying Products and Features to be Upgraded 240Using the SQL Server Upgrade Advisor (UA) 241Getting Started with the UA 241The Analysis Wizard 243The Report Viewer 249Destination: SQL Server 2014 250Side-by-Side Upgrades 251Upgrading In-Place 259Upgrading the Database Engine 260Installing Product Updates (Slipstreaming) During Upgrades 263Upgrading Using a Configuration File 264Upgrading from Pre-SQL Server 2005 Versions 266Upgrading Other SQL Server Components 266Upgrading Analysis Services 266Upgrading SQL Server Analysis Services 266Upgrading Reporting Services 266Upgrading SSIS Packages 269Migrating DTS Packages 271Summary 271 10 Client Installation and Configuration 273 What''s New in Client Installation and Configuration 273Client/Server Networking Considerations 274Server Network Protocols 275The Server Endpoint Layer 277The Role of SQL Browser 280Client Installation 281Installing the Client Tools 281Installing SNAC 282Client Configuration 284Client Configuration Using SSCM 284Connection Encryption 287Client Data Access Technologies 289Provider Choices 290Connecting Using the Various Providers and Drivers 291General Networking Considerations and Troubleshooting 296Summary 299 11 Database Backup and Restore 301 What''s New in Database Backup and Restore 301Developing a Backup and Restore Plan 302Types of Backups 303Full Database Backups 304Differential Database Backups 304Partial Backups 305Differential Partial Backups 305File and Filegroup Backups 305Copy-Only Backups 306Transaction Log Backups 306Recovery Models 306Full Recovery 307Bulk-Logged Recovery 308Simple Recovery 309Backup Devices 310Disk Devices 310Tape Devices 310Network Shares 311Media Sets and Families 311Creating Backup Devices 311Backing Up a Database 312Creating Database Backups with SSMS 312Creating Database Backups with T-SQL 315Backing Up the Transaction Log 318Creating Transaction Log Backups with SSMS 318Creating Transaction Log Backups with T-SQL 319Backup Scenarios 320Full Database Backups Only 320Full Database Backups with Transaction Log Backups 321Differential Backups 322Partial Backups 323File/Filegroup Backups 325Mirrored Backups 326Copy-Only Backups 326Compressed Backups 327Encrypted Backups 328System Database Backups 329Restoring Databases and Transaction Logs 330Restores with T-SQL 330Restoring by Using SSMS 334Restore Information 339Restore Scenarios 342Restoring to a Different Database 342Restoring a Snapshot 344Restoring a Transaction Log 344Restoring to the Point of Failure 345Restoring to a Point in Time 347Online Restores 349Restoring the System Databases 349Additional Backup Considerations 351Frequency of Backups 352Using a Standby Server 352Snapshot Backups 353Considerations for Very Large Databases 354Maintenance Plans 354Summary 355 12 Database Mail 357 What''s New in Database Mail 357Setting Up Database Mail 358Creating Mail Profiles and Accounts 359Using T-SQL to Update and Delete Mail Objects 362Setting System-Wide Mail Settings 363Testing Your Setup 364Sending and Receiving with Database Mail 364The Service Broker Architecture 364Sending Email 365Receiving Email 371Using SQL Server Agent Mail 371Job Mail Notifications 371Creating an Operator 371Enabling SQL Agent Mail 371Creating the Job 372Testing the Job-Completion Notification 373Alert Mail Notifications 373Creating an Alert 373Testing the Alert Notification 374Related Views and Procedures 375Viewing the Mail Configuration Objects 375Viewing Mail Message Data 376Summary 377 13 SQL Server Agent 379 What''s New in Scheduling and Notification 380Configuring the SQL Server Agent 380Configuring SQL Server Agent Properties 380Configuring the SQL Server Agent Startup Account 382Configuring Email Notification 384SQL Server Agent Proxy Account 385Viewing the SQL Server Agent Error Log 387SQL Server Agent Security 388Managing Operators 389Managing Jobs 391Defining Job Properties 391Defining Job Steps 392Defining Multiple Job Steps 394Defining Job Schedules 395Defining Job Notifications 397Viewing Job History 398Managing Alerts 399Defining Alert Properties 399Defining Alert Responses 402Scripting Jobs and Alerts 404Multiserver Job Management 405Creating a Master Server 406Enlisting Target Servers 407Creating Multiserver Jobs 407Event Forwarding 407Summary 408 14 SQL Server Policy-Based Management 409 What''s New in Policy-Based Management 409Introduction to Policy-Based Management 410Policy-Based Management Concepts 411Facets 411Conditions 414Policies 415Categories 415Targets 415Execution Modes 415Ce.