SQL Server 資料歸檔方案

標籤：解決方案   分區表   資料庫管理   ssis   bcp   

SQL Server 資料歸檔方案

 

目的

本文旨在從資料庫管理方面，提供將SQL Server大資料表歸檔的解決方案。可以作為新業務上線時進行方案設計的參考。

 

歸檔方案選型 方案一： 方案介紹

BCP匯出資料到本地目錄目錄後，遍曆目錄檔案BCP匯入到暫存資料表，再迴圈刪除源表資料。通過Insert into … Left Join …通過主鍵關聯暫存資料表和歸檔表排除存在的資料。（或通過2008及後續版本的Merge語句，不存在插入，存在更新）

650) this.width=650;" title="clip_image001" style="border-right-width:0px;border-bottom-width:0px;margin-left:0px;border-top-width:0px;margin-right:0px;" alt="clip_image001" src="http://s3.51cto.com/wyfs02/M01/53/97/wKioL1RsCqLjInSOAAFglcseqQ0907.jpg" height="526" width="1045" border="0" />

方案優缺點

優點：BCP效能好、通過SP容易控制邏輯、維護簡單

缺點：邏輯略繁瑣

 

方案二： 方案介紹

BCP匯出資料到遠程目錄後迴圈刪除源表資料，遍曆目錄檔案BCP匯入到暫存資料表，再通過Insert into … Left Join …通過主鍵關聯暫存資料表和歸檔表排除存在的資料。（或通過2008及後續版本的Merge語句，不存在插入，存在更新）

650) this.width=650;" title="clip_image002" style="border-right-width:0px;border-bottom-width:0px;border-top-width:0px;" alt="clip_image002" src="http://s3.51cto.com/wyfs02/M02/53/97/wKioL1RsCqbRNQdUAAFn9vtOELQ848.jpg" height="523" width="1040" border="0" />

方案優缺點

優點：BCP效能好、通過SP容易控制邏輯、維護簡單

缺點：邏輯略繁瑣，兩台主機需要能UNC共用訪問

 

方案三： 方案介紹

BCP匯出資料到本地目錄後FTP到遠程目錄，再迴圈刪除源表資料，遍曆目錄檔案BCP匯入到暫存資料表，再通過Insert into … Left Join …通過主鍵關聯暫存資料表和歸檔表排除存在的資料。（或通過2008及後續版本的Merge語句，不存在插入，存在更新）

650) this.width=650;" title="clip_image003" style="border-right-width:0px;border-bottom-width:0px;border-top-width:0px;" alt="clip_image003" src="http://s3.51cto.com/wyfs02/M00/53/97/wKioL1RsCqmDfPIOAAGRedk1F0o446.jpg" height="525" width="1042" border="0" />

方案優缺點

優點：BCP效能好、通過SP容易控制邏輯、維護簡單

缺點：邏輯略繁瑣，遠程要架設FTP伺服器

 

方案四： 方案介紹

BCP匯出資料到遠程目錄後迴圈刪除源表資料，遍曆目錄檔案SSIS資料匯入和更新。

650) this.width=650;" title="clip_image004" style="border-right-width:0px;border-bottom-width:0px;border-top-width:0px;" alt="clip_image004" src="http://s3.51cto.com/wyfs02/M01/53/97/wKioL1RsCquwNvakAAEqZXNNIh8071.jpg" height="523" width="1041" border="0" />

方案優缺點

優點：BCP效能好

缺點：欄位變更後SSIS包需同步修改，SSIS包維護麻煩，兩台主機需要能UNC共用訪問

 

方案五： 方案介紹

BCP匯出資料到本地目錄後FTP到遠程目錄，迴圈刪除源表資料，再遍曆目錄檔案SSIS資料匯入和更新。

650) this.width=650;" title="clip_image005" style="border-right-width:0px;border-bottom-width:0px;border-top-width:0px;" alt="clip_image005" src="http://s3.51cto.com/wyfs02/M02/53/97/wKioL1RsCqygff4zAAFG8gDgFk4778.jpg" height="525" width="1040" border="0" />

方案優缺點

優點：BCP效能好

缺點：欄位變更好，SSIS包需同步修改，SSIS包維護麻煩，遠程要架設FTP伺服器

 

方案六： 方案介紹

資料插入到另一個臨時歸檔庫後迴圈刪除源表資料，再將臨時歸檔表複製到遠程。

650) this.width=650;" title="clip_image006" style="border-right-width:0px;border-bottom-width:0px;border-top-width:0px;" alt="clip_image006" src="http://s3.51cto.com/wyfs02/M01/53/99/wKiom1RsCjyhU4HgAAEi-2LOpKQ419.jpg" height="525" width="1042" border="0" />

方案優缺點

優點：邏輯簡單

缺點：需要另建一個臨時歸檔庫、增加了負載

 

方案七： 方案介紹

資料通過Linkedserver插入遠程歸檔表後迴圈刪除。

650) this.width=650;" title="clip_image007" style="border-right-width:0px;border-bottom-width:0px;border-top-width:0px;" alt="clip_image007" src="http://s3.51cto.com/wyfs02/M00/53/99/wKiom1RsCkLjwWt0AADoPrjtGzs468.jpg" height="527" width="1041" border="0" />

方案優缺點

優點：邏輯簡單

缺點：LinkedServer遠程效能問題

 

方案八： 方案介紹

切換分區到暫存資料表，資料再通過LinkedServer插入遠程歸檔表。

650) this.width=650;" title="clip_image008" style="border-right-width:0px;border-bottom-width:0px;border-top-width:0px;" alt="clip_image008" src="http://s3.51cto.com/wyfs02/M01/53/99/wKiom1RsCkKA9-9CAAD3q0iN2f4877.jpg" height="524" width="1043" border="0" />

方案優缺點

優點：資料歸檔方便

缺點：只針對分區表

 

方案九： 方案介紹

SSIS將資料插入或更新到歸檔表後迴圈刪除源表。

650) this.width=650;" title="clip_image009" style="border-right-width:0px;border-bottom-width:0px;border-top-width:0px;" alt="clip_image009" src="http://s3.51cto.com/wyfs02/M02/53/99/wKiom1RsCkTT9AeSAADT6ZV6EFs378.jpg" height="525" width="1042" border="0" />

方案優缺點

優點：邏輯整合

缺點：維護成本高，定位問題麻煩

 

方案十： 方案介紹

開源軟體，如eyeArchive

方案優缺點

優點：開源軟體

缺點：方法單一

本文出自 “滴水石穿” 部落格，請務必保留此出處http://ultrasql.blog.51cto.com/9591438/1579443

SQL Server 資料歸檔方案