利用SSMS的匯入和匯出精靈來做資料傳輸,你坐的是直航機還是有轉機呢?

日前PASS好友說到一個特殊情況,他們工程師用SSMS的匯出匯入精靈到資料時,網路傳輸只衝到20MB左右。同樣的作業改用SSIS來跑,網路傳輸可以衝到80MB。

當下我第一時間想到的是SSMS匯出匯入精靈如果是在工程師電腦跑則資料不是SQL To SQL,而是會透過工程師電腦做資料傳輸。而存成SSIS到SQL再執行則就是SQL To SQL傳輸不會透過第三方,所以速度會比較快。也許朋友遇見的問題不是這一個原因,但既然是資料傳輸自然是直航比較好,透過轉機總是比較不理想

我LAB中有三台電腦模擬,一台是我作業的電腦(192.168.16.1),一台是資料來源SQL(192.168.16.135),一台是資料目的SQL(192.168.16.141)

下面兩張圖是證明兩台SQL彼此的1433是無法連接,因此資料是無法彼此交換

 

我首先透過第三台電腦(192.168.16.1)開啟SSMS並連結這兩台SQL

 

接下來我預計將AdventureWorksDW2012中的FactProductInventory資料表搬到目的SQL去,約77萬筆左右。

 

接下來我第三台電腦(192.168.16.1)開啟SSMS中的匯入匯出精靈來做資料表搬移(下兩圖所示)

 

如下圖所示我們已成功匯出77萬多筆資料

 

下圖是我在目的SQL中開Profiler錄到的資料,下圖紅色圈選處可以算出整個搬移的時間起迄(花費約略5.327秒)。藍色圈選處可以看出所有的動作是透過ROCKCHANG這一部電腦過來(這部電腦是第三方,也就是執行SSMS匯入匯出精靈的這一台)。綠色圈選處可以看見每次Bulk Insert的筆數為9216筆。

 

接下來要測試一下直航,看看速度會快一點嗎?所以我先開啟目的SQL的防火牆(192.168.16.141),讓來源端可以直接連接目的端的1433(如下圖所示)。

 

這一次我直接在SQL來源端執行匯入匯出精靈,同時將該作業存成SSIS封裝到來源SQL內。

 

跑直航模式的過程中我一樣有用Profiler來錄,由下圖藍色圈選處可以看出是兩部SQL直接互傳了,Bulk Insert還是一次9216筆,花費時間為5.460秒(沒有比較快)。

 

最後LAB我透過剛剛儲存的SSIS封裝來跑跑看。

 

Bulk Insert還是一次9216筆,花費時間為6.697秒(更慢Orz...)。依舊是直航模式SQL 對 SQL傳輸資料。

其實本次的LAB直航模式跟轉機模式在時間差異上並不大,甚至轉機的還比較快。對於這樣結果我也不意外,因為這三部機器都是在同一部電腦上的虛擬機,因此網路傳輸的影響根本微乎其微。我LAB的目的是想讓大家知道在第三方電腦用SSMS的精靈來匯入或匯出資料是會將資料再轉一手的,而不是直接SQL To SQL來傳輸。

因此如果可以的話直接在資料來源或目的端的SQL上開啟SSMS來執行匯入匯出作業會是比較好的。

我是ROCK

rockchang@mails.fju.edu.tw