Thread: Microsoft SQL Server Replication

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.

Is this possible? If so, anyone have pointers on where to go for
documentation on this?

Oh, and a SQL dump really won't work.. it's about 9gb of data right now
(getting larger every day), and changes to the main DB need to be
replicated in a timely fashion (5-10 minutes lag is OK, though).

I tried searching the archives, but they appear to be down.  :(

--
Nate Carlson <natecars@real-time.com>   | Phone : (952)943-8700
http://www.real-time.com                | Fax   : (952)943-8500

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.

- Richard Huxton

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

This is a pretty standard task for something like Lotus Enterprise
Integrator. While that package has it's limitations and I curse it
occasionally, it does ok. Either that or just code something up to do the
replication. You could even use a common scripting language <cough perl
cough> and do it simply.

Josh

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.
>
> - Richard Huxton
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>