Re: Microsoft SQL Server Replication

Sure!  I will be doing it over and over again as I load the most current data into my PostgreSQL database, so that
shouldkeep it fresh in my mind... 

Give me a week or two.

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

>>> Justin Clift <justin@postgresql.org> 07/23/01 02:42PM >>>
Hi Ian,

Would you be willing to take some time and effort to write this up into
a quick HOWTO for Migration from MS-SQL Server to PostgreSQL?

It sounds like you have a lot of hands-on experience with this subject,
and it lay a great starting foundation for people in a similar
situation.

:-)

Regards and best wishes,

Justin Clift


Ian Harding wrote:
>
> 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
scriptinglanguage (or do it by hand) to force the whole thing to lower case, and replace things like IDENTITY with
SERIALand twiddle any datatypes that need it.  Also, they user ALTER TABLE in unsupported ways, like putting multiple
alterstatements in a comma separated list.  This is an issue since all referential integrity constraints are created
usingALTER rather 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
eachtable to text.  The only gotchas here are that you have to specify 'keep nulls' and then nulls are "" (empty
string),and the 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
"My grandfather once told me that there are two kinds of people: those
who
work and those who take the credit. He told me to try to be in the first
group; there was less competition there."
   - Indira Gandhi