关于SQL Server 镜像数据库快照的创建及使用

概述从SQL Server 2005 SP1 起，SQL 开始支持数据库镜像。它的设计目的是试图为SQL Server 提供一个具有实时性数据同步的灾难恢复技术，即能够提供数据冗余备份，切换起来比较方便。

从sql Server 2005 SP1 起，sql 开始支持数据库镜像。它的设计目的是试图为sql Server 提供一个具有实时性数据同步的灾难恢复技术，即能够提供数据冗余备份，切换起来比较方便。每个主体数据库只能有一个镜像数据库。镜像数据库作为主体数据库的一个副本，在主体数据库发生故障、不可访问时能够迅速恢复数据库访问，提供故障恢复功能。镜像数据库一直处于“恢复”状态，因此不能被直接访问。

一.什么是数据库快照

为了提高资源的使用率，想让镜像数据库可以承担部分读，可以借助数据库快照技术。

数据库快照是 sql Server 数据库（源数据库）的只读静态视图。数据库快照在事务上与创建快照时刻的源数据库一致。一个源数据库可以有多个数据库快照，并且可以作为数据库驻留在一个sql Server实例中。数据库快照是一个只读的状态，这也就决定了快照的使用场景，那就是用于报表。也可以通过快照快速恢复部分误 *** 作数据。

快照创建时，sql Server会在实例中创建一个空文件的快照数据库，如果在快照数据库上查询数据，就会被重定向到源数据库中，所以返回的数据都是源数据库的数据。如果在创建数据库快照后，源数据库的原始数据发生了变更，则会把变更前的数据copy一份写入到对应的数据库快照空白文件中，这时候数据库快照就有了数据，也不再全是空白页了，此时再查询sql Server数据库快照，查询到的是数据库快照中的数据库（也就是原始数据的副本）。快照文件的大小随着对源数据库的更改而增大。 注意：数据库快照在数据页级运行。在第一次修改源数据库页之前，先将原始页从源数据库复制到快照。快照将存储原始页，保留它们在创建快照时的数据记录。 对要进行第一次修改的每一页重复此过程。

二.实现创建数据库快照的SP1.时间格式函数FormatDate

在前面的学习分析中，我们知道一个源数据库可以有多个快照，所以，为了区别同时存在的多快照，我们对快照的命名基于了时间（即包含了时间元素），例如:

SS_DBname_19022311(2019年2月23号11点产生的快照)；SS_DBname_19022312(2019年2月23号12点产生的快照)；SS_DBname_19022313(2019年2月23号13点产生的快照)。所以，先编写创建时间格式函数FormatDate。此外，快照以SS_开头是为了标示此对象为数据库快照，与其他数据库对象区别开，便于运维管理，SS为Snapshots的缩写。

USE [master]GO/****** Object:  UserdefinedFunction [dbo].[FormatDate]    Script Date: 2019/1/22 17:37:53 ******/SET ANSI_NulLS ONSET QUOTED_IDENTIFIER GOCreate FUNCTION dbo].FormatDate](@date as datetime,@formatstring varchar(100))RETURNS 100) AS  BEGIN     declare @datestring )    set @datestring=@formatstring    --year    =replace(@datestring,'yyyy',1)">cast(year(@date) char(4)))    yyright(4)),1); Font-weight: bold">2))    millisecond    msreplicate(03-len(datepart(ms,3)))) + 3)))    month    mm2month(2)))) mday    ddday(dhour    hhdatepart(hh,1)">h)))        minute    nndatepart(n,1)">nsecond    ssdatepart(ss,1)">sreturn @datestringENDGO

2.创建快照的SP

（1）首先明确那些DB需要创建快照。这里是从MirrorDB 中筛选的，并且，IN（）可以定义多个数据库。定义部分如下:

（2）明确数据库快照保留的个数

         

 （3）具体的创建脚本

***** Object:  StoredProcedure [dbo].[CreateSnapshotDB_By1H]    Script Date: 2019/1/22 17:39:07 *****--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*Program*: <Create Snapshot DB for Mirror DB>*Programer*:<Carson.Xu>*Date*:<2019/01/21>*unify*:<ALL> *Description*:<Create Snapshot DB for Mirror DB>########## Parameter Description Begin #################### Parameter Description End # ########## ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE PROCEDURE CreateSnapshotDB_By1H]  AS set nocount on@ssql nvarchar(2000)    @sMsg 4000@TransDT 10)@SS_DBname 40)''    @DBLogic_filename @SnapshotDBType '' @SS_NewDBname @SS_NewDB_filename 50)Declare @RestorePath 200) create table #DBLogic (name ) )table #oldSS_DB (name ) )@sMsg''@RestorePathD:\SnapShot' select @TransDT=dbo.FormatDate(GETDATE(),1)">YYYYMMDDHH')select DB_name(database_ID) as DBname,database_ID,mirroring_partner_name Into #MirrorDB from sys.database_mirroring where mirroring_guID is not null and DB_name(database_ID) in('Your_DBname)  While exists(Select top 1 * from #MirrorDB )begin    SS_JON/QFMS_MMDDHH    1 @SnapshotDBType=DBname  #MirrorDB        @SS_NewDBnameSS_'++_+Right(@TransDT,1); Font-weight: bold">6)    print @SS_NewDBname        IF select  name   from sys.databases where name = @SS_NewDBname )    BEGIN           BEGIN           @ssqldrop DATABASE + @SS_NewDBname           exec sp_executesql @ssql         END    END       Insert into #DBLogic    select  name  from sys.master_files where DB_name(database_ID)@SnapshotDBType and type=0    if not  )        begin              CREATE DATABASE @SS_NewDBname  ON '            ( name =+name '''\+                                 \SS_+name+ left(10).SS''),'  #DBLogic             right(@ssql,1); Font-weight: bold">1)'                begin                    SUBSTRING( 1,1)">LEN(@ssql)-1 )                end             AS SNAPSHOT OF [+  ]'                        print  @ssql            @ssql        end     else        begin            print Drop SnapShot DB( fail,it can not create it again! transDT:' @TransDT        end     @TempSS_DB )     --删除历史快照    Into #oldSS_DB    select name   from sys.databases  like %and create_date < dateadd(hour,1); Font-weight: bold">3,1)">GETDATE())    while Select  #oldSS_DB)    begin        @TempSS_DB=name  #oldSS_DB        @TempSS_DB          delete from #oldSS_DB where name@TempSS_DB    end        delete  from #MirrorDB where DBname @SnapshotDBTypeend GO

 3.创建便于访问的快照

上面的SP是创建了以时间命名的DB快照，创建时间不同，快照的名字就会不同。但是，如果DB名字不同，程序应用调用起来就非常不方便。所以我们还希望可以创建一个不带时间的数据库快照，每次创建数据的快照名字是一样的。这样前端应用程序访问数据库就不再需要修改数据库的连接配置了。

下面这个SP就是为了解决这个上面这个应用场景。代码将数据库的快照命名为SS_DBname。为了包含融合前面SP的功能，这份SP还直接调用了存储过程CreateSnapshotDB_By1H----EXEC [dbo].[CreateSnapshotDB_By1H]

***** Object:  StoredProcedure [dbo].[CreateSnapshotDB]    Script Date: 2019/1/22 17:40:57 ******Programer*:<Carson Xu>------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CreateSnapshotDBAS BEGIN ''Set "MD @RestorePathEXEC master..xp_cmdshell @ssqlSS_SMT/QSMS_MMDDHH     #MirrorDB         @RestorePath @SnapshotDBType    BEGIN            BEGIN               @SS_NewDBname            @ssql           end     end  EXEC ] END GO

 以上代码为创建镜像DB快照使用到的函数和存储过程。在调试部署OK后，就可以设置Job了，让其每小时自动执行一次。Job的设置就不再赘言了，核心代码就是:

exec CreateSnapshotDB

三.同步主体、镜像数据库间的账号

系统数据库不能被镜像，用户名密码自然也不能被同步到Mirror服务器上。快照的用户权限继承于源库，但是MIrror 实例上并没有相应的账号信息。所以，需要先到主体数据库（Principal Database）上导出用户的账号信息（可以指定某个账号），然后将打印出的SQL语句copy至Mirror实例上执行一下就可以了。

主要使用的SP为sp_help_revlogin，但是这个SP会调用到sp_hexadecimal。

1.先创建基础SP:sp_hexadecimal

***** Object:  StoredProcedure [dbo].[sp_hexadecimal]    Script Date: 2019/1/22 17:48:06 *****GO sp_hexadecimal] @binvalue varbinary(256@hexvalue varchar (514) OUTPUT DECLARE @charvalue @i int @length @hexstring 16SELECT @charvalue = 0x' @i = 1 @length DATALENGTH (@binvalue@hexstring 0123456789ABCDEFWHILE (<= @lengthBEGIN @tempint @firstint @secondint @tempint CONVERT(int,1)">SUBSTRING(@binvalue,1)">@i,1)">)) @firstint FLOOR(@tempint/@secondint - (@firstint*+ @hexstring,1)">+1) @secondintEND @hexvalue @charvalue GO

 2.创建导出用户信息的SP:sp_help_revlogin

***** Object:  StoredProcedure [dbo].[sp_help_revlogin]    Script Date: 2019/1/22 17:52:39 *****sp_help_revlogin] @login_name sysname NulL @name sysname @type @hasaccess @denylogin @is_Disabled @PWD_varbinary varbinary (@PWD_string @SID_varbinary 85@SID_string @tmpstr 1024@is_policy_checked @is_expiration_checked @defaultdbIF (@login_name IS NulLDECLARE login_curs CURSOR FOR SELECT p.sID,p.name,p.type,p.is_Disabled,p.default_database_name,l.hasaccess,l.denylogin FROM sys.server_principals p left JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( SGU' ) AND p.name <> saELSE @login_name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary,1)">@name,1)">@type,1)">@is_Disabled,1)">@defaultdb,1)">@hasaccess,1)">@denylogin @@fetch_status PRINT No login(s) found.CLOSEDEALLOCATERETURN SET @tmpstr /* sp_help_revlogin script PRINT @tmpstr ** Generated CONVERT (varchar,1)">GETDATE())  on @@SERVERname  */'' <> -- Login: @name @type BEGIN  NT authenticated account/group CREATE LOGIN QUOTEname( @name )  FROM windows WITH DEFAulT_DATABASE = [@defaultdb ELSE  sql Server authentication  obtain password and sID @PWD_varbinary CAST( LOGINPROPERTY( PasswordHash' ) AS ) ) EXEC sp_hexadecimal @PWD_varbinary,1)">@PWD_string OUT @SID_string obtain password policy state @is_policy_checked CASE is_policy_checked WHEN THEN ON0 OFFEND FROM sys.sql_logins WHERE name @is_expiration_checked CASE is_expiration_checked  WITH PASSWORD = @PWD_string  HASHED,SID = @SID_string IF ( IS NOT  ) @is_policy_checked @is_expiration_checked @denylogin  login is denIEd access ; DENY CONNECT sql TO @hasaccess 0 login exists but does not have access ; REVOKE CONNECT sql TO @is_Disabled  login is Disabled ; ALTER LOGIN  disABLE0 GO

 

四.快照原理请参阅以下分享

1.https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/database-snapshots-sql-server?view=sql-server-ver15

2.https://www.zhihu.com/question/305701792

总结

以上是内存溢出为你收集整理的关于SQL Server 镜像数据库快照的创建及使用全部内容，希望文章能够帮你解决关于SQL Server 镜像数据库快照的创建及使用所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。