Обсуждение: Microsoft SQL Server Replication
>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. I did a project with Access and VB a few years back and ran into a limit to the number of fields Access could have in a table. I don't remember the exact number of fields but it was < 256. While this is not a problem for most instances but in my case I had to deal with a legacy system where some tables had >300 fields per table. There were other nightmares but I won't digress. I just wanted to point out one limitation I was aware of for this hopscotch technique of harvesting data. Scott ________________________________________ Scott Sandeman-Allen Roderick Scott Corporation Edmonton, Alberta. Canada
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 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
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