本文共 9150 字,大约阅读时间需要 30 分钟。
Database snapshot is a great feature that offers virtual read only consistent database copy. When we create the database snapshot in the live operational database, it takes a database point in time static view and Rollback all uncommitted transactions in the snapshot database so we will not be having any inconsistent data that is yet to be committed. Database snapshot always exists on the Source database server.
数据库快照是一项出色的功能,可提供虚拟只读一致的数据库副本。 当我们在实时运行数据库中创建数据库快照时,它需要一个数据库时间点静态视图并回滚快照数据库中所有未提交的事务,因此我们不会有任何尚未提交的不一致数据。 数据库快照始终存在于源数据库服务器上。
Database snapshot works on the pages (the fundamental unit of data storage in SQL server is the page). The disk space allocated to a data file in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL server reads or writes whole data pages.
数据库快照适用于页面(SQL Server中数据存储的基本单位是页面)。 分配给数据库中数据文件的磁盘空间在逻辑上分为从0到n连续编号的页。 磁盘I / O操作在页面级别执行。 也就是说,SQL Server读取或写入整个数据页。
Basically, it creates a sparse file and will be pointing to the original databases, written only in case of any insert, update and delete statement is in the source database.
基本上,它将创建一个稀疏文件并指向原始数据库,仅在源数据库中存在任何insert,update和delete语句的情况下才编写该文件。
Suppose there is no operation going into the server when we created a database snapshot so snapshot will look like
假设我们创建数据库快照时没有任何操作进入服务器,所以快照看起来像
So we have the blank copy of the database here. Now, if any Read Operation occurs ,it will be pointing to the original database only as no page modification is done since snapshot creation.
因此,这里有数据库的空白副本。 现在,如果发生任何读取操作,它将仅指向原始数据库,因为自创建快照以来未进行任何页面修改。
Now suppose due to a DML operation (insert\update\delete) Page 2, 5, and 8 has been modified and user operation requires page 1,2, 4 and page 8 so below picture shows how the snapshot will be and how the read operation will be performed.
现在假设由于DML操作(插入\更新\删除)的第2、5和8页已被修改,并且用户操作需要第1,2、4和8页,因此下图显示了快照的方式以及如何读取快照操作将被执行。
We can see here whichever page gets changed due to any operation, its original page (before modification) is copied into the sparse file (snapshot), so if user operation request to read the snapshot database it works in below ways
我们可以在这里看到任何由于任何操作而发生更改的页面,其原始页面(修改前)将被复制到稀疏文件(快照)中,因此,如果用户操作请求读取快照数据库,它将以以下方式工作
This is the concept behind the snapshot, thus, its size is small compared to the original database but it all depends on the operation if there are too many operations in the database which gets page modified so the snapshot size will be kept increasing.
这是快照背后的概念,因此,与原始数据库相比,快照的大小较小,但是如果数据库中有太多需要修改页面的操作,则这完全取决于操作,因此快照大小将不断增加。
So whenever we create the snapshot following operation will be done
因此,无论何时创建快照,都将执行以下操作
Now let’s say there are many database pages being updated so now the snapshot will look like
现在,假设有许多数据库页面正在更新,所以现在快照看起来像
Here we can see the as there are many operations being performed all pages shown above are modified, thus the snapshot size will be increased. We should always keep an eye on the growth of the snapshot database.
在这里我们可以看到,由于正在执行许多操作,因此上面显示的所有页面均被修改,因此快照大小将增加。 我们应该时刻关注快照数据库的增长。
The best use of Database snapshot can be during production changes\ upgrades\releases. Normally, database backup is taken and if the database size is big it is really time and space consuming since we will have to keep the backup copy apart from our regular backups. So instead of backup we can create the Database snapshot which is really quick and can perform the release, and once we have verified the changes are good we can easily drop the snapshot and also in some cases we require to see the values before changes we can view that as well.
最好在生产更改\升级\发行期间使用数据库快照。 通常,进行数据库备份,如果数据库很大,则确实会浪费时间和空间,因为我们必须将备份副本与常规备份分开。 因此,代替备份,我们可以创建一个非常快的数据库快照,并且可以执行发布,并且一旦我们确认更改是好的,我们就可以轻松删除快照,并且在某些情况下,我们需要在更改之前先查看值对此也是如此。
Database snapshot works well with the log shipping and database mirroring too. Normally log shipping databases are maintained in the no recovery model means no database connections can be made and it will be useful only when failover is made.
数据库快照也可以与日志传送和数据库镜像一起很好地工作。 通常,日志传送数据库以无恢复模式维护,这意味着无法建立数据库连接,并且仅在进行故障转移时才有用。
We can create a database snapshot for log shipping secondary database and use the database to run the queries.
我们可以为日志传送辅助数据库创建数据库快照,并使用该数据库运行查询。
In database mirroring, mirror database is always in no recovery mode, whether it is synchronous and asynchronous mode. By creating snapshots of the mirror database, we can easily query the mirror database so it is quite a useful feature to offload and use it for reporting purposes.
在数据库镜像中,无论是同步模式还是异步模式,镜像数据库始终处于无恢复模式。 通过创建镜像数据库的快照,我们可以轻松地查询镜像数据库,因此,将其卸载并用于报告目的是一项非常有用的功能。
But there is some overhead if we are using synchronous mirror mode. As in synchronous mode, transactions have to be committed on both Principal and mirror servers before marking it complete so for a snapshot as it requires original page, it will add an extra task as copy database page from the mirror database to mirror Snapshot.
但是,如果我们使用同步镜像模式,则会有一些开销。 与同步模式一样,在将事务标记为完成之前,必须在主体服务器和镜像服务器上都提交事务,因此对于快照(因为它需要原始页面),它将添加一个额外的任务,即从镜像数据库复制数据库页面以镜像快照。
翻译自:
转载地址:http://hfnwd.baihongyu.com/