Help Center> Relational Database Service> Best Practices> Microsoft SQL Server> Creating a Linked Server for an RDS SQL Server DB Instance
View PDF

Creating a Linked Server for an RDS SQL Server DB Instance

Create a linked server for SQL Server DB instance named 2 to access another SQL Server DB instance named 1.

  1. Enable distributed transactions of the two DB instances by referring to Distributed Transactions and add the peer-end host information to each other. For offline servers or ECS servers, Name Resolution on Remote Servers (ECSs).

    If two DB instances 1 and 2 are in the same VPC, use the floating IP address. If the ECS server and RDS DB instances are not in the same VPC or a DB instance is offline, use an EIP. For details on how to bind an EIP to a DB instance, see Binding and Unbinding an EIP.

  2. In DB instance 1, create database dbtest1 as user rdsuser.
  3. In DB instance 2, run the following commands to create a linked server as user rdsuser.

    USE [master]

    GO

    EXEC master.dbo.sp_addlinkedserver @server = N'TEST', @srvproduct=N'mytest', @provider=N'SQLOLEDB', @datasrc=N'192.168.***.***,1433'

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TEST', @locallogin = NULL , @useself = N'False', @rmtuser = N'rdsuser', @rmtpassword = N'********'

    GO

    Table 1 Parameter description

    Parameter

    Description

    @server

    Specifies the linked server name.

    @srvproduct

    Specifies the product name.

    @provider

    Use the default value.

    @datasrc

    Specifies the IP address and port of the DB instance to be accessed.

    @rmtsrvname

    Specifies the name for logging in to the linked server.

    @rmtuser

    Specifies the username (rdsuser).

    @rmtpassword

    Specifies the user password.

  4. After the DBLink is created, you can view the databases created in DB instance 1 on the linked server.

  5. Run the following commands to check whether the data is successfully inserted, as shown in Figure 1:

    begin tran

    set xact_abort on

    INSERT INTO [LYNTEST].[dbtest1].[dbo].[user1]

    ([id],[lname],[rname])

    VALUES('19','w','x')

    GO

    commit tran

    Figure 1 Insert result

Parent topic: Microsoft SQL Server