Simply because the Microsoft SQL (MS SQL) is user-friendly and has simple interface that is easy to understand, tends to make it just one of the most widely known database management system (DBMS) worldwide. The program however has two significant drawbacks, which at times mean users ought to seek substitute DBMS. They consist of:
- strict licensing policies
- very high cost of ownership (bad for those who own large databases)
Evaluating the open-source databases is suggested as a way to cut down on price tag of ownership. In which, you will find three main open-source databases including:
- SQLite
- MySQL
- PostgreSQL
SQLite can be described as file-based database as well as a self-contained database system, developed and embedded only into applications, hence can’t be found in the multi-user environment as an alternative for big databases.
The MySQL, alternatively, is a bit more powerful and offers features that are meant for a highly developed RDBMS. These functions include: scalability, security, as well as other storage units for various functions. A couple of its drawbacks comprise of,
- no support for full text search
- doesn’t apply the full SQL standard into action
- poor support for parallel writes in a few database engines
The PostgreSQL stands out as the standard RDBMS with relational along with integral object-oriented database functionality, that makes it the best option when data integrity as well as high level of dependability are needed.
To move your database from MS SQL to PostgreSQL all of the following will help you achieve result:
- export MS SQL table definitions
- convert them to the PostgreSQL format
- load the 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.
The Best Way To Export MS SQL Table Definitions
- for the SQL 2008 and earlier versions; right-click on database in Management Studio, then simply click on Tasks, Generate Scripts. Make sure to look at the wizard to find out if the “data” is defined to false, which is default.
- 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.
The Best Way To Load End results To Postsql
- 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 by double quotes
- remove square brackets around types
- replace default MS SQL schema “dbo” by PostgreSQL “public”
- remove all optional keywords that are not 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))
- replace the MS SQL query terminator “GO” with the PostgreSQL one “;”
The next phase is to process the data, by making use of MS SQL Management Studio.
- right-click on database, then click Tasks, Export Data
- browse through the wizard and choose “Microsoft OLE DB Provider for SQL Server” as data source, and “Flat File Destination” as destination.
As soon as export is done, the exported data will appear in the destination file within the comma-separated values (CSV) format.
Workaround need to be applied if a few of the tables contain binary data. To achieve this, browse through the wizard page and click on the “Write a query to specify the data to transfer” option. This wizard page can also be referred to as the “Specify Table Copy or Query”. On the next wizard page referred to 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 enters into an infinite hang, which makes this technique not appropriate for big binary data, say 1MB and above.
The Best Way To Load up The Resulting CSV File To The Target PostgreSQL Table
- Use the “COPY” as follows: COPY table-name FROM path-to-csv-file DELIMITER ‘,’ CSV;
Try the “\COPY” command if however you get a “Permission denied” error message with the “COPY” command.
The series of steps mentioned above suggests that database migration need a great deal of effort and is often a complex approach. Manual conversions can be very expensive, time-consuming, and may often result in loss of data or data corruption resulting to incorrect results. You will find better technology currently available to help you convert and migrate data between two DBMS within few clicks, and also, the MSSQL-to-PostgreSQL is among those tools. Intelligent Converters, the program vendor, who specializes in database conversion and synchronization techniques since 2001, created the MS SQL to PostgreSQL migration tool.
The application, upon direct link with both source and target databases, provides a high quality conversion which doesn’t require ODBC drivers or any other middleware components. Additionally, it gives room for scripting, automation and booking of conversions.
