I have a database in PostgreSQL 8.3.1 that I'd like to migrate to MS SQL Server 2005 (or maybe 2008), including both the table schema and the data. The database is about 50GB in size with about 400,000,000 rows, so I think simple INSERT statements are out of the question. Could anyone recommend the best tool for performing this migration? Obviously it needs to be reliable, so the data is exactly the same in the target DB as in the source one and it needs to be able to copy this volume of data within a reasonable time.
Sql-server – Best tool to migrate a PostgreSQL database to MS SQL 2005
migrationpostgresqlsql server
Best Answer
I ended up not using any third-party tool for the data as none of the ones I've tried worked for the large tables. Even SSIS failed. I did use a commercial tool for the schema, though. So my conversion process was as follows:
The transformation step took care of some differences in the formats used by pg_dump and bcp, such as:
I also found that some unique constraints that were fine in Postgres were violated in MSSQL, so I had to drop them. This was because NULL=NULL in MSSQL (ie. NULL is treated as a unique value), but not in Postgres.