[SQL Server] 利用Linked Server轉移大量數據到SQL Server之方法(以AS400為例)

[20140110] 新增OLE DB版本
上次提到當我們進行異質資料庫的資料轉移,使用SQL Server內建的工具經常會發生很多不明的錯誤(例如Column size太大)。那怎麼辦呢?總不可能把資料全部變成insert Statement吧?經過爬文以後小弟最後選用了SQL Server裡的Linked Server功能. 也成功轉移了超過2000萬筆資料的資料庫,雖然可能並不是好方法也希望對大家有幫助 :-)

什麼是SQL Server 的Linked Server?
Linked Server (連結的伺服器) 根據msdn的解釋, 就是 "可讓 SQL Server Database Engine 對 SQL Server 執行個體外部的 OLE DB 資料來源執行命令。" 再說白一點, 就是可用T-SQL查詢其他品牌的資料庫。

來源資料特性:
  • 資料在老舊的V5R4 AS400上.
  • 很多資料表(Table)的欄位(Columns)很多,接近50個欄位以上。
  • 欄位的size很大, 長度達3000或以上, 而且因為是char的關係就算改成varchar也沒用.
  • 資料量大, 很多Table超過200萬筆.

準備工作:
  • 由於這次使用的是AS400的ODBC或OLE DB, 所以在SQL Server端一定要先安裝IBM iSeries Client Access.
  • 在SQL Server中先建立一空的資料庫
  • 需要有SQL Server SA權限的帳號
動手做:
  • 建立Linked Server

    1. 打開SQL Server Management  Studio, 登入後到Server Objects->Linked Servers->按右鍵新增Linked Server
    2. 如果你用的是ODBC
      在"Linked Server"中填入此Linked Server的名字, 任意也OK, 由於這次對象是AS400, Server type要選擇Other data source, 由於這次使用的是ODBC connection, 所以Provider一欄要選擇 "Microsoft OLE DB Provider for ODBC Drivers", Product name先填as400, Data Source選擇ODBC中的來源名稱(如AS400ODBC), Provider string跟connection string類似, 主要是加入一些連結的資訊, 例如在這個例子我要傳送使用者名稱跟密碼去登入AS400的目的地資料庫, 就要填寫uid=XXXX;Password=XXXX; Location跟Catalog可以放空不用填寫, 確定無誤後按OK.


      如果你用的是OLE DB在"Linked Server"中填入此Linked Server的名字, 任意也OK, 由於這次對象是AS400, Server type要選擇Other data source, 由於這次使用的是OLE DB connection, 所以Provider一欄要選擇 "IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider", Product name不用填寫, Data Source填寫AS400的IP或者DNS, Provider string跟connection string類似, 主要是加入一些連結的資訊, 例如在這個例子我要登入ABC資料庫, 就要填寫Default Collection=ABC; Location跟Catalog可以放空不用填寫, 確定無誤後按OK.


    3. 登登~成功的話會出現這樣的畫面

    4. 最後, 如果你是使用OLE DB的話, 
      1. 記得設定完以後需要進入 Linked Servers->Providers-> IBMDA400按右鍵->選擇Properties

      2. 勾選"Allow inprocess"以後按 OK.

      3. 不然可能會出現 "Cannot create an instance of OLE DB provider "IBMDA400" for linked server "XXX" (Microsoft SQL Server, Error:7302)" 這個錯誤.

  • 建立產生Table的Create Statement
    1. 接下來要把目的地的資料建立好, 例如我在AS400中有80個Table, 那麼在SQL Server端就要先建立80個名字跟欄位也一模一樣的空資料表.例如
    2. 建立好所有空的資料表以後, 我們就可以用T-SQL最強的語法
      INSERT INTO <目的地資料表>
      SELECT * FROM <Linked Server中的資料表>
      把資料從來源資料庫轉移到SQL Server上去了, 例如

      然後兩個小時以後, 兩千多萬筆資料就成功轉移了!!!!
  • 總結
    之前我們試過很多方法(例如SSIS的import/export expert, 把Data轉為過萬筆insert Statement)也無法成功的把AS400中有超過兩千萬筆的資料庫轉移到SQL Server上去. 使用Linked Server對我們來暫時是成功的一套方案, 希望對遇到同樣問題的大大有幫助, 如果有更好的方案歡迎提出討論喔. ^_^


  • 2013/09/02補充:
    要批次建立資料表及insert的SQL, 我自己是iSeries Navigator內建的工具產生的, 千萬不要血汗工廠自己慢慢寫, 賠了時間又容易出錯啊
  • 2014/01/15補充:
    經小弟測試後OLE DB跟ODBC在速度上很接近, 不過如果要連結大量linked server的話還是使用OLE DB比較好.

Popular posts from this blog

[SQL SERVER] 找出LOCK方法懶人包

[SQL Server] 解決log檔(ldf file)過度膨脹的實戰經驗

[開箱] Dell P2415Q 4K螢幕開箱