Shulou(Shulou.com)06/01 Report--

Principle Analysis of SQL Server VDI backup

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

In previous SQL Server versions, VDI backups required three threads to complete the backup. One control thread receives backup commands, and two worker threads handle VDI completion and metadata stream activities.

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

During the new SQL Server 2017 test, it was found that there were 12 threads in a single database, as follows:

The steps for VDI backup are as follows:

Step 1: load SQLWriter (VSS Object).

Step 2:SQLWriter enumerates all database file locations to determine the database list of files on the volume.

Step 3: execute 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 backed up databases are frozen and a snapshot is created. (note: VDI consumers perform the behavior of generating secure volume snapshots. Some solutions, such as SQL Server snapshot backup, enable replication on write (Copy On Write) behavior, while others split away from physical mirroring and other technologies provided by hardware providers)

C) once completed, Iamp O resumes, and this process is called thawing.

See SQL Writer in SQL Server 2005 for details of this process.

Copy on write (Copy On Write):

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

When testing and creating an availability group with automatic seed settings, manual log backup, Checkpoint, and INSERT operations can be completed during VDI backup.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.