关于SQL Server 数据库归档的一些思考和改进

作者: 东山絮柳仔

一.需求背景

SQL Server开源的归档工具不多,DBA一般都是通过计划任务来触发执行,执行的脚本多是SP或者是SSIS包。SSIS包的性能稍好一些,但是维护更新成本高些。所以更常见的是通过SP脚本来实现归档操作。

当数据库规模较小时,可以方便的直接在数据库上进行脚本的编写部署。但是随着数据库越来越多,管理维护成本就会越来越大,越来越不方便。现在们实行的方式是通过中央管理器来管理众多的数据库备份(这是在拥有专门的备份程序前的一个过渡方案)。们将归档基础配置信息、归档运行历史记录、异常报错等数据统一维护在中央数据库上。如此,可以方便统一的查看、管理和维护。

二.主要架构

三.主要关联表

2.1 归档基础配置表

表字段含义,请耐心查看字段说明。

CREATE TABLE [dbo].[DBData_ArchiveConfig](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IP] [varchar](50) NULL,
    [DBName] [varchar](50) NULL,
    [DataTable] [varchar](50) NULL,
    [TargetIP] [varchar](50) NULL,
    [TargetDB] [varchar](50) NULL,
    [TargetTable] [varchar](50) NULL,
    [Prerequisite] [varchar](300) NULL,
    [DelMaxQTY] [int] NULL,
    [IsCheckOrderID] [int] NULL,
    [SP_Name] [int] NULL,
    [StartTime] [datetime] NULL,
    [EndTime] [datetime] NULL
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Server IP(数据位于中央管理器中,所以归档数据库库所在的IP要维护,可维修虚拟的IP)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBData_ArchiveConfig', @level2type=N'COLUMN',@level2name=N'IP'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'要归档的数据库' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBData_ArchiveConfig', @level2type=N'COLUMN',@level2name=N'DBName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'要归档的表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBData_ArchiveConfig', @level2type=N'COLUMN',@level2name=N'DataTable'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备份指向的IP' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBData_ArchiveConfig', @level2type=N'COLUMN',@level2name=N'TargetIP'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备份指向的数据库' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBData_ArchiveConfig', @level2type=N'COLUMN',@level2name=N'TargetDB'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备份指向的表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBData_ArchiveConfig', @level2type=N'COLUMN',@level2name=N'TargetTable'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'归档条件' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBData_ArchiveConfig', @level2type=N'COLUMN',@level2name=N'Prerequisite'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'循环中一次归档删除的数据量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBData_ArchiveConfig', @level2type=N'COLUMN',@level2name=N'DelMaxQTY'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'此为 备用字段,考虑可能有些表,会和其他表关联' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBData_ArchiveConfig', @level2type=N'COLUMN',@level2name=N'IsCheckOrderID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'为提高并发度,一个DB对应的归档SP可能是多个,通过此列,进行分组。' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBData_ArchiveConfig', @level2type=N'COLUMN',@level2name=N'SP_Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'此为拓展字段,原计划根据 开始时间、结束时间,每天可以多个时间段内执行' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBData_ArchiveConfig', @level2type=N'COLUMN',@level2name=N'StartTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'此为拓展字段,原计划根据 开始时间、结束时间,每天可以多个时间段内执行' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBData_ArchiveConfig', @level2type=N'COLUMN',@level2name=N'EndTime'
GO

2.2 归档运行的Log表

CREATE TABLE [dbo].[DBData_ArchiveLog](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IP] [varchar](30) NULL,
    [DBName] [varchar](30) NULL,
    [DataTable] [varchar](80) NULL,
    [BakQTY] [varchar](30) NULL,
    [BakStartDate] [datetime] NULL,
    [BakEndDate] [datetime] NULL
) ON [PRIMARY]
GO

2.3 异常错误信息表

执行的过程中会外包一层 try…catch,将操作过程中的错误信息保存在表 DBData_ArchiveErrLog。表结构如下:

CREATE TABLE [dbo].[DBData_ArchiveErrLog](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IP] [varchar](30) NULL,
    [DBName] [varchar](60) NULL,
    [DataTable] [varchar](80) NULL,
    [TargetIP] [varchar](30) NULL,
    [TargetDB] [varchar](60) NULL,
    [TargetTable] [varchar](80) NULL,
    [Errormsg] [nvarchar](max) NULL,
    [TransDateTime] [varchar](30) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

四. 存储过程相应的主要代码

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SP_XXXXX_DataArchive]
AS
    SET NOCOUNT ON;
    DECLARE @sql1 VARCHAR(MAX) 
    DECLARE @sql VARCHAR(MAX) 
    DECLARE @sql2 VARCHAR(MAX)
    DECLARE @IP VARCHAR(MAX) 
    DECLARE @DBName VARCHAR(MAX) 
    DECLARE @DataTable VARCHAR(MAX) 
    DECLARE @TargetIP VARCHAR(MAX) 
    DECLARE @TargetDB VARCHAR(MAX) 
    DECLARE @TargetTable VARCHAR(MAX) 
    DECLARE @Prerequisite VARCHAR(MAX) 
    DECLARE @DelMaxQTY INT
    DECLARE @StartTime DATETIME
    DECLARE @EndTime DATETIME
    DECLARE @qty INT 
    DECLARE @ISCHECKORDERID INT 
----Carson   2018-12-17 备份数据的时间往往比删除的时间长3倍,因此,如果考虑将备份的操作转移到辅助库,将会对线上的操作影响降至更低
    DECLARE @BakDateIP VARCHAR(30)  
    set @BakDateIP='[XXX.XXX.XXX.XXX].'-------后面一定要有一个点
--------------------------------------------------归档操作---------------------------------
    DECLARE DBName CURSOR
    FOR
        SELECT  IP ,
                DBName ,
                DataTable ,
                TargetIP ,
                TargetDB ,
                TargetTable ,
                Prerequisite ,
                DelMaxQTY ,
                ISCHECKORDERID ,
                StartTime ,
                EndTime
        FROM    [中央管理器].[中央管理数据库].[dbo].[DBData_ArchiveConfig]
        WHERE   DataTable <> ''
                AND TargetTable <> ''
                AND DBNAME = 'XXXXXXXXX' and SP_Name='?????'
    OPEN DBName    
    FETCH NEXT FROM DBName INTO @IP, @DBName, @DataTable, @TargetIP, @TargetDB,
        @TargetTable, @Prerequisite, @DelMaxQTY, @ISCHECKORDERID,
        @StartTime, @EndTime   
    WHILE ( @@fetch_status = 0 )
    BEGIN  
        DECLARE @datetime DATETIME
        IF @ISCHECKORDERID <> '1'  AND @DataTable <> ''
        BEGIN
            SET @datetime = CONVERT(VARCHAR(10), GETDATE() - 30, 120)                
            SET @sql = 'Insert into [' + @TargetIP + '].'
                + @TargetDB + '.' + 'dbo.' + @TargetTable + '
                 select * FROM ' + @BakDateIP + @DBName + '.' + 'dbo.' + @DataTable + ' 
                 with(nolock) where ' + @Prerequisite + ''

            SET @sql1 = 'DECLARE @icount INTEGER  
                        SELECT @icount = COUNT(1)  
                        FROM ' + @BakDateIP + @DBName + '.' + 'dbo.' + @DataTable + '
                        where ' + @Prerequisite + '  
                        insert into [中央管理器].[中央管理数据库].dbo.DBData_ArchiveLog (IP, DBName, DataTable, BakQTY, BakStartDate, BakEndDate)
                        select ''' + @IP + ''',''' + @DBName + ''',''' + @DataTable
                                    + ''',@icount,getdate(),null
                        WHILE @icount > 0   
                        BEGIN  

                            DELETE TOP (' + CAST(@DelMaxQTY AS VARCHAR(10)) + ')  
                            FROM ' + @DBName + '.' + 'dbo.' + @DataTable + ' 
                            where ' + @Prerequisite + '

                            SET @icount = @icount -('
                                    + CAST(@DelMaxQTY AS VARCHAR(10)) + ')  
                            WAITFOR DELAY ''00:00:01''  
                        END  '                    
          BEGIN TRY
            EXEC (@sql)
            EXEC (@sql1) 
          END TRY
          BEGIN CATCH
             DECLARE @Errmsg AS nvarchar(MAX)
             SELECT @Errmsg=ERROR_MESSAGE()
               ------0001 BEGIN SAVE ERR LOG IN TABLE
               INSERT INTO [中央管理器].[中央管理数据库].[dbo].DBData_ArchiveErrLog  ([IP] ,[DBName],[DataTable],[TargetIP],[TargetDB],[TargetTable],[Errormsg] ,[TransDateTime])
               VALUES(@IP, @DBName, @DataTable, @TargetIP, @TargetDB, @TargetTable,@Errmsg,convert(VARCHAR(25),GETDATE(), 120))       
               ------0001 END
                -------------0002 BEGIN SEND EMAIL MESSAGE----------------              
                    DECLARE @Subject AS nvarchar(200)
                    DECLARE @Body AS nvarchar(MAX)
                    DECLARE @SPName AS nvarchar(MAX)

                    SET @Subject = '数据库归档异常 -重要!;ServerIP:' + @IP + ' DB:' + @DBName
                                SET @SPName = ''

                                SET @Body = '<html><body>Dear All,<br> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ServerIP:' +@IP + ' ; DataBase:' + @DBName+ '上的Table归档异常,请及时检查!!!


                               <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;You can get detail information from the table. <br><br><table border=1 bgcolor=aaff11>' 

                                SET @Body = @Body+ '<tr bgcolor=ff3311><td>ServerIP</td><td>DBName</td><td>TableName</td><td>TargetIP</td><td>TargetDB</td><td>Errmsg</td><td>TransDateTime</td></tr>'
                                SELECT  @SPName = @SPName + '<tr bgcolor=ffaa11><td>'+ CAST(@IP AS NVARCHAR(50))+ '</td><td>' + CAST(@DBName AS NVARCHAR(50)) + '</td><td>'+CAST(@DataTable AS NVARCHAR(50))+ '</td>
                                <td>'+ CAST(@TargetIP AS NVARCHAR(20))+ '</td><td>'+ CAST(@TargetDB AS NVARCHAR(50))+ '</td><td>'+ SUBSTRING(@Errmsg,1, 100)+ '</td><td>'+ CONVERT(varchar(100), GETDATE(), 21)+ '</td></tr>'
                                SET @Body = @Body + @SPName + '</table>'
                    SET @BODY=REPLACE(@BODY,'''','')
                    IF REPLACE(@BODY,' ','')<>''
                        BEGIN
                            DECLARE @AllEmailToAddress varchar(3000)=''
                            DECLARE @AllEmailCcAddress varchar(3000)=''
                            DECLARE @Allprofile_name varchar(100)=''
                            SELECT @AllEmailToAddress=''
                            SELECT @AllEmailCcAddress=''
                            SELECT TOP 1 @Allprofile_name=NAME FROM msdb.dbo.sysmail_profile 
                            ORDER BY profile_id
                            EXEC msdb..sp_send_dbmail @profile_name = @Allprofile_name   -- profile 名称 
                             ,@recipients   =  @AllEmailToAddress        -- 收件人邮箱 
                             ,@copy_recipients=@AllEmailCcAddress
                             ,@subject      =  @Subject                  -- 邮件标题 
                             ,@body         =  @BODY                     -- 邮件内容 
                             ,@body_format  =  'HTML'                    -- 邮件格式 
                             ,@file_attachments=''
                             ,@Importance = 'High'
                        END     
                      -------------    0002 end ------------            
             END CATCH          
            END
                FETCH NEXT FROM DBName INTO @IP, @DBName, @DataTable, @TargetIP,
                    @TargetDB, @TargetTable, @Prerequisite, @DelMaxQTY,
                    @ISCHECKORDERID, @StartTime, @EndTime      
        END

    CLOSE DBName 
    DEALLOCATE DBName
    DECLARE DELETETABLE CURSOR
    FOR
        SELECT  IP ,
                DBName ,
                DataTable ,
                TargetTable ,
                Prerequisite ,
                DelMaxQTY 
        FROM    [中央管理器].[中央管理数据库].[dbo].[DBData_ArchiveConfig]
        WHERE   DataTable <> ''
                AND TargetTable = ''
                AND DBNAME = 'XXXXXXXXX'  and SP_Name='????'
    OPEN DELETETABLE  
    FETCH NEXT FROM DELETETABLE INTO @IP, @DBName, @DataTable,
        @TargetTable, @Prerequisite, @DelMaxQTY
    WHILE ( @@fetch_status = 0 )
        BEGIN
          SET @sql1 = 'DECLARE @icount INTEGER  
                                    SELECT @icount = COUNT(1)  
                                    FROM ' + @DBName + '.' + 'dbo.' + @DataTable + '
                                    where ' + @Prerequisite + '  
                                    WHILE @icount > 0   
                                    BEGIN  

                                        DELETE TOP (' + CAST(@DelMaxQTY AS VARCHAR(10)) + ')  
                                        FROM ' + @DBName + '.' + 'dbo.' + @DataTable + ' 
                                        where ' + @Prerequisite + '

                                        SET @icount = @icount -('
                                                + CAST(@DelMaxQTY AS VARCHAR(10)) + ')  
                                        WAITFOR DELAY ''00:00:01''  
                                    END  '
                        PRINT @sql1
                        EXEC (@sql1)
            FETCH NEXT FROM DELETETABLE INTO @IP, @DBName, @DataTable,@TargetTable, @Prerequisite, @DelMaxQTY
        END 
    CLOSE DELETETABLE 
    DEALLOCATE DELETETABLE
GO

五.补充数据

1.数据库归档,一般都是先将当前库的历史数据归档到历史库,再将当前库的历史数据删除。这两个阶段,一般是前者耗时较多(一般都在2:1以上),虽然可以在select 过程加上nolock,但是或者I/O或者网络等原因,其实这个阶段对应用程序的影响还是比较大的。所以,建议将这两个阶段物理分开,即如果有配置AlwaysOn,请将第一个阶段在辅助数据库中执行。上面的SP示例,就是通过参数 @BakDateIP 来实现了这一作用。 2.存储过程中包含了try…catch,所以运行此sp就会很少报错,某一个表的异常不会相互影响。例如,们常见的当前库、历史库由于表结构变更而导致的不一致,此情况出现后,try..catch可以捕捉到异常,将异常记录在档,并将此信息以邮件的形式发送给指定人,但整个SP不会执行失败。并且还会跳过这一个异常,继续执行下一个备份归档表的归档。 本文版权归作者所有,未经作者同意不得转载,谢谢配合!!!

原文创作:东山絮柳仔

原文链接:https://www.cnblogs.com/xuliuzai/p/10168858.html

文章列表

更多推荐

更多
  • Flink | 八、flink中的专业名词 一、数据流图(Dataflow Graph),二、并行度(Parallelism),三、算子链(Operator Chain),四、作业图(JobGraph)与执行图(ExecutionGraph),五、任务(Tasks)和任务槽(Ta
  • Flink | 七、flink架构 一、系统架构,二、作业提交流程,1. 整体构成,2. 作业管理器JobManager,3. 任务管理器TaskManager,1. 高层级抽象视角,2. 独立模式Standalone,3. YARN 集群,1. JobMaster,2.
  • Flink | 三、简单项目 一、项目搭建,二、编写代码,1. 环境准备,2. 创建项目,1. 数据准备,2. 批处理,2. 流处理,2. 读取文本流,1. 创建工程,2. 添加项目依赖,3. 配置日志管理,1. 读取文本处理有界流,------- 工欲善其事,必
  • Flink | 六、flink部署模式 一、部署模式,二、独立模式Standalone,三、YARN 模式,四、K8S 模式,1. 会话模式Session Mode,2. 单作业模式PerJob Mode,3. 应用模式Application Mode,1. 会话模式部署,2
  • Flink | 四、flink单机和集群部署 一、单机部署,二、集群启动,1. 下载安装包,2.解压,3. 启动,4. 访问 Web UI,5. 关闭集群,1. 环境规划,2. 下载并解压安装包,3.修改集群配置,4.分发安装目录,5. 启动集群,6. 访问 Web UI,安装包就
  • Flink | 九、DataStream API之创建执行环境 一、程序构成,二、执行环境Execution Environment,1. 创建执行环境,2. 执行模式(Execution Mode),1. BATCH 模式的配置方法,3. 触发程序执行,1.getExecutionEnvironm
  • Flink | 一、接近flink 一、Flink 的源起和设计理念,二、Flink 的应用,三、流式数据处理的发展和演变,1. Flink 在企业中的应用,2. Flink 主要的应用场景,1. 流处理和批处理,2. 传统事务处理,3. 有状态的流处理,4. Lambd
  • Flink | 二、了解flink 一、Flink 的特性,二、Flink vs Spark,1. Flink 的核心特性,2. 分层 API,1. 数据处理架构,2. 数据模型和运行架构,3. Spark 还是 Flink?,- Flink 是第三代分布式流处理器,它的
  • Flink | 十五、DataStream API之转换算子 一、物理分区(Physical Partitioning),二、随机分区(shuffle),三、轮询分区(Round-Robin),四、重缩放分区(rescale),五、广播(broadcast),六、全局分区(global),新分布,
  • Flink | 十六、DataStream API之输出算子一 一、连接到外部系统,二、输出到文件,三、输出到 Kafka,四、输出到 Redis,image-b6404861d56244cc947bf286b90712e0.png) Flink 作为数据处理框架,最终还是要把计算处理的结果写入外部
  • 近期文章

    更多
    文章目录

      推荐作者

      更多