Microsoft® SQL Server 2012 Unleashed

Copy-on-Write Technology

The copy-on-write technology that Microsoft first introduced with SQL Server 2005 is at the core of database snapshot capabilities. In this section, we walk through a typical transactional user’s update of data in a source database to illustrate how the copy-on-write technology works.

As you can see in Figure 28.5, an update transaction is initiated against the AdventureWorks database (labeled A). As the data is being updated in the source database’s data page, the change is written to the transaction log (labeled B). The copy-on-write technology also copies the original source database data page in its unchanged state to the sparse data file (also labeled B) and updates the metadata page references in the system catalog ...