从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 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*: --*Programer*:--*Date*:<2019/01/21>--*Unify*:--*Description*:--########## 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#MirrorDBset @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#DBLogicselect 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#DBLogicif 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_DBselect 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_DBset @sSql='drop DATABASE'+ @TempSS_DB
exec sp_executesql @sSql
delete from #OldSS_DB where Name=@TempSS_DB
end
delete from#DBLogicdelete 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*: --*Programer*:--*Date*:<2019/01/21>--*Unify*:--*Description*:--########## 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_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#MirrorDBSet @sSql='"MD'+@RestorePath +'\'+@SnapshotDBType
EXEC master..xp_cmdshell @sSql,no_outputset @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#DBLogicselect 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#DBLogicif 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#DBLogicdelete 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) OUTPUTAS
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 @namesysnameDECLARE @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 @defaultdbsysnameIF (@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 FROMsys.server_principals pLEFT JOINsys.syslogins lON ( 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 FROMsys.server_principals pLEFT JOINsys.syslogins lON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPENlogin_cursFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)BEGIN
PRINT 'No login(s) found.'
CLOSElogin_cursDEALLOCATElogin_cursRETURN -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_stringOUTEXEC sp_hexadecimal @SID_varbinary,@SID_stringOUT--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
CLOSElogin_cursDEALLOCATElogin_cursRETURN 0
GO
四.快照原理请参阅以下分享