Manage default settings

It is important to understand the role of the model database when creating new databases, regardless of the method of creation. The model database and its entire contents and configuration options are copied when creating most new databases, including tempdb upon service restart. For this reason, you should never store any data (even for testing) in the model database. Similarly, do not grow the model database from its default size, because this will cause all future databases to be that size or larger. Even if you specify a smaller size than the model database, the files created will be the size of the model database.

The location of the model database’s files is not, however, used as a default for new databases. Instead, the default location for database files is at the server level.

You can view these default storage locations, which we recommend you change and must be valid, in the Server Properties window in SSMS, on the Database Settings page. There you will find the default locations for data, log, and backup files, which are stored in the Windows Registry. On the Database Settings page, you’ll also see the Recovery Interval setting, which is 0 by default, meaning that SQL Server can manage the frequency of internal automatic checkpoints. This typically results in an internal checkpoint frequency of 1 minute.

Also on the Database Settings page of Server Properties are the Default Index Fill Factor and Default Backup Compression settings. These are server-level defaults applied to each database. However, you cannot configure them separately for each database. You can change fill factor with each index operation or choose a different backup compression option each time you perform a backup.

The following settings are inherited by new databases from the model database unless they are overridden at the time of creation:

• Initial data and log file size

• Data and log file autogrowth setting

• Data and log file maximum size

• Recovery model

• Target recovery time (overrides the system default recovery interval)

• All database-scoped configurations, including the database-level settings for legacy cardinality estimation, MaxDOP, parameter sniffing, and Query Optimizer fixes

• All automatic settings, including auto close, auto shrink, and auto create/update statistics (discussed later in this chapter)

  • View the full list at https://learn.microsoft.com/sql/relational-databases/databases/model-database.

Own the databases you create

The login that runs the CREATE DATABASE statement will become the owner of any database you create, even if the account you are using is not a member of the sysadmin group. Any principal that can create a database becomes the owner of that database, even if, for example, they have only membership to the dbcreator built-in server role.

Ideally, databases are not owned by named individual accounts. You might decide to change each database to a service account specific to that database’s dependent applications. You must do this after the database is created.

• For more information on best practices with respect to database ownership and how to change the database owner, see Chapter 12, “Administer instance and database security and permissions.”

Create additional database files

Every SQL Server database needs at least one data file and one log file. You can use additional data files to maximize storage capacity, management, and performance. However, there is no performance advantage to be gained with more than one transaction log file for a database. SQL Server does not write to them randomly, but sequentially.

• We discuss physical database architecture in detail in Chapter 3.

The only scenario in which a second transaction log file could be needed is if the first fills up its volume. If no space can be created on the volume to allow for additional transaction log file data to be written, the database cannot accept new transactions and will refuse new application requests. In this scenario, one possible troubleshooting method is to temporarily add a second transaction log file on another volume to create the space to allow the database transactions to resume accepting transactions. The end resolution involves clearing the primary transaction log file, performing a one-time-only shrink operation to return it to its original size, and removing the second transaction log file. There is no other performance or stability benefit to having a second transaction log file.

Use SQL Server Management Studio to create a new database

You can create and configure database files, specifically their initial sizes, in SSMS. To begin, in Object Explorer, right-click Databases and select New Database in the shortcut menu to open the New Database dialog box.

After you have configured the new database’s settings, but before you select OK, you can script the T-SQL for the CREATE DATABASE statement.

Here are a few suggestions when creating a new database:

• Pre-grow your database and log file sizes to an expected size. This avoids autogrowth events as you initially populate your database. You can greatly speed up this process by using the Perform Volume Maintenance Task policy for the SQL Server service account so that instant file initialization is possible.

  • Chapter 3 covers instant file initialization.

• Consider the simple recovery model for your database until it enters production use. Then, the full or bulk-logged recovery models might be more appropriate.

  • For more information on database backups and the appropriate recovery model, see Chapter 10, “Develop, deploy, and manage data recovery.”

• Review the logical and physical file names of your database and the locations. The default locations for the data and log files are server-level settings, but you can override them here. You also can move the files later (covered later in this chapter).

• As soon as the database is created, follow up with your backup strategy to ensure that it is covered as appropriate with its role. This may involve adding it to an existing maintenance plan, SQL Server Agent job, or third-party backup application.

Deploy a database via SQL Server Data Tools

You can also deploy developed databases to a SQL Server instance using a database project in SQL Server Data Tools (SSDT). SSDT provides a professional and mature environment for teams across your enterprise to develop databases, check them into source control, generate change scripts for incremental deployments, and reduce object scripting errors.

SSDT can generate incremental change scripts using the Data Compare feature or deploy databases directly. It also has the option to drop or re-create databases for each deployment, although this is turned off by default.

You might find it easiest to create the new database by using SSMS and then deploy incremental changes to it with SSDT.

Restore a database backup

Restoring a backup is an easily understandable way to copy data from one instance to another. You can also carry out this method in a way that minimizes outages.

Let’s compare two simplified migration processes. The following is a sample migration checklist using only a full backup/restore:

1. Begin the application outage.

2. Perform a full backup of the database on the old instance.

3. Copy the database backup file to the new server.

4. Restore the full backup on the new instance.

5. Resolve any SQL-authenticated login issues or any other changes necessary before directing database queries to the new instance.

   • This is covered in more detail in Chapter 12, in the section, “Perform common security administration tasks.”

6. Change the connection strings in applications and/or DNS and/or aliases.

7. End the application outage.

   • For more information on types of database backups and database restores, see Chapter 10.

In the preceding scenario, the application outage must last the entire span of the backup, copy, and restore, which for large databases could be quite lengthy—even with native SQL Server backup compression reducing the file size. Ultimately, however, doing it with the downtime is faster and less complex.

Instead, consider the following strategy:

1. Perform a full backup of the database on the old instance.

2. Copy the database backup file to the new server.

3. Restore the full backup using the WITH NORECOVERY option on the new instance.

4. Begin the application outage.

5. Take a differential backup and then a log backup of the database on the old instance.

6. Copy the differential backup file and the log backup file to the new server.

7. Restore the differential backup file WITH NORECOVERY on the new instance.

8. Restore the transaction log backup WITH RECOVERY on the new instance.

9. Resolve any SQL-authenticated login issues or any other changes necessary before directing database queries to the new instance.

10. Change the connection strings in applications, DNS, and/or aliases.

11. End the application outage.

In this scenario, the application outage spans only the duration of the differential and transaction logs’ backup/copy/restore operations, which for large databases should be a tiny fraction of the overall size of the database.

This scenario does require more preparation and scripting in advance, as well as coordination with the usual backup system responsible for transaction log backups. By taking a manual transaction log backup, you can create a split transaction log backup chain for another system. You’ll want to account for this in your planning.

Attach detached database files

Detaching, copying, and attaching database files also accomplishes the goal of placing the database on a new instance. It is relatively straightforward to disassociate (detach) the files from the old SQL Server, copy the files to the new instance, and then attach the files to the new SQL Server. This is largely limited by the data-transfer speed of copying the files. You might also consider moving the SAN drives to the new server to decrease the time spent waiting for files to copy.

Attaching copied database files can be faster than restoring a full database backup; however, it cannot minimize the outage by taking advantage of transaction log backups (see earlier).

Copying the full set of database files (remember, these might contain many more files than just the .mdf and .ldf files, including secondary data files and FILESTREAM containers) is not faster than restoring a transaction log backup during the application outage, and is not a true recovery method. Because database backup files can also be compressed natively by SQL Server, the data-transfer duration between old and new servers will be reduced by using the backup/restore strategy.

Move data with BACPAC files

A BACPAC file is an XML format file that contains the database schema and row data, allowing for the migration of databases, ideally at the start of a development/migration phase (although not for large databases). SSMS can both generate and import BACPAC files, and the Azure portal can import them when moving an on-premises SQL Server to Azure SQL Database.

SQL Server edition

Editions generally progress upward in terms of cost and feature set, beginning with Express, Web, Standard, and finally Enterprise edition. (Developer edition is the same as Enterprise edition, except you can use it in a production environment.) Moving a database instance up from Express, Web, or Standard edition expands the features available for use in the database.

The concern for DBAs is when database instances need to move down from Enterprise, Standard, or Web edition. Many features that were historically exclusive to Enterprise edition were included in Standard edition for the first time in SQL Server 2016 with Service Pack 1, expanding what we could do with Standard edition as developers and administrators.

You will encounter errors related to higher-edition features when restoring or attaching to an instance that does not support that edition. There are fewer of these all the time, and many are in the realm of capacity or tuning more than in core features. This is important because you cannot turn off the use of higher-edition features on the lower-edition instance; you must disable the use of these features before restoring or attaching the database to a lower-edition instance.

You can avoid this problem by using a dynamic management view that lists all edition-specific features in use. Keep in mind that some features are supported in all editions but are limited. For example, memory-optimized databases are supported even in the Express edition, but with only a small amount of allocated memory.

To view all edition-specific features in use in each database, run the following query:

Click here to view code image

SELECT feature_name
FROM sys.dm_db_persisted_sku_features;

This DMV may return no records if no edition-sensitive features are in use in the current database context. However, if, for example, you create a partitioning function for horizontal table partitioning, the DMV will immediately return a row for the feature name “Partitioning.” While table partitioning is supported in all editions of SQL Server, certain performance benefits of table partitioning are supported only in the Enterprise edition—for example, partitioned table parallelism and distributed partitioned views. Thus, the performance of your partitioned tables may vary from edition to edition.

• For more information on the data returned by sys.dm_db_persisted_sku_features, visit https://learn.microsoft.com/sql/relational-databases/system-dynamic-management-views/sys-dm-db-persisted-sku-features-transact-sql. For more information on features by edition, visit https://learn.microsoft.com/sql/sql-server/editions-and-components-of-sql-server-2019.

SQL logins

SQL-authenticated logins and their associated database users are connected via security identifier (SID), not by name. When moving a database from one instance to another, the SIDs in the SQL logins on the old instance might be different from the SIDs in the SQL logins on the new instance, even if their names match. After migration to the new instance, SQL-authenticated logins will be unable to access databases where their database users have become “orphaned,” and you must repair this. This does not affect Windows Authenticated logins for domain accounts.

This condition must be repaired before applications and end users can access the database in its new location. Refer to the “Orphaned SIDs” section in Chapter 12.

The database owner should be included in the security objects to be accounted for on the new server. Ensure that the owner of the database, listed either in the Database Properties window or the owner_sid column in the sys.databases catalog view, is still a valid principal on the new instance.

For databases with partial containment, contained logins for each type will be restored or attached along with the database. This should not be a concern.

Transparent data encryption

Transparent data encryption (TDE) settings will follow the database as it is moved from one instance to another, but the certificate and its security method will not. For example, the server certificate created to encrypt the database key, and the private key and its password, are not backed up along with the database. This is, after all, the entire purpose of TDE—to prevent a database from being attached or restored to a server that lacks the proper certificate.

These objects must be moved to the new instance along with the database before any attempt to restore or attach the database. They must also be backed up and securely stored with the rest of your enterprise security credentials, certificates, and sensitive data.

• For more information on TDE, see Chapter 13, “Protect data through classification, encryption, and auditing.”

Database configuration settings

You should review database-specific settings at the time of migration. You can review them with a quick glance of the sys.databases catalog view or from the database Properties window in SSMS.

The following is not a complete list of database settings; we cover these and many more later in the chapter. You should pay special attention to these settings when restoring, deploying, or attaching a database to a new instance:

• Read only. If the database was put in READ_ONLY mode before the migration to prevent data movement, be sure to change this setting back to READ_WRITE.

• Recovery model. Different servers might have different backup and recovery methods. In a typical environment, the full recovery model is appropriate for production environments when the data loss tolerance of the database is smaller than the frequency of full backups, or when point-in-time recovery is appropriate. If you are copying a database from a production environment to a development environment, you will likely want to change the recovery model from Full to Simple. If you are copying a database from a testing environment to a production environment for the first time, you will likely want to change the recovery model from Simple to Full.

  Note

  After changing a database recovery model from Simple to Full, immediately take a full backup of the database to start the transaction log recovery chain. Then, you can start taking transaction log backups at regular intervals.

  • For more information about database backups and the appropriate recovery model, see Chapter 10.

• Page verify option. For all databases, this setting should be CHECKSUM. The legacy TORN_PAGE_DETECTION option is a sign that this database has been moved over the years up from a pre-SQL Server 2005 version but this setting has never changed. Since SQL Server 2005, CHECKSUM has been the superior and default setting, but it requires an administrator to manually change. Always take a full database backup before changing this setting.

  Unfortunately, just changing this setting is not sufficient. Checksums are not immediately created when you change the page verify option to CHECKSUM, so the data pages are not protected right away. To apply the checksums manually, rebuild all indexes in the database.

  • For more information about the important page verify option in each database, see the section “Set the database’s page verify option” in Chapter 8, “Maintain and monitor SQL Server.”

• Trustworthy. It is not recommended to ever enable this setting unless doing so is necessary because of an inflexible architecture requirement. Using this setting to mark a database as trustworthy could allow malicious activity on one database to affect other databases, even if specific permissions have not been granted. It is crucial not only to ensure this setting is disabled, but also to understand cross-database permission chains in a multitenant or web-hosted shared SQL Server environment.

  If this setting was enabled on a previous system and was required because of external assemblies, cross-database queries, and/or Service Broker, you will need to turn it on again on the new server (assuming it’s still needed).

  If your application security model depends on the Trustworthy setting, you must remember to enable it after restoring or attaching the database. When restoring a database, Trustworthy is set to off no matter the value when it is backed up.

  Note

  The Trustworthy setting tells SQL Server that the contents of the database should be trusted. It allows certain operations like cross-database access and common language runtime (CLR) assemblies to access data outside the server. It is not recommended unless there are no other options available, as it is not a secure method of communication between servers.

  • For more on object ownership, see Chapter 12.

Collation

Collations exist at three levels in a SQL Server instance:

• Database

• Instance

• tempdb

By default, the collation of the tempdb database matches the collation of the instance and should differ only in otherwise unavoidable circumstances. Ideally, the collations in all user databases match the collation at the instance level and for tempdb, but there are scenarios in which an individual database might need to operate in a different collation.

Often, databases differ from the server-level collation to enforce case sensitivity. But you can also enforce language usage differences (such as kana or accent sensitivity) and sort order differences at the database level.

The default collation for the server is decided at installation and is preselected for you based on your operating system’s regionalization settings. You can (and should) override this during installation. Some applications require a case-sensitive collation.

Although the server-level collation is very difficult to change (see Chapter 4), databases can change collation. You should change a database’s collation only before code is developed for the database, or only after extensive testing of existing code.

Unmatched collations in databases could cause performance issues when querying across those databases, so you should try to avoid collation differences between databases that will be shared by common applications. For example, if you write a query that includes a table in a database that’s set to the collation SQL_Latin1_General_CP1_CI_AS (which is case insensitive and accent sensitive) and a join to a table in a database that’s set to SQL_Latin1_General_CP1_CS_AS (which is case sensitive and accent sensitive), you will receive the following error:

Click here to view code image

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_
Latin1_General_CP1_CS_AS" in the equal to operation.

Short of changing either database to match the other, you will need to modify your code to use the COLLATE statement when referencing columns in each query. The following sample succeeds in joining two sample database tables together, despite the mismatched database collations:

Click here to view code image

SELECT * FROM CS_AS.sales.sales s1
INNER JOIN CI_AS.sales.sales s2
ON s1.[salestext] COLLATE SQL_Latin1_General_CP1_CI_AS = s2.[salestext];

• For more about UTF-8 in SQL Server 2019, see Chapter 7, “Understand table features.”

Recovery model

The full recovery model is appropriate for production environments in which the database’s data-loss tolerance is smaller than the frequency of full backups or when point-in-time recovery is necessary. As mentioned earlier in this chapter, if you are copying a database from a production environment to a development environment, you will likely want to change the recovery model from Full to Simple. If you are copying a database from a testing environment to a production environment for the first time, you will likely want to change the recovery model from Simple to Full, then immediately take a full backup to begin the recovery chain.

• For more information on database backups and the appropriate recovery model, see Chapter 10.

Compatibility level

SQL Server provides database compatibility levels for backward compatibility to database-level features, including improvements to the Query Optimizer, additional fields in dynamic management objects, syntax improvements, and other database-level objects.

Compatibility level is a database-level setting. Databases upgraded from an older version to a newer version will retain a prior compatibility level. You must manually promote a database’s compatibility level when restoring up to a new version of SQL Server.

Containment type

Partially contained databases represent a fundamental change in the relationship between server and database. They are an architectural decision that you make when applications are intended to be portable between multiple SQL Server instances or when security should be entirely limited to the database context, not in the traditional server login/database user sense.

• For more information about the security implications of contained databases, see Chapter 12.

Azure SQL databases are themselves a type of contained database. They can move from host to host in the Azure platform-as-a-service (PaaS) environment, transparent to administrators and users. You can design databases that can be moved between SQL Server instances in a similar fashion, should the application architecture call for such capability.

Changing the containment type from None to Partial converts the database to a partially contained database and should not be taken lightly. We do not advise changing a database that has already been developed without the partial containment setting, because there are differences with how temporary objects behave and how collations are enforced. Some database features, including change data capture (CDC), change tracking, replication, and some parts of Service Broker, are not supported in partially contained databases. You should carefully review, while logged in as a member of the sysadmin server role or the db_owner database role, the system dynamic management view sys.dm_db_uncontained_entities for an inventory of objects that are not contained.

Auto close

You should enable this setting only in very specific and resource-exhausted environments. When this feature is enabled, it can cause performance degradation on busy databases because of the increased overhead of opening and closing the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection. When active, it unravels the very purpose of application connection pooling—for example, rendering certain application architectures useless and increasing the number of login events. You should never enable this setting as part of performance tuning or a troubleshooting exercise on a busy environment.

Auto create statistics

When you enable this setting, the Query Optimizer automatically creates statistics needed for runtime plans, even for read-only databases. (Statistics are stored in tempdb for read-only databases.) Some applications, such as SharePoint, handle the creation of statistics programmatically; due to the dynamic nature of its tables and queries, SharePoint handles statistics creation and updates by itself. Unless a sophisticated, complex application like SharePoint insists otherwise, you should enable this setting. You can identify auto-created statistics in the database; they use a naming convention similar to _WA_Sys_<column_number>_<hexadecimal>.

• For much more about statistics objects, see Chapter 15, “Understand and design indexes.”

Auto create incremental statistics

Introduced in SQL Server 2014, this setting allows for the creation of statistics that take advantage of table partitioning, reducing the overhead of statistics creation. This setting has no impact on nonpartitioned tables. You should enable this setting because it can reduce the cost of creating and updating statistics.

Once enabled, this setting will have an effect only on newly created statistics. To affect existing statistics objects on tables with partitions, you should update the statistics objects to include the INCREMENTAL = ON parameter, as shown here:

Click here to view code image

UPDATE STATISTICS [Purchasing].[SupplierTransactions]
[CX_Purchasing_SupplierTransactions] WITH RESAMPLE, INCREMENTAL = ON;

When INCREMENTAL = ON, the statistics are re-created as per partition statistics. When OFF, the statistics tree is dropped and SQL Server recomputes the statistics. This is why you need to change the setting. You should also, when applicable, update any manual scripts you have implemented to update statistics to use the ON PARTITIONS parameter, as demonstrated here:

Click here to view code image

UPDATE STATISTICS [Purchasing].[SupplierTransactions]
[CX_Purchasing_SupplierTransactions] WITH RESAMPLE ON PARTITIONS (1);

To determine whether statistics were created incrementally, you can check the value of the is_incremental column in the sys.stats catalog view.

Auto shrink

You should never enable this setting. It will automatically return any free space of more than 25 percent of the data file or transaction log to the file system. You should shrink a database only as a manual, one-time operation to reduce file size after unplanned or unusual file growth. This setting will result in unnecessary fragmentation and overhead. After completion, it may result in frequent rapid log autogrowth events as the database resizes itself again.

Auto update statistics

When this setting is enabled, statistics will be updated periodically. The Query Optimizer considers statistics to be out of date when a ratio of data modifications to rows in the table has been reached. The Query Optimizer checks for and updates the out-of-date statistic before running a query plan and therefore has some overhead, though the performance benefit of updated statistics usually outweighs this cost. This is especially true when the updated statistics result in a better optimization plan. Because the Query Optimizer updates the statistics first and then runs the plan, the update is described as synchronous.

Auto update statistics asynchronously

This setting changes the behavior of the auto update statistics setting in one important way: Query runs will continue even if the Query Optimizer has identified an out-of-date statistics object. The statistics will be updated afterward.

Allow snapshot isolation

This setting allows for the use of snapshot isolation (SI) mode at the query level. When you enable this setting, the row versioning process begins in tempdb, though this setting does little more than allow for this mechanism to be used in this database. To begin to use SI mode in the database, you must change code—for example, to include SET TRANSACTION ISOLATION LEVEL SNAPSHOT.

• For much more on snapshot isolation, see Chapter 14.

Read committed snapshot isolation (RCSI)

Enabling the read committed snapshot isolation (RCSI) setting changes the default isolation mode of the database from READ COMMITTED to READ COMMITTED SNAPSHOT (RCSI). While RCSI can be beneficial and is the enterprise, scalable solution for concurrency issues, you should not enable this setting during regular business hours; instead, do it during a maintenance window. Ideally, however, this setting is on and accounted for during development.

When RCSI is enabled, the snapshot uses optimistic concurrency control, withholding any locks that would prevent other transactions from updating rows. If a snapshot transaction attempts to commit an update to a row that was changed after the transaction began, the transaction is rolled back and an error is raised.

Enabling RCSI will have an immediate impact to the use of the local database, and potentially the tempdb database as well, in the form of rising IO_COMPLETION and WAIT_XTP_RECOVERY wait types, so you need to perform proper load testing. This setting, however, potentially results in a major performance improvement and this is the core of enterprise-level concurrency.

Multiple factors could determine whether snapshot versions are contained in the user database or in tempdb. When RCSI is enabled, long-running transactions can also prevent the cleanup of the persistent version store (PVS) when the accelerated database recovery (ADR) feature is enabled. This isn’t typically a problem, but you should understand and monitor for a growing PVS when ADR is enabled.

• SQL Server 2022 introduced performance improvements for ADR and its PVS cleanup process. For more information, see https://learn.microsoft.com/sql/relational-databases/accelerated-database-recovery-troubleshoot.

• For much more about RCSI, see Chapter 14.

Page verify option

For all databases, this setting should be CHECKSUM. The presence of the legacy TORN_PAGE_DETECTION or NONE option is a sign that this database has been restored up from a pre-SQL Server 2005 version, but this setting has never changed. Since SQL Server 2005, CHECKSUM has been the superior and default setting. Always take a full database backup before changing this setting.

Unfortunately, just changing this setting is not sufficient to apply checksums to each data page. Checksums are not immediately created when you change the page verify option to CHECKSUM, so the data pages are not protected right away. To apply the checksums manually, rebuild all indexes in the database.

For more information about the important page verify option setting in each database, see the section “Set the database’s page verify option” in Chapter 8.

Trustworthy

It is not recommended to ever enable this setting unless it is made necessary because of an inflexible architecture requirement. Using this setting to mark a database as trustworthy could allow malicious activity on one database to affect other databases, even if specific permissions have not been granted. Before enabling this setting, you should understand the implications of cross-database ownership chains in a multitenant or web-hosted shared SQL Server environment.

• For more on object ownership, see Chapter 12.

Database Read-Only

You can set an older database, or a database intended for nonchanging archival, to READ_ONLY mode to prevent changes. Any member of the server sysadmin role or the database db_owner role can revert this to READ_WRITE, so you should not consider this setting a security measure.

Query Store

Introduced in SQL Server 2016, the Query Store is a built-in data gathering and reporting mechanism for measuring and tracking cached runtime plans. It is highly recommended, as it provides historical data for queries, not just for cached plans.

Though extremely useful, Query Store is not active by default in older versions of SQL Server. With the introduction of SQL Server 2022, Query Store is enabled by default in new databases. Shortly after the release of SQL Server 2022, the Query Store will be enabled by default in new databases in Azure SQL Database and Azure SQL Managed Instance. You should turn it on as soon as possible if you intend to use it to aid performance tuning and troubleshooting cached runtime plans.

• For more information on the Query Store, as well as retrieving cached query plans, see Chapter 14.

Indirect checkpoints

If your database was created in SQL Server 2016 or later, it is already configured to use indirect checkpoints, as it has been the default setting for all new databases since then—even if you created the database in a previous compatibility level.

By default, in databases created in SQL Server 2016 or higher, this setting is 60 seconds. In databases created in SQL Server 2012 or 2014, this option was available, but set to 0, which indicates that legacy automatic checkpoints are in use.

Databases created on prior versions of SQL Server, however, will continue to use the classic automatic checkpoint, which has been in place since the 1990s (SQL Server 7.0) with only minor tweaks.

Before SQL Server 2016 and since SQL Server 7.0, all databases use automatic checkpoints by default. The rate at which dirty pages are committed to memory has increased with versions, as disk I/O and memory capacities of servers have increased. The aim of automatic checkpoints was to ensure that all dirty pages were managed within a goal defined in the recovery interval server configuration option. By default, this was 0, which meant automatic checkpoints were in effect. The effective timing of a checkpoint tended to be around 60 seconds, but was highly variable, and was generally unrelated to the number of pages dirtied by transactions between checkpoints.

An indirect checkpoint follows a new strategy of taking care of dirty pages that is far more scalable and can improve performance, especially on modern systems with a large amount of memory. Indirect checkpoints manage dirty pages in memory differently; instead of scanning memory, they proactively gather lists of dirty pages. Indirect checkpoints then manage the list of dirty pages and continuously commit them from memory to the drive, on a pace to not exceed an upper bound of recovery time. This upper bound is defined in the TARGET_RECOVERY_TIME database configuration option.

So, even though the recovery time goal hasn’t really changed, the method by which it is achieved has. Indirect checkpoints are significantly faster than automatic checkpoints, especially as servers are configured with more and more memory. You might notice an improvement specifically in the performance of backups.

You can configure a database created on an older version of SQL Server to use indirect checkpoints instead of automatic checkpoints with a single command: TARGET_RECOVERY_TIME. The value will be 0 for databases still using automatic checkpoint. The master database will also have a TARGET_RECOVERY_TIME of 0 by default. Consider setting TARGET_RECOVERY_TIME to 60 seconds to match the default for all databases created in SQL Server 2016 or higher, as shown here:

Click here to view code image

ALTER DATABASE [database_name] SET TARGET_RECOVERY_TIME = 60 SECONDS;

You can check this setting for each database in the TARGET_RECOVERY_TIME_IN_SECONDS column of the sys.databases catalog view.

• For more on the differences between the different checkpoint types and on the interaction between the database TARGET_RECOVERY_TIME setting and the server’s recovery interval setting, see https://learn.microsoft.com/sql/relational-databases/logs/database-checkpoints-sql-server.

Accelerated database recovery (ADR)

New in SQL Server 2019, accelerated database recovery (ADR) does not appear in the SSMS Database Properties page as of this writing, nor is it enabled by default. There are trade-offs to be aware of that we’ll discuss later, but this is a powerful, much-desired feature of SQL Server that is available in both Enterprise and Standard editions.

You can enable ADR with the following T-SQL statement:

Click here to view code image

ALTER DATABASE [database_name] SET ACCELERATED_DATABASE_RECOVERY = ON;

Enabling ADR requires exclusive access to the database and could be blocked by other connections to the database, which might require the closure of other connections. You can see the status of this setting in the catalog view sys.databases, in the new column is_accelerated_database_recovery_on.

ADR represents a significant overhaul of the SQL Server recovery process. It is a reworking of the machinery that the Database Engine uses to recover each database on:

• SQL Server instance startup, especially after an unexpected shutdown

• Rollback of a long-running transaction

• Availability group failover

ADR results in much faster recovery times in these scenarios, including near-instant recovery for many operations. ADR accomplishes this by way of a new progressive log management pattern inside the transaction log that eliminates the need for the transaction log to ever be scanned from the beginning of the oldest active transaction. Instead, it can be processed at recovery from only the last successful checkpoint. With ADR, the transaction log is aggressively truncated, even in the presence of active long-running transactions, which prevents it from growing out of control.

The trade-offs include an increase in storage requirements for each user database with ADR enabled. This could require a sudden increase of 10 percent or more space in the user database file, so administrators should be aware of this impact.

You should monitor and consider the growth of the persistent version store (PVS) for ADR. The PVS is a local version store to retain previous state information for transactions, especially long-running transactions. The presence of snapshot isolation queries and/or enabling the RCSI database setting can increase retention of versions in the PVS.

• Microsoft has provided a variety of troubleshooting scenarios and monitoring solutions, including using the sys.dm_tran_persistent_version_store_stats DMV. For more information, see https://learn.microsoft.com/sql/relational-databases/accelerated-database-recovery-troubleshoot.

SQL Server 2022 brings several improvements to ADR and the PVR cleanup process, including lower memory usage for tracking pages that require cleanup and the ability to clean locked pages.

Locate SQL Server files

As discussed in the checklist earlier in this chapter, you can review the location of all database files by querying the sys.master_files catalog view. If you did not specify the intended location for the data files while you were on the Data Directories page of the Database Engine Configuration step of SQL Server Setup, you will generally find your system database files on the OS volume at %programfiles%Microsoft SQL ServerinstanceMSSQLData.

Ideally, there should be no data or log files on the OS volume, even system database files. You can, however, move these after SQL Server Setup is complete.

When you’re planning to move your database data or log files on Windows, prepare their new file path location by granting FULL CONTROL permissions to the per-SID name for the SQL Server instance. (This is not necessarily the SQL Server service account.) For the default instance, this will be NT SERVICEMSSQLSERVER; for named instances, it will be NT SERVICE MSSQL$instancename.

Moving database data or log files on Linux only requires that the mssql account has access to the new file path.

Move user database files

You can move user databases without a SQL Server instance restart and without disrupting other databases by taking the database offline, updating the locations and/or metadata, moving them, and then bringing the database online again.

Use the following steps to move user database files:

1. Take a manual full backup of the soon-to-be-affected databases.

2. During a maintenance outage for the database and any dependent applications, take the user database offline. Then run a T-SQL script to alter the location of each database file. Here’s an example of the T-SQL statements required:

   Click here to view code image

   ALTER DATABASE [database_name] SET OFFLINE WITH ROLLBACK IMMEDIATE
   ALTER DATABASE [database_name] MODIFY FILE ( NAME = logical_data_file_name,
   FILENAME = 'locationphysical_data_file_name.mdf' );
   ALTER DATABASE [database_name] MODIFY FILE ( NAME = logical_log_file_name,
   FILENAME = 'locationphysical_log_file_name.ldf' );
   ALTER DATABASE [database_name] SET ONLINE;

3. While the database is offline, physically copy the database files to their new location. (You will delete the old copies when you’ve confirmed the new configuration.) When the file operation is complete, bring the database back online.

4. Verify that the data files have been moved by querying sys.master_files. The physical_name column should reflect the new location correctly.

5. Delete the files in the original location to reclaim the space. To be safe and for rollback reasons, back up the database and the master database before the deletion.

6. Perform a manual backup of the master database.

Move system database files, except for master

You cannot move system database files while the SQL Server instance is online; thus, you must stop the SQL Server service.

1. Take a manual full backup of the soon-to-be-affected databases.

2. For model, msdb, and tempdb, run a T-SQL script (like the script for moving user databases provided previously). SQL Server will not use the new locations of the system databases until the next time the service is restarted. You cannot set the system databases to offline.

3. During a maintenance outage for the SQL Server instance, stop the SQL Server instance. Then copy the database files to their new location. (You will delete the old copies when you’ve confirmed the new configuration.) The only exception here is that the tempdb data and log files do not need to be moved—they will be re-created automatically by SQL Server upon service start.

4. When the file operation is complete, start the SQL Server service again.

5. Verify that the data files have been moved by querying sys.master_files. Look for the physical_name column to reflect the new location correctly.

6. Delete the files in the original location to reclaim the space.

7. Perform a manual backup of the master database.

If you encounter problems starting SQL Server after moving system databases to another volume—for example, if the SQL Server service account starts and then stops—do the following:

1. Verify that the SQL Server service account and SQL Server Agent service account have permissions to the new file location.

   • Review the list of File System Permissions Granted to SQL Server service accounts at https://learn.microsoft.com/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions#Reviewing_ACLs.

2. Check the Windows Application Event Log and System Event Log for errors.

3. If you cannot resolve the issue, start SQL Server with Trace Flag 3608, which does not start the SQL Server fully, only the master database.

4. If necessary, move all other database files, including the other system databases, back to their original location by using T-SQL commands issued through SSMS.

   Note

   For more information on moving system database files, visit https://learn.microsoft.com/sql/relational-databases/databases/move-system-databases.

Move master database files

Moving the master database files is not difficult, but it is a more complicated process than moving the other system databases. Instead of issuing an ALTER DATABASE … ALTER FILE statement, you must edit the parameters passed to the SQL Server service in SQL Server Configuration Manager.

1. Open SQL Server Configuration Manager and select SQL Server Services on the left.

2. Right-click the SQL Server service and choose Properties.

3. The Startup Parameters page contains three entries with three files in their current paths. Edit the two parameters beginning with -d and -l (lowercase L). The -e parameter is the location of the SQL Server Error Log; you might want to move that, as well.

4. After editing the master database data file (-d) and the master database log file (-l) locations, select OK. Keep in mind that the SQL Server service will not look for the files in their new location until the service is restarted. (If you have other startup parameters in this box, do not modify them now.)

5. Stop the SQL Server service. Then copy the master database data and log files to their new location. (You will delete the old copies when you’ve confirmed the new configuration.)

6. When the file operation is complete, restart the SQL Server service.

7. Verify that the data files have been moved by querying sys.master_files, a dynamic management view that returns all files for all databases. Look for the physical_name column to correctly reflect the new location.

8. After you have verified that SQL Server is recognizing the database files in their new locations, delete the files in the original location to reclaim the space.