当前位置:网站首页>關於SQL Server 映象資料庫快照的建立及使用

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

2021-01-23 16:41:52 itread01

從SQL Server 2005 SP1 起,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 ONGOSET QUOTED_IDENTIFIER ONGOCreate FUNCTION [dbo].[FormatDate](@date as datetime,@formatstring as varchar(100))RETURNS varchar(100) AS  BEGIN     declare @datestring as varchar(100)    set @[email protected]    --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 @datestringENDGO

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 ONGOSET QUOTED_IDENTIFIER ONGO----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*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 ondeclare @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_'[email protected]+'_'+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)[email protected] and type=0    if not exists(select  Name   from sys.databases where name = @SS_NewDBName )        begin             set @sSql=' CREATE DATABASE '[email protected]_NewDBName +' ON '            select @sSql= @sSql+ '( NAME ='+Name +', FILENAME = '''[email protected]+ '\'[email protected]+                                 '\SS_'+Name+'_'+ left(@TransDT,10)+'.SS''),' from #DBLogic             if right(@sSql,1)=','                begin                    set @sSql=SUBSTRING( @sSql,1,LEN(@sSql)-1 )                end            set @[email protected]+' AS SNAPSHOT OF ['+  @SnapshotDBType +']'                        print  @sSql            exec sp_executesql @sSql        end     else        begin            print 'Drop SnapShot DB('[email protected]_NewDBName+' fail, it can not create it again! transDT:' [email protected]        end     Declare @TempSS_DB nvarchar(200)     ----刪除歷史快照    Insert Into #OldSS_DB    select Name   from sys.databases  where name like 'SS_'[email protected]+'%' 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 [email protected]_DB    end        delete from #DBLogic    delete  from #MirrorDB where DBName [email protected]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 ONGOSET QUOTED_IDENTIFIER ONGO----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*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 ondeclare @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 '[email protected]EXEC master..xp_cmdshell @sSql ,no_outputselect 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 '[email protected] +'\'[email protected]    EXEC master..xp_cmdshell @sSql ,no_output        set @SS_NewDBName='SS_'[email protected]    --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)[email protected] and type=0    if not exists(select  Name   from sys.databases where name = @SS_NewDBName )        begin             set @sSql=' CREATE DATABASE '[email protected]_NewDBName +' ON '            select @sSql= @sSql+ '( NAME ='+Name +', FILENAME = '''[email protected]+ '\'[email protected]+                                 '\SS_'+Name+'.SS''),' from #DBLogic             if right(@sSql,1)=','                begin                    set @sSql=SUBSTRING( @sSql,1,LEN(@sSql)-1 )                end            set @[email protected]+' AS SNAPSHOT OF ['+  @SnapshotDBType +']'                        print  @sSql            exec sp_executesql @sSql        end     else        begin            print 'Drop SnapShot DB('[email protected]_NewDBName+' fail, it can not create it again! transDT:' [email protected]        end     delete from #DBLogic    delete  from #MirrorDB where DBName [email protected]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 ONGOSET QUOTED_IDENTIFIER ONGO 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 ONGOSET QUOTED_IDENTIFIER ONGO 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/30570

版权声明
本文为[itread01]所创,转载请带上原文链接,感谢
https://www.itread01.com/content/1611389047.html