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