You are previewing Microsoft SQL Server 2012 Administration: Real-World Skills for MCSA Certification and Beyond (Exams 70-461, 70-462, and 70-463).
O'Reilly logo
Microsoft SQL Server 2012 Administration: Real-World Skills for MCSA Certification and Beyond (Exams 70-461, 70-462, and 70-463)

Book Description

Implement, maintain, and repair SQL Server 2012 databases

As the most significant update since 2008, Microsoft SQL Server 2012 boasts updates and new features that are critical to understand. Whether you manage and administer SQL Server 2012 or are planning to get your MCSA: SQL Server 2012 certification, this book is the perfect supplement to your learning and preparation. From understanding SQL Server's roles to implementing business intelligence and reporting, this practical book explores tasks and scenarios that a working SQL Server DBA faces regularly and shows you step by step how to handle them.

  • Includes practice exams and coverage of exam objectives for those seeking MSCA: SQL Server 2012 certification

  • Explores the skills you'll need on the job as a SQL Server 2012 DBA

  • Discusses designing and implementing database solutions

  • Walks you through administrating, maintaining, and securing SQL Server 2012

  • Addresses implementing high availability and data distribution

  • Includes bonus videos where the author walks you through some of the more difficult tasks expected of a DBA

Featuring hands-on exercises and real-world scenarios, this resource guides you through the essentials of implementing, maintaining, and repairing SQL Server 2012 databases.

Note: The ebook version does not provide access to the companion files.

Table of Contents

  1. Cover
  2. Contents
  3. Title
  4. Copyright
  5. Dedication
  6. Acknowledgments
  7. About the Author
  8. Table of Exercises
  9. Introduction
  10. Part I: Introducing SQL Server 2012
    1. Chapter 1: Understanding SQL Server’s Role
      1. What Is Information Technology?
      2. Introduction to Databases
      3. Database Servers and Applications
      4. SQL Server’s Role
      5. Summary
      6. Chapter Essentials
    2. Chapter 2: Installing SQL Server 2012
      1. Installation Planning
      2. Installing a Default Instance
      3. Installing Named Instances
      4. Installing to a Cluster
      5. Installing Extra Features
      6. Upgrading from Previous Versions
      7. Validating an Installation
      8. Removing an Installation
      9. Summary
      10. Chapter Essentials
    3. Chapter 3: Working with the Administration Tools
      1. SQL Server Configuration Manager
      2. SQL Server Management Studio
      3. SQL Server Data Tools
      4. SQL Server Profiler
      5. Books Online
      6. Windows Server Administration for the DBA
      7. Summary
      8. Chapter Essentials
    4. Chapter 4: SQL Server Command-Line Administration
      1. Introducing the Command Prompt
      2. General Commands
      3. Batch Files
      4. Mastering SQLCMD
      5. Introducing Windows PowerShell
      6. Using SQL Server PowerShell Extensions
      7. Summary
      8. Chapter Essentials
    5. Chapter 5: Querying SQL Server
      1. Understanding the SQL Language
      2. SQL Statement Types
      3. SQL Syntactical Elements
      4. Coding Recommendations
      5. Using SELECT Statements
      6. Advanced Query Techniques
      7. Using DDL Statements
      8. Using DCL Statements
      9. Modifying Data
      10. Tuning and Optimizing Queries
      11. Summary
      12. Chapter Essentials
  11. Part II: Designing Database Solutions
    1. Chapter 6: Database Concepts and Terminology
      1. Relational Database Theory
      2. Database Design Processes
      3. Project Management for the DBA
      4. Summary
      5. Chapter Essentials
    2. Chapter 7: ERD and Capacity Planning
      1. Planning a Database
      2. Understanding Entity Relationship Diagramming
      3. Building an ERD
      4. Capacity Planning
      5. Summary
      6. Chapter Essentials
    3. Chapter 8: Normalization and Other Design Issues
      1. Designing for Normalization
      2. Designing for Performance
      3. Designing for Availability
      4. Designing for Security
      5. Summary
      6. Chapter Essentials
  12. Part III: Implementing Database Solutions
    1. Chapter 9: Creating SQL Server Databases
      1. SQL Server Databases
      2. Database Storage
      3. Database Options and Properties
      4. Creating Databases in the GUI
      5. Creating Databases with T-SQL
      6. Creating Databases with PowerShell
      7. Attaching and Detaching Databases
      8. Database Snapshots
      9. Summary
      10. Chapter Essentials
    2. Chapter 10: Creating Tables
      1. Data Types
      2. Collations
      3. Table Creation Process
      4. Data Partitioning
      5. Summary
      6. Chapter Essentials
    3. Chapter 11: Indexes and Views
      1. Understanding Indexes
      2. Creating Basic Indexes
      3. Creating Advanced Indexes
      4. Managing Indexes
      5. Understanding Views
      6. Creating Views
      7. Summary
      8. Chapter Essentials
    4. Chapter 12: Triggers and Stored Procedures
      1. Triggers Defined
      2. Using Triggers
      3. Creating Triggers
      4. Understanding Stored Procedures
      5. Creating Stored Procedures
      6. Summary
      7. Chapter Essentials
    5. Chapter 13: Implementing Advanced Features
      1. Understanding and Installing Analysis Services
      2. Understanding Integration Services
      3. Understanding and Installing Reporting Services
      4. Implementing Database Mail
      5. Configuring Full-Text Indexing
      6. Implementing Transparent Data Encryption
      7. Data Compression
      8. Summary
      9. Chapter Essentials
  13. Part IV: Administering and Maintaining SQL Server 2012
    1. Chapter 14: Creating Jobs, Operators, and Alerts
      1. Standardize, Automate, and Update
      2. Understanding SQL Server Jobs
      3. Creating T-SQL Jobs
      4. Creating SSIS Jobs
      5. Creating Windows Command Jobs
      6. Creating and Using Operators
      7. Creating and Using Alerts
      8. Using WSUS for SQL Server 2012
      9. Summary
      10. Chapter Essentials
    2. Chapter 15: Performance Monitoring and Tuning
      1. Performance Tuning Principles
      2. Performance and Troubleshooting Tools
      3. Blocks, Locks, and Deadlocks
      4. SQL Server Profiler
      5. Database Engine Tuning Advisor
      6. Performance Monitoring with System Monitor
      7. Using the Resource Governor
      8. Performance Studio
      9. Advanced Monitoring Tools
      10. Summary
      11. Chapter Essentials
    3. Chapter 16: Policy-Based Management
      1. Policy-Based Management
      2. Centralized Server Management
      3. Standardizing with PBM and CMS
      4. Summary
      5. Chapter Essentials
    4. Chapter 17: Backup and Restoration
      1. Backing Up a Database
      2. Backing Up System Databases
      3. Restoring a Database
      4. Backing Up the Environment
      5. Summary
      6. Chapter Essentials
  14. Part V: SQL Server Security
    1. Chapter 18: Security Threats and Principles
      1. Security Defined
      2. Security Threats
      3. Security Principles
      4. Summary
      5. Chapter Essentials
    2. Chapter 19: Authentication and Encryption
      1. Understanding Authentication
      2. SQL Server Authentication Methods
      3. Logins, Users, and Roles
      4. Understanding Encryption
      5. Summary
      6. Chapter Essentials
    3. Chapter 20: Security Best Practices
      1. Establishing Baselines
      2. Implementing Least Privilege
      3. Auditing SQL Server Activity
      4. Configuring the Surface Area
      5. Understanding Common Criteria and C2
      6. Summary
      7. Chapter Essentials
  15. Part VI: Implementing High Availability and Data Distribution
    1. Chapter 21: AlwaysOn and High Availability
      1. Introducing AlwaysOn Technology
      2. Mirroring and AlwaysOn
      3. Failover Solutions
      4. Selecting Hardware for AlwaysOn
      5. Summary
      6. Chapter Essentials
    2. Chapter 22: SQL Server Failover Clustering
      1. Understanding Windows Failover Clustering Service
      2. Implementing a Windows Cluster
      3. Installing SQL Server 2012 to a Cluster
      4. Monitoring and Managing a SQL Server Cluster
      5. Summary
      6. Chapter Essentials
    3. Chapter 23: Database Mirroring and Snapshots
      1. RAID-based Data Redundancy
      2. Using Database Mirroring
      3. Understanding Log Shipping
      4. Implementing Database Snapshots
      5. Summary
      6. Chapter Essentials
    4. Chapter 24: Implementing Replication
      1. SQL Server Replication
      2. Importing and Exporting Data
      3. Summary
      4. Chapter Essentials
  16. Part VII: Implementing Business Intelligence and Reporting
    1. Chapter 25: Data Warehousing
      1. Understanding Data Warehouses
      2. Implementing Fact Tables
      3. Implementing Dimensions
      4. Summary
      5. Chapter Essentials
    2. Chapter 26: SQL Server Integration Services
      1. Integration Issues
      2. Installing SSIS
      3. Configuring SSIS Security Settings
      4. Deploying Packages
      5. SSIS Auditing and Event Handling
      6. Extracting, Transforming, and Loading Data
      7. Summary
      8. Chapter Essentials
    3. Chapter 27: Data Quality Solutions
      1. Understanding Data Quality Concerns
      2. Installing Data Quality Services
      3. Using Master Data Services
      4. Cleaning Data
      5. Summary
      6. Chapter Essentials
  17. Appendices
    1. Appendix A: Microsoft’s Certification Program
    2. Appendix B: About the Additional Study Tools