Training Kit (Exam 70-462) Administering Microsoft SQL Server 2012 Databases (MCSA)

Cover......Page 1
 Objectives Map......Page 2
 Copyright Page......Page 4
Contents at a Glance......Page 5
Table of Contents......Page 7
 System Requirements......Page 17
  Software Requirements......Page 18
  Prepare a Computer to Function as a Windows Server 2008 R2 Domain Controller......Page 19
  Prepare a Member Server and Join It to the Domain......Page 20
  Prepare a Third Member Server and Join It to the Domain......Page 21
  Prepare a Computer Running the Server Core Installation Option and Join It to the Domain......Page 22
 Using the Companion CD......Page 23
  How to Use the Practice Tests......Page 24
 Stay in Touch......Page 25
Preparing for the Exam......Page 26
 Before You Begin......Page 27
  Evaluating Installation Requirements......Page 28
  Designing the Installation......Page 33
  Planning Scale Up versus Scale Out Basics......Page 34
  Shrinking and Growing Databases......Page 35
  Designing the Storage for New Databases......Page 39
  Identifying Windows-Level Security and Service-Level Security......Page 41
  Performing a Core Mode Installation......Page 43
  Benchmarking a Server......Page 45
  Lesson Summary......Page 49
  Lesson Review......Page 50
  Configuring an Operating System Disk......Page 52
  Installing the SQL Server Database Engine......Page 53
  Installing SQL Server 2012 from the Command Prompt......Page 59
  Installing SQL Server Integration Services......Page 60
  Enabling and Disabling Features......Page 62
  Installing SQL Server 2012 by Using a Configuration File......Page 65
  Testing Connectivity......Page 66
  Lesson Summary......Page 78
  Lesson Review......Page 79
  Case Scenario 2: SQL Server Deployment......Page 80
  Adding and Removing Features and Adding Databases to SQL Server 2012 on a Computer Running a Server Core Operating System......Page 81
  Lesson 1......Page 82
  Lesson 2......Page 83
  Case Scenario 1......Page 84
  Case Scenario 2......Page 85
 Before You Begin......Page 87
  Instance-Level Settings......Page 88
  Database Configuration and Standardization......Page 94
  Distributed Transaction Coordinator......Page 97
  Configuring Database Mail......Page 98
  Lesson Review......Page 104
  Installing Additional Instances......Page 106
  Deploying Software Updates and Patch Management......Page 110
  Configuring Resource Governor......Page 112
  Using WSRM with Multiple Database Engine Instances......Page 117
  Cycle SQL Server Error Logs......Page 119
  Lesson Review......Page 122
  Case Scenario 2: Additional Instances and Error Log Cycling......Page 124
  Install and Manage Multiple Instances......Page 125
  Lesson 1......Page 126
  Lesson 2......Page 127
  Case Scenario 1......Page 129
  Case Scenario 2......Page 130
 Before You Begin......Page 131
  Deploying and Configuring Analysis Services......Page 132
  Deploying and Configuring Reporting Services......Page 134
  Deploying and Configuring SharePoint Integration......Page 138
  Configuring SQL Server Integration Services Security......Page 140
  Managing Full-Text Indexing......Page 142
  Configuring FILESTREAM......Page 144
  Configuring FileTables......Page 146
  Lesson Summary......Page 149
  Lesson Review......Page 150
  Designing and Managing Filegroups......Page 151
  Understanding Contained Databases......Page 154
  Using Data Compression......Page 157
  Encrypting Databases with Transparent Data Encryption......Page 161
  Partitioning Indexes and Tables......Page 163
  Managing Log Files......Page 166
  Using Database Console Commands......Page 167
  Lesson Review......Page 172
  Case Scenario 1: Configuring FILESTREAM and FileTable......Page 173
  Transparent Data Encryption and Table Partitioning......Page 174
  Lesson 1......Page 175
  Lesson 2......Page 176
  Case Scenario 2......Page 177
 Before You Begin......Page 179
  Upgrading an Instance to SQL Server 2012......Page 180
  Migrating a Database to a SQL Server 2012 Instance......Page 187
  Copying Databases to Other Servers......Page 190
  Migrating SQL Logins......Page 196
  Lesson Review......Page 199
  Copying and Exporting Data......Page 201
  Using the SQL Server Import and Export Wizard......Page 202
  Using BCP to Import and Export Data......Page 204
  Importing Data by Using BULK INSERT......Page 205
  Using Format Files......Page 206
  Preparing Data for Bulk Operations......Page 207
  SELECT INTO......Page 208
  Lesson Review......Page 210
 Case Scenarios......Page 211
  Case Scenario 2: Tailspin Toys Bulk Data......Page 212
  Import and Export Data......Page 213
  Lesson 1......Page 214
  Lesson 2......Page 215
  Case Scenario 1......Page 216
  Case Scenario 2......Page 217
 Before You Begin......Page 219
  SQL Logins......Page 220
  Server Roles......Page 227
  User-Defined Server Roles......Page 229
  Credentials......Page 230
  Lesson Summary......Page 232
  Lesson Review......Page 233
  Database Users......Page 235
  Database Roles......Page 237
  Contained Users......Page 242
  Application Roles......Page 244
  Lesson Review......Page 247
  Case Scenario 1: Instance-Level Permissions for Contoso’s Accountants......Page 248
  Manage Logins and Server Roles......Page 249
  Manage Users and Database Roles......Page 250
  Lesson 1......Page 251
  Lesson 2......Page 252
  Case Scenario 2......Page 254
 Before You Begin......Page 255
  Understanding Securables......Page 256
  Assigning Permissions on Objects......Page 258
  Managing Permissions by Using Database Roles......Page 259
  Using Schemas......Page 262
  Determining Effective Permissions......Page 264
  Lesson Review......Page 265
  Troubleshooting Authentication......Page 267
  Troubleshooting Certificates and Keys......Page 270
  Troubleshooting Endpoints......Page 271
  Using Security Catalog Views......Page 272
  Lesson Summary......Page 273
  Lesson Review......Page 274
  Using SQL Server Audit......Page 276
  Configuring Login Auditing......Page 288
  Using c2 Audit Mode......Page 289
  Policy-Based Management......Page 290
  Lesson Review......Page 296
 Case Scenarios......Page 297
  Case Scenario 3: Auditing at Fabrikam......Page 298
  Audit SQL Server Instances......Page 299
  Lesson 1......Page 300
  Lesson 2......Page 301
  Lesson 3......Page 302
  Case Scenario 3......Page 303
 Before You Begin......Page 305
  Database Mirroring......Page 306
  Mirroring Prerequisites......Page 307
  Configuring Mirroring with Windows Authentication......Page 311
  Configuring Mirroring with Certificate Authentication......Page 314
  Changing Operating Modes......Page 316
  Role Switching and Failover......Page 317
  Monitoring Mirrored Databases......Page 318
  Upgrading Mirrored Databases......Page 320
  Lesson Review......Page 324
  Replication Architecture......Page 326
  Replication Types......Page 328
  Snapshot Replication......Page 329
  Transactional Replication......Page 333
  Peer-to-Peer Transactional Replication......Page 335
  Merge Replication......Page 337
  Replication Monitor......Page 341
  Controlling Replication of Constraints, Columns, and Triggers......Page 343
  Heterogeneous Data......Page 344
  Lesson Summary......Page 346
  Lesson Review......Page 347
  Case Scenario 2: Database Replication at Tailspin Toys......Page 348
  Implement Replication......Page 349
  Lesson 1......Page 350
  Lesson 2......Page 351
  Case Scenario 2......Page 352
 Before You Begin......Page 353
  Fulfilling Edition Prerequisites......Page 354
  Creating a Windows Server 2008 R2 Failover Cluster......Page 358
  Installing a SQL Server Failover Cluster......Page 360
  Multi-Subnet Failover Clustering......Page 364
  Performing Manual Failover......Page 365
  Troubleshooting Failover Clusters......Page 366
  Lesson Review......Page 370
  What Are AlwaysOn Availability Groups?......Page 372
  Configuring Availability Modes......Page 373
  Selecting Failover Modes......Page 375
  Configuring Readable Secondary Replicas......Page 378
  Deploying AlwaysOn Availability Groups......Page 379
  Using Availability Groups on Failover Cluster Instances......Page 386
  Lesson Review......Page 390
  Case Scenario 1: Failover Cluster Instances at Contoso......Page 391
  Implement AlwaysOn......Page 392
  Lesson 1......Page 393
  Lesson 2......Page 394
  Case Scenario 2......Page 395
 Before You Begin......Page 397
  Getting Started with Performance Monitor......Page 398
  Capturing Performance Monitor Data......Page 400
  Creating Data Collector Sets......Page 402
  Lesson Summary......Page 403
  Lesson Review......Page 404
  Capturing Activity with SQL Server Profiler......Page 405
  Understanding SQL Trace......Page 410
  Capturing Activity with Extended Events Profiler......Page 411
  Lesson Review......Page 413
  Monitoring Activity......Page 415
  Working with Activity Monitor......Page 418
  Lesson Review......Page 419
  Capturing and Managing Performance Data......Page 421
  Analyzing Collected Performance Data......Page 425
  Lesson Summary......Page 427
  Lesson Review......Page 428
  Monitoring Disk Usage......Page 429
  Monitoring Memory Usage......Page 431
  Monitoring CPU Usage......Page 432
  Lesson Summary......Page 433
  Case Scenario 1: Identifying Poor Query Performance......Page 434
  Capture a Performance Baseline by Using Performance Monitor......Page 435
  Lesson 1......Page 436
  Lesson 3......Page 437
  Lesson 4......Page 438
  Lesson 5......Page 439
  Case Scenario 2......Page 440
 Before You Begin......Page 443
  Understanding the Anatomy of a Balanced Tree (B-Tree)......Page 444
  Understanding Index Types and Structures......Page 446
  Designing Indexes for Efficient Retrieval......Page 449
  Understanding Statistics......Page 454
  Creating and Modifying Indexes......Page 456
  Reviewing Unused Indexes......Page 463
  Lesson Review......Page 466
  Understanding SQL Server Lock Management......Page 468
  Using AlwaysOn Replicas to Improve Concurrency......Page 475
  Detecting and Correcting Deadlocks......Page 476
  Using Activity Monitor......Page 478
  Diagnosing Bottlenecks......Page 479
  Using Reports for Performance Analysis......Page 480
  Lesson Summary......Page 483
 Case Scenarios......Page 484
  Case Scenario 2: Analyzing Concurrency at Tailspin Toys......Page 485
  Identify and Resolve Concurrency Problems......Page 486
  Lesson 1......Page 487
  Lesson 2......Page 488
  Case Scenario 2......Page 489
 Before You Begin......Page 491
  Executing Jobs by Using SQL Server Agent......Page 492
  Managing Alerts......Page 497
  Managing Jobs......Page 500
  Monitoring Multi-Server Environments......Page 507
  Lesson Summary......Page 510
  Lesson Review......Page 511
  Understanding Backup Types......Page 513
  Backing Up System Databases......Page 517
  Backing Up Replicated Databases......Page 518
  Backing Up AlwaysOn Replicas......Page 519
  Using Database Checkpoints......Page 520
  Using Backup Devices......Page 521
  Performing Backups......Page 523
  Viewing Backup History......Page 527
  Lesson Summary......Page 528
  Lesson Review......Page 529
  Restoring Databases......Page 530
  Performing File Restores......Page 534
  Performing Page Restores......Page 535
  Restoring System Databases......Page 537
  Checking Database Status......Page 538
  Lesson Summary......Page 540
  Lesson Review......Page 541
  Case Scenario 2: Fabrikam Backup Strategy......Page 542
  Configure and Maintain a Backup Strategy......Page 543
  Restore Databases......Page 544
  Lesson 1......Page 545
  Lesson 2......Page 546
  Lesson 3......Page 547
  Case Scenario 3......Page 548
 Case Study 1......Page 549
  Questions......Page 550
 Case Study 2......Page 554
  Questions......Page 555
 Case Study 3......Page 559
  Questions......Page 560
 Case Study 4......Page 565
  Questions......Page 566
  Case Study 1......Page 571
  Case Study 2......Page 576
  Case Study 3......Page 580
  Case Study 4......Page 586
Index......Page 593
About the Authors......Page 621
Survey......Page 624