2
关于SQL Server 镜像数据库快照的创建及使用
source link: http://www.cnblogs.com/xuliuzai/p/14314609.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
从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
四.快照原理请参阅以下分享
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK