Tạo Linked Server

Vũ Huy Tâm

Linked Server là cơ chế cho phép kết nối từ một SQL Server tới một database server khác (có thể là SQL Server hoặc các hệ thống khác). Nó giúp việc truy vấn hoặc cập nhật dữ liệu từ một server ở xa rất thuận tiện.

Tạo Linked server

Để tạo linked server, trong Management Studtio 2008 bạn mở rộng node Server Objects/Linked Server, right click và chọn “New Linked Server”:

Trong cửa sổ mở ra sau đó:
- Ô “Linked server” cho bạn nhập tên của linked server.
- Lựa chọn “Server type” cho phép bạn chọn “SQL Server” hoặc “Other data source”
Nếu bạn chọn “SQL Server”, các ô nhập liệu bị mờ đi và bạn chỉ có thể nhập vào ô “Linked server” là tên của SQL Server ở xa mà bạn muốn nối tới. Nói cách khác, tên của Linked server phải trùng với tên của SQL Server ở xa.
Nếu chọn “Other data source”, bạn có thể đặt tên Linked server tùy ý và bạn có thể kết nối tới 1 database server bất kỳ, SQL Server hoặc các hệ thống khác như Oracle, MySQL… (tất nhiên chỉ các hệ thống có thư viện kết nối hiện cài đặt trên server của bạn). Khi đặt lựa chọn này bạn cần chỉ định thêm một vài thông tin:
- Provider: tên của thư viện kết nối với server ở xa. Ví dụ nếu muốn kết nối với Oracle bạn cần chọn “Microsoft OLE DB Provider for Oracle”; nếu muốn kết nối với SQL Server 2008 bạn có thể chọn “SQL Server Native Client 10.0″.
- Product: tên của server ở xa.
- Data source: tên của server ở xa.

Trong hình trên, tôi tạo 1 linked server có tên LNK_SQLDB2 kết nối tới server ở xa là 1 SQL Server có tên SQLDB2.
Chuyển sang tab “Security”, bạn chọn “Be made using this security context” và nhập vào login và password. Đây là login nằm tại server ở xa và sẽ được dùng mỗi khi bạn truy nhập vào server ở xa thông qua linked server.


Lưu ý login và password phải hợp lệ (tồn tại thực) trên server ở xa. Sau đó bạn nhấn “OK” và linked server được tạo.

Sử dụng Linked Server

Việc truy vấn tới server ở xa thông qua linked server rất đơn giản, câu lệnh chỉ cần gọi đến tên đầy đủ của bảng như sau:
ten_link_server.ten_db.ten_schema.ten_bang

SELECT *
FROM LNK_SQLDB2.TestDB.dbo.TestTable

Bạn cũng có thể update bảng nếu như login được dùng ở trên có quyền update bảng ở server đầu đằng kia. Tuy nhiên, nếu thực hiện 1 thủ tục ở xa, bạn sẽ nhận được thông báo lỗi:

EXEC LNK_SQLDB2.TestDB.dbo.pr_test

Msg 7411, Level 16, State 1, Line 1
Server 'LNK_SQLDB2' is not configured for RPC.

Lý do là vì lựa chọn mặc định khi bạn tạo linked server không thiết lập RPC (Remote Procedure Call) thành thử nó không cho phép bạn gọi thủ tục từ xa. Để thiết lập lựa chọn này, bạn cần sửa lại linked server (click phải, chọn “Properties”) rồi chuyển tới cửa sổ “Server Options” và đặt “RPC Out” thành True:

Khi làm trên SQL Server 2005 hoặc mới hơn, sau khi sửa lại xong và bấm “OK”, có thể bạn sẽ gặp lỗi:
“Ad hoc updates to system catalogs are not allowed”

Sở dĩ như vậy vì bản 2005 trở về sau không cho phép update trực tiếp vào catalog hệ thống. Điều này quả là gây rắc rỗi. Bạn có thể đặt lại lựa chọn của SQL Server để vượt qua lỗi này, tuy nhiên cách dễ dàng và an toàn nhất là xóa linked server và tạo lại, và trong khi tạo lại bạn đặt các lựa chọn theo ý muốn.




Tags:

26 Comments
Posted on 18/1/2012 | Categories: Database Administration

Các bài viết tương tự

Comments
  • viet (29/02/2012 9:10 am)

    thanks thầy bài viết của thầy rất hữu ích, thật sự sql đối với em mới quá nhiều lúc thao tác với nó mà phát nãn.

  • Thuan (14/03/2012 12:37 am)

    Chào bạn, trong bài viết bạn dùng từ “server ở xa”. Xa ở đây là sao? Có áp dụng cho serer trên cty và server ở nhà không?

    • Vũ Huy Tâm (14/03/2012 7:22 am)

      “server ở xa” tức là server khác không cùng với server mà mình đang kết nối vào. Có thể là server nằm trong cùng network hoặc khác network. Trong trường hợp khác network mà kết nối qua TCP/IP thì firewall ở phía server ở xa phải mở cổng ra 1433 để các connection có thể kết nối tới

  • Cuong (28/03/2012 4:09 am)

    Lưu ý khi sử dụng Linkserver từ SQL2000 và SQL2008

    1 – Tốt nhất là chọn : Server type là : SQL Server
    2 – Chạy đoạn Script này

     
    Tạo Link server : SP_ADDLINKEDSERVER @server=, @srvproduct=
    Tạo link server login : SP_ADDLINKEDSRVLOGIN @rmtsrvname=,@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NUL
    Thiết lập Link server OPTION trên SQL 2008: 
    •	SP_SERVEROPTION @server= , @optname=N'collation compatible', @optvalue=N'false'SP_SERVEROPTION @server= , @optname=N'data access', @optvalue=N'true'SP_SERVEROPTION @server=, @optname=N'dist', @optvalue=N'false'SP_SERVEROPTION @server=, @optname=N'pub', @optvalue=N'false'SP_SERVEROPTION @server=, @optname=N'rpc', @optvalue=N'false'SP_SERVEROPTION @server=, @optname=N'rpc out', @optvalue=N'false'SP_SERVEROPTION @server=, @optname=N'sub', @optvalue=N'false'SP_SERVEROPTION @server=, @optname=N'connect timeout', @optvalue=N'0'SP_SERVEROPTION @server=, @optname=N'collation name', @optvalue=null
    •	SP_SERVEROPTION @server=, @optname=N'lazy schema validation', @optvalue=N'false'SP_SERVEROPTION @server=, @optname=N'query timeout', @optvalue=N'0'SP_SERVEROPTION @server=, @optname=N'use remote collation', @optvalue=N'true'SP_SERVEROPTION @server=, @optname=N'remote proc transaction promotion', @optvalue=N'true'
     
    Thiết lập trên máy SQL 2000
    USE master
    GO
    CREATE PROCEDURE sp_tables_info_rowset_64 @table_name sysname, @table_schema sysname = null, @table_type NVARCHAR(255) = null
    AS
    DECLARE @RESULT INT
    --SELECT @Result = 0
    SET @RESULT = 0
    EXEC @RESULT = sp_tables_info_rowset
    @table_name, @table_schema, @table_type
    GO
    GRANT EXECUTE ON dbo.sp_tables_info_rowset_64 TO [PUBLIC] GO
  • Cuong (17/04/2012 2:36 am)

    Chào anh Tâm,

    Khi tôi sử dụng LinkServer giữa SQL 2008 R2 64bit và SQL2000 SP4 tôi bị lỗi như sau.

    Tôi thực hiện Insert dữ liệu vào table trên SQL200 từ table tạm trên SQL2008 thì báo lỗi như sau:

     
    --Câu lệnh này chạy trên SQL2008 R2
    EXEC('
    	UPDATE	A
    		SET A.WorkMeal = B.WorkMeal
    	FROM sẻver2000.xxx.dbo.Table A,
    			Table B
    	WHERE A.EmplCode = B.EmplCode AND A.TimeDate = B.TimeDate
    ')
     
    Message
    Buffer provided TO READ COLUMN VALUE IS too small. 
    Run DBCC CHECKDB TO CHECK FOR any corruption. [SQLSTATE HY000] (Error 682).  The step failed.

    Mong anh giúp

    • Cuong (17/04/2012 2:38 am)

      Mặc dù tôi đã chạy

      DBCC checkTable
      DBCC CheckDB

      Cả hai hệ thống server không có lỗi gì.

      • Vũ Huy Tâm (17/04/2012 12:30 pm)

        Có vẻ như đây là lỗi hệ thống của SQL Server:
        https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124755
        Nếu bạn link đến 1 SQL 2008 khác thì có bị lỗi như vậy không?

        • Cuong (18/04/2012 10:21 am)

          Chào anh Tâm,

          Cảm ơn anh đã phản hồi.

          Đối với LinkServer với 2008 khác thì không bị lỗi như vậy. Theo tôi đoán đó là sự tương thích giữa các kiểu dữ liệu giữa SQL 32bit và SQL 64bit.

          Feedback của Microsoft tôi cũng đã đọc nhưng những thông tin cũng chung chung mà thôi và tôi cũng chưa giải quyết được vấn đề.

  • Bao (24/05/2012 12:24 am)

    Tôi làm trực tiếp trên 1 máy với 2 database thì có cần phải cài Link Server không? Mong các bạn cho ý kiến.

    • Vũ Huy Tâm (25/05/2012 9:38 am)

      Trên cùng 1 máy thì không cần dùng linked server, bạn chỉ cần tham chiếu đến database kia qua tên

      USE db1
      GO
      SELECT * FROM db2.dbo.table2
  • nhnhanqbi (30/10/2012 11:14 pm)

    Mình mới học SQL, nhưng đọc qua một số bài viết của Vũ Huy Tâm mình thấy rất thích thú. Mong bạn phát huy nhiều hơn nữa.

  • Tuân (25/08/2013 4:36 pm)

    Cho mình hỏi xíu. Vì mình đang dùng Replication nên phải chọn Linked server dạng Sql Server có nghĩa là chỉ cần nhập tên của máy chủ. Nhưng mình không thể kết nối được. Cám ơn mọi người đã đọc.

  • Vũ Huy Tâm (27/08/2013 10:00 am)

    Lỗi không kết nối được có thể do firewall chặn cổng kết nối của server ở xa (cổng TCP mặc định là 1433); hoặc server đó không cho kết nối từ xa; hoặc user bạn dùng để kết nối không hợp lệ.

  • tung (05/11/2013 12:37 am)

    Bác tâm cho hỏi khi dùng linked server thì cần phải dùng server name để 2 máy nhìn thấy nhau . Trong mạng lan thì dễ rồi , còn mạng internet mình cho địa chỉ thì nó không nhận , đọc trên mạng thì họ bảo dùng mạng lan ảo như VPN , ngoài cách này ra còn cách nào khác để 2 máy nhìn thấy nhau thông qua server name không ạ ?

    • Vũ Huy Tâm (05/11/2013 10:24 am)

      Theo mình nghĩ là không, vì server name chỉ có ý nghĩa trong cùng domain (windows domain).
      Trong trường hợp này bạn cần dùng địa chỉ IP, nhưng server ở xa và tất cả các firewall dọc đường đều cần mở cổng 1433 cho SQL. Cái này có thể không ổn về security, nên người ta mới bảo dùng VPN. Nói chung sẽ không ai khuyên bạn dùng linked server qua internet.

      • tung (05/11/2013 12:04 pm)

        Vậy em muốn dùng dữ liệu phân tán thì không dung linked server được ạ ? Bác có kinh nghiệm có thể chỉ em cách làm hoặc đường link để tham khảo được không ? Thank bác nhiều ạ .

        • Vũ Huy Tâm (05/11/2013 4:10 pm)

          Dùng được nhưng bạn cần VPN. Nói chung database server là thứ cần giấu kỹ ở bên trong, không ai muốn mở ra cho truy nhập qua internet cả. VPN giúp tạo một không gian an toàn để các server giao tiếp với nhau, bất kể khoảng cách địa lý giữa chúng ra sao.
          Ngoài ra bạn có thể dùng những synchronization tool như khi cần đồng bộ dữ liệu giữa local database và azure hoặc aws

  • Khang (10/11/2013 10:14 am)

    Chào anh,
    em có thắc mắc mong được anh giúp đỡ .Hiện tại em đang muốn phân tán một CSDL được phân mảnh theo nhân viên thuộc chi nhánh nào thì nắm trên server đó,2 chi nhánh nằm trên 2 server là :SERVER2 và SERVER3 ,SERVER1 chứa toàn bộ CSDL gốc.Em đang muốn tạo ra 3 nhóm :
    -User: user thuộc chi nhánh nào thì chỉ có thể đăng nhập chi nhánh đó và không có quyền thêm,xóa sửa
    -ADMIN_CHINHANH:là toàn bộ người dùng nào thuộc nhòm này thì sẻ có quyền xem,xóa sửa chỉ trên chi nhánh (server) mà hiện tại đang chứa nó
    -ADMIN_TOANQUYEN: là user chỉ có khả năng xem dữ liệu ,báo cáo trên cả 2 server (không có quyên sửa,xóa,..)

    Và khi đăng nhập nào thì nó sẽ hiện ra mình thuộc nhóm nào .Hiện tại em chỉ mới làm được 2 ý đầu tiền ,tức là tạo dc user bình thường và admin chi nhánh thôi,còn admin toàn quyền thì em không rõ cách tạo ra sao ? mình tạo 1 user trên server gốc hay tạo cái user đó trên cả 3 server luôn và khi muốn truy xuất vào CSDL của chi nhánh khác thì nó dùng LINKSERVER ? Nếu dùng link server thì khi muốn một user thuộc group ADMIN_TOANQUYEN mình sử dụng câu lệnh ra sao để tạo một user thuộc nhóm ADMIN_TOANQUYEN ?
    Mong được anh giúp đỡ,em cám ơn

  • PinkChina (24/02/2014 9:33 pm)

    Chào bạn!
    Bạn cho mình hỏi chút, hiện tại mình đang dùng linkserver để kết nối từ SQL sang Oracle. Nhưng mình thấy trên SQL bị mất Provider nên không querry trên SQL vào CSDL Oracle được.
    Để khắc phục lỗi này mình phải làm như nào? Mong bạn giúp đỡ, cảm ơn bạn rất nhiều!

  • chien (11/06/2014 8:29 am)

    anh ơi cho em hỏi:
    em muốn tạo user cho sql ở máy trạm để có thể truy xuất tới nhiều bảng thì phải làm như thế nào?

  • Võ Văn An (10/04/2018 4:33 am)

    Em chào Thầy

    Thầy ơi thầy có thể làm một clip hướng dẫn giúp em cài microsof OLE DB Provider for SQL với được không thầy
    Em cài mấy tuần nay mà không thể nào cài được nó cứ toàn báo lỗi
    em lên lục khắp diễn đàn và youtube mà vẫn không cài được

    Em phát nãn quá thầy ơi

    Em cảm ơn thầy nhiều!

    • Võ Văn An (10/04/2018 4:34 am)

      Mail của em : vo.an92@gmail.com
      Thầy giúp em với nhé
      Em cảm ơn thầy

  • Võ Văn An (10/04/2018 4:37 am)

    Hiện tại em chỉ muống tạo link trên SQL SERVER 2008 trên máy tính của em thôi
    không có kết nối với SERVER ở xa mà em cài hoài vẫn không được

Leave a Reply

Hướng dẫn: Để nhập mã T-SQL bạn dùng thẻ <pre lang="tsql"> và </pre>.
Ví dụ: <pre lang="tsql">SELECT * FROM MyTable</pre>