SQL Server 2017 Administration Inside Out, First Edition

Book description

Conquer SQL Server 2017 administration—from the inside out


Dive into SQL Server 2017 administration—and really put your SQL Server DBA expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds—all you need to plan, implement, manage, and secure SQL Server 2017 in any production environment: on-premises, cloud, or hybrid. Four SQL Server experts offer a complete tour of DBA capabilities available in SQL Server 2017 Database Engine, SQL Server Data Tools, SQL Server Management Studio, and via PowerShell. Discover how experts tackle today’s essential tasks—and challenge yourself to new levels of mastery.

• Install, customize, and use SQL Server 2017’s key administration and development tools

• Manage memory, storage, clustering, virtualization, and other components

• Architect and implement database infrastructure, including IaaS, Azure SQL, and hybrid cloud configurations

• Provision SQL Server and Azure SQL databases

• Secure SQL Server via encryption, row-level security, and data masking

• Safeguard Azure SQL databases using platform threat protection, firewalling, and auditing

• Establish SQL Server IaaS network security groups and user-defined routes

• Administer SQL Server user security and permissions

• Efficiently design tables using keys, data types, columns, partitioning, and views

• Utilize BLOBs and external, temporal, and memory-optimized tables

• Master powerful optimization techniques involving concurrency, indexing, parallelism, and execution plans

• Plan, deploy, and perform disaster recovery in traditional, cloud, and hybrid environments



For Experienced SQL Server Administrators and Other Database Professionals

• Your role: Intermediate-to-advanced level SQL Server database administrator, architect, developer, or performance tuning expert


• Prerequisites: Basic understanding of database administration procedures

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Dedication
  5. Table of Contents
  6. Foreword
  7. Introduction
  8. Chapter 1 Getting started with SQL Server tools
    1. SQL Server setup
    2. Tools and services installed with the SQL Server Database Engine
    3. Performance and reliability monitoring tools
    4. SQL Server Reporting Services
    5. SQL Server Management Studio
    6. SQL Server Data Tools
    7. A note on deprecation
  9. Chapter 2 Introducing database server components
    1. Memory
    2. Central Processing Unit
    3. Storing your data
    4. Connecting to SQL Server over the network
    5. High availability concepts
    6. Securing SQL Server
    7. Abstracting hardware with virtualization
    8. Summary
  10. Chapter 3 Designing and implementing a database infrastructure
    1. Physical database architecture
    2. Configuration settings
    3. Azure and the Data Platform
  11. Chapter 4 Provisioning databases
    1. What to do before installing SQL Server
    2. Installing a new instance
    3. Post-installation server configuration
    4. Installing and configuring features
    5. Adding databases to a SQL Server instance
    6. Moving and removing databases
  12. Chapter 5 Provisioning Azure SQL Database
    1. Azure and database-as-a-service concepts
    2. Provisioning a logical SQL server
    3. Provisioning a database in Azure SQL Database
    4. Provisioning an elastic pool
    5. Limitations of Azure SQL Database
    6. Security in Azure SQL Database
    7. Preparing Azure SQL Database for disaster recovery
    8. Moving to Azure SQL Database
  13. Chapter 6 Administering security and permissions
    1. Logins and users
    2. Permissions in SQL Server
    3. Moving SQL Server logins and permissions
  14. Chapter 7 Securing the server and its data
    1. Introducing security principles and protocols
    2. Encryption in SQL Server
    3. Securing data in motion
    4. Auditing with SQL Server and Azure SQL Database
    5. Securing Azure infrastructure as a service
  15. Chapter 8 Understanding and designing tables
    1. Reviewing table design
    2. Special table types
    3. Storing BLOBs
    4. Table partitioning
    5. Capturing modifications to data
  16. Chapter 9 Performance tuning SQL Server
    1. Understanding isolation levels and concurrency
    2. Understanding delayed durability
    3. Understanding execution plans
    4. Using the Query Store feature
    5. Understanding automatic plan correction
    6. Understanding execution plan operators
    7. Understanding parallelism
  17. Chapter 10 Understanding and designing indexes
    1. Designing clustered indexes
    2. Designing nonclustered indexes
    3. Designing Columnstore indexes
    4. Understanding indexing in memory-optimized tables
    5. Understanding other types of indexes
    6. Understanding index statistics
  18. Chapter 11 Developing, deploying, and managing data recovery
    1. The fundamentals of data recovery
    2. Understanding backup devices
    3. Understanding different types of backups
    4. Creating and verifying backups
    5. Defining a recovery strategy
  19. Chapter 12 Implementing high availability and disaster recovery
    1. Overview of high availability and disaster recovery technologies in SQL Server
    2. Configuring Failover Cluster Instances
    3. Configuring availability groups
    4. Configuring an availability group on Red Hat Linux
    5. Administering availability groups
  20. Chapter 13 Managing and monitoring SQL Server
    1. Detecting database corruption
    2. Maintaining indexes and statistics
    3. Maintaining database file sizes
    4. Monitoring databases by using DMVs
    5. Reintroducing extended events
    6. Capturing Windows performance metrics with DMVs and data collectors
    7. Protecting important workloads using Resource Governor
    8. Understanding the new servicing model
  21. Chapter 14 Automating SQL Server administration
    1. Components of SQL Server automated administration
    2. SQL Server Agent
    3. Maintaining SQL Server
    4. Using SQL Server Maintenance Plans
    5. Strategies for administering multiple SQL Servers
    6. Using PowerShell to automate SQL Server administration
  22. Index
  23. About the authors
  24. About the Foreword author
  25. Code Snippets

Product information

  • Title: SQL Server 2017 Administration Inside Out, First Edition
  • Author(s): Sven Aelterman, William Assaf, Randolph West
  • Release date: February 2018
  • Publisher(s): Microsoft Press
  • ISBN: 9781509305230