How SQL Server database snapshots work

How SQL Server database snapshots work, the server and database are not restarted, and the node is not switched. filestreamaccesslevel0,

How to use SQL Server Database Snapshots, the server and database are not restarted, and the node is not switched. filestream access level = 0,

How SQL Server database snapshots work

How Database Snapshots Work

A recent post titled error messages in errorlog rolled forward and rolled back

It says:

The following information is displayed at every Saturday. The server and database are not restarted, and the node is not switched. filestream access level = 0. Please help explain the cause.

Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.

FILESTREAM: Valid level = 0, configured level = 0, file system access share name = 'mssqlserver '.

148 transactions rolled forward in database 'xx _ db' (12). This is an informational message only. No user action is required.

1 transactions rolled back in database 'xx _ db' (12). This is an informational message only. No user action is required.

Recovery completed for database XX_DB (database ID 12) in 21 second (s) (analysis 22 ms, redo 15062 ms, undo 3293 ms .) this is an informational message only. no user action is required.

Why is there rolled back and rolled forward?

The respondent gave the following answer:

This error is caused by dbcc checkdb. Because dbcc checkdb needs to create a database snapshot before execution, these prompts are displayed.

These prompts are not for the current database, but for the snapshot library, so the current database does not have rolled forward and rolled back.

If there are other errors, the problem may occur.

Refer:

However, the respondent has not replied to a question:

Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.

FILESTREAM: Valid level = 0, configured level = 0, file system access share name = 'mssqlserver '.

Why does FILESTREAM appear ?? LZ says their system does not use FILESTREAM-related functions

I read another article"

It says:

Under normal circumstances, the CHECKDB/CHECKTABLE operation does not use the exclusive lock for the database, but uses the internal database snapshot (internal database snapshot ).

This internal database snapshot is essentially Sparse Filestream, which uses sparse file, COPY-ON-WRITE technology. For detailed working principles, refer to the following documents:

How database snapshots work
(V = SQL .105). aspx

I decided to translate this article to see if FILESTREAM was mentioned in the article.

Body

Database snapshots provide a read-only static view when a snapshot is created in the source database. This static view removes uncommitted transactions.

Transactions that have not been committed will be rolled back in the newly created database snapshot, because the database engine will run the repair check program after the database snapshot is created.

However, transactions in the source database are not affected.

Database snapshots are independent of the source database. The Snapshot database will exist as a database in the same database server instance.

In addition, for whatever reason, when the database becomes unavailable, the snapshot database also becomes unavailable.

Snapshot databases are not only used for report purposes. When a user error occurs in the source database, you can fix the time when the source database and database snapshot are created.

Since a database snapshot is created, data loss is limited to the loss of database data updates after the snapshot is created.

In addition, creating database snapshots is particularly useful before a large database update operation, such as changing the database architecture or table structure.

For more usage of database snapshots, you can take a look at the typical applications of database snapshots.

Understanding how database snapshots work is helpful, although you do not have to use database snapshots. The database snapshot operation level is "page level"

Before being modified for the first time on a page of the source database, the source data page will be copied to the database snapshot from the source database. This process is called "COPY-ON-WRITE ".

The database snapshot stores the source data page and retains the existing data records when the snapshot is created. Subsequent data page modification does not affect the page content in the snapshot.

The COPY-ON-WRITE operation will be repeated ON each page that is modified for the first time. In this way, the snapshot will save the original pages of all the modified data records

When a snapshot is created.

To store the copied original pages, snapshots use one or more sparse files. Initially, a sparse file is actually a blank file with no user data or disk space allocated.

When more and more data pages are updated in the source database, the size of data files increases. After a snapshot is created, sparse files only occupy a little disk space. When the source database is constantly updated,

A sparse file will grow into a large file

For more information about Sparse files, see Understanding Sparse File Sizes in Database Snapshots.

The following image describes a "copy-on-write" operation. The gray square of the snapshot below indicates that no space has been allocated in a sparse file.

When the first data page update of the first source database is received, the database engine writes the page to the sparse file, and the operating system allocates the data in the sparse file of the snapshot.

And then copy the original data page. The database engine then updates the data page of the source database.

Note: Because database snapshots are not redundant in database storage, snapshots do not prevent disk errors or damage to other types of databases. If you have to restore the original source database to

Time Point. This time point is the time when a database snapshot is created. Therefore, you need to execute a backup policy to restore data from the database backup instead of from the database snapshot.

Read database snapshots

For users, database snapshots will never change, because read operations on Database snapshots always access the raw data page, no matter where the data page comes from

If the data page of the source database has never been updated, read operations on the snapshot will read the source data page of the source database. The following figure shows a new snapshot.

The corresponding sparse file does not contain any data pages. This read operation only reads data from the source database.

When a data page is updated, the read operation on the snapshot will access the raw data page stored in the sparse file. The following figure describes the read operation on the snapshot.

Access a data page updated in the source database. Read the original page in the sparse snapshot file.

Impact of database snapshot growth on the update mode

If your source database is large and you are paying attention to disk space usage, you should replace the old snapshot with the new database snapshot at a certain time point.

The ideal database snapshot survival is based on the snapshot growth rate and the available disk space. The required disk space depends on how many data pages of the source database are updated during the snapshot period.

Therefore, if most of the updates are only repeated on a small part of the page, the snapshot growth rate will be slow, and the snapshot space will be kept relatively small.

On the contrary, when all the original pages are updated at least, the snapshots will grow to the same size as the source database. If the disk starts to fill up, snapshots and disks will compete with each other for disk space

If the disk has no space, write operations on the snapshot will fail.

Record: For more information about the actual and potential snapshot Sizes, see Understanding Sparse File Sizes in Database Snapshots.

Therefore, it is useful to know the typical database update mode during the snapshot period when the disk space is planned. For some databases, the update frequency is fixed,

For example, an inventory database may have many pages that need to be updated on a daily basis. It is useful to replace the old database snapshot on a daily or weekly basis. For other databases,

The page update ratio may vary greatly during business operations. For example, a directory database may only be updated quarterly, but occasionally updated at other times.

Creating Database snapshots before and after the seasonal alternation is a business strategy. Database snapshots before quarterly alternation allow critical update errors to be fixed

Snapshots generated after the alternate quarters can be used to write reports until the next quarter.

The following figure shows the effect of the size of the two update modes of the database snapshot.

Update Mode A only affects the snapshot Environment updated on the 30% original page.

Update mode B affects the snapshot Environment updated on the 80% original page

Metadata of database snapshots

For database snapshots, the database metadata includes the source database ID attribute, which is stored in a column in the sys. databases directory view.