Creating a Linked Server for an RDS for SQL Server DB Instance

Create a linked server for RDS for SQL Server DB instance named 2 to access another RDS for 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 on-premises servers or ECSs, see Resolving Names on Remote Servers (ECSs).
   

   RDS for SQL Server instance 2 and RDS for SQL Server instance 1 are in the same VPC. If the ECS and RDS instances are not in the same VPC or you are creating an on-premises server for an RDS instance, bind an EIP to the RDS instance. For details, 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 SQL statements to create a linked server as user rdsuser:

   USE [master]
   GO
   EXEC master.dbo.sp_addlinkedserver @server = N'TEST_SERVERNAME', @srvproduct=N'SQLServer', @provider=N'SQLOLEDB', @datasrc=N'192.168.***.***,1433'
   EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TEST_SERVERNAME', @locallogin = NULL , @useself = N'false', @rmtuser = N'rdsuser', @rmtpassword = N'********'
   GO

   Table 1 Parameter description

   Parameter	Description
   @server	The name of the linked server.
   @srvproduct	The product name of the data source. Use the default value SQL Server.
   @provider	Use the default value.
   @datasrc	The IP address and port of the DB instance to be accessed.
   @rmtsrvname	The name of the linked server.
   @locallogin	The login name on the local server. Use the default value NULL.
   @useself	Whether to connect to the linked server by simulating the local login name or username and password. Enter false, which indicates that the linked server is connected through the username and password.
   @rmtuser	The username (rdsuser).
   @rmtpassword	The password for the username.

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