October 8, 2003 at 6:36 am
Hi! I am new to this community but find it invaluable. I have one problem that is plaguing me and I need a solution which seems to be allusive to me. We have legacy databases under the Open Source PostgreSQL DBMS. These databases are very large and are updated constantly (24/7) and get larger by the day. I decided to use SQL Server to generate reports and statistics from these databases using a combination of tables and OLAP cubes (depending on the report requested). I currently update the SQL Server database once per hour using a DTS script which I am finding is not the best option as the data is becoming so large that VM is exhausted. I have tried a VB program and a scheduled Cold Fusion page to deal with this. The upshot, I would like to perform transactional updates from the PostgreSQL database (which is NOT going away - not my decision - I would love to do away with it, but it is not practical now), to the SQL Server databases. Timeliness of data in the reports is crucial and a 60-70 minute delay is not acceptable to our users. Replication procedures for SQL Server 7.0 have not been much help *from* non-SQL Server databases. Any assistance anyone can provide would be greatly appreciated. Thanks!
Joe Geiser
-- Joe
October 13, 2003 at 12:00 pm
This was removed by the editor as SPAM
October 14, 2003 at 4:57 am
The first idea that comes to mind is to write your own "replication". The approach I've used in the past is to use triggers (don't know if you have them?) to record the changes, then use some code to apply those changes to the "subscriber". Because you're doing one change at a time it's lean, if chatty because of the number of network trips you have to make.
Andy
October 14, 2003 at 6:59 am
Thanks Andy - it seems that I will have to use the Postgres Replication contributed product DBMirror, which is written in Perl. Since there is a Perl Library (FreeTDS) that permits direct writes to SQL Server, this can probably be updated to write directly to a SQL Server database. This is the path I'm exploring now.
Again, thanks for the reply. Film at 11 on how this works out.
-- Joe
October 14, 2003 at 10:06 am
If you get that working we'd be interested in a write up that we could publish. Let me know how it turns out.
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply