當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

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

發布時間：2025/3/16 数据库 12 豆豆

生活随笔收集整理的這篇文章主要介紹了关于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]GOEXEC 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' GOEXEC 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' GOEXEC 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' GOEXEC 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' GOEXEC 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' GOEXEC 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' GOEXEC 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' GOEXEC 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' GOEXEC 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' GOEXEC 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' GOEXEC 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' GOEXEC 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 GOSET QUOTED_IDENTIFIER ON GO-- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE [dbo].[SP_XXXXX_DataArchive] ASSET 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 INTDECLARE @StartTime DATETIMEDECLARE @EndTime DATETIMEDECLARE @qty INT DECLARE @ISCHECKORDERID INT ----Carson 2018-12-17 備份數據的時間往往比刪除的時間長3倍,因此,如果考慮將備份的操作轉移到輔助庫,將會對線上的操作影響降至更低DECLARE @BakDateIP VARCHAR(30) set @BakDateIP='[XXX.XXX.XXX.XXX].'-------后面一定要有一個點 --------------------------------------------------歸檔操作---------------------------------DECLARE DBName CURSORFORSELECT IP ,DBName ,DataTable ,TargetIP ,TargetDB ,TargetTable ,Prerequisite ,DelMaxQTY ,ISCHECKORDERID ,StartTime ,EndTimeFROM [中央管理器].[中央管理數據庫].[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 DATETIMEIF @ISCHECKORDERID <> '1' AND @DataTable <> ''BEGINSET @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(),nullWHILE @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 TRYEXEC (@sql)EXEC (@sql1) END TRYBEGIN CATCHDECLARE @Errmsg AS nvarchar(MAX)SELECT @Errmsg=ERROR_MESSAGE()------0001 BEGIN SAVE ERR LOG IN TABLEINSERT 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:' + @DBNameSET @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,' ','')<>''BEGINDECLARE @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_idEXEC 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 ENDFETCH NEXT FROM DBName INTO @IP, @DBName, @DataTable, @TargetIP,@TargetDB, @TargetTable, @Prerequisite, @DelMaxQTY,@ISCHECKORDERID, @StartTime, @EndTime ENDCLOSE DBName DEALLOCATE DBNameDECLARE DELETETABLE CURSORFORSELECT 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, @DelMaxQTYWHILE ( @@fetch_status = 0 )BEGINSET @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 @sql1EXEC (@sql1)FETCH NEXT FROM DELETETABLE INTO @IP, @DBName, @DataTable,@TargetTable, @Prerequisite, @DelMaxQTYEND CLOSE DELETETABLE DEALLOCATE DELETETABLEGO

五.補充數據

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

總結

以上是生活随笔為你收集整理的关于SQL Server 数据库归档的一些思考和改进的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：面试官问我：解释一下Dubbo服务暴露
下一篇： bzoj1188: [HNOI2007]