In this article, we will describe how to link a PostgreSQL Database to SQL Server, compare and deploy the PostgreSQL data against SQL Server with the help of dbForge Studio for SQL Server.
The workaround for the task suggested in this article consists of three major steps:
- Creatiing a linked server for linking a PostgreSQL database to SQL Server.
- Creating two SQL Server Databases.
- Comparing PostgreSQL table data against the SQL Server one.
Creating PostgreSQL Linked Server
The first thing we need to do is to link PostgreSQL database (in our case, the database is called jordan_sanders_DB) to SQL Server. For this step, we will use Microsoft SQL Server Management Studio and any available ODBC driver for PostgreSQL. We settled upon Devart PostgreSQL ODBC Driver for that provides quicker and more stable access to PostgreSQL databases in comparison with the standard Microsoft ODBC driver.
So, download and install Devart ODBC Driver for PostgreSQL. For the step-by-step installation instructions, refer to the product documentation.
After the driver is installed, we can go forth and set up new system data source, i.e. the PostgreSQL database we want to link. For the step-by-step configuration instructions, refer to the product documentation.
Next, launch Microsoft SQL Server Management Studio. In Object Explorer, expand the Server Objects node, rich-click Linked Servers, and select New Linked Server… from the shortcut menu.
The New Linked Server window opens. On the General tab of the window:
- provide a new linked server name;
- select Other data source as a Server type;
- in the Product Name and Data Source text boxes, specify the name of the created System data source;
- in the Catalog text box, specify the name of the PostgreSQL database.
Finally, click OK to complete the creation of the linked server.
Creating SQL Server Databases
The next step is the creation of SQL database, the first one will serve as Source with the linked PostgreSQL data, and the second one will serve as Target for comparison and deployment of the PostgreSQL data.
In dbForge Studio for SQL Server, we need to create two databases:
- PG_Source_DB with the views in which the created Linked Server is used. For instance, in our Postgre SQL database, jordan_sanders_DB, there is the table_link table with the id and column1 columns. In our case, the query for the view will be the following:
SELECT id, column1 FROM [NEW_LINKED_SERVER].jordan_sanders_DB.[public].table_link
In a similar way, we need to create the views for the rest of the tables you have. - PG_Target_DB with the structure similar to the PostgreSQL database.
Comparing Databases
Finally, it’s time for data comparison. In dbForge Studio for SQL Server, click New Data Comparison on the Comparison menu.
In the New Data Comparison wizard, set PG_Source_DB as Source, and PG_Target_DB as Target.
Next, we need to map the source and target tables. For this, switch to the Mapping tab of the wizard and click Custom Query. The Custom Queries Mapping dialog box opens.
In the Source object section of the dialog box, select Query, provide a query selecting data from the previously created view.
In the Target object section, select Table or view, and select the corresponding target table, and click OK.
Back on the Mapping page of the wizard, select Custom from the Comparison key drop-down list box to set the comparison key.
The Column Mapping dialog box opens. In the window, tick the Key checkbox next to the id column. Click OK.
If you have more than one table, set the comparison keys for all of them.
Finally, click Compare to start comparing databases. Here is the comparison result:
As you see, dbForge Studio for SQL Server has successfully compared the databases and found diffs (9 records) that can be easily deployed against Target with a single click of the Sync button.
Conclusion
In this how-to article, we considered a solution for the case when we need to compare PostgreSQL and SQL Server databases using Linked Server and the data comparison feature of dbForge Studio for SQL Server. With the state-of-the-art settings options, there’s nothing impossible for dbForge Studio. Download a free 30-day trial, and check for yourself.
