SQL Server AlwaysOn AG automatic Initialization (ix)

SQL Server VDI Backup Principle Analysis

SQL Server provides a virtual device interface (VDI) API to help stand-alone application providers, supporting the integration of SQL Server backup and recovery operations into their products. These APIs are designed to provide maximum reliability and performance, supporting all backup and recovery capabilities, including all hot spare and snapshot backup capabilities.

In earlier versions of SQL Server, VDI backups required 3 threads to complete a backup. A control thread to receive backup commands, two worker threads to handle VDI completion and meta data flow activities.

Number of VDI threads required = (number of databases on the volume of files) * 3

In the new SQL Server 2017 testing process, the number of individual database threads was found to be 12, as follows:

The steps for VDI backup are as follows:

Step 1: Load Sqlwriter (VSS Object).

Step 2:sqlwriter enumerates all the database file locations and determines the list of databases on the volume of the file.

Step 3: Perform the Sqlwriter snapshot backup command for all databases in the list.

During the snapshot backup creation phase:

A) Sqlwriter tells SQL Server to prepare a snapshot backup.

b) Then all the backed up databases are frozen and then the snapshot is created. (Note: VDI Consumers perform the behavior of generating secure volume snapshots.) Some solutions such as SQL Server snapshot backups enable write-time replication (copy on write) behavior, and other solutions split the physical image and other hardware provider-provided technology)

c) Once completed, I/O recovery, this process is called thaw.

This procedure is described in SQL Writer in SQL Server 2005

Write-on-copy (copy on Write):

When a snapshot is created, the metadata metadata of the data in the original volume is copied, and there is no data physical operation, so the snapshot creation process is very fast. When the snapshot is created and there is a write on the original volume, the snapshots track the original volume block changes, and the data to be changed is copied to the snapshot reservation space before the change, so the implementation of this principle is called write-time replication. For a snapshot read operation, if the read block is not modified after the snapshot was created, the read operation is redirected to the original volume, and if the read is a block that has been modified, reading the data saved in the snapshot before the original volume changes, simply to read the data in the snapshot, Therefore, the write-time replication mechanism ensures that the data obtained from the read snapshot is consistent with the snapshot and snapshot creation.

When you test an availability group that creates an automatic seeding, the manual log backup, Checkpoint, and insert operations are performed during the VDI backup process.

SQL Server AlwaysOn AG automatic Initialization (ix)