MSSQL · 最佳實踐 · 利用文件組實現冷熱數據隔離備份方案

Galence 發布于2019-06-25 18:35 / 998人閱讀

摘要：本次月報我們分享如何利用文件組技術來實現數據庫冷熱數據隔離備份的方案。可以針對文件組級別進行備份和還原操作，更細粒度控制備份和還原策略。

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

摘要

在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:DATAPaymentBACKUPPayment_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資源的負載均衡，提升和優化了整個數據庫的性能。

原文鏈接

閱讀更多干貨好文，請關注掃描以下二維碼：

GPU云服務器云服務器數據倉庫最佳實踐最佳實踐云服務器文件數據備份方案 webrtc最佳實踐

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/17706.html

MSSQL實踐-數據庫備份加密

摘要：本期月報我們分享使用證書做數據庫備份加密的最佳實踐。加密差異備份數據庫差異備份加密，備份操作前，我們插入一條數據，以供后續的測試數據校驗。因為數據庫備份文件已經加密。摘要在SQL Server安全系列專題月報分享中，我們已經分享了：如何使用對稱密鑰實現SQL Server列加密技術、使用非對稱密鑰實現SQL Server列加密、使用混合密鑰實現SQL Server列加密技術、列加密...

CatalpaFlat 2019-06-21 16:56 評論0 收藏0
Docker 與 Mesos 的前生今世 | 數人云CTO肖德時@KVM分享實錄

摘要：今天小數給大家帶來一篇技術正能量滿滿的分享來自社區線上群分享的實錄，分享嘉賓是數人云肖德時。第二級調度由被稱作的組件組成。它們是最小的部署單元，由統一創建調度管理。今天小數給大家帶來一篇技術正能量滿滿的分享——來自KVM社區線上群分享的實錄，分享嘉賓是數人云CTO肖德時。嘉賓介紹：肖德時，數人云CTO 十五年計算機行業從業經驗，曾為紅帽 Engineering Service ...

0x584a 2019-06-28 15:09 評論0 收藏0
MSSQL - 最佳實踐 - 如何打碼隱私數據列

摘要：本期月報我們分享使用實現隱私數據列的打碼技術最佳實踐。最后總結本期月報我們分享了使用引入的新特性實現客戶數據打碼技術，防止未授權用戶查看導出用戶關鍵隱私數據，最大限度保證用戶數據安全性。摘要在SQL Server安全系列專題月報分享中，我們已經分享了：如何使用對稱密鑰實現SQL Server列加密技術、使用非對稱密鑰加密方式實現SQL Server列加密、使用混合密鑰實現SQL S...

BingqiChen 2019-06-21 16:53 評論0 收藏0