关于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> serverip:' +@ip + ' ; database:' + @dbname+ '上的table归档异常,请及时检查!!! <br> 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不会执行失败。并且还会跳过这一个异常,继续执行下一个备份归档表的归档。
本文版权归作者所有,未经作者同意不得转载,谢谢配合!!!