欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-25 08:24:18
一.需求背景 SQL Server开源的归档工具不多,DBA一般都是通过计划任务来触发执行,执行的脚本多是SP或者是SSIS包。SSIS包的性能稍好一些,但是维护更新成本高些。所以更常见的是通过SP脚本来实现归档操作。 当数据库规模较小时,可以方便的直接在数据库上进行脚本的编写部署。但是随着数据库越 ......

一.需求背景

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

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

 二.主要架构

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

三.主要关联表

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

 

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