The user-friendly interface of SQL Server and rich collection of easy-to-use administration tools coming with the product, makes it one of the most popular DBMS in the world. At the same time, SQL Server has two valuable issues that could force users to migrate from SQL Server to Postgres. This issues includes:
- Strict licensing policies
- High cost of ownership depending on database volume, number of servers, etc
- Lack of full support for SQL standard
On the way of cutting down the total cost of ownership it is reasonable to evaluate open-source database management systems: SQLite, MySQL and PostgreSQL.
SQLite is known as a self-contained DBMS designed for integration into applications (including mobile devices), and so it cannot be used as a reliable data warehouse in the multi-user environment that could be an alternative for SQL Server.
The MySQL is more robust and it offers standard capabilities that are expected from a sophisticated database such as: scalability, security, different storage engines and others. At the same time, MySQL still has some cons:
- Poor support for the full text search
- Lack of full compatibility with the SQL standard
- Inferior support for indexing models and parallel writing in some storage engines
The final option – PostgreSQL is the full featured advanced RDBMS that complies with SQL standard and provides all capabilities required for high-load corporate scale data warehouse such as built-in job scheduler, multi-version concurrency control, sophisticated partitioning and sharding and many others.
The effective database migration from SQL Server to Postgres is usually occurred according to the following steps:
- export SQL Server schema definitions as data definition language (DDL) statements CREATE TABLE table_name (columns)
- convert them to the PostgreSQL format and load into the target data7base
- export the source SQL Server data into intermediate CSV storage
- convert data to the PostgreSQL format and load into the target database.
- export views, stored procedures, functions and triggers in form of T-SQL script
SQL Server to Postgres Schema Migration
To Extract SQL Server table definitions right-click on database in SSMS, then click on Tasks, Generate Scripts. In the General section click “Advanced” on the “Set scripting options” tab, select “data only” or “data and schema” for “Types of data to script”.
Correct the resulting DDL scripts before loading it into PostgreSQL database:
- remove SQL Server specific statements (i.e. “SET ANSI_NULLS ON”, “SET QUOTED_IDENTIFIER ON”, “SET ANSI_PADDING ON”)
- replace SQL Server escape symbols for object names (square brackets) by PostgreSQL equivalent double quotes
- replace SQL Server default schema “dbo” by PostgreSQL “public”
- remove all keywords that are not supported by PostgreSQL (“WITH NOCHECK”, “CLUSTERED”, etc)
- convert types “INT IDENTITY(…)” and “BIGINT IDENTITY(…)” into SERIAL and BIGSERIAL correspondingly
- carefully map all non-supported data types into PostgreSQL equivalents on the range of acceptable values (“DATETIME” becomes “TIMESTAMP”, “MONEY” becomes NUMERIC(19,4), etc)
- change the SQL Server query terminator “GO” with the PostgreSQL one “;”
SQL Server to Postgres Data Migration
The next step is to migrate the SQL Server data to PostgreSQL via temporary storage in comma-separated values (CSV) format. Export of SQL Server data into CSV files can be done via SQL Management Studio as follows:
- Right-click on database, then click Tasks, Export Data
- Go through the wizard and select “Microsoft OLE DB Provider for SQL Server” as data source, and “Flat File Destination” as destination.
As soon as the export is finalized, the SQL Server data will be stored in CSV files. If some table contains binary data, it required a workaround for correct conversion. Click on the “Write a query to specify the data to transfer” option after going through the wizard page of SQL Management Studio. On the next wizard page known as “Provide a Source Query”, create the following SELECT-query:
select <non-binary field1>, <non-binary field2>, …, cast(master.sys.fn_varbintohexstr(cast(<binary field> as varbinary(max))) as varchar(max)) as <binary field name> from <table name>
After SQL Server data is exported into CSV forma, you can use COPY command to load it into PostgreSQL table as follows: COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;
SQL Server to Postgres Converter
The series of actions mentioned above implies that the database migration from SQL Server to Postgres is an advanced procedure requires a much time and efforts. Manual implementation of the migration is expensive and may result to data loss or corruption due to human errors.
Fortunately, there are many software solutions which can migrate database handling all required transformation between the two DBMS in a couple of clicks. One of these solutions is SQL Server to Postgres migration tool by Intelligent Converters, a software vendor, who specializes in database conversion and synchronization techniques since 2001.
Due to implementation of low-level reading and writing techniques for both the source and target DBMS instead of using ODBC drivers or other middleware, this SQL Server to Postgres converter offers a high performance of the database migration. The product supports all modern versions SQL Server and Postgres running on both on-premises and cloud platforms. It migrates schemas, data, indexes, constraints and views. Finally, the converter supports command line arguments that enables scripting, automation and scheduling of the migration.
