In this article, we will explore the process of configuring a linked server between SQL Server and PostgreSQL. A linked server allows SQL Server to access data from external data sources, such as PostgreSQL, as if it were a local database.
Environment Setup
Before we begin, make sure you have SQL Server 2019 and PostgreSQL 13 installed and configured on your workstation. Additionally, create a database named “DVDShop” in PostgreSQL.
Configuring the ODBC Driver
To connect SQL Server to PostgreSQL, we need to configure the ODBC driver. Open the Control Panel, navigate to Administrative Tools, and open the ODBC Data Source (64 bit) tool. In the System DSN tab, select the ODBC driver for PostgreSQL (e.g., pgadmin13) and click on Configure. In the configuration dialog, change the database name to “DVDShop” and test the connectivity. Save the changes and close the dialog.
Creating the Linked Server
Next, we will create a linked server using SQL Server Management Studio (SSMS). Connect to your SQL Server instance and expand the Server Objects in the Object Explorer. Right-click on Linked Server and select New Linked Server. In the New Linked Server dialog, provide the following details:
- Linked Server: Enter a name for the linked server (e.g., DVDShop).
- Server Type: Select “Other Data source”.
- Provider: Select “Microsoft OLEDB Provider for ODBC Driver”.
- Product Name: Specify any relevant name.
- Data Source: Enter the name of the ODBC data source (e.g., pgadmin13).
- Security: Select “Be made using this security context” and provide the username and password for the PostgreSQL user.
Click OK to create the linked server.
Testing the Connectivity
To test the connectivity of the linked server, right-click on the linked server (e.g., DVDShop) and select Test Connection. If the connection is successful, you should see a confirmation message.
Accessing Data from the Linked Server
Once the linked server is created, you can access the data from the PostgreSQL database using SQL Server. For example, to retrieve data from the “address” table in the “DVDShop” database, you can run the following query:
USE master;
SELECT TOP 10 * FROM [DVDSHOP].[DVDShop].[public].[address];
Make sure to replace “DVDSHOP” with the name of your linked server and “DVDShop” with the actual database name.
Summary
In this article, we have learned how to configure a linked server between SQL Server and PostgreSQL using the ODBC driver. By creating a linked server, we can access data from PostgreSQL as if it were a local database in SQL Server.