---

title: MSSQL-最佳實踐-利用檔案組實現冷熱資料隔離備份方案

author: 風移

摘要

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

場景引入

假設某公司有一個非常重要的超大的資料庫（超過10TB），面臨如下場景：
該資料庫中儲存了近10年的使用者支付資訊（payment），非常重要
每年的資料歸檔儲存在年表中，歷史年表中的資料只讀不寫（歷史payment資訊無需再修改），只有當前年表資料既讀又寫
每次資料庫全備耗時太長，超過20小時；資料庫還原操作耗時更長，超過30小時
如何優化設計這個資料庫以及備份恢復系統，可以使得備份、還原更加高效？

檔案組簡介

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

使用檔案組的優點

SQL Server支援將表、索引資料存放到非Primary檔案組，這樣當資料庫擁有多個檔案組時就具備瞭如下好處：
分散I/O壓力到不同的檔案組上，如果不同檔案組的檔案位於不同的磁碟的話，可以分散磁碟壓力。
針對不同的檔案組進行DBCC CHECKFILEGROUP操作，並且同一個資料庫可以多個程式並行處理，減少大資料維護時間。
可以針對檔案組級別進行備份和還原操作，更細粒度控制備份和還原策略。

建立資料庫時建立檔案組

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

USE master
GO

EXEC sys.xp_create_subdir `C:SQLServerData`
EXEC sys.xp_create_subdir `C:SQLServerLogs`

CREATE DATABASE [TestFG]
 ON  PRIMARY 
( NAME = N`TestFG`, FILENAME = N`C:SQLServerDataTestFG.mdf` , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FG2010] 
( NAME = N`FG2010`, FILENAME = N`C:SQLServerDataFG2010.ndf` , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FG2011] 
( NAME = N`FG2011`, FILENAME = N`C:SQLServerDataFG2011.ndf` , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FG2012] 
( NAME = N`FG2012`, FILENAME = N`C:SQLServerDataFG2012.ndf` , SIZE = 5MB ,FILEGROWTH = 50MB )
 LOG ON 
( NAME = N`TestFG_log`, FILENAME = N`C:SQLServerLogsTestFG_log.ldf` , SIZE = 5MB , FILEGROWTH = 50MB)
GO

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

單獨建立建立組

如果資料庫已經存在，我們也同樣有能力新增檔案組，程式碼如下：

--Add filegroup FG2013
USE master
GO
ALTER DATABASE [TestFG] ADD FILEGROUP [FG2013];

-- Add data file to FG2013
ALTER DATABASE [TestFG]
ADD FILE (NAME = FG2013, SIZE = 5MB , FILEGROWTH = 50MB ,FILENAME = N`C:SQLServerDataFG2013.ndf`)
TO FILEGROUP [FG2013]
GO

USE [TestFG]
GO
SELECT * FROM sys.filegroups

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

使用檔案組

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

USE [TestFG]
GO
CREATE 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]
GO


CREATE NONCLUSTERED INDEX IX_OrderDate
ON [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 master
GO

EXEC sys.xp_create_subdir `C:DATAPaymentData`
EXEC sys.xp_create_subdir `C:DATAPaymentLog`

CREATE DATABASE [Payment]
 ON  PRIMARY 
( NAME = N`Payment`, FILENAME = N`C:DATAPaymentDataPayment.mdf` , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2008] 
( NAME = N`FGPayment2008`, FILENAME = N`C:DATAPaymentDataPayment_2008.ndf` , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2009] 
( NAME = N`FGPayment2009`, FILENAME = N`C:DATAPaymentDataPayment_2009.ndf` , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2010] 
( NAME = N`FGPayment2010`, FILENAME = N`C:DATAPaymentDataPayment_2010.ndf` , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2011] 
( NAME = N`FGPayment2011`, FILENAME = N`C:DATAPaymentDataPayment_2011.ndf` , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2012] 
( NAME = N`FGPayment2012`, FILENAME = N`C:DATAPaymentDataPayment_2012.ndf` , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2013] 
( NAME = N`FGPayment2013`, FILENAME = N`C:DATAPaymentDataPayment_2013.ndf` , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2014]
( NAME = N`FGPayment2014`, FILENAME = N`C:DATAPaymentDataPayment_2014.ndf` , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2015] 
( NAME = N`FGPayment2015`, FILENAME = N`C:DATAPaymentDataPayment_2015.ndf` , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2016] 
( NAME = N`FGPayment2016`, FILENAME = N`C:DATAPaymentDataPayment_2016.ndf` , SIZE = 5MB ,FILEGROWTH = 50MB ), 
 FILEGROUP [FGPayment2017] 
( NAME = N`FGPayment2017`, FILENAME = N`C:DATAPaymentDataPayment_2017.ndf` , SIZE = 5MB ,FILEGROWTH = 50MB )
 LOG ON 
( NAME = N`Payment_log`, FILENAME = N`C:DATAPaymentLogPayment_log.ldf` , SIZE = 5MB , FILEGROWTH = 50MB)
GO

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

--Add filegroup FGPayment2018
USE master
GO
ALTER DATABASE [Payment] ADD FILEGROUP [FGPayment2018];

-- Add data file to FGPayment2018
ALTER DATABASE [Payment]
ADD FILE (NAME = FGPayment2018, SIZE = 5MB , FILEGROWTH = 50MB ,FILENAME = N`C:DATAPaymentDataPayment_2018.ndf`)
TO FILEGROUP [FGPayment2018]
GO

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

USE [Payment]
GO
SELECT file_name = mf.name, filegroup_name = fg.name, mf.physical_name,mf.size,mf.growth 
FROM sys.master_files AS mf
    INNER JOIN sys.filegroups as fg
    ON mf.data_space_id = fg.data_space_id
WHERE mf.database_id = db_id(`Payment`)
ORDER BY mf.type;

結果展示如下圖所示：

建立年表

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

USE [Payment]
GO
CREATE 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]
GO

CREATE NONCLUSTERED INDEX IX_OrderID
ON [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]
GO

CREATE NONCLUSTERED INDEX IX_OrderID
ON [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]
GO

CREATE NONCLUSTERED INDEX IX_OrderID
ON [dbo].[Payment_2018]([OrderID])
ON [FGPayment2018];

這裡需要特別提醒兩點：
限於篇幅，建表程式碼中省略了2010 – 2017表建立，請自行補充
每個年表的Payment_ID欄位初始值是不一樣的，以免查詢所有payment資訊該欄位值存在重複的情況
其次，我們檢查所有年表的檔案組分佈情況如下：

USE [Payment]
GO
SELECT table_name = tb.[name], index_name = ix.[name], located_filegroup_name = fg.[name] 
FROM sys.indexes ix
    INNER JOIN sys.filegroups fg
    ON ix.data_space_id = fg.data_space_id
    INNER JOIN sys.tables tb
    ON ix.[object_id] = tb.[object_id] 
WHERE ix.data_space_id = fg.data_space_id
GO

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

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

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

檔案組設定

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

USE master
GO
ALTER 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]
GO
SELECT name, is_default, is_read_only FROM sys.filegroups
GO

截圖如下：

冷熱備份實現

所有檔案組建立成功，並且讀寫屬性配置完畢後，我們需要對資料庫可讀寫檔案組進行全量備份、差異備份和資料庫級別的日誌備份，為了方便測試，我們會在兩次備份之間插入一條資料。備份操作的大體思路是：
首先，對整個資料庫進行一次性全量備份
其次，對可讀寫檔案組進行週期性全量備份
接下來，對可讀寫檔案組進行週期性差異備份
最後，對整個資料庫進行週期性事務日誌備份

--Take a one time full backup of payment database
USE [master];
GO
BACKUP DATABASE [Payment]
    TO DISK = N`C:DATAPaymentBACKUPPayment_20180316_full.bak` 
    WITH COMPRESSION, Stats=5
;
GO

-- for testing, init one record
USE [Payment];
GO
INSERT 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:DATAPaymentBACKUPPayment_FGPayment2018_20180316_full.bak` 
    WITH COMPRESSION, Stats=5
;
GO

-- for testing, insert one record
INSERT 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:DATAPaymentBACKUPPayment_FGPayment2018_20180316_diff.bak`
   WITH DIFFERENTIAL, COMPRESSION, Stats=5
 ;
GO

-- for testing, insert one record
INSERT INTO [dbo].[Payment_2018] SELECT 201803;
GO

-- Take a transaction log backup of database payment
BACKUP LOG [Payment]
TO DISK = `C:DATAPaymentBACKUPPayment_20180316_log.trn`;
GO

這樣備份的好處是，我們只需要對可讀寫的檔案組（FGPayment2018）進行完整和差異備份（Primary中包含系統物件，變化很小，實際場景中，Primary檔案組也需要備份），而其他的9個只讀檔案組無需備份，因為資料不會再變化。如此，我們就實現了冷熱資料隔離備份的方案。
接下來的一個問題是，萬一Payment資料發生災難，導致資料損失，我們如何從備份集中將資料庫恢復出來呢？我們可以按照如下思路來恢復備份集：
首先，還原整個資料庫的一次性全量備份
其次，還原所有可讀寫檔案組最後一個全量備份
接下來，還原可讀寫檔案組最後一個差異備份
最後，還原整個資料庫的所有事務日誌備份

-- We restore full backup
USE master
GO
RESTORE DATABASE [Payment_Dev]
FROM DISK=N`C:DATAPaymentBACKUPPayment_20180316_full.bak` WITH
MOVE `Payment` TO `C:DATAPayment_DevDataPayment_dev.mdf`,
MOVE `FGPayment2008` TO `C:DATAPayment_DevDataFGPayment2008_dev.ndf`,
MOVE `FGPayment2009` TO `C:DATAPayment_DevDataFGPayment2009_dev.ndf`,
MOVE `FGPayment2010` TO `C:DATAPayment_DevDataFGPayment2010_dev.ndf`,
MOVE `FGPayment2011` TO `C:DATAPayment_DevDataFGPayment2011_dev.ndf`,
MOVE `FGPayment2012` TO `C:DATAPayment_DevDataFGPayment2012_dev.ndf`,
MOVE `FGPayment2013` TO `C:DATAPayment_DevDataFGPayment2013_dev.ndf`,
MOVE `FGPayment2014` TO `C:DATAPayment_DevDataFGPayment2014_dev.ndf`,
MOVE `FGPayment2015` TO `C:DATAPayment_DevDataFGPayment2015_dev.ndf`,
MOVE `FGPayment2016` TO `C:DATAPayment_DevDataFGPayment2016_dev.ndf`,
MOVE `FGPayment2017` TO `C:DATAPayment_DevDataFGPayment2017_dev.ndf`,
MOVE `FGPayment2018` TO `C:DATAPayment_DevDataFGPayment2018_dev.ndf`,
MOVE `Payment_log` TO `C:DATAPayment_DevLogPayment_dev_log.ldf`,
NORECOVERY,STATS=5;
GO

-- restore writable filegroup full backup
RESTORE DATABASE [Payment_Dev]
   FILEGROUP = N`FGPayment2018`
   FROM DISK = N`C:DATAPaymentBACKUPPayment_FGPayment2018_20180316_full.bak`
   WITH NORECOVERY,STATS=5;
GO

-- restore writable filegroup differential backup
RESTORE DATABASE [Payment_Dev]
   FILEGROUP = N`FGPayment2018`
   FROM DISK = N`C:DATAPaymentBACKUPPayment_FGPayment2018_20180316_diff.bak`
   WITH NORECOVERY,STATS=5;
GO

-- restore payment database transaction log backup
RESTORE LOG [Payment_Dev]
FROM DISK = N`C:DATAPaymentBACKUP\Payment_20180316_log.trn`
WITH NORECOVERY;
GO

-- Take database oneline to check
RESTORE DATABASE [Payment_Dev] WITH RECOVERY;
GO

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

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

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

最後總結

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