Type of Replication? -- sql server replication

sql server replication : Type of Replication?

EdwardH
3/7/2007 11:25:03 AM

I have SQL 2000 tables (5) that I want to replicate to SQL 2005 Workgroup on
Remote web Server which need to synchronise else say twice a day but without

jimkatsos NO[at]SPAM gmail.com
3/7/2007 5:03:06 PM

Assuming data is only read at the subscriber not updated. Then
snapshot replication may be a good choice here, as it is best suited
to end of day batch updating of the subscriber. Be aware if data
volumes are large that snapshot replication can take a long time as it
moves the entrire contents of the tables for each synchronise. Also
locks are placed on the publisher table so that a read consistent
point in time view of the source data is captured. So be carefull.

One way transactional replciation is also an option (if you have a
small tolerance of tables being offline at both ends) where changes
are progated almost immedaitely so that publisher and subscriber are
in sync. I would be considering this as well.

Regards Jim.
http://jims-spanakopita.blogspot.com/

Paul Ibison
3/8/2007 12:00:00 AM

We'd need to know a little more before answering. The Web server - is it
updatable and is it always connected to the publisher?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Hilary Cotter
3/8/2007 10:08:19 AM

It is unlikely your ISP will open port 1433 for inbound communication, so it
will be difficult to get this running unless they open a port for you and
you can configure your SQL Server to run on this port.

As ftp is probably open to you, you might be able to bcp the data out to an
ftp site, and have the remote SQL Server bcp the data into staging tables
and then only push the delta's into the live tables. With tracking you can
keep the data flow to a minimum.
--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

[quoted text, click to view]

EdwardH
3/11/2007 4:46:13 AM

Jim,
Many thanks,
I think I am tending towards transactional replication. The publisher data
would only flow to subscriber - is there much of an overhead on the publisher?

[quoted text, click to view]

EdwardH
3/11/2007 4:50:00 AM

Paul,
Many thanks,
The data flow would only go from Publisher to Subscriber via a "standard"
internet connection say 2Mb Broadband - always online.
I am beginning to tend towards transactional replication - would this
involve much of an overhead on publisher?

[quoted text, click to view]

EdwardH
3/11/2007 5:08:00 AM

Hilary,
Many thanks
It will be my own SQL Server box hosted in a DataCentre which should have
1433 open. Are you saying that tranactional replication would not be
practical? Dataflow would only go from Publisher to web subscriber. Two
tables of circa 50,000 recs need to be in synch.

[quoted text, click to view]

Paul Ibison
3/12/2007 12:00:00 AM

Yes - I would recommend Transactional Replication for this. As for the
overhead on the publisher, this has to be simply tested empirically, but I
find typically the log reader doesn't use much in the way of resources. If
this is a concern, then run the distributor agent on the subscriber (PULL)
and if you really want to optimise the system, you could have the
distributor on a separate server.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

View Other Months
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007

home · blog · groups

Questions? Comments? Contact the dn