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

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

问题：

 

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

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

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

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

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

 

思考：

 

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

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

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

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

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

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

解决方案：

 

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

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

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

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

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

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

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

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'

结果如下：

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

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

   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导入导出工具等，把需要删除的数据导出成文件，然后删除活动库中对应的数据。

总结：

 

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

5

时间： 2024-11-03 12:50:29

DBA日常管理——数据归档（Archiving-Data）的相关文章

Oracle DBA 日常管理

oracle Oracle DBA 日常管理修改:2000/8/23作者:Thomas B. Cox,with Christine Choi目的:这篇文档有很详细的资料记录着对一个甚至更多的ORACLE 数据库每天的,每月的,每年的运行的状态的结果及检查的结果,在文档的附录中你将会看到所有检查,修改的SQL和PL/SQL 代码.修改笔记: 1.1 在Steve DeNunzio 的'existext.sql'中所识别的类型1.2 固定的类型1.3 加的Gnu Public License:在重建

sql server大数据归档

昨天做了个日常大数据归档,归档700W数据,表字段130左右,字段比较多,分享下! ---- 先禁用表的index 1.先获取需要禁用的索引 declare @tname varchar(100) set @tname='orders' select  'alter index '+' '+c.indexname+' '+'on'+' '+@tname+' '+'disable' from ( select * from ( SELECT OBJECT_NAME(i.OBJECT_ID) AS

java-如何在j2EE项目中采用Data Service 来管理数据 ：支持数据的统一管理

问题描述 如何在j2EE项目中采用Data Service 来管理数据 :支持数据的统一管理 java项目中如何使用Dada Service 来管理项目: 要达到的目的:1,可以从多个数据源中加载数据,提供提供访问数据的统一接口 2,可以支持丰富的查询方式 ,实现内部的转换通过sqlParser 来对支持多种sql就如同 Apache 的calcite 项目中貌似可以找到蛛丝马迹,但是有好多的技术不是很了解 如:linq4jsparksplunkcsv ...等,有没有关于dataservice

Oracle闪回（flashback）功能详解 闪回数据归档（Flashback Data Archive）

Oracle闪回(flashback)功能详解  闪回数据归档(Flashback Data Archive)                     > > > > > > > >           > > >                               >                                           >                                 

归档 (Archiving)应用的最佳实践

内容摘要 归档(Archiving)--由一系列策略.流程和技术所组成,慢慢的已经成为那些遵守法律.规范以及拥有复杂信息技术的公司所必需的业务.当公司面临着重要数据每年不断增长的状况时,而存储容量的管理技术瓶颈却并没有得到改善.并且公司还面临其他的挑战,比如为了确保遵循规范而作的记录保存以及法律诉讼过程中信息的披露,并且这些披露数据的保存,使得越来越多的数据被强制保存.结果是,一种全新的归档技术出现了.为了提高管理效率.保护原有投资以及灾难恢复,全新的数据存储.归档以及归档技术的管理使得归档成为

oracle 11g的闪回数据归档

Flashback Data Archive(闪回数据归档) UNDO表空间记录的回滚信息虽然可以提供回闪查询,但时间久了,这些信息会被覆盖掉,其实只要事务一提交,他们就变成可覆盖的对象了,所以经常在做回闪查询时,我们会因为找不到undo block而收到1555错误,11G里面引入了Flashback Data Archive ,他用于存储数据的所有改变,时间由你自己设定,消耗的是更多的磁盘空间,现在来看下这个特性. 一.创建闪回数据归档 1.为了创建闪回数据归档,必须拥有DBA角色或拥有系统

DRBD 的日常管理

DRBD是一个用软件实现的.无共享的.服务器之间镜像块设备内容的存储复制解决方案.DRBD是由内核模块和相关脚本而构成,用以构建高可用性的集群.对于在高可用集群的环境里,尽管DRBD磁盘资源被作为一种集群服务由集群管理组件接管,但对于DRBD的常用命令的掌握还是非常有必要的.本文描述了一些常用的DRBD日常管理命令,供大家参考. 有关DRBD的相关知识,可以参考: DRBD原理及特性概述 快速安装及部署DRBD 一.DRBD用户空间管理工具 drbdadm: 高层的 DRBD 程序管理套件工具.

《Windows Server 2012 Hyper-V虚拟化管理实践》——3.2　Hyper-V主机日常管理

3.2 Hyper-V主机日常管理 Hyper-V主机的日常管理任务主要是配置虚拟机环境.其中,创建任务(创建虚拟机.虚拟硬盘.软件).磁盘管理任务(编辑磁盘.检查磁盘)以及虚拟交换机管理器的使用将在专门的章节分别介绍,其他管理任务将在本节介绍. 3.2.1 Hyper-V主机管理菜单 Hyper-V主机的所有管理任务集成在管理菜单或者右侧窗格的"操作"面板中,两者完成的管理任务完全相同,管理员根据自己的操作习惯选择即可,如图3-9所示."操作"面板中,上半部分是H

《Windows Server 2012 Hyper-V虚拟化管理实践》一3.2　Hyper-V主机日常管理

text Hyper-V主机的日常管理任务主要是配置虚拟机环境.其中,创建任务(创建虚拟机.虚拟硬盘.软件).磁盘管理任务(编辑磁盘.检查磁盘)以及虚拟交换机管理器的使用将在专门的章节分别介绍,其他管理任务将在本节介绍. 3.2.1 Hyper-V主机管理菜单 Hyper-V主机的所有管理任务集成在管理菜单或者右侧窗格的"操作"面板中,两者完成的管理任务完全相同,管理员根据自己的操作习惯选择即可,如图3-9所示."操作"面板中,上半部分是Hyper-V主机管理功能列