Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Blastrix
Posting Yak Master
208 Posts |
 Posted - 2003-11-15 : 11:16:49
|
| At the risk of being burned at the stake for blashpheming... Does anyone have any experience with replicating data from a SQL Server that takes CRUD, to a PostgreSQL system? We're not quite interested in taking our whole system to PostgreSQL right now. Mainly because it seems that SQL Server is more solid for CRUD operations. But we are interested in using PG servers for our front line SELECT servers, since they would be nice and free(rather than a license for SQL Server Standard).Does anyone have any performance tests they've done between the two, any experience with replicating data between the two? Drawbacks, pitfalls, bonuses?Thanks,Steve |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-11-15 : 21:01:01
|
| If you just want selects, and they are faily simple, MySQL might be a better choice (oh my god shoot me now, I don't believe I just said that). The main reason is that MySQL is better documented, easier to adminsiter and it's architecture makes read only performance very quick. Also, it's Windows package is stable as well. Postgres needs messing around with Cygwin and is generally known to be painful to run under windows.Postgres is an excellent RDBMS, it is ultra stable and handles complex stuff much better than MySQL, but for simple, read only access MySQL might save you some headaches. However, if your queries use some complex joins, I'd stay with Postgres. I have seen MySQL have horrible performance doing stuff that I would consider trivial under MS SQL.Anyway, if you are going with that sort of architecture, configuring the replication should be pretty simple. I would hand roll a solution. Either by just dumping all the data and reinserting, or have a datestamp column on every table and writing a process that inserts or updates any data since your last sync.Damian |
 |
|
|
Blastrix
Posting Yak Master
208 Posts |
Posted - 2003-11-16 : 12:31:35
|
| Yeah, we had looked at MySQL, but for exactly the reasons you mentioned, we decided it was not the right option. We do do quite a bit of complex joining.We're not too worried about running PG, as our staff is quite experienced with FreeBSD as well as Windows. On a side note, v7.4 of PGSQL contains a Win32 port, no more Cygwin. v7.4 is due out in the next one to two weeks.What would be the best method of propagating changes from SQL Server to the PG servers? Should the SQL Server push the data, or should the PG servers pull it?Steve |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-11-16 : 12:41:26
|
| Assuming that PostGres has decent ODBC or OLE DB drivers, you may be able to configure them as linked servers in SQL Server. You can then create stored procedures to push the data to PG directly. You may even be able to set up replication, but I would try another method before that one. Lastly you can export the SQL Server data to flat text files and import them into PG. DTS might also work; again, it depends on the quality of PG's drivers. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-11-16 : 20:14:10
|
quote:
(oh my god shoot me now, I don't believe I just said that)
Damian,  --------------------------------------------------------Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url] |
|
|
|
|
|
|
|
|
SQL Server Data to PostgreSQL?