O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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