關於SQL Server 映象資料庫快照的建立及使用

東山絮柳仔發表於2021-01-23

從SQL Server 2005 SP 起，SQL 開始支援資料庫映象。它的設計目的是試圖為SQL Server 提供一個具有實時性資料同步的災難恢復技術，即能夠提供資料冗餘備份，切換起來比較方便。每個主體資料庫只能有一個映象資料庫。映象資料庫作為主體資料庫的一個副本，在主體資料庫發生故障、不可訪問時能夠迅速恢復資料庫訪問，提供故障恢復功能。映象資料庫一直處於“恢復”狀態，因此不能被直接訪問。

一.什麼是資料庫快照

為了提高資源的使用率，想讓映象資料庫可以承擔部分讀，可以藉助資料庫快照技術。

資料庫快照是 SQL Server 資料庫（源資料庫）的只讀靜態檢視。資料庫快照在事務上與建立快照時刻的源資料庫一致。一個源資料庫可以有多個資料庫快照，並且可以作為資料庫駐留在一個SQL Server例項中。資料庫快照是一個只讀的狀態，這也就決定了快照的使用場景，那就是用於報表。也可以通過快照快速恢復部分誤運算元據。

快照建立時，SQL Server會在例項中建立一個空檔案的快照資料庫，如果在快照資料庫上查詢資料，就會被重定向到源資料庫中，所以返回的資料都是源資料庫的資料。如果在建立資料庫快照後，源資料庫的原始資料發生了變更，則會把變更前的資料Copy一份寫入到對應的資料庫快照空白檔案中，這時候資料庫快照就有了資料，也不再全是空白頁了，此時再查詢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語句Copy至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

SQL Server資料庫建立新使用者及關聯資料庫的方法教程
2019-01-30
SQLServer資料庫
SQL Server 資料庫映象
2015-04-24
SQLServer資料庫
Sqlserver在映象資料庫上建立快照指令碼
2018-01-10
SQLServer資料庫指令碼
sql server 2005資料庫快照
2019-04-26
SQLServer資料庫
資料庫映象和資料庫快照
2008-11-13
資料庫
有關於SQL Server資料庫死鎖的分析(轉)
2007-08-16
SQLServer資料庫
SQL Server資料庫映象的FailOver自動連線
2012-05-02
SQLServer資料庫AI
SQL Server :DBLINK建立及使用
2012-01-13
SQLServer
通過SQL Server資料庫映象保護虛擬資料庫IC
2022-03-22
SQLServer資料庫
ASP建立SQL Server資料庫的兩種方法
2007-01-28
SQLServer資料庫
Blazor使用sql server 資料庫
2024-03-10
BlazorSQLServer資料庫
關於SQL Server資料庫中的使用者許可權和角色管理
2024-03-26
SQLServer資料庫
2.1 關於建立資料庫
2020-02-23
資料庫
建立SQL Server 和資料庫加密金鑰
2008-11-26
SQLServer資料庫加密
建立SQL Server資料庫指令碼範例
2010-09-03
SQLServer資料庫指令碼
認識及操作SQL Server 資料庫
2009-05-28
SQLServer資料庫
Sql Server基礎：使用T_SQL建立，修改，檢視資料庫資訊
2015-05-07
SQLServer資料庫
SQL Server 2016資料庫快照代理過程詳解
2019-02-10
SQLServer資料庫
2.5.1 關於建立資料庫的子句
2020-03-03
資料庫
SQL SERVER收集資料庫使用增量資料
2008-09-11
SQLServer資料庫
Sql Server系列：資料庫組成及系統資料庫
2014-11-06
SQLServer資料庫
資料庫及使用者的建立
2012-09-04
資料庫
SQL Server:基於WEB的資料庫查詢
2009-11-04
SQLServerWeb資料庫
2.3.2 關於使用互動式DBCA建立資料庫
2020-02-28
資料庫
MS SQL Server 2000 系統資料庫及相關資訊
2008-08-19
SQLServer資料庫
關於SQL Server資料庫備份和恢復特性介紹
2009-02-23
SQLServer資料庫
關於SQL Server 中的25種資料型別
2010-09-09
SQLServer資料型別
SQL Server中基於WEB的資料庫查詢
2010-01-20
SQLServerWeb資料庫
在SQL Server下資料庫連結的使用
2009-01-14
SQLServer資料庫
關於建立DataGuard Physical Standby資料庫
2011-08-01
資料庫
關於SQL Server中索引使用及維護簡介(zt)
2019-06-07
SQLServer索引
為SQL Server快照snapshot DB建立login訪問
2019-05-20
SQLServer
關於jsp 呼叫bean 訪問sql-server資料庫問題
2004-07-16
JSBeanSQLServer資料庫
SQL Server資料庫安全
2021-04-22
SQLServer資料庫
SQL Server 資料庫索引
2012-06-05
SQLServer資料庫索引
關於SQL Server資料查詢基本方法的總結
2010-09-28
SQLServer
關於java連線SQL資料庫
2016-11-23
JavaSQL資料庫
SQL Server資料庫備份保護的關鍵UM
2022-03-22
SQLServer資料庫