关于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不会执行失败。并且还会跳过这一个异常，继续执行下一个备份归档表的归档。

 

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