MSSQL · 最佳實務 · 利用檔案組實現冷熱資料隔離備份方案

標籤：初始   tin   Database Backup還原   冷資料   導致   完成   利用   詳細介紹   自動成長   

摘要

在SQL Server備份專題分享中，前四期我們分享了：三種常見的Database Backup、備份策略的制定、如何尋找備份鏈以及資料庫的三種復原模式與備份之間的關係。本次月報我們分享SQL Server如何利用檔案組技術來實現資料庫冷熱資料隔離備份的方案。

情境引入

假設某公司有一個非常重要的超大的資料庫（超過10TB），面臨如下情境：

?該資料庫中儲存了近10年的使用者支付資訊（payment），非常重要

?每年的資料Archive Storage在年表中，曆史年表中的資料唯讀不寫（曆史payment資訊無需再修改），只有當前年表資料既讀又寫

?每次資料庫全備耗時太長，超過20小時；資料庫還原操作耗時更長，超過30小時

如何最佳化設計這個資料庫以及備份恢複系統，可以使得備份、還原更加高效？

檔案組簡介

檔案組的詳細介紹不是本次分享的重點，但是作為本文介紹的核心技術，有必要對其優點、建立以及使用方法來簡單介紹SQL Server中的檔案組。

使用檔案組的優點

SQL Server支援將表、索引資料存放到非Primary檔案組，這樣當資料庫擁有多個檔案組時就具備了如下好處：

?分散I/O壓力到不同的檔案組上，如果不同檔案組的檔案位於不同的磁碟的話，可以分散磁碟壓力。

?針對不同的檔案組進行DBCC CHECKFILEGROUP操作，並且同一個資料庫可以多個進程平行處理，減少大資料維護時間。

?可以針對檔案組層級進行備份與還原操作，更細粒度控製備份和還原策略。

建立資料庫時建立檔案組

我們可以在建立資料庫時直接建立檔案組，代碼如下：

USE masterGOEXEC sys.xp_create_subdir ‘C:\SQLServer\Data\‘EXEC sys.xp_create_subdir ‘C:\SQLServer\Logs\‘CREATE DATABASE [TestFG] ON  PRIMARY ( NAME = N‘TestFG‘, FILENAME = N‘C:\SQLServer\Data\TestFG.mdf‘ , SIZE = 5MB ,FILEGROWTH = 50MB ),  FILEGROUP [FG2010] ( NAME = N‘FG2010‘, FILENAME = N‘C:\SQLServer\Data\FG2010.ndf‘ , SIZE = 5MB ,FILEGROWTH = 50MB ),  FILEGROUP [FG2011] ( NAME = N‘FG2011‘, FILENAME = N‘C:\SQLServer\Data\FG2011.ndf‘ , SIZE = 5MB ,FILEGROWTH = 50MB ),  FILEGROUP [FG2012] ( NAME = N‘FG2012‘, FILENAME = N‘C:\SQLServer\Data\FG2012.ndf‘ , SIZE = 5MB ,FILEGROWTH = 50MB ) LOG ON ( NAME = N‘TestFG_log‘, FILENAME = N‘C:\SQLServer\Logs\TestFG_log.ldf‘ , SIZE = 5MB , FILEGROWTH = 50MB)GO

注意： 為了保證資料庫檔案組I/O的負載平衡能力，請將所有檔案的初始大小和自動成長參數保持一致，以保證輪詢調度分配演算法正常工作。

單獨建立建立組

如果資料庫已經存在，我們也同樣有能力添加檔案組，代碼如下：

--Add filegroup FG2013USE masterGOALTER DATABASE [TestFG] ADD FILEGROUP [FG2013];-- Add data file to FG2013ALTER DATABASE [TestFG]ADD FILE (NAME = FG2013, SIZE = 5MB , FILEGROWTH = 50MB ,FILENAME = N‘C:\SQLServer\Data\FG2013.ndf‘)TO FILEGROUP [FG2013]GOUSE [TestFG]GOSELECT * FROM sys.filegroups

最終檔案組資訊，展示如下： 

使用檔案組

檔案組建立完畢後，我們可以將表和索引放到對應的檔案組。比如： 將叢集索引放到PRIMARY檔案組；表和索引資料放到FG2010檔案組，代碼如下：

USE [TestFG]GOCREATE TABLE [dbo].[Orders_2010]([OrderID] [int] IDENTITY(1,1) NOT NULL,[OrderDate] [datetime] NULL,CONSTRAINT [PK_Orders_2010] PRIMARY KEY CLUSTERED ([OrderID] ASC) ON [PRIMARY]) ON [FG2010]GOCREATE NONCLUSTERED INDEX IX_OrderDateON [dbo].[Orders_2010] (OrderDate)ON [FG2010];

方案設計

檔案組的基本知識點介紹完畢後，根據情境引入中的內容，我們將利用SQL Server檔案組技術來實現冷熱資料隔離備份的方案設計介紹如下。

設計分析

由於payment資料庫過大，超過10TB，單次全量備份超過20小時，如果按照常規的完全備份，會導致備份檔案過大、耗時過長、甚至會因為備份操作對I/O能力的消耗影響到正常業務。我們仔細想想會發現，雖然資料庫本身很大，但是，由於只有當前年表資料會不斷變化（熱資料），曆史年表資料不會修改（冷資料），因此正真有資料變化操作的資料量相對整個庫來看並不大。那麼，我們將資料庫設計為曆史年表資料放到Read only的檔案組上，把當前年表資料放到Read write的檔案組上，備份系統僅僅需要備份Primary和當前年表所在的檔案組即可（當然首次還是需要對資料庫做一次性完整備份的）。這樣既可以大大節約備份對I/O能力的消耗，又實現了冷熱資料的隔離備份操作，還達到了分散了檔案的I/O壓力，最終達到資料庫設計和備份系統最佳化的目的，可謂一箭多雕。

以上文字分析，畫一個漂亮的設計圖出來，直觀展示如下： 

設計圖說明

以下對設計圖做詳細說明，以便對設計方案有更加直觀和深入理解。 整個資料庫包含13個檔案，包括：

?1個主檔案組（Primary File Group）：使用者存放資料庫系統資料表、視圖等對象資訊，檔案組可讀可寫。

?10個使用者自訂唯讀檔案組（User-defined Read Only File Group）：用於存放曆史年表的資料及相應索引資料，每一年的資料存放到一個檔案組中。

?1個使用者自訂可讀寫檔案組（User-defined Read Write File Group）：用於存放當前年表資料和相應索引資料，該表資料必須可讀可寫，所以檔案組必須可讀可寫。

?1個資料庫交易記錄檔：用於資料庫交易記錄，我們需要定期備份資料庫交易記錄。

方案實現

設計方案完成以後，接下來就是方案的集體實現了，具體實現包括：

?建立資料庫

?建立年表

?檔案組設定

?冷熱備份實現

建立資料庫

建立資料庫的同時，我們建立了Primary檔案組和2008 ~ 2017的檔案組，這裡需要特別提醒，請務必保證所有檔案組中檔案的初始大小和增長量相同，代碼如下：

USE masterGOEXEC sys.xp_create_subdir ‘C:\DATA\Payment\Data\‘EXEC sys.xp_create_subdir ‘C:\DATA\Payment\Log\‘CREATE DATABASE [Payment] ON  PRIMARY ( NAME = N‘Payment‘, FILENAME = N‘C:\DATA\Payment\Data\Payment.mdf‘ , SIZE = 5MB ,FILEGROWTH = 50MB ),  FILEGROUP [FGPayment2008] ( NAME = N‘FGPayment2008‘, FILENAME = N‘C:\DATA\Payment\Data\Payment_2008.ndf‘ , SIZE = 5MB ,FILEGROWTH = 50MB ),  FILEGROUP [FGPayment2009] ( NAME = N‘FGPayment2009‘, FILENAME = N‘C:\DATA\Payment\Data\Payment_2009.ndf‘ , SIZE = 5MB ,FILEGROWTH = 50MB ),  FILEGROUP [FGPayment2010] ( NAME = N‘FGPayment2010‘, FILENAME = N‘C:\DATA\Payment\Data\Payment_2010.ndf‘ , SIZE = 5MB ,FILEGROWTH = 50MB ),  FILEGROUP [FGPayment2011] ( NAME = N‘FGPayment2011‘, FILENAME = N‘C:\DATA\Payment\Data\Payment_2011.ndf‘ , SIZE = 5MB ,FILEGROWTH = 50MB ),  FILEGROUP [FGPayment2012] ( NAME = N‘FGPayment2012‘, FILENAME = N‘C:\DATA\Payment\Data\Payment_2012.ndf‘ , SIZE = 5MB ,FILEGROWTH = 50MB ),  FILEGROUP [FGPayment2013] ( NAME = N‘FGPayment2013‘, FILENAME = N‘C:\DATA\Payment\Data\Payment_2013.ndf‘ , SIZE = 5MB ,FILEGROWTH = 50MB ),  FILEGROUP [FGPayment2014]( NAME = N‘FGPayment2014‘, FILENAME = N‘C:\DATA\Payment\Data\Payment_2014.ndf‘ , SIZE = 5MB ,FILEGROWTH = 50MB ),  FILEGROUP [FGPayment2015] ( NAME = N‘FGPayment2015‘, FILENAME = N‘C:\DATA\Payment\Data\Payment_2015.ndf‘ , SIZE = 5MB ,FILEGROWTH = 50MB ),  FILEGROUP [FGPayment2016] ( NAME = N‘FGPayment2016‘, FILENAME = N‘C:\DATA\Payment\Data\Payment_2016.ndf‘ , SIZE = 5MB ,FILEGROWTH = 50MB ),  FILEGROUP [FGPayment2017] ( NAME = N‘FGPayment2017‘, FILENAME = N‘C:\DATA\Payment\Data\Payment_2017.ndf‘ , SIZE = 5MB ,FILEGROWTH = 50MB ) LOG ON ( NAME = N‘Payment_log‘, FILENAME = N‘C:\DATA\Payment\Log\Payment_log.ldf‘ , SIZE = 5MB , FILEGROWTH = 50MB)GO

考慮到每年我們都要添加新的檔案組到資料庫中，因此2018年的檔案組單獨建立如下：

--Add filegroup FGPayment2018USE masterGOALTER DATABASE [Payment] ADD FILEGROUP [FGPayment2018];-- Add data file to FGPayment2018ALTER DATABASE [Payment]ADD FILE (NAME = FGPayment2018, SIZE = 5MB , FILEGROWTH = 50MB ,FILENAME = N‘C:\DATA\Payment\Data\Payment_2018.ndf‘)TO FILEGROUP [FGPayment2018]GO

最終再次確認資料庫檔案組資訊，代碼如下：

USE [Payment]GOSELECT file_name = mf.name, filegroup_name = fg.name, mf.physical_name,mf.size,mf.growth FROM sys.master_files AS mfINNER JOIN sys.filegroups as fgON mf.data_space_id = fg.data_space_idWHERE mf.database_id = db_id(‘Payment‘)ORDER BY mf.type;

結果展示如所示： 

建立年表

資料庫以及相應檔案組建立完畢後，接下來我們建立對應的年表並插入一些測試資料，如下：

USE [Payment]GOCREATE TABLE [dbo].[Payment_2008]([Payment_ID] [bigint] IDENTITY(12008,100) NOT NULL,[OrderID] [bigint] NOT NULL,CONSTRAINT [PK_Payment_2008] PRIMARY KEY CLUSTERED ([Payment_ID] ASC) ON [FGPayment2008]) ON [FGPayment2008]GOCREATE NONCLUSTERED INDEX IX_OrderIDON [dbo].[Payment_2008] ([OrderID])ON [FGPayment2008];CREATE TABLE [dbo].[Payment_2009]([Payment_ID] [bigint] IDENTITY(12009,100) NOT NULL,[OrderID] [bigint] NOT NULL,CONSTRAINT [PK_Payment_2009] PRIMARY KEY CLUSTERED ([Payment_ID] ASC) ON [FGPayment2009]) ON [FGPayment2009]GOCREATE NONCLUSTERED INDEX IX_OrderIDON [dbo].[Payment_2009] ([OrderID])ON [FGPayment2009];--這裡省略了2010-2017的表建立，請參照以上建表和索引代碼，自行補充CREATE TABLE [dbo].[Payment_2018]([Payment_ID] [bigint] IDENTITY(12018,100) NOT NULL,[OrderID] [bigint] NOT NULL,CONSTRAINT [PK_Payment_2018] PRIMARY KEY CLUSTERED ([Payment_ID] ASC) ON [FGPayment2018]) ON [FGPayment2018]GOCREATE NONCLUSTERED INDEX IX_OrderIDON [dbo].[Payment_2018] ([OrderID])ON [FGPayment2018];

這裡需要特別提醒兩點：

?限於篇幅，建表代碼中省略了2010 - 2017表建立，請自行補充

?每個年表的Payment_ID欄位初始值是不一樣的，以免查詢所有payment資訊該欄位值存在重複的情況

其次，我們檢查所有年表的檔案組分布情況如下：

USE [Payment]GOSELECT table_name = tb.[name], index_name = ix.[name], located_filegroup_name = fg.[name] FROM sys.indexes ixINNER JOIN sys.filegroups fgON ix.data_space_id = fg.data_space_idINNER JOIN sys.tables tbON ix.[object_id] = tb.[object_id] WHERE ix.data_space_id = fg.data_space_idGO

查詢結果截取其中部分如下，我們看到所有年表及索引都按照我們的預期分布到對應的檔案組上去了。 

最後，為了測試，我們在對應年表中放入一些資料:

USE [Payment]GOSET NOCOUNT ONINSERT INTO [Payment_2008] SELECT 2008;INSERT INTO [Payment_2009] SELECT 2009;--省略掉2010 - 2017，自行補充INSERT INTO [Payment_2018] SELECT 2018;

檔案組設定

年表建立完完畢、測試資料初始化完成後，接下來，我們做檔案組讀寫屬性的設定，代碼如下：

USE masterGOALTER DATABASE [Payment] MODIFY FILEGROUP [FGPayment2008] READ_ONLY;ALTER DATABASE [Payment] MODIFY FILEGROUP [FGPayment2009] READ_ONLY;--這裡省略了2010 - 2017檔案組read only屬性的設定，請自行補充ALTER DATABASE [Payment] MODIFY FILEGROUP [FGPayment2018] READ_WRITE;

最終我們的檔案組讀寫屬性如下：

USE [Payment]GOSELECT name, is_default, is_read_only FROM sys.filegroupsGO

如下：

冷熱備份實現

所有檔案組建立成功，並且讀寫屬性配置完畢後，我們需要對資料庫可讀寫檔案組進行全量備份、差異備份和資料庫層級的記錄備份，為了方便測試，我們會在兩次備份之間插入一條資料。備份操作的大體思路是：

?首先，對整個資料庫進行一次性全量備份

?其次，對可讀寫檔案組進行周期性全量備份

?接下來，對可讀寫檔案組進行周期性差異備份

?最後，對整個資料庫進行周期性交易記錄備份

--Take a one time full backup of payment databaseUSE [master];GOBACKUP DATABASE [Payment]TO DISK = N‘C:\DATA\Payment\BACKUP\Payment_20180316_full.bak‘ WITH COMPRESSION, Stats=5;GO-- for testing, init one recordUSE [Payment];GOINSERT INTO [dbo].[Payment_2018] SELECT 201801;GO--Take a full backup for each writable filegoup (just backup FGPayment2018 as an example)BACKUP DATABASE [Payment]FILEGROUP = ‘FGPayment2018‘TO DISK = ‘C:\DATA\Payment\BACKUP\Payment_FGPayment2018_20180316_full.bak‘ WITH COMPRESSION, Stats=5;GO-- for testing, insert one recordINSERT INTO [dbo].[Payment_2018] SELECT 201802;GO--Take a differential backup for each writable filegoup (just backup FGPayment2018 as an example)BACKUP DATABASE [Payment]   FILEGROUP = N‘FGPayment2018‘   TO DISK = N‘C:\DATA\Payment\BACKUP\Payment_FGPayment2018_20180316_diff.bak‘   WITH DIFFERENTIAL, COMPRESSION, Stats=5 ;GO-- for testing, insert one recordINSERT INTO [dbo].[Payment_2018] SELECT 201803;GO-- Take a transaction log backup of database paymentBACKUP LOG [Payment]TO DISK = ‘C:\DATA\Payment\BACKUP\Payment_20180316_log.trn‘;GO

這樣備份的好處是，我們只需要對可讀寫的檔案組（FGPayment2018）進行完整和差異備份（Primary中包含系統對象，變化很小，實際情境中，Primary檔案組也需要備份），而其他的9個唯讀檔案組無需備份，因為資料不會再變化。如此，我們就實現了冷熱資料隔離備份的方案。 接下來的一個問題是，萬一Payment資料發生災難，導致資料損失，我們如何從備份組中將資料庫恢複出來呢？我們可以按照如下思路來恢複備份組：

?首先，還原整個資料庫的一次性全量備份

?其次，還原所有可讀寫檔案組最後一個全量備份

?接下來，還原可讀寫檔案組最後一個差異備份

?最後，還原整個資料庫的所有交易記錄備份

-- We restore full backupUSE masterGORESTORE DATABASE [Payment_Dev]FROM DISK=N‘C:\DATA\Payment\BACKUP\Payment_20180316_full.bak‘ WITHMOVE ‘Payment‘ TO ‘C:\DATA\Payment_Dev\Data\Payment_dev.mdf‘,MOVE ‘FGPayment2008‘ TO ‘C:\DATA\Payment_Dev\Data\FGPayment2008_dev.ndf‘,MOVE ‘FGPayment2009‘ TO ‘C:\DATA\Payment_Dev\Data\FGPayment2009_dev.ndf‘,MOVE ‘FGPayment2010‘ TO ‘C:\DATA\Payment_Dev\Data\FGPayment2010_dev.ndf‘,MOVE ‘FGPayment2011‘ TO ‘C:\DATA\Payment_Dev\Data\FGPayment2011_dev.ndf‘,MOVE ‘FGPayment2012‘ TO ‘C:\DATA\Payment_Dev\Data\FGPayment2012_dev.ndf‘,MOVE ‘FGPayment2013‘ TO ‘C:\DATA\Payment_Dev\Data\FGPayment2013_dev.ndf‘,MOVE ‘FGPayment2014‘ TO ‘C:\DATA\Payment_Dev\Data\FGPayment2014_dev.ndf‘,MOVE ‘FGPayment2015‘ TO ‘C:\DATA\Payment_Dev\Data\FGPayment2015_dev.ndf‘,MOVE ‘FGPayment2016‘ TO ‘C:\DATA\Payment_Dev\Data\FGPayment2016_dev.ndf‘,MOVE ‘FGPayment2017‘ TO ‘C:\DATA\Payment_Dev\Data\FGPayment2017_dev.ndf‘,MOVE ‘FGPayment2018‘ TO ‘C:\DATA\Payment_Dev\Data\FGPayment2018_dev.ndf‘,MOVE ‘Payment_log‘ TO ‘C:\DATA\Payment_Dev\Log\Payment_dev_log.ldf‘,NORECOVERY,STATS=5;GO-- restore writable filegroup full backupRESTORE DATABASE [Payment_Dev]   FILEGROUP = N‘FGPayment2018‘   FROM DISK = N‘C:\DATA\Payment\BACKUP\Payment_FGPayment2018_20180316_full.bak‘   WITH NORECOVERY,STATS=5;GO-- restore writable filegroup differential backupRESTORE DATABASE [Payment_Dev]   FILEGROUP = N‘FGPayment2018‘   FROM DISK = N‘C:\DATA\Payment\BACKUP\Payment_FGPayment2018_20180316_diff.bak‘   WITH NORECOVERY,STATS=5;GO-- restore payment database transaction log backupRESTORE LOG [Payment_Dev]FROM DISK = N‘C:\DATA\Payment\BACKUP\\Payment_20180316_log.trn‘WITH NORECOVERY;GO-- Take database oneline to checkRESTORE DATABASE [Payment_Dev] WITH RECOVERY;GO

最後檢查資料還原的結果，按照我們插入的測試資料，應該會有四條記錄。

USE [Payment_Dev]GOSELECT * FROM [dbo].[Payment_2018] WITH(NOLOCK)

展示執行結果，有四條結果集，符合我們的預期，如下：

最後總結

本篇月報分享了如何利用SQL Server檔案組技術來實現和最佳化冷熱資料隔離備份的方案，在大大提升Database Backup還原效率的同時，還提供了I/O資源的負載平衡，提升和最佳化了整個資料庫的效能。

原文連結

MSSQL · 最佳實務 · 利用檔案組實現冷熱資料隔離備份方案