Microsoft SQL Server Replication

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:

It's been a little while since I used MS SQL Server, but I wonder whether you
could use SQL Server's replication facilities and make PostgreSQL a subscriber?
Is there some sort of Data Transformation wizard or engine that can be set up to
do the data conversion from SQL Server to Postgresql on the fly? I seem to
remember it being fairly powerful.

If you have money to spend, you might also look at third party replication
products. At one place I worked, we used DataMirror (www.datamirror.com) for
two-way replication between a MS SQL Server database and an old OS/400 database.
It was a major pain to set up, and again to upgrade, but in between those times
it worked very well, giving us almost real-time replication. We could have
configured it for nightly batch updates or any other schedule as well. I've
also heard good things about Data Junction (www.datajunction.com). Although it
doesn't support Postgresql explicitly, you might be able to get it to work
through its ODBC interface. And of course there are others.

Does anyone know of a similar third-party replicaiton tool that explicitly
supports PostgreSQL? I think it would be great PR if we could start getting
included on these people's lists of "major databases we support," not to mention
providing us with more solutions from which to choose.

Hope this helps,

Wes Sheldahl

Nate Carlson <natecars%real-time.com@interlock.lexmark.com> on 07/20/2001
06:10:57 PM

To: pgsql-general%postgresql.org@interlock.lexmark.com
cc: (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject: [GENERAL] 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

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

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

If you are really just duplicating the database, not using replication (transactions performed on both databases more or less 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 twiddle any datatypes that need it. Also, they user ALTER TABLE in unsupported ways, like putting multiple alter statements in a comma separated list. This is an issue since all referential integrity constraints are created using ALTER 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 each table 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 COPY FROM ... to load your data. To get the data files from one box to the other, I use ftp with fget using the same list of 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

Sure! I will be doing it over and over again as I load the most current data into my PostgreSQL database, so that should keep 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 or less 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 twiddle any datatypes that need it. Also, they user ALTER TABLE in unsupported ways, like putting multiple alter statements in a comma separated list. This is an issue since all referential integrity constraints are created using ALTER 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 each table 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 COPY FROM ... to load your data. To get the data files from one box to the other, I use ftp with fget using the same list of 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