SQL Server資料歸檔的解決方案

標籤：

　　最近新接到的一項工作是把SQL Server中儲存了四五年的陳年資料（合約，付款，報銷等等單據）進行歸檔，原因是每天的資料增量很大，而曆史資料又不經常使用，影響生產環境的資料查詢等操作。要求是：

 

　　1 歸檔的資料與生產環境資料分開儲存，以便提高查詢效率和伺服器效能。

　　2 前端使用者能夠查詢已歸檔的資料，即系統提供的功能不能發生改變

 

　　看起來要求不是很高，我自然會聯想到兩種方法，第一種建立一個與生產環境一樣的資料庫，把歸檔資料儲存到這個資料庫中；第二種在生產環境為每個表建立一個尾碼為_Archive的表，例如Invoice,  那麼就要建立一個Invoice_Archive表示存放歸檔的資料表。這兩種方法可以用跨資料庫訪問或視圖的方式，解決資料查詢等需求。仔細分析後，弊端是需要對現行系統進行改造，即需要修改代碼，以便適應對歸檔資料的訪問，實際也把資料訪問和業務操作給藕荷了，是一個費力又不討好的解決方案。

 

　　有沒有一種方法可以不修改系統能夠透明的訪問生產資料和歸檔資料呢？當然是有的，就是SQL Server提供的分區表。

　　在這裡就不累贅複述分區表的定義和作用了，要想精通就要認真讀微軟官方文檔：SQL Server 2005 中的分區表和索引。我濃縮的作用就是，通過使用分區表可以將資料表分割到不用的磁碟檔案中，不同的磁碟就意味著效能的提升，因為兩個磁頭讀取資料當然要比一個磁頭讀取資料快了，然後使用者可以透明地根據不同的訪問方式選取資料。舉個例子：一個合約表，有個欄位Archived標識是否歸檔（0代表未歸檔，1代表已歸檔），我們可以用分區表的方式，將合約表分成兩個表分別儲存在不同的磁碟，例如c和d, 當我們將一個合約設定為已歸檔，這條記錄就會從c盤轉到d盤，平時我們只查詢未歸檔的記錄，如果要查已歸檔的記錄，也只需要select * from Contracts where Archived = 1這麼簡單，即透明的查詢，具體的實現我們不用關心。

 

好了，不能光說不練，就驗證一下。建立兩個檔案目錄

　　

建立一個測試資料庫

USE Master;GOIF EXISTS (SELECT nameFROM sys.databasesWHERE name = N‘TestDB‘)DROP DATABASE TestDB;GOCREATE DATABASE TestDBON PRIMARY(NAME=‘TestDB_Part1‘,FILENAME=‘D:\TestData\Primary\TestDB_Part1.mdf‘,SIZE=10,MAXSIZE=100,FILEGROWTH=1 ),FILEGROUP TestDB_Part2(NAME = ‘TestDB_Part2‘,FILENAME =‘D:\TestData\Secondary\TestDB_Part2.ndf‘,SIZE = 10,MAXSIZE=100,FILEGROWTH=1 );GO

 

 查看資料屬性，有點不一樣

開啟資料：

use TestDB

 

建立分區函數，參數類型是bit，即已歸檔的資料

Create Partition Function TestDB_ArchivePartitionRange(bit) AS RANGE right FOR VALUES(1)

 

建立一個資料分割配置，即已經歸檔的資料儲存到TestDB_Part2分區檔案上

CREATE Partition Scheme TestDB_ArchivePatitionScheme AS PARTITION TestDB_ArchivePartitionRange TO ([PRIMARY], TestDB_Part2);

 

建立一個測試資料表，綁定一個資料分割配置

CREATE TABLE TestArchiveTable  (Archived Bit NOT NULL, Date DATETIME)  ON TestDB_ArchivePatitionScheme (Archived)

 

插入一些新的資料，已供測試

INSERT INTO TestArchiveTable (Archived, Date)  VALUES (0,‘2011-01-01‘);  INSERT INTO TestArchiveTable (Archived, Date)  VALUES (0,‘2011-02-01‘);  INSERT INTO TestArchiveTable (Archived, Date)  VALUES (0,‘2011-03-01‘); 

 

先來一個普通查詢

 

看看每個分區表存放資料的情況，分區一有3條記錄，分區2沒有記錄，即沒有歸檔資料

SELECT * FROM sys.partitions  WHERE OBJECT_NAME(OBJECT_ID)=‘TestArchiveTable‘; 

 

好了，我們歸檔一條記錄看看

update TestArchiveTable  set Archived = 1 where Date = ‘2011-03-01‘  SELECT * FROM sys.partitions  WHERE OBJECT_NAME(OBJECT_ID)=‘TestArchiveTable‘; 

 

結果就是我們想要的。

 

總結：利用分區表不僅能大幅提升資料訪問效能，而且可以根據需要分別儲存資料到不同的檔案，方便我們有效地利用資料，簡化系統開發的複雜性。

SQL Server資料歸檔的解決方案