Professional Microsoft SQL Server 2014 Administration

Professional Microsoft SQL Server 2014 Administration

by Adam Jorgensen, Bradley Ball, Steven Wort, Ross LoForte, Brian Knight
Released September 2014
Publisher(s): Wrox
ISBN: 9781118859131

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Learn to take advantage of the opportunities offered by SQL Server 2014

Microsoft's SQL Server 2014 update means big changes for database administrators, and you need to get up to speed quickly because your methods, workflow, and favorite techniques will be different from here on out. The update's enhanced support of large-scale enterprise databases and significant price advantage mean that SQL Server 2014 will become even more widely adopted across the industry. The update includes new backup and recovery tools, new AlwaysOn features, and enhanced cloud capabilities. In-memory OLTP, Buffer Pool Extensions for SSDs, and a new Cardinality Estimator can improve functionality and smooth out the workflow, but only if you understand their full capabilities. Professional Microsoft SQL Server 2014 is your comprehensive guide to working with the new environment. Authors Adam Jorgensen, Bradley Ball, Ross LoForte, Steven Wort, and Brian Knight are the dream team of the SQL Server community, and they put their expertise to work guiding you through the changes.

  • Improve oversight with better management and monitoring

  • Protect your work with enhanced security features

  • Upgrade performance tuning, scaling, replication, and clustering

  • Learn new options for backup and recovery

    • Professional Microsoft SQL Server 2014 includes a companion website with sample code and efficient automation utilities, plus a host of tips, tricks, and workarounds that will make your job as a DBA or database architect much easier. Stop getting frustrated with administrative issues and start taking control. Professional Microsoft SQL Server 2014 is your roadmap to mastering the update and creating solutions that work.

    Table of contents

    1. Cover
    2. Chapter 1: SQL Server 2014 Architecture
      1. SQL SERVER 2014 ECOSYSTEM
      2. NEW IMPORTANT FEATURES IN 2014
      3. SQL SERVER ARCHITECTURE
      4. EDITIONS OF SQL SERVER
      5. SUMMARY
    3. Chapter 2: Installing SQL Server 2014 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
    4. Chapter 3: Upgrading SQL Server 2014 Best Practices
      1. WHY UPGRADE TO SQL SERVER 2014?
      2. UPGRADING TO SQL SERVER 2014
      3. PRE-UPGRADE STEPS AND TOOLS
      4. BACKWARD COMPATIBILITY
      5. SQL SERVER COMPONENT CONSIDERATIONS
      6. POST-UPGRADE CHECKS
      7. SUMMARY
    5. Chapter 4: Managing and Troubleshooting the Database Engine
      1. CONFIGURATION AND ADMINISTRATION TOOLS
      2. TROUBLESHOOTING TOOLS
      3. SQL SERVER MANAGEMENT STUDIO
      4. MONITORING PROCESSES IN T-SQL
      5. MULTISERVER MANAGEMENT
      6. TRACE FLAGS
      7. GETTING HELP FROM SUPPORT
      8. SUMMARY
    6. 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. MULTISERVER ADMINISTRATION
      6. SUMMARY
    7. Chapter 6: Service Broker in SQL Server 2014
      1. ASYNCHRONOUS MESSAGING
      2. CONFIGURING SQL SERVER SERVICE BROKER
      3. USING SQL SERVER SERVICE BROKER
      4. SUMMARY
    8. Chapter 7: SQL Server CLR Integration
      1. INTRODUCTION TO CLR
      2. CREATING CLR ASSEMBLIES
      3. CLR INTEGRATION SECURITY
      4. PERFORMANCE MONITORING
      5. SUMMARY
    9. Chapter 8: Securing the Database Instance
      1. AUTHENTICATION TYPES
      2. LOGINS AND USERS
      3. AUTHORIZING SECURABLES
      4. ROW-LEVEL SECURITY
      5. SUMMARY
    10. Chapter 9: In-Memory OLTP
      1. USING AND IMPLEMENTING IN-MEMORY OLTP
      2. CREATING NATIVELY COMPILED STORED PROCEDURES
      3. OVERVIEW OF THE ANALYZE, MIGRATE, AND REPORT (ARM) TOOL
      4. SUMMARY
    11. 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
    12. Chapter 11: Optimizing SQL Server 2014
      1. APPLICATION OPTIMIZATION
      2. THE SILENT KILLER: I/O PROBLEMS
      3. SQL SERVER INTERNALS AND FILE ALLOCATIONS
      4. TABLE AND INDEX PARTITIONING
      5. DATA COMPRESSION
      6. UNDERSTANDING SQL SERVER AND CPUs
      7. MEMORY CONSIDERATIONS AND ENHANCEMENTS
      8. RESOURCE GOVERNOR
      9. SUMMARY
    13. 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. SQL SERVER STANDARD REPORTS
      8. SYSTEM CENTER ADVISOR
      9. SUMMARY
    14. Chapter 13: Performance Tuning T-SQL
      1. OVERVIEW OF QUERY PROCESSING
      2. IDENTIFYING SQL QUERY PERFORMANCE TUNING ISSUES
      3. DATA ACCESS OPERATORS IN QUERY PLANS
      4. JOIN OPERATORS
      5. DATA MODIFICATION QUERY PLAN
      6. ANALYZING QUERY PERFORMANCE IN A PRODUCTION ENVIRONMENT
      7. PUTTING IT ALL TOGETHER
      8. SUMMARY
    15. Chapter 14: Indexing Your Database
      1. WHAT'S NEW FOR INDEXES IN SQL SERVER 2014
      2. ABOUT INDEXES AND PARTITIONED TABLES
      3. INDEX MAINTENANCE
      4. IMPROVING QUERY PERFORMANCE WITH INDEXES
      5. DATABASE TUNING ADVISOR
      6. SUMMARY
    16. 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
    17. Chapter 16: Clustering SQL Server 2014
      1. CLUSTERING AND YOUR ORGANIZATION
      2. CLUSTERING: THE BIG PICTURE
      3. UPGRADING SQL SERVER CLUSTERING
      4. GETTING PREPARED FOR CLUSTERING
      5. CLUSTERING WINDOWS SERVER 2012 R2
      6. CLUSTERING MICROSOFT DISTRIBUTED TRANSACTION COORDINATOR
      7. CLUSTERING SQL SERVER 2014
      8. MANAGING AND MONITORING THE CLUSTER
      9. TROUBLESHOOTING CLUSTER PROBLEMS
      10. SUMMARY
    18. Chapter 17: Backup and Recovery
      1. BACKUP AND RESTORE ENHANCEMENTS
      2. OVERVIEW OF BACKUP AND RESTORE
      3. PREPARING FOR RECOVERY
      4. DEVELOPING AND EXECUTING A BACKUP PLAN
      5. MANAGING BACKUPS
      6. BACKUP AND RESTORE PERFORMANCE
      7. PERFORMING RECOVERY
      8. ARCHIVING DATA
      9. SUMMARY
    19. Chapter 18: SQL Server 2014 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 2014 LOG SHIPPING
      13. SUMMARY
    20. 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 2014 EDITIONS
      5. DATABASE MIRRORING ROLE CHANGE
      6. MONITORING USING DATABASE MIRRORING MONITOR
      7. PREPARING THE MIRROR SERVER FOR FAILOVER
      8. DATABASE MIRRORING AND OTHER HIGH-AVAILABILITY SOLUTIONS
      9. DATABASE SNAPSHOTS
      10. SUMMARY
    21. 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
    22. 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. SUMMARY
    23. Chapter 22: SQL Server Reporting Services Administration
      1. SQL SERVER REPORTING SERVICES CONFIGURATION MANAGER
      2. THE REPORT EXECUTION LOG
      3. REPORT MANAGER
      4. SUMMARY
    24. Chapter 23: SQL Server 2014 SharePoint 2013 Integration
      1. COMPONENTS OF INTEGRATION
      2. DATA REFRESH
      3. SUMMARY
    25. Chapter 24: SQL Database Administration and Configuration
      1. GETTING TO KNOW WINDOWS AZURE SQL DATABASE
      2. SQL DATABASE ARCHITECTURE
      3. CONFIGURING SQL DATABASE
      4. ADMINISTERING SQL DATABASE
      5. WORKING WITH SQL DATABASE
      6. WHAT'S MISSING IN SQL DATABASE
      7. SUMMARY
    26. 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
    27. Introduction
      1. Who This Book Is For
      2. How This Book Is Structured
      3. What You Need to Use This Book
      4. Conventions
      5. Source Code
      6. Errata
      7. p2p.wrox.com
    28. Advertisement
    29. End User License Agreement

    Product information

    • Title: Professional Microsoft SQL Server 2014 Administration
    • Author(s): Adam Jorgensen, Bradley Ball, Steven Wort, Ross LoForte, Brian Knight
    • Release date: September 2014
    • Publisher(s): Wrox
    • ISBN: 9781118859131