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 SQL statements to create a linked server as user rdsuser.

   USE [master]
   GO
   EXEC master.dbo.sp_addlinkedserver @server = N'TEST_SERVERNAME', @srvproduct=N'SQL Server', @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