SQL server 2017 administrator's guide : one stop solution for DBAs to monitor, manage, and maintain enterprise databases / Marek Chmel, Vladimir Muzny.

Format:

Book

Author/Creator:

Chmel, Marek, author.

Muzny, Vladimir, author.

Language:

English

Subjects (All):

SQL server.

SQL (Computer program language).

Database management.

Physical Description:

1 online resource (1 volume) : illustrations

Edition:

1st edition

Place of Publication:

Birmingham, England ; Mumbai, [India] : Packt, 2017.

System Details:

text file

Summary:

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;...

Contents:

Cover

Title Page

Copyright

Credits

About the Authors

About the Reviewer

www.PacktPub.com

Customer Feedback

Table of Contents

Preface

Chapter 1: Setting up SQL Server 2017

Microsoft SQL Server 2017 technology overview

Understanding SQL Server DE

SQL Server integration services

Master Data Services

Data Quality Services

SQL Server Analysis Services

Multidimensional mode of SSAS installation

PowerPivot mode of SSAS installation

SQL Server Reporting Services

Machine learning services

SQL Server Agent

Conclusion

Preparing for installation

Edition comparison

Preinstallation tasks

Planning memory

Planning disk capacity

Software and other requirements

Security accounts for SQL Server

Installation process

Installation options

Installation wizard

Command line

New installation possibilities

Checking the completed installation

SQL Server services configuration node

SQL Server network configuration node

Testing connection to a fresh SQL Server

Summary

Chapter 2: Keeping Your SQL Server Environment Healthy

Understanding SQL Server patching

Installing updates

Post-installation configuration

Configuring security rights for a SQL Server account

Configuring power settings

Configuring firewall rules

SQL Server post-installation configuration

Creating a performance baseline

Chapter 3: Backup and Recovery

Data structures and transaction logging

How data is stored

Database

Database master

Database msdb

Database model

Database tempdb

Database resourcedb

Filegroup

Data files

Transaction log

Backup

Recovery model

Backup types

Full backup

Transaction log backup

Differential backup

Advanced backup scenarios

Backup media

Backup reliability.

File or filegroup backup

Backup system databases

Backup summary

Restore

Preparing for restore

Executing restores

Using the full backup strategy

Using full and transaction log backup strategy

Point-in-time recovery

Using full, transaction log, and differential backup strategies

Using file or filegroup backups

Restoring data pages

System database restore

Chapter 4: Securing Your SQL Server

SQL Server service accounts

Virtual accounts

Managed service accounts

Group managed service accounts

Domain accounts and local accounts

Authentication and authorization

Authentication

Server logins

Managing login properties

Authorization

Fixed server roles

Working with permissions on the server

Auditing

Configuring server audit

Server audit specifications

Configuring credentials

Credentials and proxies in the SQL Server Agent

Encrypting SQL Server data

Transparent data encryption

Always encrypted

Chapter 5: Disaster Recovery Options

Disaster recovery basics

SQL Server options for high availability and disaster recovery

Always On Failover Cluster

Always On Availability Groups

Database mirroring

Log shipping

Replication

Configuring replication on SQL Server

Creating a Publication

Configuring the subscription

Replication agents

Configuring database mirroring

Switching log shipping roles

Chapter 6: Indexing and Performance

Performance monitoring overview

Tools for monitoring performance

Activity Monitor

Overview

Section processes

Resource Waits

Data File I/O

Recent Expensive Queries and Active Expensive Queries

Performance monitor

SQL Server Profiler and SQL Trace

SQL Server Profiler

SQL Trace

Extended Events.

Dynamic management

How to use dynamic management

Data collection

Setting up MDW

Collecting performance data

Viewing data collection reports

Query Store

Indexes and maintenance

Types of indexes

Heap

Non-clustered B-tree index

Clustered B-tree index

Non-clustered and clustered index cooperation

Columnstore indexes

Other index types

Indexing considerations

Chapter 7: Troubleshooting SQL Server Environment and Internals

SQL Server protocols

Query processor

The storage engine layer

Performance monitoring and tuning

SQL Server memory monitoring and troubleshooting

SQL Server storage monitoring and troubleshooting

Troubleshooting wait statistics

PAGEIOLATCH

PAGELATCH

CXPACKET

Chapter 8: Migration and Upgrade

Why migration is necessary

Planning the upgrade

Upgrade scenarios

SQL Server in-place upgrade

Side-by-side migration

Rolling upgrade

Pre-upgrade checks

Data Migration Assistant

SQL Server system configuration checker

Performing the upgrade

Upgrading Reporting Services 2017

Migration from other platforms

Migration example from MS Access

Chapter 9: Automation - Using Tools to Manage and Monitor SQL Server 2017

Using SQL Server Agent

SQL Server Agent service setup

Setting up SQL Server Agent Properties

Database mail setup

Sending emails from SQL Server

Maintenance plans

Maintenance plan wizard

Check database integrity

Shrink database

Reorganize index

Rebuild index

Update statistics

Clean up history

Execute SQL Server Agent job

Backup tasks

Maintenance cleanup task

Maintenance plan designer

SQL Server Agent objects

Operators

Jobs

Job definition

Job steps definition

Schedule definition

Notification definition.

Monitoring and troubleshooting jobs

Alerts

Chapter 10: Always On High Availability Features

Installing Windows Server Failover Cluster

Always On Failover Cluster Instances

Adding nodes to the SQL Server Failover Cluster

Initiating a failover

Configuring Always On Availability Groups

Creating an Availability Group

Failover and monitoring

Basic Availability Groups

Distributed Availability Groups

Chapter 11: In-Memory OLTP - Why and How to Use it

In-Memory OLTP architecture

Data storage differences in OLTP

Request processing differences in OLTP

Cooperation of disk-based and memory-based parts of SQL Server

In-Memory OLTP limitations

Creating In-Memory tables and natively compiled stored procedures

Preparing for In-Memory OLTP

Creating In-Memory OLTP objects

Creating tables with indexes

Natively compiled stored procedures

Migrating disk-based objects to In-Memory OLTP

Monitoring In-Memory OLTP

In-Memory OLTP usage scenarios

Assignment of the user story sample

Example 1 - inserting incoming data into In-Memory tables

Example 2 - updating data in an In-Memory table

Example 3 - improving real-time analytics

Chapter 12: Combining SQL Server 2017 with Azure

Overview of data related technologies in Microsoft Azure

Understanding Azure SQL database

Starting with Azure portal

Creating the Azure SQL database

Understanding Azure SQL Server

Creating Azure SQL Server

Using Azure SQL Server

Azure SQL Data Warehouse

Creating Azure SQL Data Warehouse

Using Azure SQL Data Warehouse

Microsoft SQL Server 2017 and hybrid scenarios

StretchDB

Data files in Azure

Backups to Azure

Managed backup to Azure

Backup to a URL

Snapshot backups

Index.

Notes:

Includes index.

Description based on online resource; title from PDF title page (EBC, viewed January 11, 2018).

ISBN:

9781786467324

1786467321

OCLC:

1020288719