一.需求背景
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不会执行失败。并且还会跳过这一个异常，继续执行下一个备份归档表的归档。
本文版权归作者所有，未经作者同意不得转载,谢谢配合！！！