Blog

Creating a linked server on Babelfish for Aurora PostgreSQL

Marcelo Fernandes

By fernandesdba

Babelfish for Aurora PostgreSQL supports linked servers by using the PostgreSQL tds_fdw extension in version 3.1.0. To work with linked servers, you must install the tds_fdw extension.

Here are the steps to install the tds_fdw extension and create Linked Server on Babelfish:

  1. Connect to your PostgreSQL DB instance on the Babelfish database in the PostgreSQL port. Use an account that has the rds_superuser role.
  2. Install the tds_fdw extension. This is a one-time installation process. You don’t need to reinstall when the DB cluster restarts.
    CREATE EXTENSION tds_fdw;

Babelfish supports adding remote RDS for SQL Server or Babelfish for Aurora PostgreSQL endpoints as the linked server. You can also add other remote SQL Server instances as linked servers.

  1. Create Linked server (from SSMS connected to Babelfish)
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'sqltest', @srvproduct=N'', @provider=N'tds_fdw', @datasrc=N'sqltest.marcelodba.com', @catalog=N'dbTest'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'sqltest',@useself=N'false',@locallogin=NULL,@rmtuser=N'sqluser',@rmtpassword='<password>'
  1. Use OPENQUERY() to retrieve data from these linked servers.
-- Simple query 
SELECT * FROM openquery (sqltest, 'SELECT * FROM dbTest.dbo.tbTest')

--run proc with parameter
SELECT * FROM OPENQUERY(sqltest, 'EXEC dbTest.dbo.GetAllrecords @id = 1')

As we can see, it’s very easy to use linked server with Babelfish, now you know how to use linked server with Babelfish, happy migrations.

Fonte: Marcelo Fernandes