You are previewing Pro SQL Server 2012 Practices.
O'Reilly logo
Pro SQL Server 2012 Practices

Book Description

Pro SQL Server 2012 Practices Edition is an anthology of high-end wisdom from a group of accomplished database administrators who are quietly but relentlessly pushing the performance and feature envelope of Microsoft SQL Server 2012. With an emphasis upon performance—but also branching into release management, auditing, and other issues—the book helps you deliver the most value for your company's investment in Microsoft's flagship database system

  • Goes beyond the manual to cover good techniques and best practices

  • Delivers knowledge usually gained only by hard experience

  • Focuses upon performance, scalability, reliability

  • Helps achieve the predictability needed to be in control at all times

What you'll learn

  • Migrate to the new Extended Events framework for troubleshooting performance problems

  • Automate the tracking of key performance indicators such as available disk space, cpu utilization, index fragmentation, and more

  • Design performance into your database and its applications from the very beginning

  • Manage staged releases from development to test through to production

  • Diagnose resource and performance problems via dynamic management views

  • Analyze I/O patterns and plan reliably to obtain the performance you need

Who this book is for

Pro SQL Server 2012 Practices is aimed at SQL Server professionals who want to further their careers through expert-level practices in performance management, scalability, predictability, and security.

Table of Contents

  1. Title
  2. Dedication
  3. Contents at a Glance
  4. Contents
  5. About the Authors
  6. About the Technical Reviewers
  7. Acknowledgments
  8. Introduction
  9. Chapter 1: Be Your Developer’s Best Friend
    1. My Experience Working with SQL Server and Developers
    2. Reconciling Different Viewpoints Within an Organization
    3. Preparing to Work with Developers to Implement Changes to a System
    4. Step 1: Map Your Environment
    5. Step 2: Describe the New Environment
    6. Step 3: Create a Clear Document
    7. Step 4: Create System-Management Procedures
    8. Step 5: Create Good Reporting
    9. Ensuring Version Compatibility
    10. Setting Limits
    11. Next Steps
  10. Chapter 2: Getting It Right: Designing the Database for Performance
    1. Requirements
    2. Table Structure
    3. Design Testing
    4. Conclusion
  11. Chapter 3: Hidden Performance Gotchas
    1. Predicates
    2. Residuals
    3. Spills
    4. Conclusion
  12. Chapter 4: Dynamic Management Views
    1. Understanding the Basics
    2. Common Performance-Tuning Queries
    3. Physical Performance Queries
    4. System Performance Tuning Queries
    5. Conclusion
  13. Chapter 5: From SQL Trace to Extended Events
    1. SQL Trace
    2. Event Notifications
    3. Extended Events
    4. Conclusion
  14. Chapter 6: The Utility Database
    1. Start with Checklists
    2. Utility Database Layout
    3. Gathering Data
    4. Using the Data
    5. Conclusion
  15. Chapter 7: Indexing Outside the Bubble
    1. The Environment Bubble
    2. The Business Bubble
    3. Conclusion
  16. Chapter 8: Release Management
    1. My Release Management Process
    2. A Change Is Requested
    3. Documents
    4. Conclusion
  17. Chapter 9: Compliance and Auditing
    1. Compliance
    2. New Auditing Features in SQL Server 2012
    3. Auditing
    4. Server Audit
    5. Server Audit Specification
    6. Database Audit Specification
    7. Query the Audit File
    8. Pro Tip: Alert on Audit Events
    9. Conclusion
  18. Chapter 10: Automating Administration
    1. Tools for Automation
    2. What to Automate
    3. Conclusion
  19. Chapter 11: The Fluid Dynamics of SQL Server Data Movement
    1. Why the Need for Replicating Data?
    2. SQL Server Solutions
    3. Choosing the Right Deployment
    4. Keeping the Data Consistent
    5. Conclusion
  20. Chapter 12: Windows Azure SQL Database for DBAs
    1. SQL Database Architecture
    2. Differences with SQL Server
    3. Federations
    4. Troubleshooting Performance Issues
    5. Related Services
    6. Cost of SQL Database
    7. Conclusion
  21. Chapter 13: I/O: The Untold Story
    1. The Basics
    2. Monitoring
    3. Considerations
    4. Tactical
    5. Code or Disk?
    6. Times Have Changed
    7. Getting to the Data
    8. Addressing a Query
    9. Environmental Considerations
    10. Conclusion
  22. Chapter 14: Page and Row Compression
    1. Before You Get Started
    2. Row Compression
    3. Page Compression
    4. What Do You Compress?
    5. Fragmentation and Logged Operations
    6. Conclusion
  23. Chapter 15: Selecting and Sizing the Server
    1. Understanding Your Workload
    2. SQL Server 2012 Enterprise Edition Consideration Factors
    3. Server Vendor Selection
    4. Server Form Factor Selection
    5. Server Processor Count Selection
    6. Dell 12th Generation Server Comparison
    7. Processor Vendor Selection
    8. Processor Model Selection
    9. Memory Selection
    10. Conclusion
  24. Chapter 16: Backups and Restores Using Availability Groups
    1. Setting Up an Availability Group
    2. Enabling Backups on Availability Groups
    3. Automating Backups on Availability Groups
    4. Recovery on Availability Groups
    5. Conclusion
  25. Chapter 17: Big Data for the SQL Server DBA
    1. Big Data Arrives with Hadoop
    2. DBA As Data Architect
    3. Big Data for Analytics
    4. Using SQL Server with Hadoop
    5. Big Data for the Rest of Us
    6. Big Data in the Microsoft Future
    7. Conclusion
  26. Chapter 18: Tuning for Peak Load
    1. Define the Peak Load
    2. Determine Where You Are Today
    3. Analyze the Data
    4. Devise a Plan
    5. Conclusion
  27. Index