關於SQL Server 資料庫歸檔的一些思考和改進

一.需求背景 SQL Server開源的歸檔工具不多，DBA一般都是通過計劃任務來觸發執行，執行的腳本多是SP或者是SSIS包。SSIS包的性能稍好一些，但是維護更新成本高些。所以更常見的是通過SP腳本來實現歸檔操作。當資料庫規模較小時，可以方便的直接在資料庫上進行腳本的編寫部署。但是隨著資料庫越 ...

一.需求背景

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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ServerIP:' +@IP + ' ; DataBase:' + @DBName+ '上的Table歸檔異常,請及時檢查!!!
                               <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;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