You are previewing Professional Microsoft SQL Server 2012 Administration.
O'Reilly logo
Professional Microsoft SQL Server 2012 Administration

Book Description

An essential how-to guide for experienced DBAs on the most significant product release since 2005!

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

  • Serves as a must-have how-to guide on the new features of the 2012 release of Microsoft SQL Server

  • Walks you through ways to manage and monitor SQL Server and automate administration

  • Guides you through SQL Azure for cloud computing, data replication, and data warehouse performance improvements

  • Addresses client-connectivity enhancements, new features that ensure high availability of mission-critical applications, and tighter integration with Excel and SharePoint for data analysis and reporting

  • Highlights major changes to the suite of SQL Server BI tools, including integration, reporting, and analysis services

  • Includes real-world case examples and best practices from Microsoft insiders

If you're a database administrator eager to get started with the newest version of SQL Server, then this book is essential reading.

Table of Contents

  1. Cover
  2. Contents
  3. Chapter 1: SQL Server 2012 Architecture
    1. SQL Server 2012 Ecosystem
    2. New Important Features in 2012
    3. SQL Server Architecture
    4. Editions of SQL Server
    5. Summary
  4. Chapter 2: Installing SQL Server 2012 Best Practices
    1. Planning the System
    2. Installing SQL Server
    3. Installing Analysis Services
    4. Installing PowerPivot for SharePoint
    5. Burning in the System
    6. Post-Install Configuration
    7. Uninstalling SQL Server
    8. Troubleshooting a Failed Install
    9. Summary
  5. Chapter 3: Upgrading SQL Server 2012 Best Practices
    1. Why Upgrade to SQL Server 2012?
    2. Upgrading to SQL Server 2012
    3. Pre-Upgrade Steps and Tools
    4. Backward Compatibility
    5. SQL Server Component Considerations
    6. Post-Upgrade Checks
    7. Summary
  6. Chapter 4: Managing and Troubleshooting the Database Engine
    1. Configuration and Administration Tools
    2. Troubleshooting Tools
    3. Management Studio
    4. Monitoring Processes in T-SQL
    5. Multiserver Management
    6. Trace Flags
    7. Getting Help from Support
    8. Summary
  7. Chapter 5: Automating SQL Server
    1. Maintenance Plans
    2. Automating SQL Server with SQL Server Agent
    3. SQL Server Agent Security
    4. Configuring SQL Server Agent
    5. Database Mail
    6. Multiserver Administration
    7. Summary
  8. Chapter 6: Service Broker in SQL Server 2012
    1. Asynchronous Messaging
    2. Configuring SQL Server Service Broker
    3. Using SQL Server Service Broker
    4. Summary
  9. Chapter 7: SQL Server CLR Integration
    1. Introduction to the CLR
    2. Creating CLR Assemblies
    3. Securing CLR
    4. Performance Monitoring
    5. Summary
  10. Chapter 8: Securing the Database Instance
    1. Authentication Types
    2. Authorizing Securables
    3. Row Level Security
    4. Summary
  11. Chapter 9: Change Management
    1. Creating Solutions and Projects
    2. Policy-Based Management
    3. DDL Trigger Syntax
    4. Trigger Views
    5. Scripting Overview
    6. Creating Change Scripts
    7. Data-Tier Applications
    8. Summary
  12. Chapter 10: Configuring the Server for Optimal Performance
    1. What Every DBA Needs to Know About Performance
    2. What the Developer DBA Needs to Know About Performance
    3. What the Production DBA Needs to Know About Performance
    4. CPU
    5. Memory
    6. I/O
    7. Summary
  13. Chapter 11: Optimizing SQL Server 2012
    1. Application Optimization
    2. The Silent Killer: I/O Problems
    3. Table and Index Partitioning
    4. Data Compression
    5. CPU Considerations
    6. Memory Considerations and Enhancements
    7. Resource Governor
    8. Summary
  14. Chapter 12: Monitoring Your SQL Server
    1. The Goal of Monitoring
    2. Choosing the Appropriate Monitoring Tools
    3. Performance Monitor
    4. Monitoring Events
    5. Monitoring with Dynamic Management Views and Functions
    6. Monitoring Logs
    7. Management Data Warehouse
    8. SQL Server Standard Reports
    9. System Center Management Pack
    10. SQL Server Best Practice Analyzer
    11. System Center Advisor
    12. Summary
  15. Chapter 13: Performance Tuning T-SQL
    1. Physical Query Processing Part One: Compilation and Recompilation
    2. Physical Query Processing Part Two: Execution
    3. Summary
  16. Chapter 14: Indexing Your Database
    1. Noteworthy Index-Related Features in SQL Server
    2. Partitioned Tables and Indexes
    3. Index Maintenance
    4. Improving Query Performance with Indexes
    5. Database Tuning Advisor
    6. Too Many Indexes?
    7. Summary
  17. Chapter 15: Replication
    1. Replication Overview
    2. Replication Models
    3. Implementing Replication
    4. Peer-to-Peer Replication
    5. Scripting Replication
    6. Monitoring Replication
    7. Summary
  18. Chapter 16: Clustering SQL Server 2012
    1. Clustering and Your Organization
    2. Clustering: The Big Picture
    3. Upgrading SQL Server Clustering
    4. Getting Prepared for Clustering
    5. Clustering Windows Server 2008
    6. Clustering Microsoft Distributed Transaction Coordinator
    7. Clustering SQL Server 2012
    8. Managing and Monitoring the Cluster
    9. Troubleshooting Cluster Problems
    10. Summary
  19. Chapter 17: Backup and Recovery
    1. Types of Failure
    2. Making Plans
    3. Overview of Backup and Restore
    4. Preparing for Recovery
    5. Developing and Executing a Backup Plan
    6. Managing Backups
    7. Backup and Restore Performance
    8. Performing Recovery
    9. Archiving Data
    10. Summary
  20. Chapter 18: SQL Server 2012 LOG Shipping
    1. Log Shipping Deployment Scenarios
    2. Log-Shipping Architecture
    3. Log Shipping Process
    4. System Requirements
    5. Deploying Log Shipping
    6. Monitoring and Troubleshooting
    7. Managing Changing Roles
    8. Database Backup Plan
    9. Integrating Log Shipping with Other High-Availability Solutions
    10. Removing Log Shipping
    11. Log-Shipping Performance
    12. Upgrading to SQL Server 2012 Log Shipping
    13. Summary
  21. Chapter 19: Database Mirroring
    1. Overview of Database Mirroring
    2. Operating Modes of Database Mirroring
    3. Database Mirroring In Action
    4. Database Mirroring and SQL Server 2012 Editions
    5. Database Mirroring Catalog Views
    6. Database Mirroring Role Change
    7. Database Availability Scenarios
    8. Monitoring Database Mirroring
    9. Troubleshooting Database Mirroring
    10. Preparing the Mirror Server for Failover
    11. Mirroring Multiple Databases
    12. Database Mirroring and Other High-Availability Solutions
    13. Mirroring Event Listener Setup
    14. Database Snapshots
    15. Summary
  22. Chapter 20: Integration Services Administration and Performance Tuning
    1. A Tour of Integration Services
    2. Administration of the Integration Services Service
    3. Administration of Integration Services Packages in Package Deployment Model
    4. Administration of Integration Services Packages in Project Deployment Model
    5. Execution and Scheduling
    6. Applying Security to Integration Services
    7. Summary
  23. Chapter 21: Analysis Services Administration and Performance Tuning
    1. Tour of Analysis Services
    2. Administering Analysis Services Server
    3. Administering Analysis Services Databases
    4. Analysis Services Performance Monitoring and Tuning
    5. Management of Analysis Services MOLAP Model Storage
    6. Applying Security to Analysis Services in the MOLAP Model
    7. Summary
  24. Chapter 22: SQL Server Reporting Services Administration
    1. SQL Server Reporting Services Configuration Manager
    2. Report Server Properties
    3. The Report Execution Log
    4. Report Builder
    5. Report Manager
    6. Summary
  25. Chapter 23: SQL Server 2012 Sharepoint 2010 Integration
    1. Components of Integration
    2. Data Refresh
  26. Chapter 24: SQL Azure Administration and Configuration
    1. Introduction to SQL Azure
    2. SQL Azure Architecture
    3. Configuring SQL Azure
    4. Administering SQL Azure
    5. Working with SQL Azure
    6. What’s Missing in SQL Azure
    7. Summary
  27. Chapter 25: Alwayson Availability Groups
    1. Architecture
    2. Availability Group Example
    3. Active Secondary for Secondary Read-Only
    4. Backup on the Secondary Replica
    5. AlwaysOn Group Dashboard
    6. Monitoring and Troubleshooting
    7. Summary
  28. Introduction
  29. Advertisement