百度360必应搜狗淘宝本站头条
mqtt服务器xftp6mybatiscollectionkeyerrorc#var
当前位置：网站首页 > 热门文章 > 正文

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

bigegpt 2024-08-04 11:39 3 浏览

一.需求背景

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

本文版权归作者所有

好文要顶 关注我 收藏该文

作者：东山絮柳仔

原文：https://www.cnblogs.com/xuliuzai/p/10168858.html

相关推荐

科氪 | 华硕天选6系列正式发布，搭载满功耗RTX 5060实际到手5999元起

5月19日晚，华硕旗下潮玩新次元游戏本天选6系列正式发布。作为Z世代青年的潮酷游戏装备，天选6系列再一次印证了其出色的综合实力。搭载满功耗RTX5060笔记本电脑GPU的天选6Pro以及天选6皆...

最新历史最低价显卡汇总!低端高端一网打尽

文|宋金戈责编|吕东兴总编|唐迪近期开展的618大促，各个品类尤其是数码科技好物的产品优惠都诚意满满，而作为消费者最关心的大类之一，显卡在最近同样表现不俗——华硕、技嘉等多个品牌，从1660s...

DNF:南山必胜客“败诉”，TX被DD373实锤，谁说玩家只有使用权?

TX和DD373的官司终于落下帷幕，南山必胜客终于中断了传奇。TX这一次被判定败诉。其中关于游戏账号和虚拟财产的问题，最受玩家关注。之前TX所说的：游戏账号不是玩家私有财产，游戏币更不是，只有使用权！...

Switch最新SX OS破解系统发布:可运行国行卡带xci镜像了

本周，TX团队（Team-Xecuter）发布了SXOS自制系统的最新版本，v2.9.5Beta，该版本支持了任天堂最新的V10.0.0和v10.0.1固件，同时改善了兼容性，优化了本地联机的稳定...

神舟战神TX8R5 QHD游戏本电脑今晚开售:2.5K屏+RTX4060，6999元

IT之家4月9日消息，神舟战神TX8R5QHD游戏本电脑今晚12点正式开售，仅有16GB+512GB一个版本可选，售价6999元。神舟战神TX8R5QHD搭载13代酷...

开创第三代手游商业模式《武极天下》推无商城玩法

翻阅游戏论坛与贴吧，大家对网络游戏的一些“坑爹”的设定抱怨不已，不是游戏职业设定不合理，就是游戏太烧钱，在游戏中被碾压的抬不起头，最后从玩游戏变成了被游戏玩，俨然游戏本质完全变味。由巨人移动自主研发并...

真满血Gen5电竞存储神装!佰维X570 Pro天启高速固态硬盘测评

2025一开年，对于存储行业来说，就是速度狂飙的开局。各固态硬盘品牌PCIe5.0SSD纷纷亮相，开启了新一轮的性能PK。在2024年异常活跃的佰维科技，也于CES期间推出了佰维X570PRO...

Kingston FURY Renegade G5评测 最速PCIe5.0固态硬盘诞生

【ZOL中关村在线原创评测】说起KingstonFURYRenegade叛逆者的大名，相信PCDIY玩家都知道这是金士顿高端存储的代名词，尤其是内存产品给人印象尤为深刻。实际上它还有固态硬盘系列...

《武极天下》力挺苹果TestFlight创业界先河

关于革旧鼎新，有人说就像是摸石头过河，在摸索中前行。一场正真的创新改革会带来什么？在数字行业，苹果公司的每一个产品都代表着一个时代的开启，它独立的操作系统、顺应用户需求的体验设计，在十几年时间内收获了...

闪迪至尊超极速移动固态硬盘USB4版评测 超越SSD的三防猛兽

移动固态硬盘，凭借TB级的容量和远超传统移动机械硬盘几倍、十几倍的速度，当仁不让地成为高效移动存储的代名词，为4K/8K超高清内容创作、海量文件备份带来了超乎想象的效率提升。国际知名品牌SanDisk...

RTX5070笔记本有哪些:游戏笔记本电脑哪个牌子好?

RTX5070游戏本，个人其实不是很喜欢，对于新款显卡，要不就考虑RTX5060，要不就考虑12GB显存的RTX5070Ti这样好一些，从RTX5070显卡的游戏本的量，也能看出来，估计5060会更多...

破军天下安装指南详解 游戏安装方法介绍

破军天下游戏怎么正确安装?破军天下安装方法是什么?对于破军天下中的安装问题，今天安趣网小编就为各位玩家小伙伴来好好的讲解下，让我们一起来看看，破军天下怎么正确安装游戏的吧!希望大家喜欢!安卓系统(An...

从高性能轻薄本到硬核电竞本 华硕天选新品解锁全场景体验

来源：环球网【环球网科技综合报道】5月19日，华硕天选多款笔记本新品正式发布。其中，14英寸轻薄全能本标杆华硕天选Air2025正式发布，显卡升级至GeForceRTX5060笔记本电脑GPU...

笔记本无线网卡有哪几种 有必要对其升级吗?

无线网卡是很多朋友购买笔记本时容易忽视的部分，相比较处理器、内存、显卡和硬盘，无线网卡几乎不可选，而且很少有厂商提到自家产品采用的无线网卡型号。大家可能遇到过这种情况：为什么手机可以连接到5Ghz，而...

下班了才发现，你的手机里藏着5个定时炸弹!

各位打工人累了一天终于可以躺平刷手机了吧？且慢！工信部最新通知，有些App比加班还可怕！它们可能正在偷偷收集你的信息，比你的老板还要了解你！今天就让我们一起来扒一扒这些“职场内鬼“，看看你的手机里有没...

一周热门
最近发表
标签列表