Creating a Linked Server for an RDS for 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