On Sat, 21 Jul 2001, Richard Huxton wrote:
> From: "Nate Carlson" <natecars@real-time.com>
>
> > We have a need to replicate a Microsoft SQL server out to a PostgreSQL
> > server. Pretty much, the client uses SQL server right now, and we don't
> > want to expose that to the internet in any way, so we want to set up a
> > Postgres box with the same data at the colo facility. Also helps to have a
> > box you can really admin remotely. :)
> >
> > The data will never be updated on the Postgres box, so it will be a
> > one-way replication.
>
> Not heard of anyone doing this - be interested in hearing how you make out.
>
> MS-SQL <=> MS-SQL replication is probably going to be tricky to hack for
> this situation, but you might be able to do something with Access
> replicating from the MS-SQL server. Link to the Postgres server via ODBC.
> Can't say I've tried it, but that would be my first attempt.
Interesting. SQL Server -> Access is a done deal, so that is no problem.
There are scripts already to move from Access -> PGSQL. (Not usually using
ODBC; most instead generate an SQL dump, which you can then load. I think
that's even nicer.)
I guess it hangs on how much of the real stuff is lost between SQL Server
and Access; they're rather mismatched systems.
If you want to do it directly SQL Server <-> PG:
How sophisticated is the MS SQL server data? Are there customized
functions/types/views? Or do you just need to move the table data?
If the MS SQL server has some functions/views/etc, you could rewrite these
in PG (which might require some translation). If they don't change in MS
SQL (ie you're not writing new functions every week, etc.), you could just
keep[ these on the PG box.
The data can be brought over by dumping the MS SQL table data and
importing into PG. There are, of course, some differences in SQL types and
language between the two. I'm not very familiar w/SQL Server, but it would
seem that, for straightforward table data, a few short hours in
Perl/Python/PHP would give you a script that would convert their typenames
to ours.
Someone may have written a script like that already; if not, at
techdocs.postgresql.org, there are examples for MySQL<->PGSQL and
Interbase<->PGSQL.
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington