SQL Linked Server, Oracle DBLink and PostgreSQL Foreign Data Wrapper

Problem

In the sixth tutorial in this series about comparisons of SQL Server, Oracle and PostgreSQL, we’ll take a look at how to connect to different database platforms (SQL Server, Oracle, PostgreSQL) to transfer data back and forth between different servers and database platforms.

Solution

There are different solutions to this problem, one of the ways to accomplish this task is to setup a Linked Server, DBLink or Foreign Data Wrapper (FDW) to directly query the database on another server.

So, let’s start setting up a practical example in SQL Server, with a Linked Server, as always we will use the GitHub freely downloadable database sample Chinook, as it is available in multiple RDBMS formats. It is a simulation of a digital media store, with some sample data, all you have to do is download the version you need and you have all the scripts for data structure and all the INSERTs for data.

SQL Server Linked Server

Setting up a Linked server in SQL Server is quite easy and straightforward if we need to connect to another SQL Server. First of all, we need to have a login on the other SQL Server instance with privileges in the database that we wish to connect to. Then we will use this login to authenticate the Linked Server, so let’s make an example with a new host in which we have a database called Cherokee (another copy of our original Chinook database):

USE [master]
GO
CREATE LOGIN [linked_server_user] WITH PASSWORD=N'*********', DEFAULT_DATABASE=[Cherokee], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Cherokee]
GO
CREATE USER [linked_server_user] FOR LOGIN [linked_server_user]
GO
USE [Cherokee]
GO
EXEC sp_addrolemember N'db_datareader', N'linked_server_user'
GO
USE [Cherokee]
GO
EXEC sp_addrolemember N'db_datawriter', N'linked_server_user'
GO

Now with this script we have the login mapped to our Cherokee database with read and write privileges. We can create the linked server on the other host, again there are basically two methods for doing this, one is to use the SSMS GUI, the other is to script the actions in T-SQL like I’ve done for the login. As many DBAs, I prefer the second method that gives you a lot more control and eventually automation, so we create the Linked Server using the sp_addlinkedserver system stored procedure:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'linked-sql', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'linked-sql', @locallogin = NULL , @useself = N'False', @rmtuser = N'linked_server_user', @rmtpassword = N'********'
GO

The @server parameter is the name of the host you wish to connect to, and if we specify SQL Server as the product we do not need to add any other parameters. The other system stored procedure, sp_addlinkedsrvlogin is used to specify the login to access that instance, in this case we use the one we just created.

That’s it, with these two simple scripts we have made a new linked server that can be used to easily query data in another instance. Let’s do an example using the simple cross database query we have done in my last tip, but going to the linked server table instead of the Chinook table.

One thing that can reduce headaches is making use of synonyms in order to address tables in linked servers, that is a common practice and very useful, so first let’s add a synonym referencing the Customer table in the Cherokee database on the Linked Server:

USE [Apache]
GO
CREATE SYNONYM [dbo].[Linked_cherokee_Customer] FOR [linked_sql].[cherokee].[dbo].[customer]
GO

Now we can run the query using the synonym:

USE Apache
SELECT CustomerId, FirstName,LastName
FROM dbo.Customer
EXCEPT
SELECT CustomerId, FirstName,LastName
FROM linked_cherokee_Customer

Oracle DB Link

Now let’s try to do the same thing with Oracle, here the name of the feature changes as it is called DB Link or Database Link, but the functions are almost the same.

First, we need to make a distinction as in Oracle it is possible to have a Public DB Link available from all the schemas of the database instance and a DB Link available only in the Schema in which it has been created. Public Database Links are stored under the SYS schema, but the functions are the same, so let’s create a DB Link in the Chinook schema.

I created on another host the same schema Cherokee as I’ve done on SQL Server (except in SQL Server it is a Database), now as the first step we create a user in order to connect to this schema on the separate host, but this time we first create a role on which we assign all select permissions on the tables for the CHEROKEE schema:

create role LINKED_READER;

In order to grant permissions, we’ll use some simple dynamic SQL querying the all_tables system view:

select 'GRANT SELECT ON CHEROKEE.'||table_name||' TO LINKED_READER;' from all_tables where owner = 'CHEROKEE';

Executing the above selected rows, we can use to grant SELECT on all tables in the schema.

Now we are ready to create the user and assign the default roles:

CREATE USER "LINKED_CHEROKEE" IDENTIFIED BY "Useraccount"  
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
GRANT "LINKED_READER" TO "LINKED_CHEROKEE" ;
GRANT "CONNECT" TO "LINKED_CHEROKEE" ;
ALTER USER "LINKED_CHEROKEE" DEFAULT ROLE "LINKED_READER","CONNECT";

Now that we have the user on the separate host, we can create the db link on the Chinook schema in the other:

CREATE DATABASE LINK DBCHEROKEE 
CONNECT TO LINKED_CHEROKEE IDENTIFIED BY Useraccount
USING 'DBCHEROKEE';

Now the important part – we must create an entry in tnsnames.ora, referencing the host. The config file tnsnames.ora is normally located in /oracle_home/network/admin:

DBCHEROKEE =
  (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host db link)(PORT = 1521))    
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DatabaseServiceName)
    )
  )

Once this is done it is possible that a restart of the listener is required in order to load the changes in tnsnames. Honestly, I never had to do this. Oracle Listener is the network interface that enables connections to the Database Instance.

Now let’s check if the DB Link works with a simple script (remember that in Oracle you must always specify a FROM clause, that’s what the from dual is for):

select * from dual@"DBCHEROKEE";

Now that we’ve verified that the DB Link is working, we can repeat a similar query to the one performed on SQL Server:

SELECT CustomerId, FirstName,LastName
FROM cherokee.Customer@dbcherokee
minus
SELECT CustomerId, FirstName,LastName
FROM chinook.Customer;

Please notice how the db link is referenced: using an @ with the name of the db link, unfortunately in Oracle synonyms are not permitted for the DB Link so we cannot use the same trick as in SQL Server, but anyway there is no problem in referencing the DB Link in this way.

PostgreSQL Foreign Data Wrapper

We have already done this in another tip with a database link for PostgreSQL, which is called Foreign Data Wrapper, please see all explanations following this link: Relational Database Comparison between SQL Server, Oracle and PostgreSQL and go to the Foreign Data Wrapper PostgreSQL section in the article.

Linking to Different Database Platforms

Now it’s time to try linking a database that is not the same RDBMS, let’s do it first from SQL Server to Oracle.

SQL Server

We use again use the Linked Server feature, but this time we must specify a provider for Oracle, in our case I use the OraOLEDB.Oracle that is installed with Oracle Instant Client, downloadable here.

The provider has to be setup on the host in where SQL Server is installed and obviously the SQL Server and Oracle hosts must be able to communicate, so sometimes a firewall rule is needed if there is a firewall between the two hosts.

It is also needed to add an entry in tnsnames.ora as done on Oracle DB Link, this time on the file present under \\Oracle_home\network\admin, in the SQL Server host, in my case the path is as long as this: E:\app\client\a.gnemmi\product\12.2.0\client_1\network\admin

So with a normal text editor we add this entry that identifies our Oracle Data source:

LINKED_ORACLE=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <linked_server_oracle_host>)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST_AGNEMMI)
    )
  )

Now we are ready to add the Linked Server, using this entry in the @datasrc parameter and OraOLEDB.Oracle in the @provider one:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'LINKED_ORACLE2', @srvproduct=N'', @provider=N'OraOLEDB.Oracle',@datasrc = 'LINKED_ORACLE' 
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = 'linked_oracle', @useself = 'False', @locallogin = NULL, @rmtuser = 'chinook', @rmtpassword ='**********'

Very important, check that the Oracle Provider has the parameter Allow in Process enabled, otherwise we’ll get an error “Error 7302: Cannot create an instance of OLE DB provider”:

Now we can finally check the Linked Server with Oracle:

And we create a synonym to reference the table Customer under Oracle, (note the way to properly address with the double .. below):

USE Apache
CREATE SYNONYM [dbo].[Linked_Oracle_Customer] FOR LINKED_ORACLE2..CHINOOK.CUSTOMER

We are now able to repeat the same query that we did before, but this time against an Oracle Linked server:

USE Apache
SELECT CustomerId, FirstName,LastName
FROM dbo.Customer
EXCEPT
SELECT CustomerId, FirstName,LastName
FROM Linked_Oracle_Customer

So, three very important topics:

Use OraOLEDB.Oracle as a provider, it is a lot better than the MSDAORA provider that is included in SQL Server.
Importance of tnsnames.ora: as we have already seen in creating the Oracle DB Link, in Oracle this configuration file is fundamental in order for the listener to correctly reference other hosts/servers.
Parameter Allow in Process enabled in the provider in SQL Server.

Here is nice and well written troubleshooting document.

Oracle

In order to create an Oracle DB Link pointing to a SQL Server the steps are similar to the ones for creating a normal DB Link, except that in order to communicate with SQL Server (or with any other RDBMS) from Oracle you need to setup a Gateway.

A Gateway server, in the case of SQL Server is a Windows server (or Linux Server) with the installation of Oracle Client with ODBC and Oracle Gateway for ODBC software on it. With other RDBMS you will instead setup another Linux Server with the appropriate Oracle ODBC and Oracle Gateway Software.

I will not provide the steps to setup the Gateway as this tip would grow too long, we will concentrate only on the steps needed to add a new DB Link on an Oracle server pointing to SQL Server.

So assuming that we have our Oracle Gateway installed and setup on a Windows server, we then need to add a new service on it under:

\\Oracle_Home\tghome\dg4msql\admin\init<servicename>.ora, in my case C:\oracle\product\12.1.0\tghome\dg4msql\admin\init<servicename>.ora

We will insert the data for the connection to the SQL Server Host. So supposing we have created a new login dblink mapped to database Apache in SQL Server we create a new file named initdblinksqlserver.ora with this information:

# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
 
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=HostSQLServer//Apache
HS_FDS_TRACE_LEVEL=OFF
HS_IDLE_TIMEOUT=15
HS_FDS_RECOVERY_ACCOUNT=dblink
HS_FDS_RECOVERY_PWD=y6hRwBQbbyuHg0Bcw9sXLI5XvbyHnob47oiFgKb1WzY

Then as usual we insert a new entry in tnsnames.ora under C:\oracle\product\12.1.0\tghome\NETWORK\ADMIN pointing this time to the Oracle Gateway host and using the same servicename used in the previous file:

dblinksqlserver=
 (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=<hostname gateway>)
         (PORT=1521)
      )
      (CONNECT_DATA=
         (SID=dblinksqlserver))
      (HS=OK))

Under the same directory we will find also listener.ora in which we will add a connection too, always using the servicename dblinksqlserver:

(SID_DESC= 
         (SID_NAME=dblinksqlserver)
         (ORACLE_HOME=C:\oracle\product\12.1.0\tghome)
         (PROGRAM=dg4msql)
      )

We have finished setup on the Gateway, now the listener must be restarted, on the Windows server open a command line as administrator and use command:

lsnrctl stop

and then

lsnrctl start

Now we need to insert an entry in the tnsnames.ora for this service also in the Oracle server in which we would like to create the DB Link:

dblinksqlserver=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =<hostname gateway>)(PORT = 1521))
    (CONNECT_DATA =
      (SID = dblinksqlserver)
    )
    (HS = OK)
  )

We are finally able to create the db link on Oracle with almost the usual script (having created a login in SQL Server to be used):

CREATE DATABASE LINK DBLINKSQLSERVER 
CONNECT TO dblink IDENTIFIED BY ********
USING 'DBLINKSQLSERVER';

And we now check the db link:

select * from dual@"DBLINKSQLSERVER";

Again, a query cross database in order to see that it works:

SELECT "CustomerId"
FROM Customer@DBLINKSQLSERVER
minus
SELECT CustomerId
FROM chinook.Customer;

Two very important points to remember:

Create all entries in tnsnames.ora and listener.ora with the servicename that has been used in init<servicename>.ora under \\Oracle_Home\tghome\dg4msql\admin
Always restart the listener on the gateway after having inserted entries in tnsnames and listener

Next Steps

In this tip we explored different ways of connecting RDBMS between different hosts, I will follow up with a dedicated part on Foreign Data Wrapper with SQL Server and Oracle.
Check out these related items:

Resources

Community

Subscribe