This article explains configuring a linked server between SQL Server 2019 and PostgreSQL. In my previous article named Configure ODBC drivers for PostgreSQL, we got introduced to the ODBC driver of PostgreSQL. We also learned how we could download, install, and configure it. In this article, we will explore how we can utilize the ODBC driver to access the data of the PostgreSQL from SQL Server 2019.
Environment Setup
For demonstration, I have installed and configured an instance of SQL Server 2019 and PostgreSQL 13 on my workstation. I have created a database named DVDShop on PostgreSQL 13. In the previous article, I have configured the ODBC driver that points to the DemoDatabase of PostgreSQL. In this article, we are going to connect to the DVDShop database, so we must change the database name in the pSQLODBC driver. To do that, Open Control Panel 🡪 Open Administrative tools 🡪 Open ODBC Data Source (64 bit) 🡪 open System DSN tab 🡪 select pgadmin13 and click on Configure.
The dialog box named PostgreSQL Unicode ODBC Driver (pSQLODBC) Setup opens. We have already specified all required parameters, so we are changing the name of the database in the Database text box only. We are connecting to the DVDShop database so enter DVDShop in the Database textbox.
To test the connectivity, click on Test. The output of the Test connection command is as following.
As you can see, the connection was established successfully. Click on Save to update the changes in System DSN and close the dialog box. Now let us create the linked server in SQL Server 2019.
Create Linked Server
We will create a linked server using SQL Server management studio (SSMS). Open SQL Server management studio 🡪 Connect to SQL Server instance 🡪 In object explorer, expand Server Objects 🡪 Right-click on Linked Server and select New Linked Server.
A dialog box New Linked Server opens. To configure the linked server, specify the following details on the General page.
- Linked Server: Enter the desired name of the linked Server. This name will be used by the queries to populate the data from PostgreSQL. In our demo, the name is DVDShop
- Server Type: Specify the server type. We are using ODBC driver to connect to a data source, therefore select Other Data source
- Provider: Specify the name of the provider. We are using Microsoft OLEDB Provider for ODBC Driver. So select it from the drop-down box
- Product Name: Specify the product name. Specify any relevant name. In our demo, the name is PostgreSQL DB
- Data Source: Specify the data source name that you have specified in the pSQLODBC driver setting. We have created an ODBC data source named pgadmin13, so specify pgadmin13 in it
- Provide string: If you have not configured the data source, you can specify the entire provider string. We have created a data source, so we are not entering the provider string
- Catalog: Specify the database name. It is optional, so we are not entering the database name
Following is the screenshot of the general page of the New Linked Server dialog box.
To connect to the PostgreSQL database, we must provide the login details. So, open the security page in the New Linked Server dialog box. We are using the PostgreSQL credential to connect to the PostgreSQL database, so select Be made using this security context option and specify the username and password of the Postgres user.
We are not changing the server options, so click OK to create a linked server named DVDShop and close the dialog box.
Test the connectivity of Linked Server
Now, let us test whether we can connect to PostgreSQL or not. To do that, right-click on DVDSHOP and select Test Connection.
The output of the Test connection command:
As you can see, the connection to the linked server succeeded.
View the objects of the DVDShop database
We can view the object created in the DVDShop database using DVDSHOP linked server. To do that, expand DVDSHOP 🡪 expand Catalogs.
Under Catalogs, you can view only DVDShop database because we have specified it in the ODBC data source configuration.
- Note: If you do not specify the database, you won’t see any database of PostgreSQL under catalog
Now, Expand DVDShop database 🡪 Expand Tables. Under tables, you can view the list of tables that have been created in DVDShop database.
Under Views, you can see the list of views that have been created in DVDShop database.
Access the data of DVDShop database using Linked Server
First, let us run the SELECT statement; we want to populate the data from the public.address table. Run the following query:
|
1 2 3 4 |
USE master go SELECT TOP 10 * FROM [DVDSHOP].[DVDShop].[public].[address] |
Summary
In this article, we learned how we can utilize the PostgreSQL ODBC Driver to create a linked server. We have learned the step-by-step configuration process of creating a linked server between SQL Server 2019 and PostgreSQL 13.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
- Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022