SQL Server 2017 Administrator's Guide

SQL Server 2017 Administrator's Guide

by Marek Chmel, Vladimír Mužný
Released December 2017
Publisher(s): Packt Publishing
ISBN: 9781786462541

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

Implement and administer successful database solution with SQL Server 2017

About This Book
  • Master the required skills to successfully set up, administer, and maintain your SQL Server 2017 database solution
  • Design and configure, manage, and secure a rock-solid SQL server
  • Comprehensive guide in keeping your SQL server disaster proof and all-time availability
Who This Book Is For

This book targets database administrators with an interest in SQL Server 2017 administration. Readers are expected to have some experience with previous SQL Server versions.

What You Will Learn
  • Learn about the new features of SQL Server 2017 and how to implement them
  • Build a stable and fast SQL Server environment
  • Fix performance issues by optimizing queries and making use of indexes
  • Perform a health check of an existing troublesome database environment
  • Design and use an optimal database management strategy
  • Implement efficient backup and recovery techniques in-line with security policies
  • Combine SQL Server 2017 and Azure and manage your solution by various automation techniques Perform data migration, cluster upgradation and server consolidation
In Detail

Take advantage of the real power of SQL Server 2017 with all its new features, in addition to covering core database administration tasks. This book will give you a competitive advantage by helping you quickly learn how to design, manage, and secure your database solution.

You will learn how to set up your SQL Server and configure new (and existing) environments for optimal use. After covering the designing aspect, the book delves into performance-tuning aspects by teaching you how to effectively use indexes. The book will also teach you about certain choices that need to be made about backups and how to implement a rock-solid security policy and keep your environment healthy. Finally, you will learn about the techniques you should use when things go wrong, and other important topics - such as migration, upgrading, and consolidation - are covered in detail. Integration with Azure is also covered in depth.

Whether you are an administrator or thinking about entering the field, this book will provide you with all the skills you need to successfully create, design, and deploy databases using SQL Server 2017.

Style and approach

A comprehensive guide for database professionals, covering a wide range of topics from installation, maintenance, and configuration to managing systems for operational efficiency and high availability; best practices for maintaining a highly reliable database solution are also supplied from industry experts.

Table of contents

  1. Preface
    1. What this book covers
    2. What you need for this book
    3. Who this book is for
    4. Conventions
    5. Reader feedback
    6. Customer support
      1. Downloading the example code
      2. Errata
      3. Piracy
      4. Questions
  2. Setting up SQL Server 2017
    1. Microsoft SQL Server 2017 technology overview
      1. Understanding SQL Server DE
      2. SQL Server integration services
        1. Master Data Services
        2. Data Quality Services
      3. SQL Server Analysis Services
        1. Multidimensional mode of SSAS installation
        2. PowerPivot mode of SSAS installation
      4. SQL Server Reporting Services
      5. Machine learning services
      6. SQL Server Agent
      7. Conclusion
    2. Preparing for installation
      1. Edition comparison
      2. Preinstallation tasks
        1. Planning memory
        2. Planning disk capacity
        3. Software and other requirements
        4. Security accounts for SQL Server
    3. Installation process
      1. Installation options
        1. Installation wizard
        2. Command line
        3. New installation possibilities
      2. Checking the completed installation
        1. SQL Server services configuration node
        2. SQL Server network configuration node
        3. Testing connection to a fresh SQL Server
    4. Summary
  3. Keeping Your SQL Server Environment Healthy
    1. Understanding SQL Server patching
      1. Installing updates
    2. Post-installation configuration
      1. Configuring security rights for a SQL Server account
      2. Configuring power settings
      3. Configuring firewall rules
      4. SQL Server post-installation configuration
    3. Creating a performance baseline
    4. Summary
  4. Backup and Recovery
    1. Data structures and transaction logging
      1. How data is stored
        1. Database
          1. Database master
          2. Database msdb
          3. Database model
          4. Database tempdb
          5. Database resourcedb
        2. Filegroup
        3. Data files
        4. Transaction log
    2. Backup
      1. Recovery model
      2. Backup types
        1. Full backup
        2. Transaction log backup
        3. Differential backup
      3. Advanced backup scenarios
        1. Backup media
        2. Backup reliability
        3. File or filegroup backup
        4. Backup system databases
        5. Backup summary
    3. Restore
      1. Preparing for restore
      2. Executing restores
        1. Using the full backup strategy
        2. Using full and transaction log backup strategy
        3. Point-in-time recovery
        4. Using full, transaction log, and differential backup strategies
        5. Using file or filegroup backups
        6. Restoring data pages
        7. System database restore
    4. Summary
  5. Securing Your SQL Server
    1. SQL Server service accounts
      1. Virtual accounts
      2. Managed service accounts
      3. Group managed service accounts
      4. Domain accounts and local accounts
    2. Authentication and authorization
      1. Authentication
        1. Server logins
        2. Managing login properties
      2. Authorization
        1. Fixed server roles
        2. Working with permissions on the server
      3. Auditing
        1. Configuring server audit
        2. Server audit specifications
      4. Configuring credentials
        1. Credentials and proxies in the SQL Server Agent
    3. Encrypting SQL Server data
      1. Transparent data encryption
      2. Always encrypted
    4. Summary
  6. Disaster Recovery Options
    1. Disaster recovery basics
    2. SQL Server options for high availability and disaster recovery
      1. Always On Failover Cluster
      2. Always On Availability Groups
      3. Database mirroring
      4. Log shipping
      5. Replication
    3. Configuring replication on SQL Server
      1. Creating a Publication
      2. Configuring the subscription
      3. Replication agents
    4. Database mirroring
      1. Configuring database mirroring
    5. Log shipping
      1. Switching log shipping roles
    6. Summary
  7. Indexing and Performance
    1. Performance monitoring overview
    2. Tools for monitoring performance
      1. Activity Monitor
        1. Overview
        2. Section processes
        3. Resource Waits
        4. Data File I/O
        5. Recent Expensive Queries and Active Expensive Queries
      2. Performance monitor
      3. SQL Server Profiler and SQL Trace
        1. SQL Server Profiler
        2. SQL Trace
      4. Extended Events
      5. Dynamic management
        1. How to use dynamic management
      6. Data collection
        1. Setting up MDW
        2. Collecting performance data
        3. Viewing data collection reports
      7. Query Store
    3. Indexes and maintenance
      1. Types of indexes
        1. Heap
        2. Non-clustered B-tree index
        3. Clustered B-tree index
        4. Non-clustered and clustered index cooperation
        5. Columnstore indexes
        6. Other index types
      2. Indexing considerations
    4. Summary
  8. Troubleshooting SQL Server Environment and Internals
    1. SQL Server protocols
      1. Query processor
      2. The storage engine layer
    2. Performance monitoring and tuning
      1. SQL Server memory monitoring and troubleshooting
      2. SQL Server storage monitoring and troubleshooting
      3. Troubleshooting wait statistics
        1. PAGEIOLATCH
        2. PAGELATCH
        3. CXPACKET
    3. Summary
  9. Migration and Upgrade
    1. Why migration is necessary
    2. Planning the upgrade
      1. Upgrade scenarios
        1. SQL Server in-place upgrade
        2. Side-by-side migration
        3. Rolling upgrade
      2. Pre-upgrade checks
        1. Data Migration Assistant
        2. SQL Server system configuration checker
    3. Performing the upgrade
      1. Upgrading Reporting Services 2017
    4. Migration from other platforms
      1. Migration example from MS Access
    5. Summary
  10. Automation - Using Tools to Manage and Monitor SQL Server 2017
    1. Using SQL Server Agent
      1. SQL Server Agent service setup
      2. Setting up SQL Server Agent Properties
      3. Database mail setup
        1. Sending emails from SQL Server
    2. Maintenance plans
      1. Maintenance plan wizard
        1. Check database integrity
        2. Shrink database
        3. Reorganize index
        4. Rebuild index
        5. Update statistics
        6. Clean up history
        7. Execute SQL Server Agent job
        8. Backup tasks
        9. Maintenance cleanup task
      2. Maintenance plan designer
    3. SQL Server Agent objects
      1. Operators
      2. Jobs
        1. Job definition
        2. Job steps definition
        3. Schedule definition
        4. Notification definition
        5. Monitoring and troubleshooting jobs
      3. Alerts
    4. Summary
  11. Always On High Availability Features
    1. Installing Windows Server Failover Cluster
    2. Always On Failover Cluster Instances
      1. Adding nodes to the SQL Server Failover Cluster
      2. Initiating a failover
    3. Always On Availability Groups
      1. Configuring Always On Availability Groups
        1. Creating an Availability Group
        2. Failover and monitoring
        3. Basic Availability Groups
        4. Distributed Availability Groups
    4. Summary
  12. In-Memory OLTP - Why and How to Use it
    1. In-Memory OLTP architecture
      1. Data storage differences in OLTP
      2. Request processing differences in OLTP
      3. Cooperation of disk-based and memory-based parts of SQL Server
      4. In-Memory OLTP limitations
    2. Creating In-Memory tables and natively compiled stored procedures
      1. Preparing for In-Memory OLTP
      2. Creating In-Memory OLTP objects
        1. Creating tables with indexes
        2. Natively compiled stored procedures
        3. Migrating disk-based objects to In-Memory OLTP
        4. Monitoring In-Memory OLTP
    3. In-Memory OLTP usage scenarios
      1. Assignment of the user story sample
      2. Example 1 – inserting incoming data into In-Memory tables
      3. Example 2 – updating data in an In-Memory table
      4. Example 3 – improving real-time analytics
    4. Summary
  13. Combining SQL Server 2017 with Azure
    1. Overview of data related technologies in Microsoft Azure
      1. Understanding Azure SQL database
        1. Starting with Azure portal
        2. Creating the Azure SQL database
      2. Understanding Azure SQL Server
        1. Creating Azure SQL Server
        2. Using Azure SQL Server
      3. Azure SQL Data Warehouse
        1. Creating Azure SQL Data Warehouse
        2. Using Azure SQL Data Warehouse
    2. Microsoft SQL Server 2017 and hybrid scenarios
      1. StretchDB
      2. Data files in Azure
      3. Backups to Azure
        1. Managed backup to Azure
        2. Backup to a URL
        3. Snapshot backups
    3. Summary

Product information

  • Title: SQL Server 2017 Administrator's Guide
  • Author(s): Marek Chmel, Vladimír Mužný
  • Release date: December 2017
  • Publisher(s): Packt Publishing
  • ISBN: 9781786462541