SQL Database Migration: Steps for a Smooth Transition

Maintaining a complete and consistent data repository is essential for an efficient organizational data workflow. You can achieve this by migrating data from different sources into a centralized SQL database. This data is quickly retrievable, simplifying analytics and business intelligence activities.

SQL databases are particularly reliable data systems that help you query and analyze high volumes of data. By leveraging these capabilities, you can use SQL databases for diverse real-world applications, including social media analysis, fraud detection, and healthcare analytics.

Let’s learn what are migrations in SQL and different techniques for migrating data between two SQL databases. These methods involve using robust platforms like Airbyte or manual approaches utilizing tools such as SSMS and pgAdmin.

What are Data Migrations in SQL?

SQL data migration is the process of transferring data between SQL databases to improve accessibility and support enterprise data analytics. An SQL database is a relational data storage system that allows you to store, query, and analyze data using the SQL programming language. This simple computational language provides the necessary commands for retrieving, querying, and transferring data between different databases. Some prominent examples of SQL databases are MySQL, MSSQL, PostgreSQL, and Oracle Database.

Your Step-by-Step Guide on SQL Database Migrations with Airbyte

Database migration involves extracting data from a source data system and loading it to a destination database. Considering the compatibility of the target system, you might need to perform certain transformations. Several database migration tools are available that facilitate the automation of the SQL migration process. One prominent solution is Airbyte, an effective data movement platform. It offers a vast library of 550+ pre-built connectors to help you perform SQL data migration with ease.

Here, we will migrate data from Microsoft SQL Server (MSSQL) to PostgreSQL. The steps for performing SQL Server data migration using Airbyte are as follows:

Step 1: Set up MSSQL as a Source

• Log in to your Airbyte Cloud account. On the dashboard, click Sources from the left navigation pane.
• Enter MSSQL in the Search box and click on the MSSQL Server Connector.

• You will be redirected to the Create a Source page. Here, enter the mandatory fields: Host, Port, Database, Username, and Password.

• In the SSL Method field, mention the encryption mechanism through which you want to sync your data. There are three options: Unencrypted, Encrypted (trust server certificate), and Encrypted (verify certificate).
• Next is the Update Method field, where you need to specify the approach using which you want to extract data. It consists of two approaches: Read Changes using the Change Data Capture (CDC) and Scan Changes With User-Defined Cursor. To learn more about these approaches, you can refer to the MSSQL documentation.
• Next, you may enter the optional fields Schemas and JDBC URL Params.
• Finally, click the Set up Source button.

Step 2: Set up Postgres as a Destination

• Click on Destinations from the left navigation pane. Enter Postgres in the Search box.

• You will be redirected to the Create a Destination page. Here, enter the Destination Name, Host, Port, DB Name, Default Schema, and Username.

• Next, select SSL Modes or SSH Tunnel method for data authentication.
• Lastly, click the Set up Destination button.

Step 3: Create Connection

• On the dashboard, click Connections from the left navigation pane. Choose MSSQL Server as the source and Postgres as the destination.
• Select a suitable data sync mode from Incremental Append or Full Refresh sync modes.
• Next, toggle the streams that you want to replicate. In Airbyte, streams are the group of related data records. Click Next.
• You will be redirected to the Configure Connection page. On this page, enter the fields, including the Connection Name, Schedule Type, Replication Frequency, and Destination Namespace.

• Finally, click Finish & Sync.
• You will be redirected to the Connection Overview page. It consists of various tabs, including Status, Timeline, and Schema, which you can use to overview your connection.
• After the first sync is completed, you can check the data in the destination to verify whether the sync was successful.

This 3 step approach completes the data migration from MSSQL Server to Postgres using Airbyte. You can then integrate Airbyte with dbt, a command line tool, to perform various data transformation operations, including removing duplicate or null values. This standardized data can be analyzed and visualized to gain insights for business growth.

Another method to migrate data from MSSQL to Postgres is by using PyAirbyte. It is an open-source Python library that provides a set of utilities for using Airbyte connectors in the Python ecosystem. You can use PyAirbyte to extract data from sources such as MSSQL and load it to SQL caches. This PyAirbyte-cached data is compatible with Python libraries like Pandas, where you can clean and transform the data. You can then load this data to Postgres as the destination for further analysis and reporting.

Manual Method for SQL Database Migrations

In this method, you need to extract data from the MSSQL Server, store it in a local CSV file, and export it into Postgres. To perform this, you can use SQL Server Management Studio (SSMS) and pgAdmin.

Step 1: Extract Data from the MSSQL Server

SSMS provides essential utilities to access and manage SQL Server data. You can follow these steps to extract data for SQL Server replication using SSMS:

• Log in or sign up for SSMS and connect to the database from which you want to export the table.
• Expand the Databases icon and right-click to navigate to Tasks > Export Data.

• Now, on the SQL Server Import and Export Wizard page, click Next.

• You will be redirected to the Choose a Data Source window. Here, enter your Data Source name and other details, including the authentication method. Click Next.

• In the Choose a Destination window, from the Destinations drop-down menu, select Flat File Destination.
• Enter your file name in the File Name field. After this, to select the file destination path, click Browse and select the path to store a CSV file. Click Next.
• On the Specify Table Copy or Query page, choose a suitable option and click Next.
• Select the table that you want to export in the Source Table or View drop-down menu. Click Next.
• On the Save and Run Package Window, click Next.
• Finally, click Finish. You will get a report of your export as follows:

• To save the report, click Report and then choose the desired saving option. Lastly, click Close.

The other manual ways of transferring data from MSSQL Server to CSV file are:

• Using PowerShell, a task automation and configuration management program with a command line shell.
• Using the Bulk Copy Program (BCP) Tool.
• By Exporting SQL Query Results to CSV With or Without Headers using SSMS.

After extracting data from SQL Server using a suitable method, you need to perform cleaning and transformation operations. You should ensure that the column names and data types match before loading them into Postgres. This can be a time-consuming process when performed manually.

This completes the process of exporting data from the MSSQL Server to a CSV file.

Step 2: Load SQL Server Data from the CSV File to Postgres

You can now transfer the CSV data to the Postgres database using any of the following two methods:

‍pgAdmin is an open-source management tool, and you can use it to copy data into Postgres tables through these steps:

• Log in or sign up for the pgAdmin if you do not already have an account. Create a table in its GUI and click Tables > Create >Table.

• You will see the Create-Table page. Here, enter the Name of your table and specify the Schema. Click Save.

• From the left navigation pane, right-click, click on the table name, and then Import/Export.

• On the Import/Export Data page, in the Options window, enter the details, including Filename, and set the format as CSV. Lastly, click OK.

• After successfully copying data from the CSV file to the Postgres table, you will see the following popup.

An alternative approach for loading data from a CSV file to a Postgres table is by using a COPY command. First, you can create a table in Postgres. Here is a sample code for a table named employee:

CREATE TABLE employee(
  id SERIAL,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  dob DATE,
  email VARCHAR(255),
  PRIMARY KEY (id)
);

Now, you can use the COPY statement in the following manner to transfer data from CSV to Postgres:

COPY employee(first_name, last_name, dob, email)
FROM 'C:\sampledb\employee.csv'
DELIMITER ','
CSV HEADER;

This completes the MSSQL to Postgres data migration using SSMS and pgAdmin.

The manual approach has some limitations, such as:

• Limited Scalability: Handling large datasets manually can be challenging and inefficient, especially for frequent migrations.‍
• Time-Consuming: Migrating data manually is tedious as the procedure involves numerous intermediate steps. This can result in delays, decreasing the overall operational efficiency of your data transfer process.‍
• Complex: You may find the manual data migration method complex if you do not know how to use SQL databases, PgAdmin, or ODBC drivers. The learning curve of these solutions is high, making migrating data through manual processes difficult.‍
• Difficulty in Tracking Changes: Tracking changes made during manual migration can be cumbersome. It can be difficult to troubleshoot issues and revert to previous states.‍
• High Costs: Due to operational complexity, errors are highly likely to occur during manual data migration. The presence of errors increases the need for rework, which results in more resource consumption and elevates the total operational costs.

Why Should You Choose Airbyte to Perform SQL Database Migrations?

• Flexibility to Develop Custom Connectors: Airbyte allows you to build your own custom connectors. Connector Builder, Low Code Connector Development Kit (CDK), Python CDK, and Java CDK are some options Airbyte offers to build custom connectors.‍
• AI-enabled Connector Builder: While developing custom connectors using Connector Builder, you can use AI assistant. It pre-fills and configures several fields and also provides intelligent solutions to fine-tune the connector configuration process.‍
• Change Data Capture (CDC): Airbyte’s CDC feature lets you incrementally capture changes made at the source and replicate them at the destination. This helps you to keep your source and destination in sync with each other, ensuring data consistency.‍
• Schema Change Management: You can manage changes made to the source schema for each connection by manually refreshing the schema whenever required. Using this feature, you can ensure efficient data synchronization.‍
• Streamline GenAI Workflows: With Airbyte, you can directly load semi-structured and unstructured data into vector store destinations like Pinecone or Milvus. You can integrate these vector stores with LLMs to enhance the functionality of GenAI workflows.

Conclusion

This blog gives you a comprehensive overview of what are migrations in SQL and different techniques of migrating data between MSSQL Server and Postgres. Opting for manual techniques is time-consuming and expensive. So, you can use no-code platforms like Airbyte to migrate data effectively. After migration, you can easily access the data for further analysis, reporting, or business intelligence purposes.