Problem
We need to have PostgreSQL and SQL Server database platforms communicate in our environment. We need to access the PostgreSQL data from SQL Server in an efficient manner. Based on the steps from your first tip, how can we take the next steps to setup the data access? Can we create a Linked Server from SQL Server to PostgreSQL to access the data?
Solution
In part 1 of this series, we rolled out a simple database infrastructure with a PostgreSQL instance and a SQL Server instance. We have seen how both the servers could communicate with each other at network level. Then we restored a sample database in PostgreSQL and created two of its table structures in SQL Server.
In this tip, we will show how SQL Server can access Postgres data and populate those tables.
Install PostgreSQL ODBC Driver
Although the de-facto data access library for any modern database should be based on OLE DB, PostgreSQL’s official site doesn’t list any freely available x64 bit OLE DB providers. As mentioned in part 1, there’s a 64-bit OLE DB provider available from a third-party vendor. However, that driver comes with a price tag. The free OLE DB version available from Postgres site is for 32-bit only.
However, PostgreSQL also provides a 64-bit ODBC driver that’s downloadable from its official site. In our example, we will download and install this 64-bit ODBC driver (psqlODBC) on our SQL Server.
Step 1: Remote desktop to SQL Server
Step 2: Browse to PostgreSQL’s official download site for psqlODBC and download the zip file containing the x64 bit .msi installer. The file we will download is called psqlodbc_09_03_0300-x64-1.zip. We can see the driver is for PostgreSQL 9.3 and it’s meant for 64-bit Windows. Once the download completes, unzip the file. The extracted content looks like this:
Step 3: Double-click to start the psqlodbc_x64.msi installer. The next few images show the straightforward installation process.
Create ODBC Data Source
Once the driver has been installed, it’s time to create a System DSN from it. So let’s start the ODBC Data Source (64 bit) application from the Server Manager applet (see below).
In the next few screenshots, we can see how an ODBC data source is created.
Step 1: First let’s choose the System DSN tab and then click Add…
Step 2: Next we choose the PostgreSQL Unicode (x64) version and click Finish.
Step 3: In the dialog box that pops-up, provide a name and description for the data source, specify the database name, server’s IP address, port, user name and password as connection parameters. Once done, test the details by clicking on the Test button.
If the test is successful, click Save and then click OK in the ODBC Data Source Administrator.
Create a SQL Server Linked Server to PostgreSQL
Step 1: Start SQL Server Management Studio and connect to the SQL Server instance as “sa” or a sysdmin role member. Expand the Server Objects folder, right click on the Linked Servers node, and then choose “New Linked Server…” option from the pop-up menu.
On the General tab of the New Linked Server dialog box, choose the “Other data source” option, select “Microsoft OLE DB Provide for ODBC Drivers” option from the Provider drop-down list, provide a name for the Product and specify the Data Source name. The data source should be the one we just created: in this case it’s world_db_postgres.
Step 2: In the Security tab, choose the fourth option (“Be made using this security context”) and provide a login name and password to connect to the remote PostgreSQL instance. In this case we have used the built-in Postgres super user account to keep things simple.
Step 3: In the Server Options tab, choose the following options:
Click OK. If the connection is successful, the Linked Server will be created without any error.
Expanding the Linked Server node in SQL Server Management Studio would show us the tables in the world database in PostgreSQL.
Access PostgreSQL Data from SQL Server
Now that we can see the remote data, let’s fetch it into SQL Server. Open a new query window in Management Studio, select the world database and execute the following commands:
INSERT INTO city SELECT * FROM WORLD_SAMPLE.world.[public].city
The result should show 4079 rows have been copied. Next, execute this command:
INSERT INTO country SELECT * FROM WORLD_SAMPLE.world.[public].country
This should show 239 rows have been copied.
To be sure, you can count the number of rows in the local tables.
Conclusion
So now we have it. We have created an ODBC connection against the remote PostgreSQL instance, created a linked server on top of it and then executed two commands to copy across the data. There was no need to export the source data into text files and importing them using BCP or BULK INSERT.
This process can obviously be automated via scripts and stored procedures that are called by SQL Server Integration Services Packages or SQL Server Agent Jobs. SQL Server doesn’t give us any option to create push or pull replication subscription against PostgreSQL databases.
We haven’t discussed data access speed via ODBC, nor have we discussed any migration pitfalls like data type mismatches. The idea was to show how SQL Server can access PostgreSQL data seamlessly. Executing any PostgreSQL functions or stored procedures from SQL Server is another area your data migration team may have to consider.
Next Steps
- Stay tuned for the final part of this series
- Download and install the PostgreSQL ODBC driver and configure a data source and linked server to access PostgreSQL data
- Visit PostgreSQL official website for more information
