经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » 数据库/运维 » MS SQL Server » 查看文章
关于SQL Server 数据库归档的一些思考和改进
来源：cnblogs　　作者：东山絮柳仔　　时间：2018/12/25 9:24:38　　对本文有异议

一.需求背景

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

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

 二.主要架构

 

三.主要关联表

2.1 归档基础配置表

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

  1. CREATE TABLE [dbo].[DBData_ArchiveConfig](
  2.     [ID] [int] IDENTITY(1,1) NOT NULL,
  3.     [IP] [varchar](50) NULL,
  4.     [DBName] [varchar](50) NULL,
  5.     [DataTable] [varchar](50) NULL,
  6.     [TargetIP] [varchar](50) NULL,
  7.     [TargetDB] [varchar](50) NULL,
  8.     [TargetTable] [varchar](50) NULL,
  9.     [Prerequisite] [varchar](300) NULL,
  10.     [DelMaxQTY] [int] NULL,
  11.     [IsCheckOrderID] [int] NULL,
  12.     [SP_Name] [int] NULL,
  13.     [StartTime] [datetime] NULL,
  14.     [EndTime] [datetime] NULL
  15. ) ON [PRIMARY]
  16.  
  17. GO
  18.  
  19. 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'
  20. GO
  21.  
  22. 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'
  23. GO
  24.  
  25. 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'
  26. GO
  27.  
  28. 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'
  29. GO
  30.  
  31. 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'
  32. GO
  33.  
  34. 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'
  35. GO
  36.  
  37. 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'
  38. GO
  39.  
  40. 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'
  41. GO
  42.  
  43. 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'
  44. GO
  45.  
  46. 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'
  47. GO
  48.  
  49. 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'
  50. GO
  51.  
  52. 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'
  53. GO

2.2 归档运行的Log表

  1. CREATE TABLE [dbo].[DBData_ArchiveLog](
  2.     [ID] [int] IDENTITY(1,1) NOT NULL,
  3.     [IP] [varchar](30) NULL,
  4.     [DBName] [varchar](30) NULL,
  5.     [DataTable] [varchar](80) NULL,
  6.     [BakQTY] [varchar](30) NULL,
  7.     [BakStartDate] [datetime] NULL,
  8.     [BakEndDate] [datetime] NULL
  9. ) ON [PRIMARY]
  10.  
  11. GO

2.3 异常错误信息表

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

  1. CREATE TABLE [dbo].[DBData_ArchiveErrLog](
  2.     [ID] [int] IDENTITY(1,1) NOT NULL,
  3.     [IP] [varchar](30) NULL,
  4.     [DBName] [varchar](60) NULL,
  5.     [DataTable] [varchar](80) NULL,
  6.     [TargetIP] [varchar](30) NULL,
  7.     [TargetDB] [varchar](60) NULL,
  8.     [TargetTable] [varchar](80) NULL,
  9.     [Errormsg] [nvarchar](max) NULL,
  10.     [TransDateTime] [varchar](30) NULL
  11. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  12.  
  13. GO

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

  1. SET ANSI_NULLS ON
  2. GO
  3.  
  4. SET QUOTED_IDENTIFIER ON
  5. GO
  6.  
  7. -- =============================================
  8. -- Author:        <Author,,Name>
  9. -- Create date: <Create Date,,>
  10. -- Description:    <Description,,>
  11. -- =============================================
  12. CREATE PROCEDURE [dbo].[SP_XXXXX_DataArchive]
  13. AS
  14.     SET NOCOUNT ON;
  16.     DECLARE @sql1 VARCHAR(MAX) 
  17.     DECLARE @sql VARCHAR(MAX) 
  18.     DECLARE @sql2 VARCHAR(MAX)
  19.     DECLARE @IP VARCHAR(MAX) 
  20.     DECLARE @DBName VARCHAR(MAX) 
  21.     DECLARE @DataTable VARCHAR(MAX) 
  22.     DECLARE @TargetIP VARCHAR(MAX) 
  23.     DECLARE @TargetDB VARCHAR(MAX) 
  24.     DECLARE @TargetTable VARCHAR(MAX) 
  25.     DECLARE @Prerequisite VARCHAR(MAX) 
  26.     DECLARE @DelMaxQTY INT
  27.     DECLARE @StartTime DATETIME
  28.     DECLARE @EndTime DATETIME
  29.     DECLARE @qty INT 
  30.     DECLARE @ISCHECKORDERID INT 
  31. ----Carson   2018-12-17 备份数据的时间往往比删除的时间长3倍,因此,如果考虑将备份的操作转移到辅助库,将会对线上的操作影响降至更低
  32.     DECLARE @BakDateIP VARCHAR(30)  
  33.     set @BakDateIP='[XXX.XXX.XXX.XXX].'-------后面一定要有一个点
  34. --------------------------------------------------归档操作---------------------------------
  35.     DECLARE DBName CURSOR
  36.     FOR
  37.         SELECT  IP ,
  38.                 DBName ,
  39.                 DataTable ,
  40.                 TargetIP ,
  41.                 TargetDB ,
  42.                 TargetTable ,
  43.                 Prerequisite ,
  44.                 DelMaxQTY ,
  45.                 ISCHECKORDERID ,
  46.                 StartTime ,
  47.                 EndTime
  48.         FROM    [中央管理器].[中央管理数据库].[dbo].[DBData_ArchiveConfig]
  49.         WHERE   DataTable <> ''
  50.                 AND TargetTable <> ''
  51.                 AND DBNAME = 'XXXXXXXXX' and SP_Name='?????'
  52.     OPEN DBName    
  53.     FETCH NEXT FROM DBName INTO @IP, @DBName, @DataTable, @TargetIP, @TargetDB,
  54.         @TargetTable, @Prerequisite, @DelMaxQTY, @ISCHECKORDERID,
  55.         @StartTime, @EndTime   
  56.     WHILE ( @@fetch_status = 0 )
  57.     BEGIN  
  58.         DECLARE @datetime DATETIME
  59.         IF @ISCHECKORDERID <> '1'  AND @DataTable <> ''
  60.         BEGIN
  61.             SET @datetime = CONVERT(VARCHAR(10), GETDATE() - 30, 120)                
  62.             SET @sql = 'Insert into [' + @TargetIP + '].'
  63.                 + @TargetDB + '.' + 'dbo.' + @TargetTable + '
  64.                  select * FROM ' + @BakDateIP + @DBName + '.' + 'dbo.' + @DataTable + ' 
  65.                  with(nolock) where ' + @Prerequisite + ''
  66.                 
  67.             SET @sql1 = 'DECLARE @icount INTEGER  
  68.                         SELECT @icount = COUNT(1)  
  69.                         FROM ' + @BakDateIP + @DBName + '.' + 'dbo.' + @DataTable + '
  70.                         where ' + @Prerequisite + '  
  71.                         insert into [中央管理器].[中央管理数据库].dbo.DBData_ArchiveLog (IP, DBName, DataTable, BakQTY, BakStartDate, BakEndDate)
  72.                         select ''' + @IP + ''',''' + @DBName + ''',''' + @DataTable
  73.                                     + ''',@icount,getdate(),null
  75.                         WHILE @icount > 0   
  76.                         BEGIN  
  77.                     
  78.                             DELETE TOP (' + CAST(@DelMaxQTY AS VARCHAR(10)) + ')  
  79.                             FROM ' + @DBName + '.' + 'dbo.' + @DataTable + ' 
  80.                             where ' + @Prerequisite + '
  81.       
  82.                             SET @icount = @icount -('
  83.                                     + CAST(@DelMaxQTY AS VARCHAR(10)) + ')  
  84.                             WAITFOR DELAY ''00:00:01''  
  85.                         END  '                    
  86.           BEGIN TRY
  87.             EXEC (@sql)
  88.             EXEC (@sql1) 
  89.           END TRY
  90.           BEGIN CATCH
  91.              DECLARE @Errmsg AS nvarchar(MAX)
  92.              SELECT @Errmsg=ERROR_MESSAGE()
  93.                ------0001 BEGIN SAVE ERR LOG IN TABLE
  94.                INSERT INTO [中央管理器].[中央管理数据库].[dbo].DBData_ArchiveErrLog  ([IP] ,[DBName],[DataTable],[TargetIP],[TargetDB],[TargetTable],[Errormsg] ,[TransDateTime])
  95.                VALUES(@IP, @DBName, @DataTable, @TargetIP, @TargetDB, @TargetTable,@Errmsg,convert(VARCHAR(25),GETDATE(), 120))       
  96.                ------0001 END
  97.                 -------------0002 BEGIN SEND EMAIL MESSAGE----------------              
  98.                     DECLARE @Subject AS nvarchar(200)
  99.                     DECLARE @Body AS nvarchar(MAX)
  100.                     DECLARE @SPName AS nvarchar(MAX)
  101.             
  102.                     SET @Subject = '数据库归档异常 -重要!;ServerIP:' + @IP + ' DB:' + @DBName
  103.                                 SET @SPName = ''
  104.                                 SET @Body = '<html><body>Dear All,<br> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ServerIP:' +@IP + ' ; DataBase:' + @DBName+ '上的Table归档异常,请及时检查!!!
  105.                                <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;You can get detail information from the table. <br><br><table border=1 bgcolor=#aaff11>' 
  106.                                 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>'
  107.                                 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>
  108.                                 <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>'
  109.                                 SET @Body = @Body + @SPName + '</table>'
  110.  
  111.                     SET @BODY=REPLACE(@BODY,'''','')
  113.                     IF REPLACE(@BODY,' ','')<>''
  114.                         BEGIN
  115.                             DECLARE @AllEmailToAddress varchar(3000)=''
  116.                             DECLARE @AllEmailCcAddress varchar(3000)=''
  117.                             DECLARE @Allprofile_name varchar(100)=''
  118.                             SELECT @AllEmailToAddress=''
  119.                             SELECT @AllEmailCcAddress=''
  120.                             SELECT TOP 1 @Allprofile_name=NAME FROM msdb.dbo.sysmail_profile 
  121.                             ORDER BY profile_id
  123.                             EXEC msdb..sp_send_dbmail @profile_name = @Allprofile_name   -- profile 名称 
  124.                              ,@recipients   =  @AllEmailToAddress        -- 收件人邮箱 
  125.                              ,@copy_recipients=@AllEmailCcAddress
  126.                              ,@subject      =  @Subject                  -- 邮件标题 
  127.                              ,@body         =  @BODY                     -- 邮件内容 
  128.                              ,@body_format  =  'HTML'                    -- 邮件格式 
  129.                              ,@file_attachments=''
  130.                              ,@Importance = 'High'
  131.                         END     
  132.                       -------------    0002 end ------------            
  133.              END CATCH          
  134.             END
  135.  
  136.                 FETCH NEXT FROM DBName INTO @IP, @DBName, @DataTable, @TargetIP,
  137.                     @TargetDB, @TargetTable, @Prerequisite, @DelMaxQTY,
  138.                     @ISCHECKORDERID, @StartTime, @EndTime      
  139.         END
  140.         
  141.     CLOSE DBName 
  142.     DEALLOCATE DBName
  144.     DECLARE DELETETABLE CURSOR
  145.     FOR
  146.         SELECT  IP ,
  147.                 DBName ,
  148.                 DataTable ,
  149.                 TargetTable ,
  150.                 Prerequisite ,
  151.                 DelMaxQTY 
  152.         FROM    [中央管理器].[中央管理数据库].[dbo].[DBData_ArchiveConfig]
  153.         WHERE   DataTable <> ''
  154.                 AND TargetTable = ''
  155.                 AND DBNAME = 'XXXXXXXXX'  and SP_Name='????'
  156.     OPEN DELETETABLE  
  157.     FETCH NEXT FROM DELETETABLE INTO @IP, @DBName, @DataTable,
  158.         @TargetTable, @Prerequisite, @DelMaxQTY
  159.     WHILE ( @@fetch_status = 0 )
  160.         BEGIN
  161.           SET @sql1 = 'DECLARE @icount INTEGER  
  162.                                     SELECT @icount = COUNT(1)  
  163.                                     FROM ' + @DBName + '.' + 'dbo.' + @DataTable + '
  164.                                     where ' + @Prerequisite + '  
  165.                                     WHILE @icount > 0   
  166.                                     BEGIN  
  167.                     
  168.                                         DELETE TOP (' + CAST(@DelMaxQTY AS VARCHAR(10)) + ')  
  169.                                         FROM ' + @DBName + '.' + 'dbo.' + @DataTable + ' 
  170.                                         where ' + @Prerequisite + '
  171.       
  172.                                         SET @icount = @icount -('
  173.                                                 + CAST(@DelMaxQTY AS VARCHAR(10)) + ')  
  174.                                         WAITFOR DELAY ''00:00:01''  
  175.                                     END  '
  176.                         PRINT @sql1
  177.                         EXEC (@sql1)
  178.             FETCH NEXT FROM DELETETABLE INTO @IP, @DBName, @DataTable,@TargetTable, @Prerequisite, @DelMaxQTY
  179.         END 
  180.     CLOSE DELETETABLE 
  181.     DEALLOCATE DELETETABLE
  183. GO

五.补充数据

1.数据库归档，一般都是先将当前库的历史数据归档到历史库，再将当前库的历史数据删除。这两个阶段，一般是前者耗时较多（一般都在2:1以上），虽然可以在select 过程加上nolock，但是或者I/O或者网络等原因，其实这个阶段对应用程序的影响还是比较大的。所以，建议将这两个阶段物理分开，即如果有配置AlwaysOn，请将第一个阶段在辅助数据库中执行。上面的SP示例，就是通过参数 @BakDateIP 来实现了这一作用。

2.存储过程中包含了try...catch，所以运行此sp就会很少报错，某一个表的异常不会相互影响。例如，我们常见的当前库、历史库由于表结构变更而导致的不一致，此情况出现后，try..catch可以捕捉到异常，将异常记录在档，并将此信息以邮件的形式发送给指定人，但整个SP不会执行失败。并且还会跳过这一个异常，继续执行下一个备份归档表的归档。

 

本文版权归作者所有，未经作者同意不得转载,谢谢配合！！！

 友情链接：直通硅谷 　点职佳 　北美留学生论坛

MS SQL Server热门文章

  • [sql Server]除非另外还指定了TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效
  • SQL 拼接字符串 使用IN查询方法
  • Microsoft SQL Server 双机热备份，实时同步
  • SQL Server 2016 + AlwaysOn 无域集群
  • Microsoft SQL Server 2012安装说明
  • mssql sqlserver 不固定行转列数据(动态列)
  • 解釋 SQL Server 的狀態值 Runnable、Running、Suspended、Sleeping
  • sql server中format函数的yyyyMMddHHmmssffff时间格式兼容旧版sql写法
  • The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
  • SQL去除数据库表中tab、空格、回车符等特殊字符的解决方法

    • MS SQL Server推荐文章

  • 拯救SQL Server数据库事务日志文件损坏的终极大招
  • SQL Server实战七：自定义数据类型、标量值、内嵌表值、多语句表值函数的操作
  • SQL Server实战六：T-SQL、游标、存储过程的操作
  • SQL Server实战四：查询数据库的数据
  • 模拟SQLserver死锁现象
  • SqlServer的执行计划如何分析？
  • SQL Server实例间同步登录用户
  • SQL Server 2022新功能概览
  • SQL Server 2022 AlwaysOn新特性之包含可用性组介绍
  • 当 SQL Server（mssql-jdbc） 遇上 BigDecimal → 精度丢失，真坑！

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号