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

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

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

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

快照创建时，SQL Server会在实例中创建一个空文件的快照数据库，如果在快照数据库上查询数据，就会被重定向到源数据库中，所以返回的数据都是源数据库的数据。如果在创建数据库快照后，源数据库的原始数据发生了变更，则会把变更前的数据一份写入到对应的数据库快照空白文件中，这时候数据库快照就有了数据，也不再全是空白页了，此时再查询SQL Server数据库快照，查询到的是数据库快照中的数据库（也就是原始数据的副本）。快照文件的大小随着对源数据库的更改而增大。 注意：数据库快照在数据页级运行。在第一次修改源数据库页之前，先将原始页从源数据库复制到快照。快照将存储原始页，保留它们在创建快照时的数据记录。 对要进行第一次修改的每一页重复此过程。 二.实现创建数据库快照的SP 1.时间格式函数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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION [dbo].[FormatDate]
(@date as datetime,
@formatstring as varchar(100)
)
RETURNS varchar(100) AS  
BEGIN 
    declare @datestring as varchar(100)
    set @datestring=@formatstring
    --year
    set @datestring=replace(@datestring, 'yyyy', cast(year(@date) as char(4)))
    set @datestring=replace(@datestring, 'yy', right(cast(year(@date) as char(4)),2))
    --millisecond
    set @datestring=replace(@datestring, 'ms', replicate('0',3-len(cast(datepart(ms,@date) as varchar(3)))) + cast(datepart(ms, @date) as varchar(3)))
    --month
    set @datestring=replace(@datestring, 'mm', replicate('0',2-len(cast(month(@date) as varchar(2)))) + cast(month(@date) as varchar(2)))
    set @datestring=replace(@datestring, 'm', cast(month(@date) as varchar(2)))
    --day
    set @datestring=replace(@datestring, 'dd', replicate('0',2-len(cast(day(@date) as varchar(2)))) + cast(day(@date) as varchar(2)))
    set @datestring=replace(@datestring, 'd',  cast(day(@date) as varchar(2)))
    --hour
    set @datestring=replace(@datestring, 'hh', replicate('0',2-len(cast(datepart(hh,@date) as varchar(2)))) + cast(datepart(hh, @date) as varchar(2)))
    set @datestring=replace(@datestring, 'h',  cast(datepart(hh, @date) as varchar(2)))

    --minute
    set @datestring=replace(@datestring, 'nn', replicate('0',2-len(cast(datepart(n,@date) as varchar(2)))) + cast(datepart(n, @date) as varchar(2)))
    set @datestring=replace(@datestring, 'n', cast(datepart(n, @date) as varchar(2)))
    --second
    set @datestring=replace(@datestring, 'ss', replicate('0',2-len(cast(datepart(ss,@date) as varchar(2)))) + cast(datepart(ss, @date) as varchar(2)))
    set @datestring=replace(@datestring, 's', cast(datepart(ss, @date) as varchar(2)))
    return @datestring
END
GO

2.创建快照的SP

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

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

（3）具体的创建脚本

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[CreateSnapshotDB_By1H]    Script Date: 2019/1/22 17:39:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--*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 [dbo].[CreateSnapshotDB_By1H]  
AS

set nocount on
declare @sSql nvarchar(2000)    
declare @sMsg varchar(4000)    
declare @TransDT varchar(10)
declare @SS_DBName varchar(40)=''    
declare @DBLogic_FileName varchar(40)=''    
declare @SnapshotDBType varchar(40)='' 
declare @SS_NewDBName varchar(40)='' 
declare @SS_NewDB_FileName varchar(50)='' 
Declare @RestorePath varchar(200)

create table DBLogic (Name nvarchar(200) )
create table OldSS_DB (Name nvarchar(200) )
set @sMsg=''
set @RestorePath='D:\SnapShot' 
select @TransDT=dbo.FormatDate(GETDATE(),'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
    select top 1 @SnapshotDBType=DBName from MirrorDB

    set @SS_NewDBName='SS_'+@SnapshotDBType+'_'+Right(@TransDT,6)
    --print @SS_NewDBName

    IF exists(select  Name   from sys.databases where name = @SS_NewDBName )
    BEGIN  
         BEGIN
           set @sSql='drop 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 exists(select  Name   from sys.databases where name = @SS_NewDBName )
        begin
             set @sSql=' CREATE DATABASE '+@SS_NewDBName +' ON '
            select @sSql= @sSql+ '( NAME ='+Name +', FILENAME = '''+@RestorePath+ '\'+@SnapshotDBType+
                                 '\SS_'+Name+'_'+ left(@TransDT,10)+'.SS''),' from DBLogic 
            if right(@sSql,1)=','
                begin
                    set @sSql=SUBSTRING( @sSql,1,LEN(@sSql)-1 )
                end
            set @sSql=@sSql+' AS SNAPSHOT OF ['+  @SnapshotDBType +']'

            print  @sSql
            exec sp_executesql @sSql
        end 
    else
        begin
            print 'Drop SnapShot DB('+@SS_NewDBName+' fail, it can not create it again! transDT:' +@TransDT
        end

    Declare @TempSS_DB nvarchar(200) 
    ----删除历史快照
    Insert Into OldSS_DB
    select Name   from sys.databases  where name like 'SS_'+@SnapshotDBType+'%' and create_date < dateadd(hour,-3, GETDATE())
    while exists(Select * from OldSS_DB)
    begin
        select top 1 @TempSS_DB=Name from OldSS_DB
        set @sSql='drop DATABASE '+ @TempSS_DB  
        exec sp_executesql @sSql
        delete from OldSS_DB where Name=@TempSS_DB
    end

    delete from DBLogic
    delete  from MirrorDB where DBName =@SnapshotDBType
end

GO

3.创建便于访问的快照

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

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

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[CreateSnapshotDB]    Script Date: 2019/1/22 17:40:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--*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 [dbo].[CreateSnapshotDB]  
AS

BEGIN 
set nocount on
declare @sSql nvarchar(2000)    
declare @sMsg varchar(4000)    
declare @SS_DBName varchar(40)=''
declare @TransDT char(10)    
declare @DBLogic_FileName varchar(40)=''    
declare @SnapshotDBType varchar(40)='' 
declare @SS_NewDBName varchar(40)='' 
declare @SS_NewDB_FileName varchar(50)='' 
Declare @RestorePath varchar(200)

create table DBLogic (Name nvarchar(200) )
create table OldSS_DB (Name nvarchar(200) )
set @sMsg=''
set @RestorePath='D:\SnapShot' 
select @TransDT=dbo.FormatDate(GETDATE(),'YYYYMMDDHH')
Set @sSql='"MD '+@RestorePath
EXEC master..xp_cmdshell @sSql ,no_output
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_SMT/QSMS_MMDDHH
    select top 1 @SnapshotDBType=DBName from MirrorDB

    Set @sSql='"MD '+@RestorePath +'\'+@SnapshotDBType
    EXEC master..xp_cmdshell @sSql ,no_output

    set @SS_NewDBName='SS_'+@SnapshotDBType
    --print @SS_NewDBName

    IF exists(select  Name   from sys.databases where name = @SS_NewDBName )
    BEGIN 
           BEGIN
               set @sSql='drop 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 exists(select  Name   from sys.databases where name = @SS_NewDBName )
        begin
             set @sSql=' CREATE DATABASE '+@SS_NewDBName +' ON '
            select @sSql= @sSql+ '( NAME ='+Name +', FILENAME = '''+@RestorePath+ '\'+@SnapshotDBType+
                                 '\SS_'+Name+'.SS''),' from DBLogic 
            if right(@sSql,1)=','
                begin
                    set @sSql=SUBSTRING( @sSql,1,LEN(@sSql)-1 )
                end
            set @sSql=@sSql+' AS SNAPSHOT OF ['+  @SnapshotDBType +']'

            print  @sSql
            exec sp_executesql @sSql
        end 
    else
        begin
            print 'Drop SnapShot DB('+@SS_NewDBName+' fail, it can not create it again! transDT:' +@TransDT
        end

    delete from DBLogic
    delete  from MirrorDB where DBName =@SnapshotDBType
end

 EXEC [dbo].[CreateSnapshotDB_By1H]

END 
GO

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

exec CreateSnapshotDB

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

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

主要使用的SP为sp_help_revlogin，但是这个SP会调用到sp_hexadecimal。 1.先创建基础SP:sp_hexadecimal

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_hexadecimal]    Script Date: 2019/1/22 17:48:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_hexadecimal]

@binvalue varbinary(256),

@hexvalue varchar (514) OUTPUT

AS

DECLARE @charvalue varchar (514)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

DECLARE @tempint int

DECLARE @firstint int

DECLARE @secondint int

SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

SELECT @firstint = FLOOR(@tempint/16)

SELECT @secondint = @tempint - (@firstint*16)

SELECT @charvalue = @charvalue +

SUBSTRING(@hexstring, @firstint+1, 1) +

SUBSTRING(@hexstring, @secondint+1, 1)

SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

GO

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

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_help_revlogin]    Script Date: 2019/1/22 17:52:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_help_revlogin] @login_name sysname = NULL AS

DECLARE @name sysname

DECLARE @type varchar (1)

DECLARE @hasaccess int

DECLARE @denylogin int

DECLARE @is_disabled int

DECLARE @PWD_varbinary varbinary (256)

DECLARE @PWD_string varchar (514)

DECLARE @SID_varbinary varbinary (85)

DECLARE @SID_string varchar (514)

DECLARE @tmpstr varchar (1024)

DECLARE @is_policy_checked varchar (3)

DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)

DECLARE 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 ( 'S', 'G', 'U' ) AND p.name <> 'sa'

ELSE

DECLARE 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 ( 'S', 'G', 'U' ) AND p.name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

IF (@@fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr

SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr

IF (@type IN ( 'G', 'U'))

BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'

END

ELSE BEGIN -- SQL Server authentication

-- obtain password and sid

SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state

SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )

BEGIN

SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked

END

IF ( @is_expiration_checked IS NOT NULL )

BEGIN

SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked

END

END

IF (@denylogin = 1)

BEGIN -- login is denied access

SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )

END

ELSE IF (@hasaccess = 0)

BEGIN -- login exists but does not have access

SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )

END

IF (@is_disabled = 1)

BEGIN -- login is disabled

SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'

END

PRINT @tmpstr

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

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

原文创作：东山絮柳仔

原文链接：https://www.cnblogs.com/xuliuzai/p/14314609.html

文章列表

• 通过Python收集MySQL MHA 部署及运行状态信息的功能实现
• 通过Python将监控数据由influxdb写入到MySQL
• 通过Python实现生成excel并邮件发送的功能
• 通过Python实现对SQL Server 数据文件大小的监控告警
• 通过 Telegraf + InfluxDB + Grafana 快速搭建监控体系的详细步骤
• 迁移Report Server DataBase时遇到的坑
• 谨慎 mongodb 关于数字操作可能导致类型及精度变化
• 详解MongoDB中的多表关联查询$lookup
• 瞧一瞧！这儿实现了MongoDB的增量备份与还原含部署代码
• 时序数据库InfluxDB的基本语法
• 数据库服务器资源使用情况周报
• 数据库如何应对保障大促活动
• 我10亿级ES数据迁到MongoDB节省90%成本!转载
• 应用部署架构演进转载 -
• 学习ProxySQL参考到几个网址
• 名言小抄五
• 关于SQL Server 镜像数据库快照的创建及使用
• 关于SQL Server 数据库归档的一些思考和改进
• 关于MongoDB时间格式转换和时间段聚合统计的用法总结
• 以实现MongoDB副本集状态的监控为例，看Telegraf系统中Exec输入插件如何编写部署
• 一个磁盘I/O故障导致的AlwaysOn FailOver 过程梳理和分析
• shell 操作钉钉机器人实现告警提醒
• python 学习笔记 四
• python 学习 三
• kapacitor的安装及部分常用命令
• TiDB 架构及设计实现
• TiDB 学习笔记一运维管理
• SQL Server 查看当前会话状态sp_WhoIsActive 转载 -
• SQL Server DB迁移工作List
• SQL Server CPU 利用率毛刺的分析定位与解决
• SQL Server Alwayson架构下 服务器 各虚拟IP漂移监控告警的功能实现 1服务器视角
• Python发送的邮件设置收件人隐藏与显示
• MySQL索引设计需要考虑哪些因素？
• MySQL日志收集之Filebeat和Logstsh的一键安装配置ELK架构
• MySQL数据归档小工具推荐及优化mysql_archiver
• MySQL数据库规范 设计规范+开发规范+操作规范
• MySQL数据库Inception工具学习与测试 笔记
• MySQL常用命令汇总偏向运维管理
• MySQL在线DDL工具 ghost
• MySQL 学习笔记四
• MySQL 学习笔记三
• MySQL alter table时执行innobackupex全备再谈Seconds_Behind_Master
• MySQL MHA 运行状态监控
• MongoDB数据库的设计规范
• MongoDB实例重启失败探究大事务Redo导致
• MongoDB 那些事一文以蔽之
• MongoDB 中的加减乘除 - 运算
• MongoDB 中数据的替换方法实现 类Replace函数功能
• Linux常用命令总结二
• K8S基础学习