Skip to main content
Top

2023 | Book

Pro SQL Server 2022 Administration

A Guide for the Modern DBA

insite
SEARCH

About this book

Get your daily work done efficiently using this comprehensive guide for SQL Server DBAs that covers all that a practicing database administrator needs to know. Updated for SQL Server 2022, this edition includes coverage of new features, such as Ledger, which provides an immutable record of table history to protect you against malicious data tampering, and integration with cloud providers to support hybrid cloud scenarios. You’ll also find new content on performance optimizations, such as query pan feedback, and security controls, such as new database roles, which are restructured for modern ways of working. Coverage also includes Query Store, installation on Linux, and the use of containerized SQL.
Pro SQL Server 2022 Administration takes DBAs on a journey that begins with planning their SQL Server deployment and runs through installing and configuring the instance, administering and optimizing database objects, and ensuring that data is secure and highly available. Readers will learn how to perform advanced maintenance and tuning techniques, and discover SQL Server's hybrid cloud functionality.
This book teaches you how to make the most of new SQL Server 2022 functionality, including integration for hybrid cloud scenarios. The book promotes best-practice installation, shows how to configure for scalability and high availability, and demonstrates the gamut of database-level maintenance tasks, such as index maintenance, database consistency checks, and table optimizations.

What You Will LearnIntegrate SQL Server with Azure for hybrid cloud scenariosAudit changes and prevent malicious data changes with SQL Server’s LedgerSecure and encrypt data to protect against embarrassing data breachesEnsure 24 x 7 x 365 access through high availability and disaster recovery features in today’s hybrid worldUse Azure tooling, including Arc, to gain insight into and manage your SQL Server enterpriseInstall and configure SQL Server on Windows, Linux, and in containers Perform routine maintenance tasks, such as backups and database consistency checks Optimize performance and undertake troubleshooting in the Database Engine
Who This Book Is For
SQL Server DBAs who manage on-premise installations of SQL Server. This book is also useful for DBAs who wish to learn advanced features, such as integration with Azure, Query Store, Extended Events, and Policy-Based Management, or those who need to install SQL Server in a variety of environments.

Table of Contents

Frontmatter

Installation and Configuration

Frontmatter
Chapter 1. Planning the Deployment
Abstract
Planning a deployment of SQL Server 2022, in order to best support the business’s needs, can be a complicated task. You should make sure to consider many areas, including edition, licensing requirements, on-premises vs. cloud hosting, hardware considerations, software configuration, and even if Windows is the best platform. For example, if your new instance will be supporting a PHP Web App, hosted on Linux, then maybe your instance should also be hosted on Linux? And all of this is before you even start to consider which features of SQL Server you may need to install to support the application.
Peter A. Carter
Chapter 2. GUI Installation
Abstract
You can invoke SQL Server’s Installation Center by running SQL Server’s setup.exe application. The Installation Center provides many utilities that will help you install an instance; these include links and tools to assist you with planning your deployment, stand-alone and clustered installation capability, and advanced tools, which will allow you to build instances using configuration files or based upon prepared images.
Peter A. Carter
Chapter 3. Server Core Installation
Abstract
Because SQL Server does not support remote installations and because Windows Server Core provides only a command-line interface (CLI) and no graphical user interface (GUI), you must perform installation of SQL Server on Windows Server Core as a command-line operation. You can also use a configuration file to produce consistent, repeatable installations.
Peter A. Carter
Chapter 4. Installation on Heterogeneous Operating Systems
Abstract
The last two major releases of SQL Server have focused heavily on providing capability to install SQL Server in a variety of nontraditional environments. In this chapter, we will explore how to install SQL Server on Linux and how to build and run Docker images and containers that include SQL Server.
Peter A. Carter
Chapter 5. Configuring the Instance
Abstract
The installation and configuration of your SQL Server instance does not end when setup successfully completes. There are many other considerations that you should take into account, both inside the database engine and outside of it, using tools such as SQL Server Configuration Manager. In this chapter, we will discuss many of the most important instance-level configuration options, including processor and memory configuration, SQL Server’s buffer pool extension, and hybrid buffer pools. We will also explore important configuration choices for system databases, how your instance can configure and how to configure SQL Server, to work with your firewall. We will also look at some useful trace flags and how to set them.
Peter A. Carter

Database Administration

Frontmatter
Chapter 6. Database Configuration
Abstract
Within a database, data is stored in one or more data files. These files are grouped into logical containers called filegroups. Every database also has at least one log file. Log files sit outside of filegroup containers and do not follow the same rules as data files. This chapter begins by discussing filegroup strategies that database administrators (DBAs) can adopt before it looks at how DBAs can maintain data and log files.
Peter A. Carter
Chapter 7. Table Optimizations
Abstract
During the life cycle of your data-tier applications, you may need to perform a number of maintenance tasks and performance optimizations against the tables that hold your application’s data. These operations may include partitioning a table, compressing a table, or migrating data to a memory-optimized table. In this chapter, we will explore these three concepts in detail.
Peter A. Carter
Chapter 8. Indexes and Statistics
Abstract
Recent versions of SQL Server support many different types of index that are used to enhance query performance. These include traditional clustered and nonclustered indexes, which are built on B-tree (balanced-tree) structures and enhance read performance on disk-based tables. There are also indexes that support complex data types, such as XML, JSON, and geospatial data types. These advanced data type indexes are beyond the scope of this book, but a full discussion can be found in the Apress title SQL Server Advanced Data Types, which can be found at www.apress.com/gp/book/9781484239001. DBAs can also create Columnstore indexes to support data warehouse–style queries, where analysis is performed on very large tables. SQL Server also supports in-memory indexes, which enhance the performance of tables that are stored using In-Memory OLTP. This chapter discusses many of the available index types inside the Database Engine.
Peter A. Carter
Chapter 9. Database Consistency
Abstract
Databases involve lots of I/O. When you have a lot of I/O, you inherently run the risk of corruption. Your primary defense against database corruption is to take regular backups of your database and to periodically test that these backups can be restored. You need to look out for database corruption, however, and SQL Server provides tools you can use to check the consistency of your database, as well as to resolve consistency issues if backups are not available. This chapter will look at the options you have for both checking and fixing consistency issues.
Peter A. Carter

Security, Resilience, and Scaling Workloads

Frontmatter
Chapter 10. SQL Server Security Model
Abstract
SQL Server 2022 offers a complex security model with overlapping layers of security that help database administrators (DBAs) counter the risks and threats in a manageable way. It is important for DBAs to understand the SQL Server security model so that they can implement the technologies in the way that best fits the needs of their organization and applications. This chapter discusses the SQL Server security hierarchy before demonstrating how to implement security at the instance, database, and object levels.
Peter A. Carter
Chapter 11. Auditing and Ledger
Abstract
Passive security refers to the practice of logging user activity in order to avoid the threat of nonrepudiation. This is important because if an attack is launched by a privileged user, it allows for appropriate disciplinary or even legal action to be taken. SQL Server provides SQL Server Audit to assist with implementing passive security. SQL Server 2022 also introduces Ledger, which uses blockchain technology to make data tamper-evident, which allows for streamlined auditing processes. In this chapter, we will discuss the new ledger functionality, how to implement it and how to use it to discover who has changed data. We will also explore the concepts involved in auditing before demonstrating how to implement SQL Server Audit, including the creation of custom audit event.
Peter A. Carter
Chapter 12. Encryption
Abstract
Encryption is a process of obfuscating data with an algorithm that uses keys and certificates so that if security is bypassed and data is accessed or stolen by unauthorized users, then it will be useless, unless the keys that were used to encrypt it are also obtained. This adds an additional layer of security over and above access control, but it does not replace the need for an access control implementation. Encrypting data also has the potential to considerably degrade performance, so you should use it on the basis of need, as opposed to implementing it on all data as a matter of routine.
Peter A. Carter
Chapter 13. Backups and Restores
Abstract
Backing up a database is one of the most important tasks that a DBA can perform. Therefore, after discussing the principles of backups, we look at some of the backup strategies that you can implement for SQL Server databases. We then discuss how to perform the backup of a database before we finally look in-depth at restoring it, including restoring to a point in time, restoring individual files and pages, and performing piecemeal restores.
Peter A. Carter
Chapter 14. High Availability and Disaster Recovery Concepts
Abstract
In today’s 24×7 environments that are running mission-critical applications, businesses rely heavily on the availability of their data. Although servers and their software are generally reliable, there is always the risk of a hardware failure or a software bug, each of which could bring a server down. To mitigate these risks, business-critical applications often rely on redundant hardware to provide fault tolerance. If the primary system fails, then the application can automatically fail over to the redundant system. This is the underlying principle of high availability (HA).
Peter A. Carter
Chapter 15. Implementing AlwaysOn Availability Groups
Abstract
AlwaysOn Availability Groups provide a flexible option for achieving high availability, recovering from disasters, and scaling out read-only workloads. The technology synchronizes data at the database level, but health monitoring and quorum are provided by a Windows cluster.
Peter A. Carter
Chapter 16. Implementing Log Shipping
Abstract
As discussed in Chapter 14, log shipping is a technology you can use to implement disaster recovery and the scale out of read-only reporting. It works by taking the transaction log backups of a database, copying them to one or more secondary servers, and then restoring them, in order to keep the secondary server(s) synchronized. This chapter demonstrates how to implement log shipping for disaster recovery (DR). You also discover how to monitor and fail over log shipping.
Peter A. Carter
Chapter 17. Scaling Workloads
Abstract
SQL Server provides multiple technologies that allow DBAs to horizontally scale their workloads between multiple databases to avoid lock contention or to scale them horizontally between servers to spread resource utilization. These technologies include database snapshots, replication, and AlwaysOn Availability Groups. This chapter discusses the considerations for these technologies and demonstrates how to implement them.
Peter A. Carter

Performance and Maintenance

Frontmatter
Chapter 18. SQL Server Metadata
Abstract
Metadata is data that describes other data. SQL Server exposes a vast array of metadata including structural metadata, which describes every object, and descriptive metadata, which described the data itself. Metadata is exposed through a series of
Peter A. Carter
Chapter 19. Locking and Blocking
Abstract
Locking is an essential aspect of any RDBMS, because it allows concurrent users to access the same data, without the risk of their updates conflicting and causing data integrity issues. This chapter discusses how locking, deadlocks, and transactions work in SQL Server; it then moves on to discuss how transactions impact In-Memory transaction functionality and how the DBA can observe lock metadata regarding transactions and contention.
Peter A. Carter
Chapter 20. Extended Events
Abstract
Extended Events are a lightweight monitoring system offered by SQL Server. Because the architecture uses so few system resources, they scale very well and allow you to monitor their instances, with minimal impact on user activity. They are also highly configurable, which gives you in your role as a DBA a wide range of options for capturing details from a very fine grain, such as page splits, to higher-level detail, such as CPU utilization. You can also correlate Extended Events with operating system data to provide a holistic picture when troubleshooting issues. The predecessor to Extended Events was SQL Trace, and its GUI, called Profiler. This is now deprecated for use with the Database Engine, and it is recommended that you only use it for tracing Analysis Service activity.
Peter A. Carter
Chapter 21. Monitoring and Managing a Hybrid Environment
Abstract
SQL Server 2022 has functionality, which helps you to manage your SQL Server estate in a hybrid environment, meaning that you can centralize administration and gain some of the benefits of cloud, even for your on-premises SQL Server estate.
Peter A. Carter
Chapter 22. Query Store
Abstract
The Query Store captures the history of queries, their plans, and statistics. It allows DBAs to easily view the plans that were used by queries and troubleshoot performance issues. In this chapter, we will discuss how Query Store can be enabled and configured. We will also examine how the Query Store can be used to diagnose and resolve performance issues.
Peter A. Carter
Chapter 23. Automating Maintenance Routines
Abstract
Automation is a critical part of database administration because it reduces the total cost of ownership (TCO) of the enterprise by allowing repeatable tasks to be carried out with little or no human intervention. SQL Server provides a rich set of functionality for automating routine DBA activity, including a scheduling engine, decision-tree logic, and a comprehensive security model. In this chapter, we discuss how you can harness SQL Server Agent to reduce the maintenance burden on your time. We also look at how you can reduce effort by using multiserver jobs, which allow you to operate a consistent set of routines across the enterprise.
Peter A. Carter
Chapter 24. Policy-Based Management
Abstract
Policy-Based Management (PBM) is a system DBAs can use to report on or enforce standards across the enterprise, when used with a Central Management Server. This chapter first introduces you to the concepts used by PBM and then demonstrates how to use PBM to effectively manage an estate through the GUI and with PowerShell.
Peter A. Carter
Chapter 25. Resource Governor
Abstract
Resource Governor provides a method for throttling applications at the SQL Server layer by imposing limits on CPU, memory, and physical I/O on different classifications of connection. This chapter discusses the concepts the Resource Governor uses before demonstrating how to implement them. We then look at how to monitor the effect that Resource Governor has on resource utilization.
Peter A. Carter
Backmatter
Metadata
Title
Pro SQL Server 2022 Administration
Author
Peter A. Carter
Copyright Year
2023
Publisher
Apress
Electronic ISBN
978-1-4842-8864-1
Print ISBN
978-1-4842-8863-4
DOI
https://doi.org/10.1007/978-1-4842-8864-1

Premium Partner