Simply because the Microsoft SQL (MS SQL) 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 program has two significant drawbacks, which might at times suggest end users to look out 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.
To move database from MS SQL to PostgreSQL, the process below are required:
- 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 anointer mediate storage
- convert data to the PostgreSQL format
- load into the target database.
How You Can 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. Ensure to check the wizard to see that “data” is set 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.
How You Can Load Outcomes 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 aren’t supported by the target DBMS (i.e. “WITH NOCHECK”, “CLUSTERED”)
- remove all reference to file group (i.e. “ON PRIMARY”)
- replace types “INT IDENTITY(…)”by “SERIAL”
- update all non-supported datatypes (i.e. “DATETIME” becomes “TIMESTAMP”, “MONEY” becomes NUMERIC(19,4))
- replace the MS SQL query terminator “GO” with the PostgreSQL one”;”
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 and select “Microsoft OLE DB Provider for SQL Server” as data source, and “Flat File Destination” as destination.
Once export is performed, the exported data will appear in the destination file inside the comma-separated values (CSV) format.
Work around has to be applied if a few of the tables contain binary data. 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( binaryfieldasvarbinary(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 1 MB 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 database migration 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 in correct results. However, you will find modern tools, that can enable you to convert and move data between two DBMS in a few clicks, and the MSSQL-to-PostgreSQL is just one of those tools and they are also readily available on the market.
Intelligent Converters, is a vendor of software programs that specializes in database conversion and synchronization procedures since 2001, made the SQL Server to PostgreSQL tool.
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. Additionally, it allows scripting, automation and scheduling of conversions.