如何利用SQL Server 2005数据库快照形成报表

 

在SQL Server 2005中，它的另外一个强大的新特点是数据库快照。数据库快照是一个数据库的只读副本，它是数据库所有数据的映射，由快照被执行的时间点来决定它的内容。

这些数据库快照在报表方面是非常有价值，因为在快照数据库中或者在原数据库中，对于任何查询而言没有锁就将被执行。快照也可以使用在灾难恢复中，因为你可以将现有的数据恢复到现有的快照中，或者还可以在有害数据操作声明的事件中存储个别必要的表和数据。

数据库快照是如何工作的?

可以使用典型的数据库命令CREATE DATABASE语句来生成一个数据库快照，在声明中有一个源数据库快照的附加说明。当快照被建立时，同时生成一个稀疏文件。这个文件（只能使用在NTFS卷中）在初始化的时候并没有磁盘空间分配给它——尽管你可能在WINDOWS资源管理器中看到了文件的大小，它会看上去与原始的源数据库文件的大小相同。对磁盘来说其实这个文件的大小接近于零。

数据库快照在初始化时读的数据文件是来自于源数据库的。当源数据库的数据发生变化时，数据引擎就会将原始数据从源数据库拷贝到快照数据库中。这个技术确保快照数据库只反映快照被执行时数据的状态。当SELECT命令被用来发布反对数据库快照时，不管数据页的读取是否被定位在源数据库数据文件中还是在快照数据库数据文件中都是没有锁被发布的。因为在只读数据库快照中是没有锁被发布，数据库快照对于报表解决方案是一个重要的解决方案。

一个快照的实例

现在，让我们来看看数据库快照在SQL Server 2005中是如何工作的。为此，首先我需要一个源数据库作为快照的来源。下面的脚本将创建一个源数据库：

USE master

GO

IF EXISTS(SELECT name from sysdatabases where [name] = 'SourceDatabase')

DROP DATABASE SourceDatabase

GO

CREATE DATABASE SourceDatabaseON PRIMARY

(

NAME = SourceDatabase_Data,

FILENAME = 'C:SQLServerSourceDatabase_Data.mdf'

) LOG ON

(

NAME = SourceDatabase_Log,

FILENAME = 'C:SQLServerSourceDatabase_Log.ldf'

)

GO

注意这里产品区域的大小。我定义它的大小为CHAR（150）来强调数据文件的增长级数，这样在我接下来的实例中将更容易解释清楚快照是如何工作的。

现在既然我已经有了一个源数据库，现在我装载一些数据来扩展数据文件的大小位。如此，使用下面的脚本来创建销售历史表。

  

USE SourceDatabase

GO

IF OBJECT_ID('SalesHistory')>0
      DROP TABLE SalesHistory

GO

CREATE TABLE SalesHistory

(
      SaleID INT IDENTITY(1,1),      
      Product CHAR(150), 
      SaleDate DATETIME, 
      SalePrice MONEY

)



DECLARE @i INT

SET @i = 1


WHILE (@i <=10000)



BEGIN
      INSERT INTO SalesHistory
      (Product, SaleDate, SalePrice)
      VALUES
      ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57) )




      INSERT INTO SalesHistory
      (Product, SaleDate, SalePrice)
      VALUES
      ('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13) )




      INSERT INTO SalesHistory
      (Product, SaleDate, SalePrice)
      VALUES
      ('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29) )




      SET @i = @i + 1



END

GO

一旦你运行以下的脚本，就可以来操纵数据库文件储存的地方了。在这个实例上面，我将这些文件放在C:SQL Server文件夹下。在我的计算机上，当数据库被初始化创建时数据文件的大小是1,216KB，数据装载后的大小是7,360KB。既然源数据库有一些数据已经在里面了，所以我们现在就能建立一个快照了。使用以下的脚本来建立数据库快照。

CREATE DATABASE SnapshotDatabase

ON

(

NAME = 'SourceDatabase_Data',

FILENAME = 'C:SQLServerSnapshotDatabase.mdf'

) AS SNAPSHOT OF SourceDatabase

这个创建快照的语法与创建数据库的语法非常相似。两个主要的区别：第一个区别是AS SNAPSHOT OF SourceDatabase语句，在服务器实例上指明哪个数据库将作为快照的源数据库；第二个区别是，在事实上数据库快照是不会生成日志文件的。因为没有数据操作事物只会发生在只读数据库上，所以没有也不需要日志。

这个数据库脚本创建一个名为SnapshotDatabase.mdf（数据文件的扩展名不需要mdf）的稀疏文件。如果你在WINDOWS资源管理器中操作这个文件，并同时查看它的属性，你将看到这个文件的大小与源数据库文件的大小相同；然而，实际上磁盘上的大小却接近于零。这时，数据库快照是没有它自己的数据。

你能运行上面我使用的脚本重新在SourceDatabase数据库里面插入10,000行数据到SalesHistory表中。这时，我的SourceDatabase数据库大小是12,480KB，同时，磁盘上我的SourceDatabase数据库大小现在为448KB。这时在SourceDatabase数据库中所改变的数据页都已经被拷贝到SnapshotDatabase数据库中了，这样就能解释为什么在磁盘上它的大小被增加了。


从数据库快照中恢复数据库到快照创建的时刻

USE [Master]

RESTORE DATABASE SourceDatabase FROM

DATABASE_SNAPSHOT = 'SnapshotDatabase'

GO

--删除数据库快照

DROP DATABASE SnapshotDatabase

进一步思考

数据库快照允许你为报表创建只读数据库，并且如果有必要的话你可以恢复你的源数据库到你的数据库快照上。同样的，你也可以根据你的报表目的创建任意多个数据库快照。

特别值得注意的是，这些数据库快照会占用磁盘空间，如果有太多的数据库快照的话，它将很快的将你的磁盘阵列填满，尤其在一个产品环境下，如果数据经常要被更新，就更容易将磁盘阵列填满。

另外，使用数据库快照会降低数据库的性能，因为在数据库中将数据页作为执行写操作拷贝是增加了数据库输入/输出。

虽然有这些小缺点，如果你能为报表提出一个好的数据库快照创建方案，SQL Server 2005的这个新的特性将有更多的人使用。

Tim Chapman是肯塔基州路易维尔市一家银行的SQL Server数据库管理员，他有超过七年的行业经验。他还通过了微软SQL Server 2000和SQL Server 2005的认证。



数据库快照的工作方式 （MSDN）

 

数据库快照提供源数据库在创建快照时的只读、静态视图，不包含未提交的事务。由于数据库引擎 在创建快照时运行恢复，因此未提交的事务在新近创建的数据库快照中回滚（数据库中的事务不受影响）。

数据库快照与源数据库相关。数据库快照必须与数据库在同一服务器实例上。此外，如果数据库因某种原因而不可用，则它的所有数据库快照也将不可用。

快照可用于报表。另外，如果源数据库出现用户错误，还可将源数据库恢复到创建快照时的状态。丢失的数据仅限于创建快照后数据库更新的数据。有关快照使用方式的详细信息，请参阅数据库快照的典型用法。

虽然不一定必须使用快照，但是了解其工作原理会有所帮助。数据库快照在数据页级运行。在第一次修改源数据库页之前，先将原始页从源数据库复制到快照。此过程称为“写入时复制操作”。快照将存储原始页，保留它们在创建快照时的数据记录。对已修改页中的记录进行后续更新不会影响快照的内容。对要进行第一次修改的每一页重复此过程。这样，快照将保留自创建快照后经修改的所有数据记录的原始页。

为了存储复制的原始页，快照使用一个或多个“稀疏文件”。最初，稀疏文件实质上是空文件，不包含用户数据并且未被分配存储用户数据的磁盘空间。随着源数据库中更新的页越来越多，文件的大小也不断增长。创建快照时，稀疏文件占用的磁盘空间很少。然而，由于数据库随着时间的推移不断更新，稀疏文件会增长为一个很大的文件。有关稀疏文件的详细信息，请参阅了解数据库快照中的稀疏文件大小。

下图说明了写入时复制操作。快照关系图中的浅灰色方框表示稀疏文件中尚未分配的潜在空间。收到源数据库中页的第一次更新时，数据库引擎 将写入文件，操作系统向快照的稀疏文件分配空间并将原始页复制到该处。然后，数据库引擎 更新源数据库中的页。下图说明了此类写入时复制操作。

在更新页之后对快照的读操作
注意：
由于数据库快照不是冗余存储，因此，它们不会防止磁盘出现错误或其他类型的损坏。为了保护数据库，非常有必要定期执行备份并测试还原计划。如果数据库快照包含重要数据，则应该实施备份策略以便将源数据库的副本还原到创建快照的任意时点。

 

 

对数据库快照的读操作
更新模式对数据库快照增长的影响

如果您的源数据库过大并且您担心磁盘空间使用量，则您应该在某个时候用新快照替换旧快照。快照理想的使用期限取决于其增长率以及可用于其稀疏文件的磁盘空间。快照所需的磁盘空间取决于在快照使用期限内源数据库中更新的不同页的数量。因此，如果大多数情况下更新重复更新的页的小子集，则随着时间的推移，增长率会降低，快照所需空间也会相对较小。相反，如果最终将所有原始页至少更新一次，则快照将会增长到源数据库的大小。如果磁盘将满，则快照会互相争用磁盘空间。如果磁盘驱动器已满，则无法将操作写入所有快照。

注意：
有关了解快照的实际大小和潜在大小的信息，请参阅了解数据库快照中的稀疏文件大小。

 

 

因此，在计划快照预计使用期限内所需空间量时，了解数据库的通常更新模式是很有用的。对于某些数据库，更新率可能相当稳定；例如，库存数据库可能每天都更新很多页，这对每天或每周替换旧快照非常有用。对于其他数据库，更新页的比例在业务周期内可能有所不同；例如，目录数据库可能通常每季度更新，会在其他时间偶尔更新；逻辑策略是在每季度更新前后创建快照。如果发生严重更新错误，允许还原更新前快照，而更新后快照用于报告下一季度的写入。

下图说明了两种相对的更新模式对快照大小的影响。更新模式 A 反映的是在快照使用期限内仅有 30% 的原始页更新的环境。更新模式 B 反映的是在快照使用期限内有 80% 的原始页更新的环境。

备用更新模式和快照大小
数据库快照的元数据

对于数据库快照，数据库元数据包括 source_database_id 属性，该属性存储在 sys.databases 目录视图的列中。有关此属性的详细信息，请参阅 sys.databases (Transact-SQL)。

通常，数据库快照不公开自己的元数据，但会公开源数据库的元数据。例如，此元数据包括下列语句返回的数据：

USE <database_snapshot> SELECT * FROM sys.database_files

其中，<database_snapshot> 是数据库快照的名称。

唯一的例外情况是当源数据库使用全文搜索或数据库镜像时，此时由于更改了快照元数据中的一些值，因此在快照上禁用了源数据库。

请参阅

posted on 2007-01-26 17:14  Sherrys  阅读(793)  评论(0)  编辑  收藏  举报

导航