Re: Microsoft SQL Server Replication - Mailing list pgsql-general

If you are really just duplicating the database, not using replication (transactions performed on both databases more
orless simultaneously) this is a very easy thing to do.   

Dump the table defininitions with the MS SQL Server scripting tool.  Include the indexes.  Use your favorite scripting
language(or do it by hand) to force the whole thing to lower case, and replace things like IDENTITY with SERIAL and
twiddleany datatypes that need it.  Also, they user ALTER TABLE in unsupported ways, like putting multiple alter
statementsin a comma separated list.  This is an issue since all referential integrity constraints are created using
ALTERrather than in the CREATE TABLE statement. 

Then, create a file containing the table names.  Use a handy dandy script to read that list, and call BCP to dump each
tableto text.  The only gotchas here are that you have to specify 'keep nulls' and then nulls are "" (empty string),
andthe fact that embedded newlines in text fields will booger things up.  A handy dandy script can fix those.   

After that, it's as easy as using that same list of table names on the PG box to call psql with a one line query to
COPYFROM ... to load your data.  To get the data files from one box to the other, I use ftp with fget using the same
listof table names.  I think this takes care of translating CR/LF as well.  At least it did not give ma any trouble. 

I would seriously discourage you from using a kluge like DTS for this.  It is beta quality and will cost you time.

If you love TCL like I do, I can send you the scripts I used.

Ian

Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: ianh@tpchd.org

>>> Joshua Jore <moomonk@daisy-chan.org> 07/21/01 02:47PM >>>
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
>


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl