關於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不會執行失敗。並且還會跳過這一個異常，繼續執行下一個備份歸檔表的歸檔。

本文版權歸作者所有

好文要頂 關注我 收藏該文

作者：東山絮柳仔

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

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！