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

来源:https://www.cnblogs.com/jewel0516/archive/2018/04/11/8795842.html
-Advertisement-
Play Games

文件組的基本知識點介紹完畢後，根據場景引入中的內容，我們將利用SQL Server文件組技術來實現冷熱數據隔離備份的方案設計介紹如下。 設計分析 由於payment資料庫過大，超過10TB，單次全量備份超過20小時，如果按照常規的完全備份，會導致備份文件過大、耗時過長、甚至會因為備份操作對I/O能力 ...


 

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

設計分析

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

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

設計圖說明

以下對設計圖做詳細說明，以便對設計方案有更加直觀和深入理解。 整個資料庫包含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:\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 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:\DATA\Payment\Data\Payment_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;

結果展示如下圖所示： 03.png

創建年表

資料庫以及相應文件組創建完畢後，接下來我們創建對應的年表並插入一些測試數據，如下：

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

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

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

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

截圖如下：

05.png

冷熱備份實現

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

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

其次，對可讀寫文件組進行周期性全量備份

接下來，對可讀寫文件組進行周期性差異備份

最後，對整個資料庫進行周期性事務日誌備份

--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 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:\DATA\Payment\BACKUP\Payment_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:\DATA\Payment\BACKUP\Payment_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:\DATA\Payment\BACKUP\Payment_20180316_log.trn';
GO

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

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

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

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

最後，還原整個資料庫的所有事務日誌備份

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

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

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

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

06.png

最後總結

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

閱讀原文


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 未能載入文件或程式集“ RevitAPIUI.dll”
    revit二次開發中遇到的問題 RevitAPIUI.dll 只能 Native Library 中執行； 脫離了Native Library，API是跑不起來的 。 檢查程式流程：登錄，配置，啟動revit。 在啟動revit之前不能執行RevitAPI.dll和RevitAPIUI.dll的相關 ...
  • mysql之外鍵
    本文內容： 什麼是外鍵 外鍵的增加 外鍵的修改和刪除 外鍵的約束模式 首發日期：2018-04-12 什麼是外鍵： 外鍵就是表中存在一個欄位指向另外一個表的主鍵，那麼這個欄位就可以稱為外鍵。 一張表可以有多個外鍵。 外鍵用於約束表與表之間的關係，可以說外鍵是表之間的映射關係，這個關係可以幫助我們處理... ...
  • 基礎SQL語句學習
    （註：sql對大小寫不敏感，附：命令行操作：cd 目錄名 可進入文件，cd .. 可返回上級木目錄） a) Select * from 表名（顯示表內所有數據） b) Select 屬性 from 表名 where 符合某條件（如id=’1’） c) Delete from 表名 where 屬性= ...
  • SQL Server數據類型轉換方法
    在SQL Server日常的函數、存儲過程和SQL語句中，經常會用到不同數據類型的轉換。在SQL Server有兩種數據轉換類型：一種是顯性數據轉換；另一種是隱性數據轉換。下麵分別對這兩種數據類型轉換進行簡要的說明： 1 顯式轉換 顯示轉換是將某種數據類型的表達式顯式轉換為另一種數據類型。常用的是C ...
  • mysql 存儲過程 實現數據同步
    資料庫 表 發生變化，需要把2.0的表數據 同步到3.0庫中去； -- 同步數據存儲過程執行 -- 更新留言舊表數據到新表數據中 /*DEFINER：Vector*/ drop procedure if exists p_for_feedback_message; create procedure ...
  • 通過觸發器記錄資料庫連接信息
    最近在梳理資料庫程式連接信息，這裡對腳本做個總結，希望對需要的同仁有所幫助 一、Oracle連接信息統計 sys_context函數使用方法以及含義如下： 二、SQLServer連接信息統計 三、MySQL連接信息統計 四、PG連接信息統計 ...
  • MSSQL sql server 2005/2008 row_number()函數應用之–刪除表中重覆記錄,只保留一條不重覆數據
    轉自:http://www.maomao365.com/?p=4942 下文主要講述：重覆數據只獲取一條的方法 row_number函數在資料庫中的功能是為每一行 按照一定的規則生成一個編號，我們常常利用這一屬性，對錶進行分頁操作，下文我們將講述採用 row_number函數刪除表中重覆數據行 ...
  • mysql之連接查詢、聯合查詢、子查詢
    本文內容： 連接查詢 聯合查詢 子查詢 from子查詢 where子查詢 exists子查詢 首發日期：2018-04-11 連接查詢： 連接查詢就是將多個表聯合起來查詢，連接查詢方式有內連接、外連接、自然連接、交叉連接。連接查詢使得可以同時查看多張表中數據。 內連接：有條件連接，多個表之間依據指定... ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類