当前位置:网站首页>On the establishment and use of SQL Server image database snapshot

On the establishment and use of SQL Server image database snapshot

2021-01-23 16:43:10 itread01

From SQL Server 2005 SP1 rise ,SQL Start supporting database image . It's designed to try to make SQL Server Provide a disaster recovery technology with real-time data synchronization , That is, it can provide data redundancy backup , It's easier to switch . Each principal database can only have one image database . The image database serves as a copy of the principal database , A failure occurred in the principal database 、 It can quickly resume database access when it is not accessible , Provide fault recovery function . The image database has been in “ Recovery ” Status , So it can't be directly accessed .

One . What is a database snapshot

In order to improve the utilization of resources , I want the image database to be able to bear part of the reading , With the help of database snapshot technology .

The database snapshot is  SQL Server  Database ( Source database ) Read only static view of . The database snapshot is consistent in transaction with the source database at the time of snapshot creation . A source database can have multiple database snapshots , And can be hosted as a database in a SQL Server In the example . The database snapshot is a read-only state , This also determines the use scenario of the snapshot , That's for reporting . Some misoperation data can also be quickly recovered by snapshot .

When a snapshot is created ,SQL Server A snapshot database of empty files will be created in the example , If you query data in the snapshot database , Will be redirected to the source database , So all the data returned are from the source database . If after creating a database snapshot , The original data of the source database has changed , The data before the change will be Copy A copy is written to the corresponding database snapshot blank file , At this time, the database snapshot has data , It's not all blank pages anymore , At this time, we will query again SQL Server Database snapshot , The query is the database in the database snapshot ( A copy of the original ). The size of the snapshot file increases with changes to the source database .  Be careful : Database snapshots are executed at the data page level . Before modifying the source database page for the first time , First copy the original page from the source database to the snapshot . The snapshot will store the original page , Keep a record of their data at the time of snapshot creation .  Repeat this process for each page you want to make the first change .

Two . To create a database snapshot SP

1. Time format function FormatDate

In the previous learning analysis , We know that a source database can have multiple snapshots , therefore , To distinguish multiple snapshots that exist at the same time , Our naming of snapshots is based on time ( That is, it contains the time element ), for example :

SS_DBName_19022311(2019 year 2 month 23 No 11 A snapshot of a point );SS_DBName_19022312(2019 year 2 month 23 No 12 A snapshot of a point );SS_DBName_19022313(2019 year 2 month 23 No 13 A snapshot of a point ). therefore , First write the time format function FormatDate. Besides , Snapshot to SS_ The beginning is to mark this object as a database snapshot , Different from other database objects , Convenient for operation and maintenance management ,SS For Snapshots The abbreviation for .

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. Creating a snapshot SP

(1) First, identify those DB You need to create a snapshot . This is from MirrorDB In the selection of , And ,IN() You can define multiple databases . The definition is as follows :

(2) Specify the number of database snapshots reserved

         

 (3) The specific establishment instruction code

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)     ---- Delete historical snapshot     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. Create a snapshot for easy access

above SP It's the establishment of a system named after time DB snapshot , Set up at different times , The name of the snapshot will be different . however , If DB Different names , It's very inconvenient for applications to call . So we also hope to create a database snapshot without time , Every time you create a snapshot of data, the name is the same . In this way, the front-end application does not need to modify the connection configuration of the database when accessing the database .

Here is SP This is to solve the above application scenario . The code names the snapshot of the database SS_DBName. In order to include the fusion front SP The function of , This SP And called the stored procedure directly 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

  The above code is to create an image DB Functions and stored procedures used by snapshot . Deploy in debug OK After , You can set Job 了 , Let it run automatically every hour .Job I don't want to talk about it any more , The core code is :

exec CreateSnapshotDB

3、 ... and . Synchronous subject 、 The account number between the image databases

The system database cannot be mapped , User name and password can't be synchronized to Mirror On the server . The user permissions of the snapshot are inherited from the source library , however MIrror There is no corresponding account information on the example . therefore , You need to go to the main database first (Principal Database) Export the user's account information on the Internet ( You can specify an account number ), And then print out SQL Sentence Copy to Mirror Just do it on the example .

Mainly used SP For sp_help_revlogin, But this SP Will call to sp_hexadecimal.

1. Build the foundation first 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. Set up a 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

 

Four . For snapshot principle, please refer to the following sharing

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://chowdera.com/2021/01/20210123164134666z.html