DBA日常管理——数据归档（Archiving-Data）

最新推荐文章于 2023-08-04 18:17:57 发布

置顶發糞塗牆

最新推荐文章于 2023-08-04 18:17:57 发布

阅读量1.2w

点赞数 2

分类专栏：数据库管理 DBA 企业管理文章标签： sql 管理 dba 数据归档

本文链接：https://blog.csdn.net/dba_huangzj/article/details/11143305

版权

数据库管理同时被 3 个专栏收录

277 篇文章 38 订阅

订阅专栏

DBA

186 篇文章 6 订阅

订阅专栏

企业管理

40 篇文章 1 订阅

订阅专栏

问题：

随着数据库越来越大，对性能及管理方面的挑战也会越来越大。每次查询可能需要查找更多的数据页，特别是当查询存在扫描操作时，会导致查询越来越慢。同时，需要备份的数据也会越来越多，备份操作持续越来越久。备份文件及数据库所使用的数据文件也会越来越大等等，一系列的问题都会随之产生。

此时数据归档就变成非常重要。数据归档和容量规划其实息息相关，不过容量规划将在别的文章中介绍。

数据归档的目标是监控数据的大小和增长速度，这里特别重要的是即使是小库，也要做好规划，因为你几乎无法保证小库以后会不会在未来的某个时间段快速增长或者变得非常重要，做好规划将会对未来的管理和优化都有非常重要的影响。另外，归档意味着数据需要保存，以便后续使用，也就是说，你不能直接删除。因为很多数据仓库需要这些数据，但是业务数据库并不总是需要保存，这时候又体现出归档的重要性。

另外，归档的好处正如开始所说的，减少备份文件的大小，加快数据库还原的速度和资源开销，减少管理数据库的开销，提高运行性能等等，整理数据时，也只需要整理活动数据即可。

总之，做好数据归档、容量规划是作为DBA或者管理数据库人员日常工作的重点任务之一。

思考：

1、定位哪些数据需要保留？

一般归档的数据都是有比较明显的时间列或者业务列，这一点能比较好地标识出需要处理的数据。但是具体还是需要根据业务需求而定。

2、如何在需要的时候能够访问这些数据？

根据归档的方式，访问数据有不同的方式，这个在下面会分别描述。

3、归档数据的安全性要求？

由于归档数据不再存在于对应的表甚至库甚至服务器上，所以在归档的时候要考虑好安全性问题，同时对归档数据的保存也要做好考虑。

解决方案：

对于上面的问题，有这几个需要考虑的地方：

如果数据必须保存在同一个数据库：

那么可以把需要归档的数据移到新的表，从实践上来说，归档表应该有一定的前缀或者后缀，以便日常使用。同时，把这些表放到一个独立的文件组中，因为这些文件几乎就只有“只读”的特性，所以放到一个文件组中，一方面，可以减少备份大小，你只需要使用文件备份或者部分备份功能，备份活动的数据所在的文件组即可。至于归档数据，只需要定期做一次备份即可。还原的时候也快。另外一方面，由于这部分的文件组是只读的，所以可以使用一些对于静态数据很有效的性能提高技术，如索引视图、列存储索引等，另外对于只读文件及文件组，不需要加锁，可以减少锁争用的问题。

但是需要注意如果你做一次完整备份，这部分数据还是会包含在备份文件里面，并没有减少文件大小，所以对于这类归档，通常建议使用部分备份或者文件备份。另外就是当需要访问这些数据的时候，一般都是使用视图，合并所需的数据然后展示，这种展示一般性能不会有很大的提升，因为视图不是用来提升性能的，同时在权限访问方面也要考虑，但是由于都在一个库，所以这方面的影响并不是非常明显。

对于这种情况的归档，2005及以后版本出现了一个非常有用的功能——表分区，通过表分区，可以在逻辑上不做任何改变，但是物理上已经分成了若干个区，理想情况下，数据操作可以仅仅发生在少数几个甚至一个区里面，配以分区索引，能够很好地提升I/O利用率。如果加上合适的数据压缩功能（记住不是收缩，2008才出现的功能），更能提升I/O利用率和降低空间使用率。

下面是示例操作，演示如何把不需要的数据移到新的文件组并做访问操作，本例子包含两种实现方式：

对于2000或者2005以上，但是不支持表分区的版本，如标准版，只能用这种方式。
是演示如何使用表分区来实现。

下面先演示不用表分区来实现：

1、创建文件组，并创建文件单独用来存放归档数据，本例使用示例数据库AdventureWorks2012中的表Sales.SalesOrderHeader，归档该表2006年的数据：

   1:  USE master

2:

   3:  GO

4:

   5:  ALTER DATABASE AdventureWorks2012

6:

   7:  ADD FILEGROUP Test1FG1;

8:

   9:  GO

10:

  11:  ALTER DATABASE AdventureWorks2012

12:

  13:  ADD FILE

14:

  15:  (

16:

  17:  NAME = test1dat3,

18:

  19:  FILENAME = 'D:\DB_Data\t1dat3.ndf',

20:

  21:  SIZE = 5MB,

22:

  23:  MAXSIZE = 100MB,

24:

  25:  FILEGROWTH = 5MB

26:

  27:  ),

28:

  29:  (

30:

  31:  NAME = test1dat4,

32:

  33:  FILENAME = 'D:\DB_Data\t1dat4.ndf',

34:

  35:  SIZE = 5MB,

36:

  37:  MAXSIZE = 100MB,

38:

  39:  FILEGROWTH = 5MB

40:

  41:  )

42:

  43:  TO FILEGROUP Test1FG1;

44:

  45:  GO

查看文件组及文件信息：

   1:  SELECT * FROM sys.filegroups

结果如下：

查看文件信息：

   1:  USE AdventureWorks2012

2:

   3:  GO

4:

   5:  SELECT file_guid,name,physical_name

6:

   7:  FROM sys.database_files

结果如下：

可以看到已经创建了两个文件了。

2、现在把需要归档的数据，在新文件组的文件中创建归档表，并插入：

   1:  USE AdventureWorks2012

2:

   3:  GO

4:

   5:  /*

6:

   7:  由于需要指定表所在的文件，所以不能用select * into 来创建表

8:

   9:  */

10:

  11:  IF OBJECT_ID(N'Sales.Ar_SalesOrderHeader','U') IS NULL

12:

  13:  CREATE TABLE Sales.Ar_SalesOrderHeader

14:

  15:  (

16:

  17:  [SalesOrderID] [INT] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,

18:

  19:  [RevisionNumber] [TINYINT] NOT NULL CONSTRAINT [DF_Arc_SalesOrderHeader_RevisionNumber] DEFAULT ((0)),

20:

  21:  [OrderDate] [DATETIME] NOT NULL CONSTRAINT [DF_Arc_SalesOrderHeader_OrderDate] DEFAULT (Getdate()),

22:

  23:  [DueDate] [DATETIME] NOT NULL,

24:

  25:  [ShipDate] [DATETIME] NULL,

26:

  27:  [Status] [TINYINT] NOT NULL CONSTRAINT [DF_Arc_SalesOrderHeader_Status] DEFAULT ((1)),

28:

  29:  [OnlineOrderFlag] [dbo].[FLAG] NOT NULL CONSTRAINT [DF_Arc_SalesOrderHeader_OnlineOrderFlag] DEFAULT ((1)),

30:

  31:  [SalesOrderNumber] AS ( Isnull(N'SO'

32:

  33:  + CONVERT([NVARCHAR](23), [SalesOrderID]), N'*** ERROR ***') ),

34:

  35:  [PurchaseOrderNumber] [dbo].[ORDERNUMBER] NULL,

36:

  37:  [AccountNumber] [dbo].[ACCOUNTNUMBER] NULL,

38:

  39:  [CustomerID] [INT] NOT NULL,

40:

  41:  [SalesPersonID] [INT] NULL,

42:

  43:  [TerritoryID] [INT] NULL,

44:

  45:  [BillToAddressID] [INT] NOT NULL,

46:

  47:  [ShipToAddressID] [INT] NOT NULL,

48:

  49:  [ShipMethodID] [INT] NOT NULL,

50:

  51:  [CreditCardID] [INT] NULL,

52:

  53:  [CreditCardApprovalCode] [VARCHAR](15) NULL,

54:

  55:  [CurrencyRateID] [INT] NULL,

56:

  57:  [SubTotal] [MONEY] NOT NULL CONSTRAINT [DF_Arc_SalesOrderHeader_SubTotal] DEFAULT ((0.00)),

58:

  59:  [TaxAmt] [MONEY] NOT NULL CONSTRAINT [DF_Arc_SalesOrderHeader_TaxAmt] DEFAULT ((0.00)),

60:

  61:  [Freight] [MONEY] NOT NULL CONSTRAINT [DF_Arc_SalesOrderHeader_Freight] DEFAULT ((0.00)),

62:

  63:  [TotalDue] AS ( Isnull(( [SubTotal] + [TaxAmt] ) + [Freight], ( 0 )) ),

64:

  65:  [Comment] [NVARCHAR](128) NULL,

66:

  67:  [rowguid] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Arc_SalesOrderHeader_rowguid] DEFAULT (Newid()),

68:

  69:  [ModifiedDate] [DATETIME] NOT NULL CONSTRAINT [DF_Arc_SalesOrderHeader_ModifiedDate] DEFAULT (Getdate()),

70:

71: CONSTRAINT [PK_Arc_SalesOrderHeader_SalesOrderID] PRIMARY KEY CLUSTERED ( [SalesOrderID] ASC )

  72:   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON

  73:  )

74:

  75:  ON [Test1FG1]

插入数据：

   1:  USE AdventureWorks2012 --使用目标数据库，特别是对于有自增列的表归档操作，更需要使用目标数据库

2:

   3:  GO

4:

   5:  SET IDENTITY_INSERT Sales.Ar_SalesOrderHeader ON;--由于这个表存在自增主键，所以需要使用这个SET选项

6:

   7:  INSERT INTO Sales.Ar_SalesOrderHeader

8:

   9:  ([SalesOrderID],

10:

  11:  [RevisionNumber],

12:

  13:  [OrderDate],

14:

  15:  [DueDate],

16:

  17:  [ShipDate],

18:

  19:  [Status],

20:

  21:  [OnlineOrderFlag],

22:

  23:  --[SalesOrderNumber],--这个列在表中是计算列，所以不用插入

24:

  25:  [PurchaseOrderNumber],

26:

  27:  [AccountNumber],

28:

  29:  [CustomerID],

30:

  31:  [SalesPersonID],

32:

  33:  [TerritoryID],

34:

  35:  [BillToAddressID],

36:

  37:  [ShipToAddressID],

38:

  39:  [ShipMethodID],

40:

  41:  [CreditCardID],

42:

  43:  [CreditCardApprovalCode],

44:

  45:  [CurrencyRateID],

46:

  47:  [SubTotal],

48:

  49:  [TaxAmt],

50:

  51:  [Freight],

52:

  53:  --[TotalDue],--这个列在表中是计算列，所以不用插入

54:

  55:  [Comment],

56:

  57:  [rowguid],

58:

  59:  [ModifiedDate])

60:

  61:  SELECT [SalesOrderID],

62:

  63:  [RevisionNumber],

64:

  65:  [OrderDate],

66:

  67:  [DueDate],

68:

  69:  [ShipDate],

70:

  71:  [Status],

72:

  73:  [OnlineOrderFlag],

74:

  75:  --[SalesOrderNumber],

76:

  77:  [PurchaseOrderNumber],

78:

  79:  [AccountNumber],

80:

  81:  [CustomerID],

82:

  83:  [SalesPersonID],

84:

  85:  [TerritoryID],

86:

  87:  [BillToAddressID],

88:

  89:  [ShipToAddressID],

90:

  91:  [ShipMethodID],

92:

  93:  [CreditCardID],

94:

  95:  [CreditCardApprovalCode],

96:

  97:  [CurrencyRateID],

98:

  99:  [SubTotal],

 100:

 101:  [TaxAmt],

 102:

 103:  [Freight],

 104:

 105:  --[TotalDue],

 106:

 107:  [Comment],

 108:

 109:  [rowguid],

 110:

 111:  [ModifiedDate]

 112:

 113:  FROM [Sales].[SalesOrderHeader]

 114:

 115:  WHERE CONVERT(NVARCHAR(30), OrderDate, 121) BETWEEN '2006-01-01 00:00:00.000' AND '2006-12-31 23:59:59.997'

 116:

 117:  SET IDENTITY_INSERT Sales.Ar_SalesOrderHeader OFF;

验证数据：

   1:  USE AdventureWorks2012

2:

   3:  GO

4:

   5:  SELECT * FROM Sales.Ar_SalesOrderHeader

6:

   7:  EXCEPT

8:

   9:  SELECT * FROM Sales.SalesOrderHeader

结果如下：

注意这里使用了2005才出现的EXCEPT集合运算符，如果有数据，证明有不一致的数据。没有数据证明两个表已经完全一样，对于2000，可以使用NOT EXISTS来实现。

3、删除源表对应数据，完成归档：

   1:  USE AdventureWorks2012

2:

   3:  GO

4:

   5:  DELETE FROM Sales.SalesOrderHeader

6:

   7:  WHERE CONVERT(NVARCHAR(30), OrderDate, 121) BETWEEN '2006-01-01 00:00:00.000' AND '2006-12-31 23:59:59.997'

4、我们可以看看现在归档表所在的文件组：

后续可以把这个文件组在非归档时间段设为只读即可，当然也可以不设置。备份还原可以把这部分忽略，对于备份还原方面的知识将放入其他文章中介绍。

现在来演示用表分区来归档：

这部分也将仅仅是演示而不做过多说明，关于分区的详细说明，将在别的文章中体现。使用分区的一大好处是不需要修改程序和查询语句，其他方式需要修改查询语句，使用视图或者加上union/union all。

1、检查需要查询的表，由于示例数据库中没有合适的表做演示，因为已经有分区在上面，所以新建一个表：

   1:  USE AdventureWorks2012

2:

   3:  GO

4:

   5:  DELETE FROM Sales.SalesOrderHeader

6:

   7:  WHERE CONVERT(NVARCHAR(30), OrderDate, 121) BETWEEN '2006-01-01 00:00:00.000' AND '2006-12-31 23:59:59.997'

2、检查表上的年份段：

   1:  SELECT DISTINCT DATEPART(yyyy,OrderDate)

2:

   3:  FROM Sales.SalesOrderHeader_Demo

4:

   5:  ORDER BY DATEPART(yyyy,OrderDate)

结果：

假设现在只需要查询当年的数据，这样我们可以用年来做分区的依据。

第一步：创建分区函数：

   1:  USE AdventureWorks2012

2:

   3:  GO

4:

   5:  /*

6:

   7:  注意：所有用户自定义数据类型、别名数据类型、时间戳、图像、XML、varchar(max)、nvarchar(max)及varbinary(max)都不能做为分区列

8:

   9:  这里使用OrderDate作为分区依据，但是分区函数中输入的是数据类型，而不是列名，详见联机丛书说明

10:

  11:  */

12:

  13:  CREATE PARTITION FUNCTION PFL_Years(DATETIME)

14:

  15:  AS RANGE LEFT --LFET代表包含VALUES中的值，也就是说这个例子中，第一个分区包含小于等于2005-12-31 23:59:59.997的数据，

16:

  17:  FOR VALUES('2005-12-31 23:59:59.997','2006-12-31 23:59:59.997','2007-12-31 23:59:59.997','2008-12-31 23:59:59.997','2009-12-31 23:59:59.997')

18:

  19:  GO

第二步：创建文件组，分区可以不创建，但是作为最佳实践，可以把归档数据放到新文件组中，即可分摊开销（同一个库的文件组放到不同的物理磁盘）、减少数据库损坏时，影响范围等等。将归档数据所在的文件组移到较低I/O的磁盘，活动数据所在的文件组移到高I/O的物理磁盘能带来性能上的提升。

   1:  USE [master]

2:

   3:  GO

4:

   5:  ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [CY00]

6:

   7:  GO

8:

   9:  ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [CY02]

10:

  11:  GO

12:

  13:  ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [CY04]

14:

  15:  GO

16:

  17:  ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [CY05]

18:

  19:  GO

20:

  21:  ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [CY06]

22:

  23:  GO

24:

  25:  ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [CY07]

26:

  27:  GO

创建文件到每个文件组中：

   1:  USE [master]

2:

   3:  GO

4:

   5:  ALTER DATABASE [AdventureWorks2012] ADD FILE ( NAME = N'test', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [CY00]

6:

   7:  GO

8:

   9:  ALTER DATABASE [AdventureWorks2012] ADD FILE ( NAME = N'test1', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test1.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [CY02]

10:

  11:  GO

12:

  13:  ALTER DATABASE [AdventureWorks2012] ADD FILE ( NAME = N'test2', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test2.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [CY04]

14:

  15:  GO

16:

  17:  ALTER DATABASE [AdventureWorks2012] ADD FILE ( NAME = N'test3', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test3.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [CY05]

18:

  19:  GO

20:

  21:  ALTER DATABASE [AdventureWorks2012] ADD FILE ( NAME = N'test4', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test4.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [CY06]

22:

  23:  GO

24:

  25:  ALTER DATABASE [AdventureWorks2012] ADD FILE ( NAME = N'test5', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test5.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [CY07]

26:

  27:  GO

第三步：创建分区方案：

   1:  USE AdventureWorks2012

2:

   3:  GO

4:

   5:  CREATE PARTITION SCHEME CYScheme

6:

   7:  AS

8:

   9:  PARTITION PFL_Years

10:

  11:  TO ([CY00],[CY02],[CY04],[CY05],[CY06],[CY07])

第四步：对目标表进行分区：

   1:  USE [AdventureWorks2012]

2:

   3:  GO

4:

   5:  BEGIN TRANSACTION

6:

   7:  CREATE CLUSTERED INDEX [ClusteredIndex_on_CYScheme_635139020318369500] ON [Sales].[SalesOrderHeader_Demo]

8:

   9:  (

10:

  11:  [OrderDate]

12:

  13:  )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [CYScheme]([OrderDate])

14:

  15:  DROP INDEX [ClusteredIndex_on_CYScheme_635139020318369500] ON [Sales].[SalesOrderHeader_Demo]

16:

  17:  COMMIT TRANSACTION

第五步：检查分区情况：

   1:  SELECT * FROM sys.partitions

2:

   3:  WHERE object_id=OBJECT_ID(N'[Sales].[SalesOrderHeader_Demo]')

至此，分区完毕，分区不是本文的重点，所以很多细节问题并没有说明白，只是告诉读者，可以用分区来实现数据归档。

如果数据没必要存在同一个数据库中：

对于这种情况，性能提升就比较明显了，可以把归档数据通过简单的查询移到新的库中对应的表里面，这个新库可以是同一台服务器也可以是在不同的服务器上，然后在原有的数据库上删除这些数据。这样处于活动状态的数据库上数据量就减少了，也实现了“瘦身”的效果。由于归档一般都是周期性发起，所以可以在两次归档期间，把归档库设为只读，减少访问这个库的开销，也不用经常对这个库做备份。由于活动库已经瘦身了，所以查询、管理方面的开销就能保持在一定程度上，不会随着时间的增长而明显增大。

和上面一样，在需要使用归档数据时，可以使用视图或者链接服务器来合并数据，但那是由于在不同的库甚至服务器上，所以需要额外添加权限，一般只读权限即可，不建议使用大权限角色。

示例：

1、定位好需要归档的数据，本例假设示例数据库AdventureWorks2012中的表Sales.SalesOrderHeader需要归档，这个表有明显的归档列：OrderDate，我们假设把2005年产生的订单归档（2012这个数据库包含了2005~2008的订单数据），需要先建一个归档库，然后创建一个一模一样的归档表：

   1:  USE master

2:

   3:  GO

4:

   5:  /*

6:

   7:  判断是否存在库，不存在才创建，记住不要使用“存在则删除”的逻辑，否则会把归档数据删掉

8:

   9:  */

10:

  11:  IF DB_ID(N'Arc_AdventureWorks2012') IS NULL

12:

  13:  BEGIN

14:

  15:  CREATE DATABASE Arc_AdventureWorks2012

16:

  17:  END

18:

  19:  /*

20:

  21:  判断表是否存在，不存在则创建，由于示例数据库并不是dbo架构，

22:

  23:  所以这里先创建一个架构，如果需要归档的表是dbo，那么没必要额外创建

24:

  25:  */

26:

  27:  USE Arc_AdventureWorks2012

28:

  29:  GO

30:

  31:  CREATE SCHEMA [Sales] AUTHORIZATION [db_owner]--这一步要单独执行

32:

  33:  GO

34:

  35:  IF OBJECT_ID(N'Sales.Arc_SalesOrderHeader') IS NULL

36:

  37:  BEGIN

38:

  39:  SELECT * INTO Sales.Arc_SalesOrderHeader FROM AdventureWorks2012.Sales.SalesOrderHeader WHERE 1=2 --创建表结构ENDND

40:

  41:  END

打开SSMS检查：

2、把活动库中的2005年数据查出来并插入归档库，验证插入成功后删除活动库中的数据：

   1:  USE Arc_AdventureWorks2012 --使用目标数据库，特别是对于有自增列的表归档操作，更需要使用目标数据库

2:

   3:  GO

4:

   5:  SET IDENTITY_INSERT [Sales].[Arc_SalesOrderHeader] ON;--由于这个表存在自增主键，所以需要使用这个SET选项

6:

   7:  INSERT INTO [Sales].[Arc_SalesOrderHeader]

8:

   9:  ([SalesOrderID],

10:

  11:  [RevisionNumber],

12:

  13:  [OrderDate],

14:

  15:  [DueDate],

16:

  17:  [ShipDate],

18:

  19:  [Status],

20:

  21:  [OnlineOrderFlag],

22:

  23:  [SalesOrderNumber],

24:

  25:  [PurchaseOrderNumber],

26:

  27:  [AccountNumber],

28:

  29:  [CustomerID],

30:

  31:  [SalesPersonID],

32:

  33:  [TerritoryID],

34:

  35:  [BillToAddressID],

36:

  37:  [ShipToAddressID],

38:

  39:  [ShipMethodID],

40:

  41:  [CreditCardID],

42:

  43:  [CreditCardApprovalCode],

44:

  45:  [CurrencyRateID],

46:

  47:  [SubTotal],

48:

  49:  [TaxAmt],

50:

  51:  [Freight],

52:

  53:  [TotalDue],

54:

  55:  [Comment],

56:

  57:  [rowguid],

58:

  59:  [ModifiedDate])

60:

  61:  SELECT [SalesOrderID],

62:

  63:  [RevisionNumber],

64:

  65:  [OrderDate],

66:

  67:  [DueDate],

68:

  69:  [ShipDate],

70:

  71:  [Status],

72:

  73:  [OnlineOrderFlag],

74:

  75:  [SalesOrderNumber],

76:

  77:  [PurchaseOrderNumber],

78:

  79:  [AccountNumber],

80:

  81:  [CustomerID],

82:

  83:  [SalesPersonID],

84:

  85:  [TerritoryID],

86:

  87:  [BillToAddressID],

88:

  89:  [ShipToAddressID],

90:

  91:  [ShipMethodID],

92:

  93:  [CreditCardID],

94:

  95:  [CreditCardApprovalCode],

96:

  97:  [CurrencyRateID],

98:

  99:  [SubTotal],

 100:

 101:  [TaxAmt],

 102:

 103:  [Freight],

 104:

 105:  [TotalDue],

 106:

 107:  [Comment],

 108:

 109:  [rowguid],

 110:

 111:  [ModifiedDate]

 112:

 113:  FROM AdventureWorks2012.[Sales].[SalesOrderHeader]

 114:

 115:  WHERE CONVERT(NVARCHAR(30), OrderDate, 121) BETWEEN '2005-01-01 00:00:00.000' AND '2005-12-31 23:59:59.997'

 116:

 117:  SET IDENTITY_INSERT [Sales].[Arc_SalesOrderHeader] OFF;

验证数据：

   1:  USE Arc_AdventureWorks2012

2:

   3:  GO

4:

   5:  SELECT *

6:

   7:  FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]

8:

   9:  WHERE CONVERT(NVARCHAR(30), OrderDate, 121) BETWEEN '2005-01-01 00:00:00.000' AND '2005-12-31 23:59:59.997'

10:

  11:  EXCEPT

12:

  13:  SELECT *

14:

  15:  FROM [Arc_AdventureWorks2012].[Sales].[Arc_SalesOrderHeader]

16:

  17:  WHERE CONVERT(NVARCHAR(30), OrderDate, 121) BETWEEN '2005-01-01 00:00:00.000' AND '2005-12-31 23:59:59.997'

结果如下：

验证完毕后，删除活动库的对应数据，注意选定的库，别使用了归档库：

   1:  USE AdventureWorks2012

2:

   3:  GO

4:

   5:  /*

6:

   7:  切记反复验证WHERE 条件和表名

8:

   9:  */

10:

  11:  DELETE FROM Sales.SalesOrderHeader

12:

  13:  WHERE CONVERT(NVARCHAR(30), OrderDate, 121)

14:

  15:  BETWEEN '2005-01-01 00:00:00.000' AND '2005-12-31 23:59:59.997'

16:

  17:  GO

18:

  19:  /*

20:

  21:  验证是否删除成功

22:

  23:  */

24:

  25:  IF (SELECT COUNT(1) FROM Sales.SalesOrderHeader

26:

  27:  WHERE CONVERT(NVARCHAR(30), OrderDate, 121)

28:

  29:  BETWEEN '2005-01-01 00:00:00.000' AND '2005-12-31 23:59:59.997')=0

30:

  31:  BEGIN

32:

  33:  SELECT '删除成功'

34:

  35:  END

到这步为止，数据已经归档成功，对于一些有外键关联或者业务关联的数据，需要做更多的操作，但是步骤和这里演示的类似。

3、使用归档数据，这里假设已经给与了在AdventureWorks2012上特定用户访问归档库对应表的权限，只需要创建视图来合并查询即可，当然也可以不用视图：

   1:  USE AdventureWorks2012

2:

   3:  GO

4:

   5:  /*

6:

   7:  创建视图

8:

   9:  */

10:

  11:  IF OBJECT_ID(N'Arc_View','V') IS NOT NULL

12:

  13:  DROP VIEW Arc_View

14:

  15:  GO

16:

  17:  CREATE VIEW Arc_View

18:

  19:  AS

20:

  21:  SELECT *

22:

  23:  FROM AdventureWorks2012.Sales.SalesOrderHeader

24:

  25:  UNION ALL

26:

  27:  SELECT *

28:

  29:  FROM Arc_AdventureWorks2012.Sales.Arc_SalesOrderHeader

30:

  31:  go

32:

  33:  /*

34:

  35:  查询视图

36:

  37:  */

38:

  39:  SELECT * FROM arc_view

40:

  41:  ORDER BY OrderDate

结果如下：

4、可以把实现的脚本修改一下，变成自动化，然后放入SQL Agent中定期执行，对于归档库，只需要在数据有改动的前后做一次备份即可。

数据没有保存的必要：

这中情况较为极端，但是并不少见，这部分由于在可见的未来并不一定会用到，所以可以把这些数据备份好，然后在活动数据库中删除，这里的备份可以是做一次完整备份，可以是把数据备份成文件（如文本文件）并保存。在需要的时候，导入即可。这种情况几乎不存在数据库内安全性的问题。

对于这种情况，就不做演示了，可以使用SSIS、BCP、SQLServer导入导出工具等，把需要删除的数据导出成文件，然后删除活动库中对应的数据。

总结：

上面的三种情况要根据实际情况而定，特别是业务要求。并没有什么哪个更好的说法。但是无论哪种方式，都需要做好数据的备份和保存，以免永久性丢失数据。并且上面三种情况均可使用代理作业来实现自动化操作。只需要定期检验和监控即可。

發糞塗牆

关注

2
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
DBA日常管理——数据归档（Archiving-Data）

原文出处：http://www.sqlnotes.cn/post/2013/09/05/DBA-Daily-Jobs-One%E2%80%94%E2%80%94-Archiving-Data均为本人博客问题：随着数据库越来越大，对性能及管理方面的挑战也会越来越大。每次查询可能需要查找更多的数据页，特别是当查询存在扫描操作时，会导致查询越来越慢。同时，需要备份的数据也会越来越多，备份操作
复制链接

扫一扫