You are previewing Tribal SQL.
O'Reilly logo
Tribal SQL

Book Description

Tribal SQL is a reflection of how a DBA's core and long-standing responsibilities sit alongside new thinking and fresh ideas about where the DBA role is going, and what it means to be a DBA in today's businesses.

Table of Contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Table of Contents
  5. About this Book
    1. The Tribal Authors
    2. Computers 4 Africa
    3. The Tribal Reviewers and Editors
    4. The Tribal Sponsors
  6. Introduction
  7. SQL Server Storage Internals 101
    1. The Power of Deductive Reasoning
    2. Records
    3. Pages
    4. Investigating Page Contents Using DBCC Commands
      1. DBCC Page
      2. DBCC Ind
    5. Heaps and Indexes
      1. Heaps
      2. Indexes
    6. Crunching the Numbers
      1. Index design
      2. Storage requirements
    7. Summary
  8. SQL Server Data Compression
    1. Compression Basics
      1. Overview of SQL Server compression
      2. Version and edition requirements
    2. Row Compression
    3. Page Compression
      1. Stage 1: Column prefix compression
      2. Stage 2: Page dictionary compression
      3. Additional overhead of page compression
    4. Where and when to Deploy Compression
      1. Data compression ratio vs. CPU overhead
      2. Performance monitoring
      3. Data usage patterns
    5. A Brief Review of Data Compression Syntax
    6. Combining Data Compression with Backup Compression and Transparent Data Encryption
    7. Summary
  9. Verifying Backups Using Statistical Sampling
    1. Why Backup Validation?
    2. Why Automate Backup Validation?
    3. Planning Considerations
    4. Automating Restores with Statistical Sampling: the Parts
      1. Retrieving a list of databases and backup files from each server
      2. Performing test restores
      3. The magic happens here
    5. Summary
  10. Performance Tuning with SQL Trace and Extended Events
    1. Is SQL Trace Still Relevant?
    2. How SQL Trace Works
      1. Profiler: pros, cons, and best practices
      2. Server-side trace
      3. Resolution
    3. Preparing for Migration to Extended Events
    4. Conclusion
  11. Windows Functions in SQL Server 2012
    1. Sliding Windows
      1. Defining the window frame extent
      2. Behavioral differences between Range and Rows
      3. Performance differences between Range and Rows
    2. Analytic Functions
      1. Lag and Lead
      2. FIRST_VALUE and LAST_VALUE
      3. Statistical analysis functions
      4. Performance of analytic functions
    3. Summary
  12. SQL Server Security 101
    1. Securing SQL Server, the Bigger Picture
    2. SQL Server Security Architecture, a Brief Overview
      1. The hierarchy of principals
      2. The hierarchy of securables
      3. Granting permissions in the hierarchy
      4. The three permission keywords
    3. Applying the Principle of Least Privilege
      1. Creating SQL Server logins
      2. Fixed server-level roles
      3. Server-level permissions
      4. Database users
      5. Database roles
      6. Granting Least Privileges in the database: schema: object hierarchy
    4. Taking the “Least Privileges” Challenge
      1. Example 1
      2. Example 2
      3. Answer 1
      4. Answer 2
    5. Summary and Next Steps
    6. Further reading
  13. What Changed? Auditing Solutions in SQL Server
    1. Auditing Options Not Covered
      1. C2 auditing
      2. Policy Based Management
      3. Change Data Capture and Change Tracking
    2. The Basis of Auditing: Events and Event Classes
    3. SQL Trace
      1. Server-side trace for DDL auditing
      2. Using the default trace
      3. SQL Trace: pros and cons
    4. SQL Audit
      1. SQL Audit: how it works
      2. SQL Audit: terminology
      3. SQL Audit: creating the audit
      4. SQL Audit: creating the audit specification
      5. SQL Audit: viewing audit output
      6. SQL Audit: pros and cons
    5. Develop your own Audit: Event Notifications or Triggers
      1. Event notifications: how it works
      2. Event notifications: creating an event notification
      3. DDL and Logon triggers: how they work
      4. DDL and Logon triggers: creating triggers
      5. Event notifications and triggers: pros and cons
    6. Third-party Solutions
    7. Conclusion
  14. SQL Injection: How it Works and how to Thwart it
    1. My First SQL Injection Attack
    2. Attacking Websites
    3. Defending Websites
      1. Blacklists
      2. Why blacklists don't work
      3. Whitelists
      4. Parameterized queries
    4. Protecting Stored Procedures
    5. Other Forms of Defense
      1. QUOTENAME and REPLACE instead of sp_executesql
      2. Appropriate permissions
      3. Automated tools
      4. Web application firewalls
    6. Additional Resources
  15. Using Database Mail to Email Enable your SQL Server
    1. Getting Started with Database Mail
      1. Enabling Database Mail
      2. Configuring Database Mail
      3. Testing Database Mail
      4. Security requirements
    2. Using Database Mail in your own Applications
      1. Sending an email with a file attachment
      2. Sending an email with the results of a T-SQL statement
      3. Sending an email to multiple recipients
      4. Producing customized email alerts
    3. Reporting On Success or Failure of SQL Server Agent Jobs
      1. Enable a database mail profile for alert notification
      2. Designate a Fail-safe operator
      3. Configure an operator
      4. Configuring notifications for Agent jobs
    4. Provide a Real-time Notification System for SQL Server Alerts
    5. Troubleshooting Database Mail
      1. Common problems
      2. Interrogating the Database Mail system views
      3. Maintaining the Database Mail log table
      4. The SSMS Database Mail log
    6. Summary
  16. Taming Transactional Replication
    1. Transactional Replication Architecture
    2. Transactional Replication Use Cases
    3. Other Types of Replication
      1. Snapshot replication
      2. Merge replication
      3. Transactional replication with updating subscriptions
      4. Peer-to-peer transactional replication
    4. When not to Use Replication
    5. Deeper into Transactional Replication
      1. Publishers, publications and articles
      2. The Distributor
      3. The Subscribers
      4. The Replication Agents
    6. Transactional Replication Requirements
    7. Transactional Replication Walk-through
      1. Configuring the Distributor
      2. Configuring the Publisher and publication
      3. Configuring the Subscriber
    8. Tuning Query Performance on the Subscriber
    9. Monitoring Replication with ReplMon
      1. Subscription Watch List
      2. Replication latency and tracer tokens
      3. Getting details of errors
    10. Configuring Alerts
    11. Performance Tuning
      1. Server and database file configuration and tuning
      2. Tuning replication
    12. Troubleshooting Replication
      1. Finding more details on an error
      2. Using verbose error output
      3. Finding large batches
      4. Restoring accidentally dropped replication objects
      5. Reinitializing Subscribers
    13. Summary
  17. Building Better Reports
    1. Key Report Development Skills
      1. Communication
      2. Technical skills and versatility
      3. Report writer, Business Intelligence (BI) specialist or data scientist?
    2. The Fundamentals of Good Report Design
      1. Requirements gathering
      2. Initial visualization
      3. Designing tables and graphs
    3. Choosing the Right Reporting Tools
      1. Reporting in the Microsoft stack
      2. Predicting the future
      3. Outside the Microsoft stack
      4. Who decides and, most importantly, who pays?
      5. A personal story
    4. Summary
  18. Communication isn't Soft
    1. The Written Word
      1. Read more to write better
      2. Write right now
    2. The Spoken Word
      1. A different kind of audience
      2. Lunch-and-learn
      3. User groups, SQL Saturday and beyond
    3. Practice Pays Off
      1. Self-belief
      2. Communicating expertise
    4. Practice Makes Perfect
  19. Guerrilla Project Management for DBAs
    1. A DBA's Crazy Workload
    2. A DBA's Place in the Organization
    3. The Dark Side of Being a DBA
    4. The Shield of Project Management
      1. Common project management terms cheat sheet
      2. Specific defensive moves
      3. Offensive project management
      4. Onwards
  20. Agile Database Development
    1. Agile 101: A History
    2. Agile 201: Implications
    3. ABC – Automate, Balance, and Communicate
      1. Automation
      2. Balance
      3. Communicate, constantly
    4. DDT – Design, Deployments and Tests
      1. Design
      2. Deployments
      3. Tests
    5. Conclusion
  21. Nine Habits to Secure a Stellar Performance Review
    1. Habit 1: Work your Tail Off
    2. Habit 2: Establish the Parameters for your Success
    3. Habit 3: Work with Vision
    4. Habit 4: Train to Gain an Edge
    5. Habit 5: Stand on the Shoulders of Giants
    6. Habit 6: Control the Headlines
    7. Habit 7: Write a Self-Appraisal that Sparks Memory of Success
    8. Habit 8: Use your Review to Negotiate Rewards
    9. Habit 9: Don't Rest on Past Success
  22. Index