摘要：?摘要 在SQL Server備份專題分享中，前四期我們分享了：三種常見的數(shù)據(jù)庫備份、備份策略的制定、如何查找備份鏈以及數(shù)據(jù)庫的三種恢復(fù)模式與備份之間的關(guān)系。本次月報我們分享SQL Server如何利用文件組技術(shù)來實(shí)現(xiàn)數(shù)據(jù)庫冷熱數(shù)據(jù)隔離備份的方案。

摘要

在SQL Server備份專題分享中，前四期我們分享了：三種常見的數(shù)據(jù)庫備份、備份策略的制定、如何查找備份鏈以及數(shù)據(jù)庫的三種恢復(fù)模式與備份之間的關(guān)系。本次月報我們分享SQL Server如何利用文件組技術(shù)來實(shí)現(xiàn)數(shù)據(jù)庫冷熱數(shù)據(jù)隔離備份的方案。

場景引入

假設(shè)某公司有一個非常重要的超大的數(shù)據(jù)庫（超過10TB），面臨如下場景：

該數(shù)據(jù)庫中存儲了近10年的用戶支付信息（payment），非常重要

每年的數(shù)據(jù)歸檔存儲在年表中，歷史年表中的數(shù)據(jù)只讀不寫（歷史payment信息無需再修改），只有當(dāng)前年表數(shù)據(jù)既讀又寫

每次數(shù)據(jù)庫全備耗時太長，超過20小時；數(shù)據(jù)庫還原操作耗時更長，超過30小時

如何優(yōu)化設(shè)計(jì)這個數(shù)據(jù)庫以及備份恢復(fù)系統(tǒng)，可以使得備份、還原更加高效？

文件組簡介

文件組的詳細(xì)介紹不是本次分享的重點(diǎn)，但是作為本文介紹的核心技術(shù)，有必要對其優(yōu)點(diǎn)、創(chuàng)建以及使用方法來簡單介紹SQL Server中的文件組。

使用文件組的優(yōu)點(diǎn)

SQL Server支持將表、索引數(shù)據(jù)存放到非Primary文件組，這樣當(dāng)數(shù)據(jù)庫擁有多個文件組時就具備了如下好處：

分散I/O壓力到不同的文件組上，如果不同文件組的文件位于不同的磁盤的話，可以分散磁盤壓力。

針對不同的文件組進(jìn)行DBCC CHECKFILEGROUP操作，并且同一個數(shù)據(jù)庫可以多個進(jìn)程并行處理，減少大數(shù)據(jù)維護(hù)時間。

可以針對文件組級別進(jìn)行備份和還原操作，更細(xì)粒度控制備份和還原策略。

創(chuàng)建數(shù)據(jù)庫時創(chuàng)建文件組

我們可以在創(chuàng)建數(shù)據(jù)庫時直接創(chuàng)建文件組，代碼如下：

USE master

GO

EXEC 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

注意： 為了保證數(shù)據(jù)庫文件組I/O的負(fù)載均衡能力，請將所有文件的初始大小和自動增長參數(shù)保持一致，以保證輪詢調(diào)度分配算法正常工作。

單獨(dú)創(chuàng)建創(chuàng)建組

如果數(shù)據(jù)庫已經(jīng)存在，我們也同樣有能力添加文件組，代碼如下：

--Add filegroup FG2013USE master

GOALTER 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

最終文件組信息，展示如下：?

使用文件組

文件組創(chuàng)建完畢后，我們可以將表和索引放到對應(yīng)的文件組。比如： 將聚集索引放到PRIMARY文件組；表和索引數(shù)據(jù)放到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];

方案設(shè)計(jì)

文件組的基本知識點(diǎn)介紹完畢后，根據(jù)場景引入中的內(nèi)容，我們將利用SQL Server文件組技術(shù)來實(shí)現(xiàn)冷熱數(shù)據(jù)隔離備份的方案設(shè)計(jì)介紹如下。

設(shè)計(jì)分析

由于payment數(shù)據(jù)庫過大，超過10TB，單次全量備份超過20小時，如果按照常規(guī)的完全備份，會導(dǎo)致備份文件過大、耗時過長、甚至?xí)驗(yàn)閭浞莶僮鲗/O能力的消耗影響到正常業(yè)務(wù)。我們仔細(xì)想想會發(fā)現(xiàn)，雖然數(shù)據(jù)庫本身很大，但是，由于只有當(dāng)前年表數(shù)據(jù)會不斷變化（熱數(shù)據(jù)），歷史年表數(shù)據(jù)不會修改（冷數(shù)據(jù)），因此正真有數(shù)據(jù)變化操作的數(shù)據(jù)量相對整個庫來看并不大。那么，我們將數(shù)據(jù)庫設(shè)計(jì)為歷史年表數(shù)據(jù)放到Read only的文件組上，把當(dāng)前年表數(shù)據(jù)放到Read write的文件組上，備份系統(tǒng)僅僅需要備份Primary和當(dāng)前年表所在的文件組即可（當(dāng)然首次還是需要對數(shù)據(jù)庫做一次性完整備份的）。這樣既可以大大節(jié)約備份對I/O能力的消耗，又實(shí)現(xiàn)了冷熱數(shù)據(jù)的隔離備份操作，還達(dá)到了分散了文件的I/O壓力，最終達(dá)到數(shù)據(jù)庫設(shè)計(jì)和備份系統(tǒng)優(yōu)化的目的，可謂一箭多雕。

以上文字分析，畫一個漂亮的設(shè)計(jì)圖出來，直觀展示如下：?

設(shè)計(jì)圖說明

以下對設(shè)計(jì)圖做詳細(xì)說明，以便對設(shè)計(jì)方案有更加直觀和深入理解。 整個數(shù)據(jù)庫包含13個文件，包括：

1個主文件組（Primary File Group）：用戶存放數(shù)據(jù)庫系統(tǒng)表、視圖等對象信息，文件組可讀可寫。

10個用戶自定義只讀文件組（User-defined Read Only File Group）：用于存放歷史年表的數(shù)據(jù)及相應(yīng)索引數(shù)據(jù)，每一年的數(shù)據(jù)存放到一個文件組中。

1個用戶自定義可讀寫文件組（User-defined Read Write File Group）：用于存放當(dāng)前年表數(shù)據(jù)和相應(yīng)索引數(shù)據(jù)，該表數(shù)據(jù)必須可讀可寫，所以文件組必須可讀可寫。

1個數(shù)據(jù)庫事務(wù)日志文件：用于數(shù)據(jù)庫事務(wù)日志，我們需要定期備份數(shù)據(jù)庫事務(wù)日志。

方案實(shí)現(xiàn)

設(shè)計(jì)方案完成以后，接下來就是方案的集體實(shí)現(xiàn)了，具體實(shí)現(xiàn)包括：

創(chuàng)建數(shù)據(jù)庫

創(chuàng)建年表

文件組設(shè)置

冷熱備份實(shí)現(xiàn)

創(chuàng)建數(shù)據(jù)庫

創(chuàng)建數(shù)據(jù)庫的同時，我們創(chuàng)建了Primary文件組和2008 ~ 2017的文件組，這里需要特別提醒，請務(wù)必保證所有文件組中文件的初始大小和增長量相同，代碼如下：

USE master

GO

EXEC 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

考慮到每年我們都要添加新的文件組到數(shù)據(jù)庫中，因此2018年的文件組單獨(dú)創(chuàng)建如下：

--Add filegroup FGPayment2018USE master

GOALTER 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

最終再次確認(rèn)數(shù)據(jù)庫文件組信息，代碼如下：

USE [Payment]

GOSELECT 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_idWHERE mf.database_id = db_id('Payment')ORDER BY mf.type;

結(jié)果展示如下圖所示：?

創(chuàng)建年表

數(shù)據(jù)庫以及相應(yīng)文件組創(chuàng)建完畢后，接下來我們創(chuàng)建對應(yīng)的年表并插入一些測試數(shù)據(jù)，如下：

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的表創(chuàng)建，請參照以上建表和索引代碼，自行補(bǔ)充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];

這里需要特別提醒兩點(diǎn)：

限于篇幅，建表代碼中省略了2010 - 2017表創(chuàng)建，請自行補(bǔ)充

每個年表的Payment_ID字段初始值是不一樣的，以免查詢所有payment信息該字段值存在重復(fù)的情況

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

USE [Payment]

GOSELECT 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_idGO

查詢結(jié)果截取其中部分如下，我們看到所有年表及索引都按照我們的預(yù)期分布到對應(yīng)的文件組上去了。?

最后，為了測試，我們在對應(yīng)年表中放入一些數(shù)據(jù):

USE [Payment]

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

文件組設(shè)置

年表創(chuàng)建完完畢、測試數(shù)據(jù)初始化完成后，接下來，我們做文件組讀寫屬性的設(shè)置，代碼如下：

USE master

GOALTER DATABASE [Payment] MODIFY FILEGROUP [FGPayment2008] READ_ONLY;ALTER DATABASE [Payment] MODIFY FILEGROUP [FGPayment2009] READ_ONLY;--這里省略了2010 - 2017文件組read only屬性的設(shè)置，請自行補(bǔ)充ALTER DATABASE [Payment] MODIFY FILEGROUP [FGPayment2018] READ_WRITE;

最終我們的文件組讀寫屬性如下：

USE [Payment]

GOSELECT name, is_default, is_read_only FROM sys.filegroupsGO

截圖如下：

冷熱備份實(shí)現(xiàn)

所有文件組創(chuàng)建成功，并且讀寫屬性配置完畢后，我們需要對數(shù)據(jù)庫可讀寫文件組進(jìn)行全量備份、差異備份和數(shù)據(jù)庫級別的日志備份，為了方便測試，我們會在兩次備份之間插入一條數(shù)據(jù)。備份操作的大體思路是：

首先，對整個數(shù)據(jù)庫進(jìn)行一次性全量備份

其次，對可讀寫文件組進(jìn)行周期性全量備份

接下來，對可讀寫文件組進(jìn)行周期性差異備份

最后，對整個數(shù)據(jù)庫進(jìn)行周期性事務(wù)日志備份

--Take a one time full backup of payment database

USE [master];

GO

BACKUP DATABASE [Payment]

? ? TO DISK = N'C:\DATA\Payment\BACKUP\Payment_20180316_full.bak'

? ? WITH COMPRESSION, Stats=5;

GO

-- for testing, init one recordUSE [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:\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 payment

BACKUP LOG [Payment]TO DISK = 'C:\DATA\Payment\BACKUP\Payment_20180316_log.trn';

GO

這樣備份的好處是，我們只需要對可讀寫的文件組（FGPayment2018）進(jìn)行完整和差異備份（Primary中包含系統(tǒng)對象，變化很小，實(shí)際場景中，Primary文件組也需要備份），而其他的9個只讀文件組無需備份，因?yàn)閿?shù)據(jù)不會再變化。如此，我們就實(shí)現(xiàn)了冷熱數(shù)據(jù)隔離備份的方案。 接下來的一個問題是，萬一Payment數(shù)據(jù)發(fā)生災(zāi)難，導(dǎo)致數(shù)據(jù)損失，我們?nèi)绾螐膫浞菁袑?shù)據(jù)庫恢復(fù)出來呢？我們可以按照如下思路來恢復(fù)備份集：

首先，還原整個數(shù)據(jù)庫的一次性全量備份

其次，還原所有可讀寫文件組最后一個全量備份

接下來，還原可讀寫文件組最后一個差異備份

最后，還原整個數(shù)據(jù)庫的所有事務(wù)日志備份

-- We restore full backup

USE master

GO

RESTORE DATABASE [Payment_Dev]FROM DISK=N'C:\DATA\Payment\BACKUP\Payment_20180316_full.bak' WITH

MOVE '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 backup

RESTORE 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 backup

RESTORE 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 backup

RESTORE LOG [Payment_Dev]FROM DISK = N'C:\DATA\Payment\BACKUP\\Payment_20180316_log.trn'

WITH NORECOVERY;

GO

-- Take database oneline to check

RESTORE DATABASE [Payment_Dev] WITH RECOVERY;

GO

最后檢查數(shù)據(jù)還原的結(jié)果，按照我們插入的測試數(shù)據(jù)，應(yīng)該會有四條記錄。

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

展示執(zhí)行結(jié)果，有四條結(jié)果集，符合我們的預(yù)期，截圖如下：

最后總結(jié)

本篇月報分享了如何利用SQL Server文件組技術(shù)來實(shí)現(xiàn)和優(yōu)化冷熱數(shù)據(jù)隔離備份的方案，在大大提升數(shù)據(jù)庫備份還原效率的同時，還提供了I/O資源的負(fù)載均衡，提升和優(yōu)化了整個數(shù)據(jù)庫的性能。

原文鏈接

閱讀更多干貨好文，請關(guān)注掃描以下二維碼：?