Part I: Introduction

Chapter 1, “Get started with SQL Server tools,” gives you a tour of modern tooling for SQL Server administrators, from the installation media and all tooling, including SQL Server Management Studio and Azure Data Studio, to performance and reliability monitoring tools, tools for writing PowerShell, and more.

Chapter 2, “Introduction to database server components,” introduces the working vocabulary and concepts of database administration, starting with hardware-level topics such as memory, processors, storage, and networking. We then move into high availability basics (much more on those later), security, and hardware and OS virtualization.

Chapter 3, “Design and implement an on-premises database infrastructure,” introduces the architecture and configuration of SQL Server, including deep dives into transaction log virtual log files (VLFs), data files, in-memory online transaction processing (OLTP), accelerated database recovery (ADR), and other new features of SQL Server 2022. We also spend time with tempdb and its optimal configuration and server-level configuration options. Finally, we introduce you to Kubernetes.

Part II: Deployment

Chapter 4, “Install and configure SQL Server instances and features,” reviews installation of SQL Server for Windows platforms when SQL Server Setup is needed to install SQL Server. We discuss volume settings and layout for a SQL Server instance, editions, Smart Setup and unattended setup configuration, and setup logging. Look here also for post-installation checklists and configuration guidance, and for configuration and guidance for other features including SSIS, SSAS, and SSRS, as well as PolyBase.

Chapter 5, “Install and configure SQL Server on Linux,” reviews configuration of SQL Server on Linux instances, including feature differences between Windows and Linux. We’ll provide guidance and caveats on Linux distributions, Linux-specific monitoring and storage considerations, and tooling for setup and administration.

Chapter 6, “Provision and configure SQL Server databases,” reviews creation and configuration of SQL Server databases on any SQL Server platform, including strategies for migrating and moving databases. Database options and properties are discussed, as are database collations.

Chapter 7, “Understand table features,” completes the drill down from instances to databases to tables, covering table design, data types, keys, and constraints. The use of IDENTITY and sequences, computed columns and other column properties, as well as special table types, are discussed. We review special types of tables including temporal tables, introduce memory-optimized tables (more on these in Chapter 14), and graph tables. We review FILESTREAM and FileTable for storing blobs, table partitioning for storing and switching large amounts of data, and strategies for tracking data changes. Finally, we dive deep into PolyBase, the powerful SQL Server feature for virtualization of third-party or non-relational data sources.

Part III: SQL Server management

Chapter 8, “Maintain and monitor SQL Server,” covers the care and feeding of SQL Server instances on both Windows and Linux, including monitoring for database corruption, monitoring index activity and fragmentation, and maintaining and monitoring indexes and index statistics. We dive into Extended Events, the superior alternative to traces, and cover Resource Governor, used for insulating your critical workloads. We review monitoring and data collection strategies based in Windows, Linux, and Azure, as well as the SQL Assessment API. Finally, we discuss the current Microsoft servicing model for SQL Server.

Chapter 9, “Automate SQL Server administration,” introduces automating activities for SQL Server, including maintenance plans, but also custom solutions involving PowerShell, including the latest features available in PowerShell. We also review built-in tools and features needed to automate tasks to your SQL Server, including database mail, SQL Server Agent jobs, proxies, SQL Server Agent alerts, event forwarding, and Policy-Based Management.

Chapter 10, “Develop, deploy, and manage data recovery,” covers the fundamentals of SQL Server database backups in preparation for disaster recovery scenarios, including a backup and recovery strategy appropriate for your environment. We use a memorable narrative to explain various factors, features, and failures in a fictional disaster recovery scenario. We discuss how backups and restores in a hybrid environment, Azure SQL Database recovery, and geo-replication are important assets for the modern DBA.

Chapter 11, “Implement high availability and disaster recovery,” goes beyond backups and into strategies for disaster recovery, from log shipping to availability groups, as well as monitoring and troubleshooting availability groups. We compare HA and DR strategies and dive into proper architecture for maximizing SQL Server uptime.

Part IV: Security

Chapter 12, “Administer instance and database security and permissions,” begins with the basics of authentication: the configuration, management, and troubleshooting of logins and users. Then, we dive into permissions, including how to grant and revoke server and database-level permissions and role membership, with a focus on moving security from server to server.

Chapter 13, “Protect data through classification, encryption, and auditing,” takes the security responsibilities of the SQL Server DBA past the basics of authentication and permissions and discusses advanced topics including the various features and techniques for encryption, such as transparent data encryption (TDE) and Always Encrypted, as well as protecting data in motion with TLS. We cover modern strategies for row-level security and protection of sensitive data. We discuss security measures to be taken for SQL Server instances and Azure SQL databases as well as the SQL Server Audit feature.

Part V: Performance

Chapter 14: “Performance tune SQL Server,” dives deep into isolation and concurrency options, including read committed snapshot isolation (RCSI), and why your developers shouldn’t be using NOLOCK. We discuss various strategies for memory-optimized data, including delayed durability. We review graphical execution plans analysis, the important Query Store feature, and automatic plan correction. We also review important performance-related dynamic management objects (DMOs) and new SQL Server 2022 performance features in the intelligent query processing family, including degree of parallelism (DOP) feedback, cardinality estimation (CE) feedback, and enhancements to memory grant feedback.

Chapter 15: “Understand and design indexes,” tackles performance from the angle of indexes, including their creation, monitoring, and tuning. We review all the various forms of indexes at our disposal, past rowstore clustered and nonclustered indexes and into other types of indexes including columnstore and memory-optimized hashes. We review statistics and statistics options, including how they work on a variety of index and table types, such as the new XML compression feature in SQL Server 2022.

Part VI: Cloud

Chapter 16, “Design and implement hybrid and Azure database infrastructure,” discusses the infrastructure options for Azure-based SQL Server databases, including platform as a service (PaaS) options of Azure SQL Database, Azure SQL Managed Instance, and infrastructure as a service (IaaS) options of Azure VMs running SQL Server instances. We discuss the resource scalability options for Azure SQL Database, which have dramatically expanded recently. We discuss management and governance in the Azure SQL data platform using the Azure portal and PowerShell.

Chapter 17, “Provision Azure SQL Database,” covers the cloud-first database service without peer in the marketplace. This platform powers many web-based applications and services, scalable from a basic $5/month plan, to 128-vCore powerhouses, to hyperscale hardware. You will learn about the Azure SQL Database platform, compatibility, security, and availability. You will also learn how to create servers, databases, and elastic pools, and how to perform important management tasks for your databases.

Chapter 18, “Provision Azure SQL Managed Instance,” details the powerful Azure SQL Managed Instance offering, including provisioning, managing, and scaling the instance. We review the service objectives, limitations and advantages, and security features of the managed instance.

Chapter 19, “Migrate to SQL Server solutions in Azure,” covers various strategies for Azure migrations, including the Microsoft tools provided for testing and migrating SQL Server workloads. We review differences and limitations for on-premises feature migration strategies to Azure platforms, including how to migrate SSIS packages to the integration runtime. Finally, we review post-migration steps, best practices for security and resiliency during migration, and the common causes for migration failures.

Text conventions

The following conventions are used in this book:

• Boldface type is used to indicate text that you should type where directed.

• For your convenience, this book uses abbreviated menu commands. For example, “Select Tools > Track Changes > Highlight Changes” means you should select the Tools menu, point to Track Changes, and then select the Highlight Changes command.

• Elements with the Code typeface are meant to be entered on a command line or inside a dialog box. For example, “type cd Windows to change to the Windows subdirectory” means that you should be entering cd Windows with your keyboard or text input device.

• The first letters of the names of menus, dialog boxes, dialog box elements, and commands are capitalized—for example, the Save As dialog box.

• Italicized type indicates new terms.

Book features

In addition to the text conventions, this book contains sidebars to provide additional context, tips, or suggestions.