Microsoft SQL Server (MSSQL) to PostgreSQL Migration

As already mentioned in a previous blog, we want to migrate our database from MSSQL to PostgreSQL and the first step of this was to see if this was even possible.

We decided on a fairly straightforward table (customers), that had many fields and many records (20000), but little in the way of constraints.

There are a few guides online showing the easy steps to go from a typical MSSQL database to the advantageous PostgreSQL, one of them being (http://www.postgresql.org/docs/techdocs.29)

Basically the steps in that guide explain how to use the MSSQL Enterprise Manger tool, to firstly create a script of your table, and then change all invalid data types, and then how to create a dump of data and export it to PostgreSQL.

This is all very self-explanatory, if the above process works; but as per usual, not everything goes to plan!

When trying to export our data, we had the unfriendly error message (EXCEPTION_ACCESS_VIOLATION) and that was the end of that.

Research into this error message suggested the need to upgrade or MSSQL to SP4, but it already was!

Further research began to show that other people had been having the same issues, and that there was not much of a solution present.

At this stage, I now considered looking for another method to migrate our table, there were a few easy and complex ways to do it, such as running scripts on our database, or using other extraction tools, and I tried a coupe of different methods but the same error message stopped us in our tracks.

I then decided to try an alternative way, by using a custom PHP script. I made the script to read each record from our MSSQL database, and to add each INSERT statement created to a .txt file. This seemed to work fine, until I tried to run the file on our Linux server to add the 20000 records. The first time I ran it only 8 records were entered, this is a big gap compared to 20000!