在MSSQL中設定連結伺服器 (Linked Server)

shazi7804

10 年前

在專案執行中，因需要將兩個不同的DB Server做整合，讓 A-DB 可以寫入 B-DB，架構如下

Server A 透過 DBLink 直接讀取到 Server B上的 Database。

Lab環境

ServerA：A1資料庫 SQL 2005
ServerB：B1資料庫 SQL 2008 R2

Step 1. 在Server A打開 SQL Server Management Studio

Step 2. 展開【伺服器物件】→【連結的伺服器】→右鍵點選【新增連結的伺服器】

連結的伺服器
- 提供用來參考此連結的伺服器名稱，在下TSQL的使用會使用，避免TSQL錯誤盡量不要使用特殊字元。

SQL Server
- 將連結的伺服器識別為 Microsoft SQL Server 的執行個體。如果您使用這個定義 SQL Server 連結之伺服器的方法，[連結的伺服器] 中所指定的名稱就必須是伺服器的網路名稱。另外，從伺服器擷取的任何資料表，都會是來自已連結伺服器上之登入所定義的預設資料庫。

其他資料來源
- 指定 SQL Server 以外的 OLE DB 伺服器。按一下這個選項會啟動在它下面的選項。
  - 提供者
    - 從清單方塊中選取 OLE DB 資料來源。在登錄中，OLE DB 提供者是使用給定的 PROGID 註冊。
    - 如果你要連結到 SQL 2000/2005/2008 的資料庫，可以選擇 Microsoft OLE DB Provider for SQL Server 或是 SQL Native Client
  - 產品名稱
    - 輸入 OLE DB 資料來源的產品名稱，以加入成為連結的伺服器。
    - 其實就只是Windows中的描述或註解
  - 資料來源
    - 依 OLE DB 提供者所解譯的，輸入資料來源的名稱。
    - 輸入ServerB的連線位置，亦可為具名連線位置，或指向file路徑(Excel or Access)
  - 位置：尚未有實際用途，可不填。
  - 目錄：尚未有實際用途，可不填。

Step 3. 點選【安全性】選項

可以直接選擇【使用此安全性內容建立】，把ServerB登入SQL的密碼權限登入即可

Step 4. 測試

設定完成後，在Standard版本點選剛剛建立好的伺服器連結右鍵，可以找到【測試連接】，若設定完成會提示【與連結伺服器的連接測試成功】

若是在Express版本的時候，基本上是沒有任何測試功能的，只能使用以下語法測試是否能查到ServerB的資料，或是升級到 Service Pack 2。

Example：

SELECT * FROM [連結的伺服器].[資料庫].[Table]

SELECT * FROM SERVERB.B2.dbo.users

UPDATE SERVERB.B2.dbo.users SET PASSWORD='456'
WHERE ACCOUNT='123'

參考資料：
http://blog.miniasp.com/post/2008/07/30/How-to-setup-Linked-Server-in-SQL-Server-2005.aspx
http://technet.microsoft.com/zh-tw/library/ms188279(v=sql.100).aspx

分享此文：