0

I'm trying to copy a record from a production database table into a staging database table. They are both SQL Server databases on Amazon RDS. My query looks something like this:

INSERT INTO [neonpig.staging1.us-east-1.rds.amazonaws.com].[neonpig_prod_db].[dbo].[AspNetUsers] SELECT * FROM [neonpig.prod1.us-east-1.rds.amazonaws.com].[neonpig_prod_db].[dbo].[AspNetUsers]

But this seems to produce the error below:

Could not find server 'neonpig.prod1.us-east-1.rds.amazonaws.com' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

I've ensured that the server address are correct. Anyone know what I'm doing wrong?

Note that the database name is the same on both the prod server and the staging server.

1

To execute such statement you need to create LinkedServer first. In this case, you need to have LinkedServer from one RDS instance to another RDS instance.

https://aws.amazon.com/blogs/database/implement-linked-servers-with-amazon-rds-for-microsoft-sql-server/

RDS SQL Server to RDS SQL Server

For this scenario, both instances of RDS SQL Server must be private instances (not publicly accessible) within the same VPC. If the RDS SQL Server instances are publicly accessible, then you need to refer to the private IP of the RDS instances when creating the linked servers. Because you don’t have access to the private IP addresses of the servers, you need to use an EC2 instance in the same VPC to run NSLookup on the private instances of RDS. Do this for each RDS instance name.

NSlookup privatesql.cb2xkfsffmy7.us-west-2.rds.amazonaws.com

Address: 10.0.4.236

From there, the code is very much like the RDS to EC2 code, except now you use the private IP address of the remote RDS instance.

EXEC master.dbo.sp_addlinkedserver @server = N’RDSPrivate‘, @srvproduct=N”, @provider=N’SQLNCLI’, @datasrc=N’10.0.4.236′;

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’RDSPrivate’,@useself=N’False’,@locallogin=NULL,@rmtuser=N'<username>’,@rmtpassword=‘<password>’;

GO

Just as we did preceding, use a four-part name to reference the remote RDS instance:

SELECT * from RDSPrivate.TestDB.dbo.t1;

Note one limitation for all RDS instances: If the physical server supporting your RDS SQL Server instance changes (such as when upgrading to a new version of SQL Server or changing the instance type), the private IP address of the RDS instance of SQL Server might change. This change can happen with no alerts or notifications to your administrative team. Thus, if the linked server connection fails, your first troubleshooting steps should include verifying that the private IP address of the RDS instance has not changed. If this risk is unacceptable in your environment, then AWS doesn’t recommend using the linked server feature with RDS SQL Server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.