Microsoft SQL Server 2012 Management and Administration

This is the most complete, fast-paced, task-based reference for DBAs who need to deploy, manage, and secure Microsoft SQL Server 2012. Authored by a world-class architect who now crafts SQL Server solutions for Microsoft's largest Silicon Valley customers, it goes far beyond the basics, taking on the complex tasks that DBAs need to succeed with SQL Server 2012 in mission-critical environments. Ross Mistry draws on extensive testing in large production environments to offer step-by-step solutions and tips readers won't find anywhere else. Every chapter identifies SQL Server 2012's most valuable new improvements, and concludes with a convenient summary of best practices. Coverage includes: * Choosing the right installation or upgrade strategies * Managing memory, processors, auditing, database files, autogrowth, and new FileTables * Using new Contained Databases to streamline data deployment * Leveraging full-text search * Managing backup/restore * Working with Utility Control Points * Implementing consolidation, virtualization, and the private cloud * Using the Policy Based Framework to manage compliance and configuration * Managing workloads and consumption with the new Resource Governor * Hardening SQL Server environments * Utilizing SQL Server authorization and encryption * Operating new AlwaysOn Availability Groups * Using Failover Clustering, Database Mirroring, Replication, and other enterprise capabilities * Monitoring and troubleshooting SQL Server 2012

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