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.
- 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.
- In DB instance 1, create database dbtest1 as user rdsuser.
- 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.
- After the DBLink is created, you can view the databases created in DB instance 1 on the linked server.
- 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
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot