Exam 70-462: Administering Microsoft SQL
Server 2012 Databases
OBJECTIVE CHAPTER LESSON
INSTALL AND CONFIGURE (19 PERCENT)
Plan installation. 1 1
Install SQL Server and related services. 1 2
Implement a migration strategy. 4 1
Congure additional SQL Server components. 3 1
Manage SQL Server Agent. 11 1
MAINTAIN INSTANCES AND DATABASES (17 PERCENT)
Manage and congure databases. 3 3
Congure SQL Server instances. 2 1
Implement a SQL Server clustered instance. 8 1
Manage SQL Server instances. 2 2
OPTIMIZE AND TROUBLESHOOT (14 PERCENT)
Identify and resolve concurrency problems. 10 2
Collect and analyze troubleshooting data. 9 1–6
Audit SQL Server instances. 6 3
MANAGE DATA (20 PERCENT)
Congure and maintain a back up strategy. 11 2
Restore databases. 11 3
Implement and maintain indexes. 10 1
Import and export data. 4 2
IMPLEMENT SECURITY (18 PERCENT)
Manage logins and server roles. 5 1
Manage database permissions. 6 1
Manage users and database roles. 5 2
Troubleshoot security. 6 2
IMPLEMENT HIGH AVAILABILITY (12 PERCENT)
Implement AlwaysOn. 8 2
Implement database mirroring. 7 1
Implement replication. 7 2
Exam Objectives The exam objectives listed here are current as of this book’s publication date. Exam objectives are
subject to change at any time without prior notice and at Microsoft’s sole discretion. Please visit the Microsoft Learning
website for the most current listing of exam objectives: http://www.microsoft.com/learning/en/us/exams/70-462.mspx.
Contents
Introduction xvii
System Requirements xvii
Hardware Requirements xviii
Software Requirements xviii
Practice Setup Instructions xix
Prepare a Computer to Function as a Windows Server 2008 R2 Domain Controller xix
Prepare AD DS xx
Prepare a Member Server and Join It to the Domain xx
Prepare a Second Member Server and Join It to the Domain xxi
Prepare a Third Member Server and Join It to the Domain xxi
Prepare a Fourth Member Server and Join It to the Domain xxii
Prepare a Computer Running the Server Core Installation Option and Join It to the Domain xxii
Using the Companion CD xxiii
How to Install the Practice Tests xxiv
How to Use the Practice Tests xxiv
How to Uninstall the Practice Tests xxv
Acknowledgments xxv
Errata & Book Support xxv
We Want to Hear from You xxv
Stay in Touch xxv
Preparing for the Exam xxvi
CHAPTER 1
Planning and Installing SQL Server 2012 1
Before You Begin 1
Lesson 1: Planning Your Installation 2
Evaluating Installation Requirements 2
Designing the Installation 7
Planning Scale Up versus Scale Out Basics 8
Shrinking and Growing Databases 9
Designing the Storage for New Databases 13
Remembering Capacity Constraints 15
Identifying a Standby Database for Reporting 15
Identifying Windows-Level Security and Service-Level Security 15
Performing a Core Mode Installation 17
Benchmarking a Server 19
Lesson Summary 23
Lesson Review 24
Lesson 2: Installing SQL Server and Related Services 26
Conguring an Operating System Disk 26
Installing the SQL Server Database Engine 27
Installing SQL Server 2012 from the Command Prompt 33
Installing SQL Server Integration Services 34
Enabling and Disabling Features 36
Installing SQL Server 2012 by Using a Conguration File 39
Testing Connectivity 40
Lesson Summary 52
Lesson Review 53
Case Scenarios 54
Case Scenario 1: Planning Deployment of SQL Server 2012 54
Case Scenario 2: SQL Server Deployment 54
Suggested Practices 55
Congure Additional Firewall Rules and Generate a Features Discovery Report 55
Adding and Removing Features and Adding Databases to SQL Server 2012 on a Computer Running a Server Core Operating System 55
Answers 56
Lesson 1 56
Lesson 2 57
Case Scenario 1 58
Case Scenario 2 59
CHAPTER 2
Conguring and Managing SQL Server Instances 61
Before You Begin 61
Lesson 1: Conguring SQL Server Instances 62
Instance-Level Settings 62
Database Conguration and Standardization 68
Distributed Transaction Coordinator 71
Conguring Database Mail 72
Lesson Summary 78
Lesson Review 78
Lesson 2: Managing SQL Server Instances 80
Installing Additional Instances 80
Deploying Software Updates and Patch Management 84
Conguring Resource Governor 86
Using WSRM with Multiple Database Engine Instances 91
Cycle SQL Server Error Logs 93
Lesson Summary 96
Lesson Review 96
Case Scenarios 98
Case Scenario 1: Instance Conguration 98
Case Scenario 2: Additional Instances and Error Log Cycling 98
Suggested Practices 99
Congure Instances 99
Install and Manage Multiple Instances 99
Answers 100
Lesson 1 100
Lesson 2 101
Case Scenario 1 103
Case Scenario 2 104
CHAPTER 3
Conguring SQL Server 2012 Components 105
Before You Begin 105
Lesson 1: Conguring Additional SQL Server Components 106
Deploying and Conguring Analysis Services 106
Deploying and Conguring Reporting Services 108
Deploying and Conguring SharePoint Integration 112
Conguring SQL Server Integration Services Security 114
Managing Full-Text Indexing 116
Conguring FILESTREAM 118
Conguring FileTables 120
Lesson Summary 123
Lesson Review 124
Lesson 2: Managing and Conguring Databases 125
Designing and Managing Filegroups 125
Conguring and Standardizing Databases 128
Understanding Contained Databases 128
Using Data Compression 131
Encrypting Databases with Transparent Data Encryption 135
Partitioning Indexes and Tables 137
Managing Log Files 140
Using Database Console Commands 141
Lesson Summary 146
Lesson Review 146
Case Scenarios 147
Case Scenario 1: Conguring FILESTREAM and FileTable 147
Case Scenario 2: Deploying Transparent Data Encryption 148
Suggested Practices 148
FILESTREAM and FileTable 148
Transparent Data Encryption and Table Partitioning 148
Answers 149
Lesson 1 149
Lesson 2 150
Case Scenario 1 151
Case Scenario 2 151
CHAPTER 4
Migrating, Importing, and Exporting 153
Before You Begin 153
Lesson 1: Migrating to SQL Server 2012 154
Upgrading an Instance to SQL Server 2012 154
Migrating a Database to a SQL Server 2012 Instance 161
Copying Databases to Other Servers 164
Migrating SQL Logins 170
Lesson Summary 173
Lesson Review 173
Lesson 2: Exporting and Importing Data 175
Copying and Exporting Data 175
Using the SQL Server Import and Export Wizard 176
Using BCP to Import and Export Data 178
Importing Data by Using BULK INSERT 179
Importing Data by Using OPENROWSET(BULK) 180
Using Format Files 180
Preparing Data for Bulk Operations 181
SELECT INTO 182
Lesson Summary 184
Lesson Review 184
Case Scenarios 185
Case Scenario 1: Consolidation at Contoso 186
Case Scenario 2: Tailspin Toys Bulk Data 186
Suggested Practices 187
Implement a Migration Strategy 187
Import and Export Data 187
Answers 188
Lesson 1 188
Lesson 2 189
Case Scenario 1 190
Case Scenario 2 191
CHAPTER 5
SQL Server Logins, Roles, and Users 193
Before You Begin 193
Lesson 1: Managing Logins and Server Roles 194
SQL Logins 194
Server Roles 201
User-Dened Server Roles 203
Credentials 204
Lesson Summary 206
Lesson Review 207
Lesson 2: Managing Users and Database Roles 209
Database Users 209
Database Roles 211
Contained Users 216
Least Privilege 218
Application Roles 218
Lesson Summary 221
Lesson Review 221
Case Scenarios 222
Case Scenario 1: Instance-Level Permissions for Contoso’s Accountants 222
Case Scenario 2: Contained Databases at Fabrikam 223
Suggested Practices 223
Manage Logins and Server Roles 223
Manage Users and Database Roles 224
Answers 225
Lesson 1 225
Lesson 2 226
Case Scenario 1 228
Case Scenario 2 228
CHAPTER 6
Securing SQL Server 2012 229
Before You Begin 229
Lesson 1: Managing Database Permissions 230
Understanding Securables 230
Assigning Permissions on Objects 232
Managing Permissions by Using Database Roles 233
Protecting Objects from Modication 236
Using Schemas 236
Determining Effective Permissions 238
Lesson Summary 239
Lesson Review 239
Lesson 2: Troubleshooting SQL Server Security 241
Troubleshooting Authentication 241
Troubleshooting Certicates and Keys 244
Troubleshooting Endpoints 245
Using Security Catalog Views 246
Lesson Summary 247
Lesson Review 248
Lesson 3: Auditing SQL Server Instances 250
Using SQL Server Audit 250
Conguring Login Auditing 262
Using c2 Audit Mode 263
Common Criteria Compliance 264
Policy-Based Management 264
Lesson Summary 270
Lesson Review 270
Case Scenarios 271
Case Scenario 1: Conguring Database Permissions 272
Case Scenario 2: Troubleshooting Security 272
Case Scenario 3: Auditing at Fabrikam 272
Suggested Practices 273
Manage Database Permissions 273
Troubleshoot Security 273
Audit SQL Server Instances 273
Answers 274
Lesson 1 274
Lesson 2 275
Lesson 3 276
Case Scenario 1 277
Case Scenario 2 277
Case Scenario 3 277
CHAPTER 7
Mirroring and Replication 279
Before You Begin 279
Lesson 1: Mirroring Databases 280
Database Mirroring 280
Mirroring Prerequisites 281
Conguring Mirroring with Windows Authentication 285
Conguring Mirroring with Certicate Authentication 288
Changing Operating Modes 290
Role Switching and Failover 291
Monitoring Mirrored Databases 292
Upgrading Mirrored Databases 294
Lesson Summary 298
Lesson Review 298
Lesson 2: Database Replication 300
Replication Architecture 300
Replication Types 302
Snapshot Replication 303
Transactional Replication 307
Peer-to-Peer Transactional Replication 309
Merge Replication 311
Replication Monitor 315
Controlling Replication of Constraints, Columns, and Triggers 317
Heterogeneous Data 318
Lesson Summary 320
Lesson Review 321
Case Scenarios 322
Case Scenario 1: Database Mirroring at Coho Vineyard 322
Case Scenario 2: Database Replication at Tailspin Toys 322
Suggested Practices 323
Implement Database Mirroring 323
Implement Replication 323
Answers 324
Lesson 1 324
Lesson 2 325
Case Scenario 1 326
Case Scenario 2 326
CHAPTER 8
Clustering and AlwaysOn 327
Before You Begin 327
Lesson 1: Clustering SQL Server 2012 328
Fullling Edition Prerequisites 328
Creating a Windows Server 2008 R2 Failover Cluster 332
Installing a SQL Server Failover Cluster 334
Multi-Subnet Failover Clustering 338
Performing Manual Failover 339
Troubleshooting Failover Clusters 340
Lesson Summary 344
Lesson Review 344
Lesson 2: AlwaysOn Availability Groups 346
What Are AlwaysOn Availability Groups? 346
Meeting Availability Group Prerequisites 347
Conguring Availability Modes 347
Selecting Failover Modes 349
Conguring Readable Secondary Replicas 352
Deploying AlwaysOn Availability Groups 353
Using Availability Groups on Failover Cluster Instances 360
Lesson Summary 364
Lesson Review 364
Case Scenarios 365
Case Scenario 1: Failover Cluster Instances at Contoso 365
Case Scenario 2: AlwaysOn Availability Groups at Fabrikam 366
Suggested Practices 366
Implement a SQL Server Clustered Instance 366
Implement AlwaysOn 366
Answers 367
Lesson 1 367
Lesson 2 368
Case Scenario 1 369
Case Scenario 2 369
CHAPTER 9
Troubleshooting SQL Server 2012 371
Before You Begin 371
Lesson 1: Working with Performance Monitor 372
Getting Started with Performance Monitor 372
Capturing Performance Monitor Data 374
Creating Data Collector Sets 376
Lesson Summary 377
Lesson Review 378
Lesson 2: Working with SQL Server Proler 379
Capturing Activity with SQL Server Proler 379
Understanding SQL Trace 384
Reviewing Trace Output 385
Capturing Activity with Extended Events Proler 385
Lesson Summary 387
Lesson Review 387
Lesson 3: Monitoring SQL Server 389
Monitoring Activity 389
Working with Activity Monitor 392
Lesson Summary 393
Lesson Review 393
Lesson 4: Using the Data Collector Tool 395
Capturing and Managing Performance Data 395
Analyzing Collected Performance Data 399
Lesson Summary 401
Lesson Review 402
Lesson 5: Identifying Bottlenecks 403
Monitoring Disk Usage 403
Monitoring Memory Usage 405
Monitoring CPU Usage 406
Lesson Summary 407
Lesson Review 408
Case Scenarios 408
Case Scenario 1: Identifying Poor Query Performance 408
Case Scenario 2: Deploying Auditing 409
Suggested Practices 409
Create a Trace by Using SQL Server Proler 409
Capture a Performance Baseline by Using Performance Monitor 409
Answers 410
Lesson 1 410
Lesson 2 411
Lesson 3 411
Lesson 4 412
Lesson 5 413
Case Scenario 1 414
Case Scenario 2 414
CHAPTER 10
Indexes and Concurrency 417
Before You Begin 417
Lesson 1: Implementing and Maintaining Indexes 418
Understanding the Anatomy of a Balanced Tree (B-Tree) 418
Understanding Index Types and Structures 420
Designing Indexes for Efcient Retrieval 423
Understanding Statistics 428
Creating and Modifying Indexes 430
Tracking Missing Indexes 437
Reviewing Unused Indexes 437
Lesson Summary 440
Lesson Review 440
Lesson 2: Identifying and Resolving Concurrency Problems 442
Dening Transactions and Transaction Scope 442
Understanding SQL Server Lock Management 442
Using AlwaysOn Replicas to Improve Concurrency 449
Detecting and Correcting Deadlocks 450
Using Activity Monitor 452
Diagnosing Bottlenecks 453
Using Reports for Performance Analysis 454
Lesson Summary 457
Lesson Review 458
Case Scenarios 458
Case Scenario 1: Fabrikam Year-Query Performance 459
Case Scenario 2: Analyzing Concurrency at Tailspin Toys 459
Suggested Practices 460
Implement and Maintain Indexes and Statistics 460
Identify and Resolve Concurrency Problems 460
Answers 461
Lesson 1 461
Lesson 2 462
Case Scenario 1 463
Case Scenario 2 463
CHAPTER 11
SQL Server Agent, Backup, and Restore 465
Before You Begin 465
Lesson 1: Managing SQL Server Agent 466
Executing Jobs by Using SQL Server Agent 466
Managing Alerts 471
Managing Jobs 474
Monitoring Multi-Server Environments 481
Lesson Summary 484
Lesson Review 485
Lesson 2: Conguring and Maintaining a Backup Strategy 487
Understanding Backup Types 487
Backing Up System Databases 491
Backing Up Replicated Databases 492
Backing Up Mirrored Databases 493
Backing Up AlwaysOn Replicas 493
Using Database Checkpoints 494
Using Backup Devices 495
Backing Up Media Sets 497
Performing Backups 497
Viewing Backup History 501
Lesson Summary 502
Lesson Review 503
Lesson 3: Restoring SQL Server Databases 504
Restoring Databases 504
Performing File Restores 508
Performing Page Restores 509
Restoring a Database Protected with Transparent Data Encryption 511
Restoring System Databases 511
Restoring Replicated Databases 512
Checking Database Status 512
Lesson Summary 514
Lesson Review 515
Case Scenarios 516
Case Scenario 1: SQL Server Agent at Contoso 516
Case Scenario 2: Fabrikam Backup Strategy 516
Case Scenario 3: Recovery at Adatum 517
Suggested Practices 517
Manage SQL Server Agent 517
Congure and Maintain a Backup Strategy 517
Restore Databases 518
Answers 519
Lesson 1 519
Lesson 2 520
Lesson 3 521
Case Scenario 1 522
Case Scenario 2 522
Case Scenario 3 522
CHAPTER 12
Code Case Studies 523
Case Study 1 523
Questions 524
Case Study 2 528
Questions 529
Case Study 3 533
Questions 534
Case Study 4 539
Questions 540
Answers 545
Case Study 1 545
Case Study 2 550
Case Study 3 554
Case Study 4 560
About the Authors 595
Contributor 596
www.it-ebooks.info
