Migration from MSSQL to PostgreSQL?
– Do you know anyone who has gone this path or have experiences of your own?
– Which Delphi drivers would you recommend?
– Do you know of enterprise quality tools for PostgreSQL (editing, schema comparison and syncing, versioning, tuning, debugging)?
Do you know anyone who has gone this path or have experiences of your own?
no, but, unless you use something very specific, you should have any trouble switching to Postgre, even so, you should find something very similar!
Which Delphi drivers would you recommend?
We’re using UniDac successfully since around 2010 for postgresql and sqlite.
Do you know of enterprise quality tools for PostgreSQL (editing, schema comparison and syncing, versioning, tuning, debugging)?
PgAdmin works great, lightweight, available on multiple platforms(windows, nix, os x), we haven’t yet used advanced features, but there are plenty!
Since we are developing on multiple database instances, we need a good diff and sync tool. Today we use ApexSQL Diff for MSSQL, not only to sync schema and code changes between databases, but also to export sql scripts which we then commit to SVN.
Lars Fosdal Maybe you could try ZeosLib 7, I use it for MySQL, it’s stable and reliable.
Can you try it. http://tpostgres.org/se/
You will not be wrong if you depend on one of Devart’s solutions, either pgDac or Unidac.
Lars Fosdal +1 to ZeosDB I’m currently using v 7.2 to connect to PG 9.3 with succes
We did a version of this at my previous job — we’d started porting applications from a legacy DB system to MS SQL, then we switched to PostgreSQL.
From an administrative standpoint, getting things going with PgSQL wasn’t difficult, and porting the SQL wasn’t too bad for us. Having actual sequences was heavenly, but the continued lack of MERGE support is a real bite in the ass at times. I loves my MERGE statements!
For data access, we ended up picking UniDAC over postgresDAC mostly because it gave us a unified component interface to all the DBMS systems we’d need to talk to (MS SQL, PgSQL, MySQL, and SQLite was a nice bonus) — Devart’s support has been top notch and the components are actively maintained. We never had a lot of luck with ZeosLib, but this was before 7 was released. We’d almost chosen AnyDAC and in retrospect that seems to have been a good decision (Pro SKU owners).
pgAdmin is an OK tool for administering the server, but if you want something for doing DB diff & sync, you’ll need something more — I’d recommend Navicat or PostgreSQL Maestro. We had licences for EMS SQL Studio for PostgreSQL, but I was never very pleased with it — and it hasn’t had any significant updates in years.
To be honest, if your usage profile fits inside of MS SQL Express Edition, the SQL Server Data Tools offered by Visual Studio for schema and data compare are very, very nice. And free, even working in VS Express! I’ve never tried using them with other DMBS systems, but I get the feeling that they wouldn’t work. 🙂
The databases are rather large and busy, so Express Edition is not an option. We have a large number of MSSQL Standard edition servers, 50/50 2008 R2 and 2012 version at location, and a few enterprise version 2012 servers centrally, so migrating to PostgreSQL will save us a bundle on licenses.
Navicat looks good and maintained, so we’ll check it out, as well as pgAdmin.
As for drivers – any thoughts on the FireDAC drivers contra UniDAC?
Lars Fosdal
As for drivers – any thoughts on the FireDAC drivers contra UniDAC?
Embarcadero vs (company that bases a good part of it’s business on the components), need I say more?
Lars Fosdal _any thoughts on the FireDAC drivers contra UniDAC?_
One gets regularly maintained for every version of Delphi that it supports. The other abandoned their userbase after being bought by Embarcadero. 😛
If you regularly update all of your projects to new IDEs on release and have the right IDE SKU, I’m sure FireDAC is fine.
Graeme Geldenhuys – Red Gate mostly do MSSQL and Azure, it seems.
For data structure comparison and sync between development and production databases we’ve had good luck with DB Comparer from EMS SQL Manager
http://www.sqlmanager.net/en/products/postgresql/dbcomparer
Another vote for EMS Sql Manager. I have been using this tool for last 5 years. I use to synchronize data between development and production databases and general SQL queries.
For the development library I would recommend Devart. I have their Oracle library and documentation is much better the Firedac.