Microsoft SQL Server 2012 Management and Administration

Ross Mistry is an author, public speaker, principal enterprise architect, and veteran in the Silicon Valley. He has published numerous books and articles and frequently speaks at conferences worldwide. With more than 15 years of experience, Ross has been a trusted advisor providing strategic guidance for many C-level executives and has been responsible for planning and implementing technology solutions for some of the largest companies in the world. He has taken on the lead architect role for many Silicon Valley internet startups and Fortune 100 organizations, including Network Appliance, McAfee, The Sharper Image, Visa, CIBC, Levi’s, Wells Fargo, and Intel. He specializes in data platform, business productivity, unified communications, core infrastructure, and private cloud. At Microsoft, Ross is a director and principal enterprise architect at the Microsoft Technology Centers (MTCs). The MTCs are collaborative environments that provide access to innovative technologies and world-class expertise, giving organizations the ability to envision, design, and deploy solutions to meet their exact needs. He is currently in the process of launching and leading the first MTC in Canada. Ross is an active participant in the SQL Server community. He comanaged the main Microsoft SQL Server Twitter account @SQLServer and frequently speaks at technology conferences around the world. He has recently spoken at TechEd, PASS Summit, SQL Connections, and SQL BITS. He is a series author and has formulated many best practices and written many whitepapers and articles for Microsoft, SQL Server Magazine, and Techtarget.com.

Introduction   1
Part I: SQL Server 2012 Database Engine Administration and Management Topics

1 Installing or Upgrading the Database Engine to SQL Server 2012   13
What’s New for Installation with SQL Server 2012?   14
Preplanning and Preparing a SQL Server 2012 Server Database Engine Installation   16
Gathering Additional Information Necessary to Proceed   25
Installing a New Installation of SQL Server 2012   31
Upgrading the Database Engine to SQL Server 2012   40
Finalizing the SQL Server 2012 Installation or Upgrade   51
Installing SQL Server 2012 on Windows Server Core   53
Managing SQL Server 2012 Installations   62
Summary   64
Best Practices   64

2 Administering and Configuring the Database Engine Settings   67
What’s New for DBAs When Administering the Database Engine on SQL Server 2012   68
Administering SQL Server 2012 Server Properties   68
Administering the SQL Server Database Engine Folders   87
Administering Database Properties   98
SQL Server Database Engine Management Tasks   114
Administering the SQL Server Agent.   119
Summary   123
Best Practices   124

3 Administering Storage, I/O, and Partitioning   127
What’s New for DBAs When Administering Storage on SQL Server 2012   128
Storage Hardware Overview   129
Designing and Administering Storage on SQL Server 2012   140
Designing for BLOB Storage   158
Designing and Administrating Partitions in SQL Server 2012   164
Data Compression in SQL Server 2012   172
Summary   175
Best Practices   175

4 Creating Packages and Transferring Data On-Premise and to the Cloud.   179
What’s New in Integration Services for SQL Server 2012.   180
Options for Creating Packages   181
Integration Services Packages   182
Developing Packages   189
Enhancing Packages   199
Deploying and Running IntegrationServices Packages   212
Transferring Data with Integration Services   221
Moving Data to the Cloud   224
Summary   228
Best Practices   228

5 Managing and Optimizing SQL Server 2012 Indexes   229
What’s New for Indexes with SQL Server 2012   229
The Importance of Indexes   230
How Indexes Work   230
General Index Characteristics   238
Index Design and Strategy   242
Administering Indexes   247
Configuring Indexes for Maximum Performance   268
Summary   280
Best Practices   280

6 Backing Up and Restoring SQL Server 2012 Databases   283
What’s New for Backup and Recovery with SQL Server 2012?   283
The Importance of Backups   285
Backing Up and Recovering the Database Engine   289
Backup and Recovery Impact on Other Database Engine Features   324
Summary   330
Best Practices   330

Part II: Hardening, Auditing, and Securing SQL Server 2012

7 Hardening and Auditing a SQL Server 2012 Implementation   335
What’s New for Hardening and Auditing SQL Server 2012 Implementation?   336
Windows and SQL Server Authentication   337
Using Configuration Tools to Harden the Installation   343
Hardening SQL Server Service Accounts   350
Installing Service Packs and Critical Fixes   355
Leveraging SQL Server Auditing Strategies   357
Monitoring Events with SQL Server Audit   359
Additional SQL Server Hardening Recommendations   373
Summary   379
Best Practices   380

8 Administering SQL Server 2012 Security and Authorization   383
What’s New for Security and Authorization with SQL Server 2012?   383
SQL Server Security   385
Security Management DDL   398
Administering SQL Server Security   404
Contained Database Authentication   421
Summary   427
Best Practices   427

9 Encrypting SQL Server 2012 Data and Communications   429
What’s New for Encryption with SQL Server 2012?   430
Encryption in SQL   431
Column Encryption   432
Securing the Data Storage   435
Extensible Key Management   444
Transparent Data Encryption   446
Securing Connections   452
Using Certificates   456
SQL Server and BitLocker Drive Encryption   465
Configuring BitLocker Drive Encryption on a SQL Server System   469
Summary   478
Best Practices   478

Part III: SQL Server 2012 AlwaysOn High-Availability and Disaster Recovery Alternatives

10 Implementing and Managing AlwaysOn Availability Groups   483
SQL Server 2012 AlwaysOn Availability Groups Overview   484
Implementing AlwaysOn Availability Groups   490
Managing AlwaysOn Availability Groups   509
Monitoring and Troubleshooting AlwaysOn Availability Groups   520
Summary   525
Best Practices   526

11 Implementing and Managing AlwaysOn Failover Cluster Instances   527
SQL Server 2012 AlwaysOn Failover Cluster Instances Overview   527
What’s New for SQL Server 2012 AlwaysOn Failover Cluster Instances?   528
Additional Elements of AlwaysOn Failover Cluster Instances   537
Implementing a Single-Instance SQL Server 2012 Failover Cluster.   544
Implementing a Multiple-Instance SQL Server 2012 Failover Cluster   564
Multi-subnet SQL Server 2012 Failover Cluster Overview   568
Implementing Multi-subnet SQL Server 2012 Failover Cluster   571
Managing Failover Clusters from a SQL Server 2012 Perspective   574
Verifying the Status of Clustered Service and Applications, Nodes, Storage, and Networks   574
Managing Failover Clusters from a Windows Server 2008 R2 Perspective   580
Summary   583
Best Practices   583
 12 Implementing and Managing Database Mirroring   587
SQL Server 2012 Database Mirroring Overview   588
Combining Database Mirroring with Other SQL Server 2012 Technologies   596
Implementing a Database Mirroring Session   599
Managing a Database Mirroring Session   607
Monitoring and Troubleshooting a Database Mirroring Session   614
Summary   618
Best Practices   619

13 Implementing and Managing Replication   621
SQL Server 2012 Replication Essentials   622
Implementing SQL Server Replication   639
Managing SQL Server 2012 Replication   667
Monitoring and Troubleshooting SQL Server Replication   670
Summary   672
Best Practices   672

Part IV: Performance Tuning, Monitoring, Troubleshooting, and Maintenance

14 Performance Tuning and Troubleshooting SQL Server 2012   677
Platform Troubleshooting and Optimization   680
Database Troubleshooting and Optimization   691
Application Optimization and Troubleshooting   714
Introducing Extended Events to Troubleshoot SQL Server   718
Summary   726
Best Practices   726

15 Monitoring SQL Server 2012.   729
What’s New for Monitoring in SQL Server 2012?   730
Gaining Quick Insight into a SQL Server System   731
The Data Collector and the Management Data Warehouse   744
SQL Server Utilities   757
Creating Operators and Sending Email Alerts   763
Using the Windows Server 2008 R2 Performance
Monitoring Tools   769
Additional Tools to Monitor SQL Server 2012   776
Summary   777
Best Practices   777

16 SQL Server 2012 Maintenance Practices   779
What’s New for Maintenance with SQL Server 2012?   779
Establishing a SQL Server Maintenance Plan   780
Creating a Maintenance Plan   792
Establishing Maintenance Schedules for SQL Server   805
Summary   807
Best Practices   807

Part V: Multi-Instance Management, Consolidation, and Private Clouds

17 Implementing and Managing Policy-Based Management   811
Introduction to Policy-Based Management   811
Policy-Based Management Concepts   813
Implementing Policy-Based Management   819
Monitoring and Enforcing Best Practices by Using Policy-Based Management   830
Summary   836
Best Practices   836

18 Managing Workloads with Resource Governor   839
What’s New for Resource Governor in SQL Server 2012   840
Overview of Resource Governor Concepts, Workflow, and Scenarios   841
Implementing and Configuring Resource Governor   845
Creating a Workload Group   852
Understanding Default Transact-SQL Syntax with Resource Governor   858
Managing Resource Governor   860
Monitoring Resource Governor   862
Summary   867
Best Practices   868

19 Consolidation, Virtualization, and Private Clouds   869
Understanding Consolidation, Virtualization, and Private Cloud Strategies   870
Planning Your Implementation of Hyper-V   878
Installation of the Microsoft Hyper-V Server Role   883
Becoming Familiar with the Hyper-V Administrative Console   886
Installing a SQL Server 2012 Guest Operating System Session   889
Modifying SQL Server 2012 Guest Session Configuration Settings   893
Launching a Hyper-V Guest Session   898
Using Snapshots of Guest Operating System Sessions   900
Optimizing SQL Server 2012 for Private Cloud   903
Summary   905
Best Practices   905
Index   909