The user-friendly capabilities and easy to learn interface of the Microsoft SQL Server (MS SQL), makes it to be one of the most widely known database management system (DBMS) in this field. Despite of all these benefits, MS SQL has two significant disadvantages that may force customers to find replacement for the DBMS:
- Strict commercial licenses
- High cost of product, technical support and related services
Evaluating the open-source databases is advisable so as to cut-down on total cost of ownership. There are three famous databases management systems falling in that list:
- SQLite
- MySQL
- PostgreSQL.
SQLite can be referred to as a document-based database and a self-contained database system, created and integrated only into programs, therefore, it cannot be used in the multi-user environment as an alternative for large databases.
MySQL is a more robust alternative, it offers most features of a sophisticated RDBMS: scalability, security, and various storage units for various functions. However, it has some weaknesses that may be essential limitation in building corporate scale data warehouse:
- No assistance for full text search
- Does not have full compatibility with SQL standard
- Inferior support for parallel writes in certain database engines.
PostgreSQL is a relational database management system coming with built-in object-oriented functionality. This feature makes PostgreSQL ideal choice when it comes to data integrity and high level of reliability.
To effectively move database from MS SQL to PostgreSQL, the following need to be done:
- export MS SQL table definitions as SQL CREATE-statements
- translate them to be compatible with PostgreSQL format
- import results into PostgreSQL database
- export the data to an intermediate CSV files
- process data with respect to differences between the SQL Server and PostgreSQL syntax (pay special attention to dates and binary data)
- import the processed data into the target database
How To Export SQL Server Table Definitions
Highlight database by right-click on its title in SQL Server Management Studio (SSMS), then click on Tasks, Generate Scripts. Enter the required settings and do not forget to verify the resulting script before you proceed to the next step.
The Best Way to Load Results to PostgreSQL
- remove MS SQL specific statements
- replace square brackets around all entry names by double quotes
- replace SQL Server default schema “dbo” by PostgreSQL equivalent “public”
- wipe out all keywords that are not supported by the target DBMS
- change types “INT IDENTITY(…)” by “SERIAL”
- update all non-supported data types (i.e. “DATETIME” becomes “TIMESTAMP”, “MONEY” becomes NUMERIC(19,4))
- replace query terminator “GO” used by SQL Server with semicolon (;) that is PostgreSQL equivalent
The next step is to process the data, which can be accomplished with the use of SSMS.
- Highlight database viz right-click, select Tasks and Export Data items in popup menu
- Navigate through the appeared wizard dialog to select data source “Microsoft OLE DB Provider for SQL Server” and destination “Flat File Destination”
After completing those steps, the data be exported in comma-separated values (CSV) format.
There is a workaround that must be applied to binary data. Click on the “Write a query to specify the data to transfer” option after going through the wizard page. On the next wizard page known as “Provide a Source Query”, create the following SELECT-query:
select <non-binary field #1>, <non-binary field #2>, cast(master.sys.fn_varbintohexstr(
cast(<binary field name> as varbinary(max))) as varchar(max)) as <binary field name>
from <table name>
The query runs into an infinite hang, this method is not suitable for heavy binary data, let’s say 1MB and above.
The series of steps mentioned above implies that the database migration requires a whole lot of effort and most times, it is often an advanced procedure.
Database migrations covered by human efforts are expensive, time-consuming, and may often result to data loss or corruption which could lead to inaccurate results. It is reasonable to use modern-day software, which can migrate data between MS SQL and PostgreSQL in a couple of clicks. One of such tools is SQL Server to PostgreSQL converter provided by Intelligent Converters, a software vendor focused on database migration and synchronization techniques since 2001.
The tool provides high performance of migration since it does not use ODBC or another middleware. It also can automate and schedule the database migration though command line support. Visit official site of Intelligent Converters to learn more about their SQL Server to PostgreSQL migration tool.
