How to Migrate from MsSQL to PostgreSQL?

Easily move your data from MSSQL to PostgreSQL to enhance your analytics capabilities. With Hevo’s intuitive pipeline setup, data flows in real-time—check out our 1-minute demo below to see the seamless integration in action!

Database technology has been growing rapidly today and new innovations in this field are becoming quite common. Nowadays, many tools help you connect different databases with each other so that you can use the advantages of both technologies. One such process is connecting MsSQL to PostgreSQL.

Both MsSQL and PostgreSQL are widely used databases in big and small companies. They are widely used relational database tools that share several key similarities as well as differences. It is important to understand the key characteristics of both these databases in order to set up a connection between them.

This article provides a comprehensive guide on connecting MsSQL to PostgreSQL. Furthermore, you will also come across a  few limitations of this process. Read along to find out how you to perform a SQL Server connect to PostgreSQL for your business.

What are the Methods to Connect MsSQL to PostgreSQL?

There are 3 methods you can use to connect Microsoft SQL Server to PostgreSQL:

• Method 1: Using Hevo Data to Connect MsSQL to PostgreSQL
• Method 2: Manual MsSQL to PostgreSQL Connection
• Method 3: MsSQL Server to PostgreSQL using Migration Toolkit

Method 1: Using Hevo Data to Connect MsSQL to PostgreSQL

Step 1: Select SQL Server as the Source

Step 2: Select PostgreSQL as the destination

Method 2: Manual MsSQL to PostgreSQL Connection

Connecting MsSQL to PostgreSQL does not take a significant amount of time if you use this method and is a pretty straightforward process. You need to go to your Object Explorer and follow the given steps to set up the connection:

• Step 1: Create a Linked Server in MsSQL
• Step 2: Access the Data Stored in PostgreSQL

Step 1: Create a Linked Server in MsSQL

To perform this step follow the given procedure:

• Start your MsSQL management studio and choose ‘SQL Server Instance.’ 
• Head on to the Object Explorer pane and click on ‘Server Objects’ to expand. Locate Linked Servers and click to create a new Linked Server. 
• Once the new dialog box appears, perform the following:
  • Name your Linked Server.
  • Next, once prompted to specify the server type, click on ‘Other Data Source.’
  • A drop-down list with providers will appear. Select ‘Microsoft OLE DB Provider for ODBC Drivers.’
  • Finally, key in the name of your DSN in the Data Source Field.

Once you have performed all these steps, you will notice a Linked Server appearing in the object explorer pane. As mentioned earlier, Linked Servers allow you access to data stored in Non-SQL sources such as PostgreSQL. Hence, you can now distribute queries to and access data stored in PostgreSQL. 

Advantages of using SQL Server Linked Servers to connect to PostgreSQL

• The ability to connect to several local or remote host database instances.
• The capacity to execute distributed queries on many data sources within the organization.
• The capacity to operate with several data sources in the same manner.

Step 2: Access the Data Stored in PostgreSQL

Now that we have successfully added the linked server option in the Object Explorer pane here comes the fun part, accessing data stored in PostgreSQL. You should adhere to a set of steps for you to access your data successfully.

• First, head to the provider’s section, and under the list, locate ‘MSDASQL OLE DB’ and double-click on it as illustrated below:

•  Once you double-click, a list of provider options will appear as shown: 

• Disable the ‘Allow Inprocess’ option, which is illustrated above. Once this is done, create a new Linked Server under the Object Explorer pane as shown below.

• Under providers, ensure you click on ‘Microsoft OLE DB Provider‘ for ODBC Drivers and specify the following:

This is all you have to do, and you will now get access to the data in PostgreSQL. You can query your linked server by clicking ‘New Query’ in the toolbar. 

That’s it! You have successfully connected MsSQL to PostgreSQL using the Manual Method!

One limitation of this method includes:

• Creating a linked server with the ‘Allow Inprocess’ option enabled. –Always disable this option when creating the server, and in case you forget, just delete and repeat the process. 

To overcome this limitation, and several others, and connect MsSQL to PostgreSQL quickly and efficiently, you can use Hevo’s No-code Data Pipelines. Check out the next method to understand how Hevo can help you connect them within minutes.

Method 3: Migrate from MsSQL Server to PostgreSQL using Migration Toolkit

Installation instructions for EnterpriseDB’s Migration Toolkit

• Step 1: Open the Application Stack Builder first. When you download PostgreSQL, you also get Stack Builder.

• Step 2: Choose the PostgreSQL server on which the Migration Toolkit will be installed. Ensure that you have access to the Internet.

• Step 3: The wizard will display the available applications for installation. Go to EnterpriseDB Tools > Categories > Registration-required and trial packages. Check the Migration Toolkit and then move on to the next step.

• Step 4: You’ll be prompted to choose a download location. Select the folder you wish to download to and then click Next. You will be requested to install the Migration Toolkit after it has been downloaded. The Migration Toolkit is ready to use once it has been installed.

Building the toolkit.properties File

The toolkit.properties file is required to give the source and target databases’ configuration and connection information.

Step 1: Open the toolkit.properties file first. The file will be found in the following folder on Windows:

C:Program Filesedbmtketc

Step 2: Edit the file with your preferred editor. The following information should be included in the file:

• SRC_DB_URL : specifies the source database’s connection details.
• SRC_DB_USER : defines the source database’s username.
• SRC_DB_PASSWORD : provides the source database’s password.
• TARGET_DB_URL : specifies the target database’s connection details.
• TARGET_DB_USER : provides the target database’s username.
• TARGET_DB_PASSWORD : specifies the target database’s password.

The database’s URL must be in the following format:

{TARGET_DB_URL|SRC_DB_URL}=jdbc:edb://host:port/database_id

How to verify Database Migration Connection?

To verify a database migration connection, you will need to perform the following steps:

• Step 1: Establish a connection to the source database. This can typically be done using a database client or library in your programming language of choice. You will need to provide the necessary connection details, such as the hostname, port number, username, and password.
• Step 2: Test the connection. Once you have established a connection to the source database, you can test it by running a simple query, such as SELECT 1. This will verify that the connection is working and that you are able to communicate with the source database.
• Step 3: Establish a connection to the target database. Follow the same steps as above to establish a connection to the target database.
• Step 4: Test the connection to the target database. Run a simple query on the target database to verify that the connection is working and you are able to communicate with the target database.
• Step 5: Begin the migration. Once you have verified that both the source and target database connections are working, you can begin the process of migrating the data from the source database to the target database. This can typically be done using a database migration tool or by writing custom scripts to transfer the data.

Why Connect MS SQL Server to PostgreSQL?

MS SQL Server offers a vast and diverse collection of techniques for putting data into a database as a feature-rich and mature solution. The SQL Server Import and Export Wizard is one method for importing data into your database. You will be able to bulk load data from a variety of supported data sources using it and a visual interface.

PostgreSQL runs on a variety of platforms, including Linux, Unix, Windows, and Mac OS X. Supports primary keys, foreign keys, and exclusion constraints and is compatible with data integrity. It also supports various features native to SQL, including multi-conversion currency control, SQL sub-selects, and complex SQL queries. It is compatible with several data types such as arrays, integers, and boolean. 

Connecting the two platforms will help you gain the best out of your Data. You will be able to transform the data in a more efficient way.

Conclusion

This article gave you a step-by-step guide on connecting MsSQL to PostgreSQL. Overall, connecting MsSQL to PostgreSQL can be done using either the manual method or by using Hevo. You will need to implement it manually, which will consume your time & resources and is error-prone. Moreover, you need full working knowledge of the backend tools to successfully implement the in-house Data transfer mechanism. You must also regularly map new MsSQL data to Postgres as the AWS Pipeline is not fully managed.

Hevo Data provides an Automated No-code Data Pipeline that empowers you to overcome the above-mentioned limitations. Hevo caters to 150+ data sources (including 60+ free sources) and can seamlessly transfer your data from MsSQL to PostgreSQL within minutes. Hevo’s Data Pipeline enriches your data and manages the transfer process in a fully automated and secure manner without having to write any code. It will make your life easier and make data migration hassle-free.

Try a 14-day free trial and experience the feature-rich Hevo suite firsthand. Also, check out our unbeatable pricing to choose the best plan for your organization.

FAQ on MsSQL to PostgreSQL