Grouping data pages with extents

SQL Server data pages are 8 KB in size. Eight of these contiguous pages is called an extent, which is 64 KB in size.

There are two types of extents in a SQL Server data file:

• Uniform Extent. All eight 8-KB pages per extent are assigned to a single object.

• Mixed Extent. (Rare) Each page in the extent is assigned to its own separate object (one 8-KB page per object).

Mixed extents were originally created to reduce storage requirements for database objects, back when mechanical hard drives were much smaller and more expensive. As storage becomes faster and cheaper, and SQL Server more complex, this causes contention (a hotspot) at the beginning of a data file, especially if a lot of small objects are being created and deleted.

Mixed extents are turned off by default for TempDB and user databases, while they are turned on by default for system databases. If you want, you can configure mixed extents on a user database by using the following command:

Click here to view code image

ALTER DATABASE <dbname> SET MIXED_PAGE_ALLOCATION ON;

Contents and types of data pages

All data pages begin with a header of 96 bytes, followed by a body containing the data itself. At the end of the page is a slot array, which fills up in reverse order, beginning with the first row, as illustrated in Figure 3-2. It instructs the Database Engine where a particular row begins on that particular page. Note that the slot array does not need to be in any particular order after the first row.

At certain points in the data file, there are system-specific data pages (also 8 KB in size). These help SQL Server recognize and manage the different data within each file.

Several types of pages can be found in a data file:

• Data. Regular data from a heap, or clustered index at the leaf level (the data itself; what you would see when querying a table).

• Index. Nonclustered index data at the leaf and non-leaf level as well as clustered indexes at the non-leaf level.

• Large object data types. These include text, ntext, image, nvarchar(max), varchar(max), varbinary(max), Common Language Runtime (CLR) data types, xml, and sql_variant where it exceeds 8 KB. Overflow data can also be stored here (data that has been moved “off-page” by the Database Engine), with a pointer from the original page.

• Global Allocation Map (GAM). Keeps track of all free extents in a data file. There is one GAM page for every GAM interval (64,000 extents, or roughly 4 GB).

• Shared Global Allocation Map (SGAM). Keeps track of all extents that can be mixed extents. It has the same interval as the GAM.

• Page Free Space (PFS). Keeps track of free space inside heap and large object pages. There is one PFS page for every PFS interval (8,088 pages, or roughly 64 MB).

• Index Allocation Map (IAM). Keeps track of which extents in a GAM interval belong to a particular allocation unit (an allocation unit is a bucket of pages that belong to a partition, which in turn belongs to a table). It has the same interval as the GAM. There is at least one IAM for every allocation unit. If more than one IAM belongs to an allocation unit, it forms an IAM chain.

• Bulk Changed Map (BCM). Keeps track of extents that were modified by bulk-logged operations since the last full backup. It is used by transaction log backups in the bulk-logged recovery model to see which extents to back up.

• Differential Changed Map (DCM). Sometimes called a differential bitmap. Keeps track of extents that were modified since the last full or differential backup that is used for differential backups.

• Boot Page. Only one per database and contains information about the database.

• File Header Page. One per data file and contains information about the file.

• To find out more about the internals of a data page, visit Microsoft Docs at https://docs.microsoft.com/sql/relational-databases/pages-and-extents-architecture-guide, and read Paul Randal’s post, “Anatomy of a page,” at https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page.

Verifying data pages by using a checksum

By default, when a data page is read into the buffer pool, a checksum is automatically calculated over the entire 8-KB page and compared to the checksum stored in the page header on the drive. This is how SQL Server keeps track of page-level corruption. If the checksum stored on the drive does not match the checksum in memory, corruption has occurred. A record of this suspect page is stored in the msdb database and you will see an error message when that page is accessed.

The same checksum is performed when writing to a drive. If the checksum on the drive does not match the checksum in the data page in the buffer pool, page-level corruption has occurred.

Although the PAGE_VERIFY property on new databases is set to CHECKSUM by default, it might be necessary to check databases that have been upgraded from previous versions of SQL Server, especially those created prior to SQL Server 2005 (compatibility level 90).

You can look at the checksum verification status on all databases by using the following query:

Click here to view code image

SELECT name, page_verify_option_desc

FROM sys.databases;

You can reduce the likelihood of data page corruption by using Error-Correcting Code (ECC) memory. Data page corruption on the drive is detected by using DBCC CHECKDB and other operations.

• For information on how to proactively detect corruption, read the section “Database corruption” in Chapter 8, “Maintaining and monitoring SQL Server.”

Flushing data to the storage subsystem with checkpoints

Recall from Chapter 2 that any changes to the data are written to the database file asynchronously, for performance reasons. This process is controlled by a database checkpoint. As its name implies, this is a database-level setting that can be changed under certain conditions by modifying the Recovery Interval or by running the CHECKPOINT command in the database context.

The checkpoint process takes all of the modified pages in the buffer pool, as well as transaction log information that is in memory, and writes that to the storage subsystem. This reduces the time it takes to recover a database because only the changes made after the latest checkpoint need to be rolled forward in the Redo phase (see the “Restarting with recovery” section later in the chapter).

Inside the transaction log file

A transaction log file is split into logical segments, called virtual log files (VLFs). These segments are dynamically allocated when the transaction log file is created and whenever the file grows. The size of each VLF is not fixed and is based on an internal algorithm, which depends on the version of SQL Server, the current file size, and file growth settings. Each VLF has a header containing a Minimum Log Sequence Number and whether it is active.

Every transaction is uniquely identified by a Log Sequence Number (LSN). Each LSN is ordered, so a later LSN will be greater than an earlier LSN. The LSN is also used by database backups and restores.

• For more information see Chapter 8, and Chapter 10, “Developing, deploying, and managing data recovery.”

Figure 3-3 illustrates how the transaction log is circular. When a VLF is first allocated by creation or file growth, it is marked inactive in the VLF header. Transactions can be recorded only in active portions of the log file, so the SQL Server engine looks for inactive VLFs sequentially, and as it needs them, marks them as active to allow transactions to be recorded.

Marking a VLF inactive is called log truncation, but this operation does not affect the size of the physical transaction log file. It just means that an active VLF has been marked inactive and can be reused.

There are several reasons why log truncation can be delayed. After the transactions that make use of an active VLF are committed or rolled back, what happens next depends on a number of factors:

• The recovery model:

  • Simple. An automatic checkpoint is queued after the recovery interval timeout is reached or if the log becomes 70 percent full.

  • Full/bulk-logged. A transaction log backup must take place after a transaction is committed. A checkpoint is queued if the log backup is successful.

• Other processes that can delay log truncation:

  • Active backup or restore. The transaction log cannot be truncated if it is being used by a backup or restore operation.

  • Active transaction. If another transaction is using an active VLF, it cannot be truncated.

  • Database mirroring. Mirrored changes must be synchronized before the log can be truncated. This occurs in high-performance mode or if the mirror is behind the principal database.

  • Replication. Transactions that have not yet been delivered to the distribution database can delay log truncation.

  • Database snapshot creation. This is usually brief, but creating snapshots (manually or through database consistency checks, for instance) can delay truncation.

  • Log scan. Usually brief, but this, too, can delay a log truncation.

  • Checkpoint operation. See the section “Flushing data to the storage subsystem with checkpoints” later in the chapter.

• To learn more, read “Factors that can delay log truncation” at https://docs.microsoft.com/sql/relational-databases/logs/the-transaction-log-sql-server#FactorsThatDelayTruncation.

After the checkpoint is issued and the dependencies on the transaction log (as just listed) are removed, the log is truncated by marking those VLFs as inactive.

The log is accessed sequentially in this manner until it gets to the end of the file. At this point, the log wraps around to the beginning, and the Database Engine looks for an inactive VLF from the start of the file to mark active. If there are no inactive VLFs available, the log file must create new VLFs by growing in size according to the auto growth settings.

If the log file cannot grow, it will stop all operations on the database until VLFs can be reclaimed or created.

The Minimum Recovery LSN

When a checkpoint occurs, a log record is written to the transaction log stating that a checkpoint has commenced. After this, the Minimum Recovery LSN (MinLSN) must be recorded. This LSN is the minimum of either the LSN at the start of the checkpoint, the LSN of the oldest active transaction, or the LSN of the oldest replication transaction that hasn’t been delivered to the transactional replication distribution database.

In other words, the MinLSN “…is the log sequence number of the oldest log record that is required for a successful database-wide rollback.” (https://docs.microsoft.com/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide).

• To learn more about the distribution database, read the “Replication” section in Chapter 11, “Implementing high availability and disaster recovery.”

This way, crash recovery knows to start recovery only at the MinLSN and can skip over any older LSNs in the transaction log if they exist.

The checkpoint also records the list of active transactions that have made changes to the database. If the database is in the simple recovery model, the unused portion of the transaction log before the MinLSN is marked for reuse. All dirty data pages and information about the transaction log are written to the storage subsystem, the end of the checkpoint is recorded in the log, and (importantly) the LSN from the start of the checkpoint is written to the boot page of the database.

Types of database checkpoints

Checkpoints can be activated in a number of different scenarios. The most common is the automatic checkpoint, which is governed by the recovery interval setting (see the Inside OUT sidebar that follows to see how to modify this setting) and typically takes place approximately once every minute for active databases (those databases in which a change has occurred at all).

Other checkpoint events include the following:

• Database backups (including transaction log backups)

• Database shutdowns

• Adding or removing files on a database

• SQL Server instance shutdown

• Minimally logged operations (for example, in a database in the simple or bulk-logged recovery model)

• Explicit use of the CHECKPOINT command

There are four types of checkpoints that can occur:

• Automatic. Issued internally by the Database Engine to meet the value of the recovery interval setting at the instance level. On SQL Server 2016 and higher, the default is one minute.

• Indirect. Issued to meet a user-specified target recovery time at the database level, if the TARGET_RECOVERY_TIME has been set.

• Manual. Issued when the CHECKPOINT command is run.

• Internal. Issued internally by various features, such as backup and snapshot creation, to ensure consistency between the log and the drive image.

• For more information about checkpoints, visit Microsoft Docs at https://docs.microsoft.com/sql/relational-databases/logs/database-checkpoints-sql-server.

Restarting with recovery

Whenever SQL Server starts, recovery (also referred to as crash recovery or restart recovery) takes place on every single database (on at least one thread per database, to ensure that it completes as quickly as possible) because SQL Server does not know for certain whether each database was shut down cleanly.

The transaction log is read from the latest checkpoint in the active portion of the log, or the LSN it gets from the boot page of the database (see the “The Minimum Recovery LSN” section earlier in the chapter), and scans all active VLFs looking for work to do.

All committed transactions are rolled forward (Redo portion) and then all uncommitted transactions are rolled back (Undo portion). The total number of rolled forward and rolled back transactions are recorded for each database with a respective entry in the ERRORLOG file.

SQL Server Enterprise edition brings the database online immediately after the Redo portion is complete. Other editions must wait for the Undo portion to complete before the database is brought online.

• See Chapter 8 for more information about database corruption and recovery.

The reason why we cover this in such depth in this introductory chapter is to help you to understand why drive performance is paramount when creating and allocating database files.

When a transaction log is first created or file growth occurs, the portion of the drive must be zeroed-out (the file system literally writes zeroes in every byte in that file segment).

Instant file initialization does not apply to transaction log files for this reason, so keep this in mind when growing or shrinking transaction log files. All activity in a database will stop until the file operation is complete.

As you can imagine, this can be time consuming for larger files, so you need to take care when setting file growth options, especially with transaction log files. You should measure the performance of the underlying storage layer and choose a fixed growth size that balances performance with reduced VLF count. Consider setting file growth for transaction log files in multiples of 8 GBs. At a sequential write speed of 200 MBps, this would take under a minute to grow the transaction log file.

MinLSN and the active log

As mentioned earlier, each VLF contains a header that includes an LSN and an indicator as to whether the VLF is active. The portion of the transaction log, from the VLF containing the MinLSN to the VLF containing the latest log record, is considered the active portion of the transaction log.

All records in the active log are required in order to perform a full recovery if something goes wrong. The active log must therefore include all log records for uncommitted transactions, too, which is why long-running transactions can be problematic. Replicated transactions that have not yet been delivered to the distribution database can also affect the MinLSN.

Any type of transaction that does not allow the MinLSN to increase during the normal course of events affects overall health and performance of the database environment, because the transaction log file might grow uncontrollably.

When VLFs cannot be made inactive until a long-running transaction is committed or rolled back, or if a VLF is in use by other processes (including database mirroring, availability groups, and transactional replication, for example), the log file is forced to grow. Any log backups that include these long-running transaction records will also be large. The recovery phase can also take longer because there is a much larger volume of active transactions to process.

• You can read more about transaction log file architecture at https://docs.microsoft.com/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide.

A faster recovery with Accelerated Database Recovery

In SQL Server 2019 and Azure SQL Database, you can enable Accelerated Database Recovery (ADR) at the database level. At a high level, ADR trades extra space in the data file for reduced space in the transaction log, and improved performance during manual transaction rollbacks and crash recovery, especially in environments where long-running transactions are common.

It introduces four components:

• Persisted version store (PVS). This works in a similar way to Read Committed Snapshot Isolation (RCSI) by recording a previously committed version of a row until a transaction is committed. The main difference is that the PVS is stored in the user database and not in TempDB, which allows database-specific changes to be recorded in isolation from other instance-level operations.

• Logical revert. If a long-running transaction is aborted, the versioned rows created by the PVS can be safely ignored by concurrent transactions. Additionally, upon rollback, the previous version of the row is immediately made available by releasing all locks.

• Secondary log stream (sLog). The sLog is a low-volume in-memory log stream that records non-versioned operations (including lock acquisitions). It is persisted to the transaction log file during a checkpoint operation, and aggressively truncated when transactions are committed.

• Cleaner. This asynchronous process periodically cleans page versions that are no longer required.

Where ADR shines is in the Redo and Undo phases of crash recovery. In the first part of the Redo phase, the sLog is processed first. Because it contains only uncommitted transactions since the last checkpoint, it is processed extremely quickly. The second part of the Redo phase begins from the last checkpoint in the transaction log, as opposed to the oldest committed transaction.

In the Undo phase, ADR is able to complete almost instantly by firstly undoing non-versioned operations recorded by the sLog, and then performing a logical revert on row-level versions in the PVS and releasing all locks.

Customers using this feature may notice faster rollbacks, a significant reduction in transaction log usage for long-running transactions, faster SQL Server startup times, and a small increase in the size of the data file for each database where this is enabled (on account of the storage required for the PVS). However, as with all features of SQL Server, we recommend that you do testing before enabling this on all production databases.

Table and index compression

SQL Server provides two main ways to reduce the amount of storage required for data in tables and indexes. We discuss compressing rowstore data in this section. You can read more about columnstore indexes in Chapter 15, “Understanding and designing indexes.”

Backup compression

Whereas page-level and row-level compression operate at the table level, backup compression applies to the backup file for the entire database.

Compressed backups are usually smaller than uncompressed backups, which means fewer drive I/O operations are involved, which in turn means a reduction in the time it takes to perform a backup or restore. For larger databases, this can have a dramatic effect on the time it takes to recover from a disaster.

Backup compression ratio is affected by the type of data involved, whether the database is encrypted, and whether the data is already compressed. In other words, a database making use of page and/or row compression might not gain any benefit from backup compression.

The CPU can be limited for backup compression in Resource Governor (you can read more about Resource Governor in the section “Configuration settings” later in the chapter, and in more detail in Chapter 8).

In most cases, we recommend turning on backup compression, keeping in mind that you might need to monitor CPU utilization.

Storage options for TempDB

Every time SQL Server restarts, TempDB is cleared out. If the files don’t exist, they are recreated. If the files are configured at a size that is different from their last active size, they will automatically be resized. Like the database file structure described earlier, there is usually one TempDB transaction log file, and one or more data files in a single filegroup.

Performance is critical for TempDB, even more than with other databases, to the point that the current recommendation is to use your fastest storage for TempDB, before using it for user database transaction log files.

Where possible, use solid-state storage for TempDB. If you have a Failover Cluster Instance, have TempDB on local storage on each node.

Starting with SQL Server 2019, TempDB can store certain metadata in memory-optimized tables for increased performance.

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Recommended number of files

As with every database, only one transaction log file should exist for TempDB.

For physical and virtual servers, the default number of TempDB data files recommended by SQL Server Setup should match the number of logical processor cores, up to a maximum of eight, keeping in mind that your logical core count includes symmetrical multithreading (for example, Hyper-Threading). Adding more TempDB data files than the number of logical processor cores rarely results in positive performance. Adding too many TempDB data files could in fact severely harm SQL Server performance.

• You can read more about processors in Chapter 2.

Increasing the number of files to eight (and other factors) reduces TempDB contention when allocating temporary objects. If the instance has more than eight logical processors allocated, you can test to see whether adding more files helps performance, and is very much dependent on the workload.

You can allocate the TempDB data files together on the same volume (see the “Types of storage” section in Chapter 2), provided that the underlying storage layer is able to meet the low-latency demands of TempDB on your instance. If you plan to share the storage with other database files, keep latency and IOPS in mind.

Managing system usage with Resource Governor

Using Resource Governor, you can specify limits on resource consumption at the application-session level. You can configure these in real time, which allows for flexibility in managing workloads without affecting other workloads on the system.

• You can find out more about Resource Governor in Chapter 8.

A resource pool represents the physical resources of an instance, which means that you can think of a resource pool itself as a mini SQL Server instance. To make the best use of Resource Governor, it is helpful to logically group similar workloads together into a workload group so that you can manage them under a specific resource pool.

This is done via classification, which looks at the incoming application session’s characteristics. That incoming session will be categorized into a workload group based on your criteria. This facilitates fine-grained resource usage that reduces the impact of certain workloads on other, more critical workloads.

For example, a reporting application might have a negative impact on database performance due to resource contention at certain times of the day, so by classifying it into a specific workload group, you can limit the amount of memory or disk I/O that reporting application can use, reducing its effect on, say, a month-end process that needs to run at the same time.

Configuring the operating system page file

Operating systems use a portion of the storage subsystem for a page file (also known as a swap file, or swap partition on Linux) for virtual memory for all applications, including SQL Server, when available memory is not sufficient for the current working set. It does this by offloading (paging out) segments of RAM to the drive. Because storage is slower than memory (see Chapter 2), data that has been paged out is also slower when working from the system page file.

The page file also serves the role of capturing a system memory dump for crash forensic analysis, a factor that dictates its size on modern operating systems with large amounts of memory. This is why the general recommendation for the system page file is that it should be at least the same size as the server’s amount of physical memory.

Another general recommendation is that the page file should be managed by the operating system. For Windows Server, this should be set to System Managed, and, since Windows Server 2012, that guideline has functioned well. However, in servers with large amounts of memory, this can result in a very large page file, so be aware of that if the page file is located on your operating system (OS) volume. This is also why the page file is often moved to its own volume, away from the OS volume.

On a dedicated SQL Server instance, you can set the page file to a fixed size, relative to the amount of Max Server Memory assigned to SQL Server. In principle, the database instance will use up as much RAM as you allow it, to that Max Server Memory limit, so Windows will preferably not need to page SQL Server out of RAM. On Linux, the swap partition can be left at the default size, or reduced to 80% of the physical RAM, whichever is lower.

• For more about Lock pages in memory, see the section by the same name later in this chapter.

Taking advantage of logical processors by using parallelism

SQL Server is designed to run on multiple logical processors (for more information, refer to the section “Central Processing Unit” in Chapter 2).

• You can find out more about parallel query plans in Chapter 14.

In SQL Server, parallelism makes it possible for portions of a query (or the entire query) to run on more than one logical processor at the same time. This has certain performance advantages for larger queries, because the workload can be split more evenly across resources. There is an implicit overhead with running queries in parallel, however, because a controller thread must manage the results from each logical processor and then combine them when each thread is completed.

The SQL Server query optimizer uses a cost-based optimizer when coming up with query plans. This means that it makes certain assumptions about the performance of the storage, CPU and memory, and how they relate to different query plan operators. Each operation has a cost associated with it.

SQL Server will consider creating parallel plan operations, governed by two parallelism settings: Cost threshold for parallelism and Max degree of parallelism. These two settings can make a world of difference to the performance of a SQL Server instance if it is using default settings.

Query plan costs are recorded in a unitless measure. In other words, the cost bears no relation to resources such as drive latency, IOPS, number of seconds, memory usage, or CPU power, which can make query tuning difficult without keeping this in mind. What matters is the magnitude of this measure.

SQL Server memory settings

Since SQL Server 2012, the artificial memory limits imposed by the license for lower editions (Standard, Web, and Express) apply to the buffer pool only (see https://docs.microsoft.com/sql/sql-server/editions-and-components-of-sql-server-version-15).

This is not the same thing as the Max Server Memory, though. According to Microsoft Docs, the Max Server Memory setting controls all of SQL Server’s memory allocation, which includes, but is not limited to the buffer pool, compile memory, caches, memory grants, and CLR (Common Language Runtime, or .NET) memory (https://docs.microsoft.com/sql/database-engine/configure-windows/server-memory-server-configuration-options).

Additionally, limits to columnstore and memory-optimized object memory are over and above the buffer pool limit on non-Enterprise editions, which gives you a greater opportunity to make use of available physical memory.

This makes memory management for non-Enterprise editions more complicated, but certainly more flexible, especially taking columnstore and memory-optimized objects into account.

SELECT AVG(runnable_tasks_count)

FROM sys.dm_os_schedulers

WHERE status = 'VISIBLE ONLINE';

Allocating CPU cores with an affinity mask

It is possible to assign certain logical processors to SQL Server. This might be necessary on systems that are used for instance stacking (more than one SQL Server instance installed on the same OS) or when workloads are shared between SQL Server and other software.

SQL Server on Linux does not support instance stacking, and virtual consumers (virtual machines or containers) are probably a better way of allocating these resources, but there might be legitimate or legacy reasons for setting core affinity.

Suppose that you have a dual-socket NUMA server, with both CPUs populated by 16-core processors. Excluding simultaneous multithreading (SMT), this is a total of 32 cores, and SQL Server Standard edition is limited to 24 cores, or four sockets, whichever is lower.

When it starts, SQL Server will allocate all 16 cores from the first NUMA node, and eight from the second NUMA node. It will write an entry to the ERRORLOG stating this case, and that’s where it ends. Unless you know about the core limit, you will be stuck with unbalanced CPU core and memory access, resulting in unpredictable performance.

One way to solve this without using a VM or container, is to limit 12 cores from each CPU to SQL Server, using an affinity mask (see Figure 3-5). This way, the cores are allocated evenly and combined with a reasonable MAXDOP setting of 8, foreign memory access is not a concern.

By setting an affinity mask, you are instructing SQL Server to use only specific cores. The remaining unused cores are marked as offline to SQL Server. When SQL Server starts, it will assign a scheduler to each online core.

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 0 TO 3;

File system configuration

This section primarily deals with the default file system on Windows Server. Any references to other file systems, including Linux file systems are noted separately.

The NT File System (NTFS) was originally created for the first version of Windows NT, bringing with it more granular security than the older File Allocation Table (FAT)-based file system as well as a journaling file system (think of it as a transaction log for your file system). You can configure a number of settings that deal with NTFS in some way to improve your SQL Server implementation and performance.

Instant file initialization

As stated previously in this chapter, transaction log files need to be zeroed out at the file system in order for recovery to work properly. However, data files are different, and with their 8-KB page size and allocation rules, the underlying file might contain sections of unused space.

With instant file initialization (IFI), a feature enabled by the Active Directory policy Perform volume maintenance tasks, data files can be instantly resized without zeroing-out the underlying file. This adds a major performance boost.

The trade-off is a tiny, perhaps insignificant security risk: data that was previously used in drive allocation currently dedicated to a database’s data file now might not have been fully erased before use. Because you can examine the underlying bytes in data pages using built-in tools in SQL Server, individual pages of data that have not yet been overwritten inside the new allocation could be visible to a malicious administrator.

Note

It is important to control access to SQL Server’s data files and backups. When a database is in use by SQL Server, only the SQL Server service account and the local administrator have access. However, if the database is detached or backed up, there is an opportunity to view that deleted data on the detached file or backup file that was created with instant file initialization turned on.

Because this is a possible security risk, the Perform volume maintenance tasks policy is not granted to the SQL Server service by default, and a summary of this warning is displayed during SQL Server setup.

Without IFI, you might find that the SQL Server wait type PREEMPTIVE_OS_WRITEFILEGATHER is prevalent during times of data-file growth. This wait type occurs when a file is being zero-initialized; thus, it can be a sign that your SQL Server is wasting time that could skipped with the benefit of IFI. Keep in mind that PREEMPTIVE_OS_WRITEFILEGATHER will also be generated by transaction log files, which do not benefit from IFI.

Note that SQL Server Setup takes a slightly different approach to granting this privilege than SQL Server administrators might take. SQL Server assigns Access Control Lists (ACLs) to automatically created security groups, not to the service accounts that you select on the Server Configuration Setup page. Instead of granting the privilege to the named SQL Server service account directly, SQL Server grants the privilege to the per-service security identifier (SID) for the SQL Server database service; for example, the NT SERVICEMSSQLSERVER principal. This means that the SQL Server service will maintain the ability to use IFI even if its service account changes.

You can determine whether the SQL Server Database Engine service has been granted access to IFI by using the sys.dm_server_services dynamic management view via the following query:

Click here to view code image

SELECT servicename, instant_file_initialization_enabled

FROM sys.dm_server_services

WHERE filename LIKE '%sqlservr.exe%';

If IFI was not configured during SQL Server setup, and you want to do so later, go to the Windows Start menu, and then, in the Search box, type Local Security Policy. Next, in the pane on the left expand Local Policies (see Figure 3-6), and then click User Rights Assignment. Find the Perform Volume Maintenance Tasks policy, and then add the SQL Server service account to the list of objects with that privilege.

Kubernetes support for SQL Server

Microsoft introduced support for Kubernetes after the release of SQL Server 2017 (see Figure 3-7). Early releases of Kubernetes lacked support for persisted storage, which is an obvious problem for database containers. The implementation uses a Kubernetes service to act as a persisted front-end name and IP address for the container. In this scenario, if the pod fails, the service stays running, and a new copy of the pod is launched and then pointed at the persisted storage. This is nearly analogous to the architecture of a SQL Server Failover Cluster Instance (FCI).

• Refer to Chapter 2 for a more in-depth discussion on FCIs.

The SQL Server 2017 Kubernetes deployment provides for just a single container per SQL Server pod. SQL Server 2019 may include other services in the pod deployment. Services provide load balancing and persistent IP addressing, while Persistent Volume Claims ensure that storage is persisted across container failures or node movement. By defining a PersistentVolumeClaim, you are aligning a specific disk volume to your pod deployment to persist data files.

Recent releases of both Kubernetes and Windows Server allow for Kubernetes to support both Windows nodes and Windows containers, but SQL Server Big Data Clusters (BDC) currently only support containers on Linux. Kubernetes is also much more broadly used on Linux, so community support will be much more prevalent on that platform.

• You can learn more about Kubernetes from the following books: The Kubernetes Book (2019), by Nigel Poulton; and Kubernetes: Up and Running (2019), by Kelsey Hightower, Brendan Burns, et al.

BDC licensing offers some flexibility compared to traditional SQL Server core licensing. With Software Assurance on Enterprise edition, you can use those core licenses for your master instance node, and you will get eight BDC core node licenses for every master instance core.

• You can download the SQL Server 2019 licensing guide in PDF format directly from https://aka.ms/sql2019licenseguide.

• You can find out more about RHEL in Chapter 5.

Deploying SQL Server in containers

As we mentioned previously, SQL Server runs on Windows, Linux, and in Docker containers. When originally released with SQL Server 2017, container support was touted for use in development. After all, there was limited support in the container world for persisted storage at the time, and SQL Server lacked support for an enterprise orchestration framework like Kubernetes. While database containers still make for a fantastic development environment, the future support in SQL Server for availability groups, Active Directory authentication (not yet available at the time of writing), and Big Data Clusters, means that container deployment is quickly becoming an option for production workloads as well.

• You can read more about availability groups in Chapter 11.

Getting started with SQL Server in a Docker container

One of the biggest attractions of running SQL Server in a container is that your choice of operating system does not matter. While the container images of SQL Server use Linux as their base, your host machine can run Windows, Linux, or macOS. First, you will need to install Docker Desktop on your workstation.

• Download Docker Desktop from https://www.docker.com/products/docker-desktop.

After you have Docker installed, you can deploy a SQL Server container with the following steps. The first thing you should do is pull a copy of the container image from the Microsoft Container Registry (MCR) to your local environment. You should run these commands from either a bash shell on Linux and macOS, or an elevated PowerShell prompt on Windows.

Click here to view code image

sudo docker pull mcr.microsoft.com/mssql/server:2019-latest

• You can find out more about the bash shell in Chapter 5, and PowerShell in Chapter 9, “Automating SQL Server administration.”

After you have pulled down the image, you then need to deploy the container. The backslash in this command is a way to split a single bash command across multiple lines:

Click here to view code image

sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' 
   -p 1433:1433 --name sql2019 
   -v /users/randolph/mssql:/mssql 
   -d mcr.microsoft.com/mssql/server:2019-latest

Caution

There is currently no secure way to obfuscate the SA password in a Docker deployment. Microsoft recommends that you change your SA password after you have deployed your container.

You may be curious what these parameters (also called switches in Linux) mean:

• The docker pull command downloads the container image to your local container repository.

• The docker run command is where the deployment takes place.

• The -e switch allows for an environmental variable to be passed into the deployment (we cover environment variables in Chapter 5). In this case, you are accepting the End-User License Agreement (EULA) for SQL Server, and providing a strong password for the SA account.

• The -p (or --publish, note the double-dash before the parameter) switch publishes the container’s public and private port for your container. To run multiple SQL Server containers simultaneously, specify a TCP port other than 1433 for the subsequent containers that are deployed.

• The --name switch (note the double-dash before the parameter) specifies the name of your container (which is not required but will result in a system-generated name if not specified).

• The -v switch is probably the most important in terms of database use. It allows a persistent volume to be mounted from your local machine to your container. In this the case the local directory /users/randolph/mssql will appear in the container as /mssql. Use this directory to store database backups, or data files to be mounted to the container.

• The -d switch refers to the container image you are deploying. In this case you are a deploying a SQL Server 2019 container from the MCR.

Note

Docker on macOS does not support persistent volumes. Microsoft recommends that you use separate data container volumes to persist data files that are stored in /var/opt/mssql/data. You can read the background to this issue at https://github.com/microsoft/mssql-docker/issues/12, and you can learn more about data container volumes at https://docs.docker.com/storage/volumes/.

Inside OUT

Can you use containers in development?

Yes, development is one of the main uses for containers. Given the ease of deploying SQL Server in a container, you can envision a process where a software vendor builds an automated process to perform regression tests against every cumulative update (CU) of a release of SQL Server, or across multiple releases. This is just one excellent use case for databases in containers.

After the container has been deployed, you can execute the docker ps command (lists all of the running containers) to confirm that your container is running (in some environments you may need to run sudo docker ps). Also, you can connect to your container using SQL Server tools like SQL Server Management Studio (SSMS), Azure Data Studio, or sqlcmd, by connecting to localhost. This is possible because when you deployed the container, you configured it to run on TCP port 1433, which is the default SQL Server port.

Note

If you use a custom TCP port (or deploy multiple SQL Server containers) you can connect to localhost followed by the port number, separated with a comma. For example, localhost,1455.

You can also connect into your container with an interactive shell and execute sqlcmd. The first command will launch the bash shell within your container:

Click here to view code image

sudo docker exec -it sql1 "/bin/bash"

After launching the interactive bash shell within your container, you will then call sqlcmd using the full path, since it is not in the path by default:

Click here to view code image

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourNewStrong!Passw0rd>'

Once your SQL Server container is deployed, you can execute T-SQL just like it was any other SQL Server.

Getting started with SQL Server on Kubernetes

Although running SQL Server in a single Docker container is relatively easy, running SQL Server on a Kubernetes infrastructure is more challenging.

Kubernetes using the Azure Kubernetes Service

The recommended way to fully simulate a production environment is to deploy using Azure Kubernetes Service (AKS). AKS is a managed service that allows you to quickly deploy a Kubernetes cluster of one node, up to 100 nodes.

• Configuring AKS is part of the main Azure documentation, available at https://docs.microsoft.com/azure/aks/kubernetes-walkthrough.

AKS offers the benefit of hosting a highly available control plane for the cluster in Azure, as well as deploying the latest release of Kubernetes without installing software, worrying about dependencies, or finding hardware to build on. The other benefit to AKS is that the service itself is free. You are only charged for the underlying VM compute costs. Storage in AKS is provided by either using Azure Managed Disks or the Azure File service that acts as a file share.

Deploying SQL Server on Kubernetes

Once you have a Kubernetes cluster or simulated cluster, you can get started with deploying SQL Server. First, you will need to create a secret in Kubernetes to store your SA password.

Click here to view code image

kubectl create secret generic mssql --from-literal=SA_PASSWORD="MyC0m9l&xP@ssw0rd"

If kubectl (the Kubernetes command line tool) is not installed on the machine where you are managing your cluster, you will need to install it in order to manage your deployment.

• Instructions for installing kubectl are available at https://kubernetes.io/docs/tasks/tools/install-kubectl.

Next, you will create a Persistent Volume Claim (PVC). As mentioned earlier, containers were originally designed to be ephemeral and not persist data across restarts or failures. A PVC will ask the cluster to provide a mapping to a Persistent Volume (PV). A PV can be statically or dynamically provisioned.

• A statically-provisioned PV is defined by the cluster administrator and a PersistentVolumeClaim will be matched to that PV based on size and access mode.

• A dynamically-provisioned PV will be provisioned from a cluster defined StorageClass. A PV asks the Storage Class to provision the volume from the underlying storage subsystem of the cluster. This can be a cloud provider’s persistent volume such as Azure Disks, or even an on-premises SAN.

If you are using Azure Kubernetes Services, save the following code to a file called pvc.yaml.

Click here to view code image

kind: StorageClass

apiVersion: storage.k8s.io/v1beta1

metadata:

     name: azure-disk

provisioner: kubernetes.io/azure-disk

parameters:

  storageaccounttype: Standard_LRS

  kind: Managed

---

kind: PersistentVolumeClaim

apiVersion: v1

metadata:

  name: mssql-data

  annotations:

    volume.beta.kubernetes.io/storage-class: azure-disk

spec:

  accessModes:

  - ReadWriteOnce

  resources:

    requests:

      storage: 8Gi

This code defines the Azure storage class, and then defines an 8-GB volume. This code example uses Azure storage, which is how you would implement on AKS. You will use slightly different code if you are using storage local to your cluster, like you do when using Minikube or Docker.

Click here to view code image

kind: PersistentVolumeClaim

apiVersion: v1

metadata:

  name: mssql-data-claim

spec:

  accessModes:

  - ReadWriteOnce

  resources:

   requests:

    storage: 8Gi

Just like in the Azure example, save this file to pvc.yaml and then deploy using the kubectl apply command shown next:

Click here to view code image

kubectl apply -f C:scriptspvc.yaml

The next step is to deploy the SQL Server service and the pod itself. In the following code, you are specifying the load balancer service, as well as the container running SQL Server. Kubernetes can use extensive metadata to help describe and categorize your environment, as you will note from the metadata and label fields in the below YAML. Much like in the Docker script earlier, you are defining a port, passing in the SA password you defined in the secret, and accepting the EULA. Finally, in the last section, you are defining the load balancer, which will give you a persistent IP address for your SQL instance.

Click here to view code image

apiVersion: apps/v1beta1

kind: Deployment

metadata:

  name: mssql-deployment

spec:

  replicas: 1

  template:

    metadata:

      labels:

        app: mssql

    spec:

      terminationGracePeriodSeconds: 10

      containers:

      - name: mssql

        image: mcr.microsoft.com/mssql/server:2019-latest

        ports:

        - containerPort: 1433

        env:

        - name: MSSQL_PID

          value: "Developer"

        - name: ACCEPT_EULA

          value: "Y"

        - name: MSSQL_SA_PASSWORD

          valueFrom:

            secretKeyRef:

              name: mssql

              key: SA_PASSWORD

        volumeMounts:

        - name: mssqldb

          mountPath: /var/opt/mssql

      volumes:

      - name: mssqldb

        persistentVolumeClaim:

          claimName: mssql-data

---

apiVersion: v1

kind: Service

metadata:

  name: mssql-deployment

spec:

  selector:

    app: mssql

  ports:

    - protocol: TCP

      port: 1433

      targetPort: 1433

  type: LoadBalancer

You can save this YAML as sql.yaml, and using the same kubectl apply -f command, you can deploy it from where you manage Kubernetes.

Congratulations! You now have SQL Server running on Kubernetes. You can run the kubectl get pods and get services commands as shown in Figure 3-10 to see your deployment.

If you review the output of the kubectl get services command, you will see the external IP address of your SQL Server service. You can now use any SQL Server client tool to connect to that address with the SA password you created in the secret.

Reviewing cluster health

Kubernetes provides a built-in dashboard for monitoring the overall health of your cluster and its resource utilization. If you are using AKS, you can view this by running the az aks browse command with the resource group and cluster names. Depending on the configuration and version of your cluster, you may need to create a cluster role binding in order to view the dashboard as seen in Figure 3-11.

• If you are not using AKS, you can find instructions on installing and configuring a dashboard for your cluster at https://kubernetes.io/docs/tasks/access-application-cluster/web-ui-dashboard.

Kubernetes deployments move all of your infrastructure into scripts. For some automation-focused administrators, this may be the holy grail that they have been waiting for, but it is important to manage these scripts just as you would do with your application code. They should be version-controlled in a source control system like Azure DevOps or GitHub. If you are hosting your own repositories, you should ensure that they are backed up and highly available.