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:\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 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:\SQLServer\Data\FG2013.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:\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;

结果展示如下图所示：

创建年表

数据库以及相应文件组创建完毕后，接下来我们创建对应的年表并插入一些测试数据，如下：

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:\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)

展示执行结果，有四条结果集，符合我们的预期，截图如下：

最后总结

本篇月报分享了如何利用SQL Server文件组技术来实现和优化冷热数据隔离备份的方案，在大大提升数据库备份还原效率的同时，还提供了I/O资源的负载均衡，提升和优化了整个数据库的性能。

MSSQL-最佳实践-利用文件组实现冷热数据隔离备份方案

author: 风移

摘要

场景引入

文件组简介

使用文件组的优点

创建数据库时创建文件组

单独创建创建组

使用文件组

方案设计

设计分析

设计图说明

方案实现

创建数据库

创建年表

文件组设置

冷热备份实现

最后总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MSSQL-最佳实践-利用文件组实现冷热数据隔离备份方案

author: 风移

摘要

场景引入

文件组简介

使用文件组的优点

创建数据库时创建文件组

单独创建创建组

使用文件组

方案设计

设计分析

设计图说明

方案实现

创建数据库

创建年表

文件组设置

冷热备份实现

最后总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景