Simply because the Microsoft SQL Server has a user-friendly interface and also simple to understand, makes it the most popularly well-known database management system (DBMS) in the world. However, the system has two significant drawbacks, which might at times suggest end users to lookout for an alternative DBMS. They comprise of:
• strict licensing policies
• high cost of ownership (bad for managers of large databases)
Analyzing the open-source databases is highly recommended to be able to cut back on price tag of ownership. You will need the PostgreSQL. It is simply one of the most popular open-source DBMS with relational database with object-oriented database functionality, making it the most suitable choice when it comes to data integrity as well as high level of reliability.
The procedure to migrate SQL Server to PostgreSQL requires the steps listed below:
• export MS SQL table definitions
• convert them to the PostgreSQL format
• load the final results to a PostgreSQL server
• export the source SQL data to an intermediate storage
• convert data to the PostgreSQL format
• load into the target database.
How to Export MS SQL Table Definitions
• For SQL 2008 and earlier versions; simply right-click on database in Management Studio, then click on Tasks, Generate Scripts. When navigating though the wizard, make sure to set “data” option to false.
• for SQL 2012 and later versions: right-click on database in Management Studio, then click on Tasks, Generate Scripts. On the “Set scripting options” tab click on Advanced, and select “data only”, or “data and schema” for “Types of data to script” (in the General section).
Correct the resulting script before you proceed to the next step.
How You Can Load Outcomes to PostgreSQL
• remove MS SQL specific statements (i.e. “SET ANSI_NULLS ON”, “SET QUOTED_IDENTIFIER ON”, “SET ANSI_PADDING ON”)
• replace square brackets around database object names and types by double quotes
• default MS SQL schema “dbo” must be replaced by “public”
• remove all optional keywords that aren’t supported by the target DBMS (i.e. “WITH NOCHECK”, “CLUSTERED”)
• remove all reference to filegroup (i.e. “ON PRIMARY”)
• replace types “INT IDENTITY(…)” by “SERIAL”
• update all non-supported data types (i.e. “DATETIME” becomes “TIMESTAMP”, “MONEY” becomes NUMERIC(19,4))
Step 2 is to process the data, which can be performed by using the MS SQL Management Studio.
• right-click on database, then click Tasks, Export Data
• go through the wizard selecting ” OLE DB Provider for MS SQL” as data source, and “Flat File” as the target.
Once export is performed, the exported data will appear in the destination file inside the comma-separated values (CSV) format.
Workaround has to be applied if a few of the tables contain binary data to migrate from SQL Server to PostgreSQL. To achieve this, check out the wizard page and then click the “Write a query to specify the data to transfer” option. This wizard page can also be referred to as “Specify Table Copy or Query”. On the next wizard page known as “Provide a Source Query”, create the following SELECT-query:
SELECT
nonbinaryfield1,
nonbinaryfield2,
cast( master.sys.fn_varbintohexstr(
cast( binaryfield as varbinary(max))) as varchar(max)
) as binary-field-name
FROM
mssql_table_name
The query goes into an infinite hang, making this approach not applicable for large binary data, say 1MB and above.
How You Can Load the Resulting CSV File to the Target PostgreSQL Table
Make use of the “COPY” as follows: COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;
Try the “\COPY” command in case you get a “Permission denied” error message with the “COPY” command.
The series of steps mentioned above suggests that task to migrate SQL Server to PostgreSQL does call for a great deal of effort and it is often a complex process. Manual conversions cost a lot, time-consuming, and might often cause loss of data or corruption resulting in incorrect results. However, you will find modern tools, that can enable you to convert and migrate data between two DBMS in a few clicks.
Intelligent Converters, is a vendor of software programs that specializes in database conversion and synchronization procedures since 2001, made such tool to migrate SQL Server to PostgreSQL.
The tool, upon direct link with both source and target databases, provides a high quality conversion that doesn’t require ODBC drivers or any other middleware components. Instead, it uses low-level libraries such as Microsoft OLE DB to read the source data and PostgreSQL C API (aka libpq) to write data into the target tables. Additionally, it allows scripting, automation and scheduling of conversions through the command line version included in the installation pack of the product.
Other features of the product:
- Works with recent versions of on-premises and cloud variations of SQL Server and PostgreSQL
- Tables, data, indexes, constraints, sequences and views are migrated from SQL Server to PostgreSQL
- Complex data types (like XML and spatial) are supported
- Option to connect to PostgreSQL server via SSL
- Existing PostgreSQL tables can be merged or synchronized with SQL Server data
- Filtering data to migrate from SQL Server to PostgreSQL through SELECT-queries
- Option to edit tables (customize name and other attributes of any column) and customize type mapping
This SQL Server and PostgreSQL converter does not support stored procedures/functions and triggers. Free trial version of the product migrates not more than 50 rows per table, does not migrate foreign keys and views.
