INTRODUCTION xxxvii CHAPTER 1: SQL SERVER 2014 ARCHITECTURE 1 SQL Server 2014 Ecosystem 1 New Important Features in 2014 2 Production DBA 2 Development DBA 3 Business Intelligence DBA and Developer 4 SQL Server Architecture 5 Database Files and Transaction Log 5 SQL Server Native Client 6 Standard System Databases 7 Schemas 9 Synonyms 10 Dynamic Management Objects 10 SQL Server 2014 Data Types 12 Editions of SQL Server 18 Edition Overview 18 Licensing 21 Summary 22 CHAPTER 2: INSTALLING SQL SERVER 2014 BEST PRACTICES 23 Planning the System 24 Hardware Options 24 Software and Install Options 29 Installing SQL Server 32 New Installs 32 Side-by-Side Installs 32 Upgrades 33 Attended Installations 33 Unattended Installs 37 Installing Analysis Services 43 Multidimensional and Data Mining Mode (UDM Mode) 44 Tabular Mode 46 Installing PowerPivot for SharePoint 47 Burning in the System 48 Post-Install Configuration 49 Configuring SQL Server Settings for Performance 49 tempdb 51 Configuring SQL Server Settings for Security 52 SQL Server Configuration Manager 54 Back It Up 54 Uninstalling SQL Server 54 Uninstalling Reporting Services 55 Uninstalling Analysis Services 55 Uninstalling the SQL Server Database Engine 55 Troubleshooting a Failed Install 55 Summary 56 CHAPTER 3: UPGRADING SQL SERVER 2014 BEST PRACTICES 57 Why Upgrade to SQL Server 2014? 57 Risk Mitigation--the Microsoft Contribution 58 Independent Software Vendors and SQL Community Contributions 59 Upgrading to SQL Server 2014 59 In-Place Upgrading 60 Side-by-Side Upgrade 62 In-Place Upgrade versus Side-by-Side Upgrade Considerations 62 Pre-Upgrade Steps and Tools 63 Pre-Upgrade Steps 63 Pre-Upgrade Tools 64 Backward Compatibility 72 Unsupported and Discontinued Features in SQL Server 2014 72 SQL Server 2014 Deprecated Database Features 74 Other SQL Server 2014 Changes Affecting Behavior 74 SQL Server Component Considerations 75 Upgrading Full-Text Catalog 75 Upgrading Reporting Services 75 Upgrading to 64-Bit 76 Post-Upgrade Checks 76 Summary 77 CHAPTER 4: MANAGING AND TROUBLESHOOTING THE DATABASE ENGINE 79 Configuration and Administration Tools 80 SQL Server Configuration Manager 80 Startup Parameters 82 Startup Stored Procedures 84 Partially Contained Databases 86 Troubleshooting Tools 87 Dedicated Administrator Connection 87 Rebuilding the System Databases 89 SQL Server Management Studio 90 Reports 90 Configuring SQL Server in SQL Server Management Studio 93 Filtering Objects 97 Error Logs 97 Activity Monitor 98 Monitoring Processes in T-SQL 104 sp_who and sp_who2 104 sys.dm_exec_connections 105 sys.dm_exec_sql_text 105 Multiserver Management 106 Central Management Servers and Server Groups 106 Trace Flags 107 Getting Help from Support 109 SQLDumper.exe 109 SQLDiag.exe 110 Summary 112 CHAPTER 5: AUTOMATING SQL SERVER 113 Maintenance Plans 114 Maintenance Plan Wizard 114 Maintenance Plan Designer 119 Automating SQL Server with SQL Server Agent 122 Jobs 123 Schedules 128 Operators 129 Alerts 133 SQL Server Agent Security 138 Service Account 138 Access to SQL Agent 138 SQL Server Agent Proxies 139 Configuring SQL Server Agent 143 General Properties 143 Advanced Properties 144 Alert System Properties 145 Job System Properties 146 Connection Properties 147 History Properties 147 Multiserver Administration 147 Using Token Replacement 148 Event Forwarding 150 Using WMI 151 Multiserver Administration--Using Master and Target Servers 152 Summary 154 CHAPTER 6: SERVICE BROKER IN SQL SERVER 2014 155 Asynchronous Messaging 155 SQL Service Broker Overview 157 SQL Server Service Broker versus Other Message Queues 158 Configuring SQL Server Service Broker 159 Setting Broker State 159 Message Types 161 Contracts 162 Queues 163 Services 165 Routes 166 Priorities 167 Conversation Groups 168 Using SQL Server Service Broker 169 Sending Messages 169 Receiving Messages 172 Sending Messages between Instances 174 External Activation 176 Log User Example 177 Summary 184 CHAPTER 7: SQL SERVER CLR INTEGRATION 185 Introduction to CLR 185 SQL Server as a .NET Runtime Host 187 Application Domains 187 T-SQL versus CLR 187 Enabling CLR Integration 188 Creating CLR Assemblies 189 The Non-Visual Studio Way 189 Using Microsoft SQL Server Data Tools 191 CLR Integration Security 193 Performance Monitoring 194 Windows System Monitor 194 Extended Events 196 Dynamic Management Views (DMVs) 197 CLR Integration Design Goals 197 Summary 198 CHAPTER 8: SECURING THE DATABASE INSTANCE 199 Authentication Types 200 SQL Authentication 200 Windows Authentication 201 SQL versus Windows Authentication 202 Logins and Users 202 Authorizing Securables 202 Server Securables 203 Database Securables 209 Permission Chains 210 Cross-Database Permission Chains 211 Row-Level Security 213 Summary 214 CHAPTER 9: IN-MEMORY OLTP 215 Using and Implementing In-Memory OLTP 215 Enabling In-Memory OLTP 216 In-Memory OLTP Structures 218 Records 218 Indexes 219 CPU Considerations 224 Virtualization Considerations 224 Memory Considerations 225 Managing Memory with Resource Governor 227 Creating Natively Compiled Stored Procedures 230 Overview of the Analyze, Migrate, and Report (ARM) Tool 233 Summary 246 CHAPTER 10: CONFIGURING THE SERVER FOR OPTIMAL PERFORMANCE 247 What Every DBA Needs to Know About Performance 248 The Performance Tuning Cycle 248 Configuration 250 Power Configuration 250 Instant Database File Initialization 252 Trace Flags 256 Defining Good Performance 256 Focus on What''s Most Important 257 What the Developer DBA Needs to Know About Performance 258 Users 258 SQL Statements 259 Data Usage Patterns 259 Database Schema 259 What the Production DBA Needs to Know About Performance 260 Optimizing the Server''s Hardware 261 Hardware Management 262 CPU 263 x64 263 Cache 264 Hyper-Threading 264 Multicore Terminology 265 Memory 266 Physical Memory 266 Physical Address Space 267 Virtual Memory Manager 267 The Page File 267 Page Faults 268 I/O 269 Network 270 Magnetic Disks 271 Solid State/Flash Drives 274 Storage Considerations 274 Summary 278 CHAPTER 11: OPTIMIZING SQL SERVER 2014 281 Application Optimization 281 Defining a Workload 282 The Silent Killer: I/O Problems 282 SQL Server I/O Process Model 282 Database File Placement 283 tempdb Considerations 284 SQL Server Internals and File Allocations 288 Table and Index Partitioning 290 Why Consider Partitioning? 290 Creating a Partition Function 292 Creating Filegroups 294 Creating a Partition Scheme 295 Data Compression 296 Row Compression 296 Page Compression 299 Estimating Space Savings 300 Monitoring Data Compression 302 Data Compression Considerations 303 Understanding SQL Server and CPUs 304 NUMA and Hot Add CPUs 305 Cache Coherency 306 Affinity Mask 307 Max Degree of Parallelism (MAXDOP) 307 Cost Threshold for Parallelism 308 Memory Considerations and Enhancements 310 Buffer Pool Extensions 310 Tuning SQL Server Memory 312 Data Locality 314 Max Server Memory 315 Resource Governor 316 The Basic Elements of Resource Governor 317 Using Resource Governor from SQL Server 2014 Management Studio 321 Monitoring Resource Governor 322 Summary 323 CHAPTER 12: MONITORING YOUR SQL SERVER 325 The Goal of Monitoring 326 Determining Your Monitoring Objectives 326 Establishing a Baseline 327 Comparing Current Metrics to the Baseline 327 Choosing the Appropriate Monitoring Tools 328 Performance Monitor 329 CPU Resource Counters 331 Disk Activity 332 Memory Usage 338 Performance Monitoring Tools 341 Monitoring Events 343 The Default Trace 345 system_health Session 346 SQL Trace 347 Event Notifications 351 SQL Server Extended Events 354 Monitoring with Dynamic Management Views and Functions 375 What''s Going on Inside SQL Server? 377 Viewing the Locking Information 380 Viewing Blocking Information 380 Index Usage in a Database 381 Indexes Not Used in a Database 382 View Queries Waiting for Memory Grants 383 Connected User Information 384 Filegroup Free Space 384 Query Plan and Query Text for Currently.