| Groups | Blog | Home |
sql server replication : Which replication will fit in this structure
immediate updating will require that the publisher always be online, if it goes off line the subscriber will be read only. If you use queued updating it is not scalable as dml originating on the subscriber will have added latency and queued can support only a small amount of the total dml originating on the subscriber. Merge replication uses triggers to track changes which will mean all dml on the subscriber and publisher will have added latency, which may not offer the performance you need for a web application. Which leaves two alternatives bi-directional transactional replication or peer to peer replication. Bi-directional transactional replication is ideal but difficult to set up. Peer to peer is simple to set up, but is not resilient to conflicts. Updates to the same row occurring simultaneously will lead to one of the updates being overwritten by the other. Microsoft recommends that all updates occur on one node in your peer to peer topology to minimize any chance of data loss. Peer to peer offers much greater performance than merge replication. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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] "Gary Lou" <garylou@gmail.com> wrote in message news:1158792644.421402.125310@i42g2000cwa.googlegroups.com... > Hi Paul, > > Thanks for the fast response. How does Peer-to-Peer recplication work > compare to merge replication? Does Peer-to-Peer Replication handle > record update conflicts automatically? Thanks. > > Gary > > Paul Ibison wrote: >> Gary, >> I'd recommend merge replication or transactional with updating >> subscribers. >> Merge has a greater array of conflict resolution options, which might be >> a >> factor in determining which to use, as well as it can cope with BLOB >> datatypes. Immediate updating transactional will need a constant >> connection >> to work. For queued updating and merge, you'll need to consider >> partitioning >> the identity ranges. >> For the fault-tolerant part, RAID level 1 for the transaction log and >> raid 5 >> or 1+0 for the data files are pretty normal. For full server >> high-availability, you can consider a cluster and you might consider >> database mirroring as an alternative >> Cheers, >> Paul Ibison SQL Server MVP, www.replicationanswers.com . >> >> >> >> >> >> >> "Gary Lou" <garylou@gmail.com> wrote in message >> news:1158789498.164575.261430@m7g2000cwm.googlegroups.com... >> > Hi All, >> > >> > We are going to upgrade to SQL 2005 in the next couple months. We are >> > not sure which version (either standare or enterprise) yet. >> > >> > Here is the situation. We have two servers, SQLSERVER1(internal user) >> > and SQLSERVER2(web user). Both servers have the exact same data. We >> > want to configure both servers to replicate data in real time, and >> > allow insert/modify in both servers. The goal here is to load balance >> > the workload. >> > >> > We also want to implement a fault tolerant solution on the Web DB >> > (SQLSERVER2). What are the options here? Thanks. >> > >> > -Gary >> > >
Hi All,
We are going to upgrade to SQL 2005 in the next couple months. We are not sure which version (either standare or enterprise) yet. Here is the situation. We have two servers, SQLSERVER1(internal user) and SQLSERVER2(web user). Both servers have the exact same data. We want to configure both servers to replicate data in real time, and allow insert/modify in both servers. The goal here is to load balance the workload. We also want to implement a fault tolerant solution on the Web DB (SQLSERVER2). What are the options here? Thanks. -Gary
Hi Paul,
Thanks for the fast response. How does Peer-to-Peer recplication work compare to merge replication? Does Peer-to-Peer Replication handle record update conflicts automatically? Thanks. Gary [quoted text, click to view] Paul Ibison wrote:
> Gary, > I'd recommend merge replication or transactional with updating subscribers. > Merge has a greater array of conflict resolution options, which might be a > factor in determining which to use, as well as it can cope with BLOB > datatypes. Immediate updating transactional will need a constant connection > to work. For queued updating and merge, you'll need to consider partitioning > the identity ranges. > For the fault-tolerant part, RAID level 1 for the transaction log and raid 5 > or 1+0 for the data files are pretty normal. For full server > high-availability, you can consider a cluster and you might consider > database mirroring as an alternative > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com . > > > > > > > "Gary Lou" <garylou@gmail.com> wrote in message > news:1158789498.164575.261430@m7g2000cwm.googlegroups.com... > > Hi All, > > > > We are going to upgrade to SQL 2005 in the next couple months. We are > > not sure which version (either standare or enterprise) yet. > > > > Here is the situation. We have two servers, SQLSERVER1(internal user) > > and SQLSERVER2(web user). Both servers have the exact same data. We > > want to configure both servers to replicate data in real time, and > > allow insert/modify in both servers. The goal here is to load balance > > the workload. > > > > We also want to implement a fault tolerant solution on the Web DB > > (SQLSERVER2). What are the options here? Thanks. > > > > -Gary > >
Gary,
I'd recommend merge replication or transactional with updating subscribers. Merge has a greater array of conflict resolution options, which might be a factor in determining which to use, as well as it can cope with BLOB datatypes. Immediate updating transactional will need a constant connection to work. For queued updating and merge, you'll need to consider partitioning the identity ranges. For the fault-tolerant part, RAID level 1 for the transaction log and raid 5 or 1+0 for the data files are pretty normal. For full server high-availability, you can consider a cluster and you might consider database mirroring as an alternative Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com . [quoted text, click to view] "Gary Lou" <garylou@gmail.com> wrote in message news:1158789498.164575.261430@m7g2000cwm.googlegroups.com... > Hi All, > > We are going to upgrade to SQL 2005 in the next couple months. We are > not sure which version (either standare or enterprise) yet. > > Here is the situation. We have two servers, SQLSERVER1(internal user) > and SQLSERVER2(web user). Both servers have the exact same data. We > want to configure both servers to replicate data in real time, and > allow insert/modify in both servers. The goal here is to load balance > the workload. > > We also want to implement a fault tolerant solution on the Web DB > (SQLSERVER2). What are the options here? Thanks. > > -Gary >
Difficult to say. If connectivity can be guaranteed then immediate updating
would be viable. And a loss of connectivity might invalidate the setup anyway - I have systems like this. Agree that Queued updating is not scalable, but we are only talking about one node here, so this shouldn't be a problem here at this stage, but the issues with BLOBs would be a showstopper. Of the other alternatives, merge, bi-directional and peer-to-peer, I'd say the main deciding factor is "can there be updates?" ie can the same row be updated on both publisher and subscriber. If this is possible, then I'd go for merge, and if not then opne of the laternatives for the reasons you mention - greater performance. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com
Thanks for all the replies. I appreciate your thought and comments.
Merge Replication is slower. We have two servers that are connected in a local LAN with 1 gbs speed. What is the estimate latency in second for merge replication to replicate data between two servers? Many thanks. Gary [quoted text, click to view] Paul Ibison wrote:
> Difficult to say. If connectivity can be guaranteed then immediate updating > would be viable. And a loss of connectivity might invalidate the setup > anyway - I have systems like this. > Agree that Queued updating is not scalable, but we are only talking about > one node here, so this shouldn't be a problem here at this stage, but the > issues with BLOBs would be a showstopper. > Of the other alternatives, merge, bi-directional and peer-to-peer, I'd say > the main deciding factor is "can there be updates?" ie can the same row be > updated on both publisher and subscriber. If this is possible, then I'd go > for merge, and if not then opne of the laternatives for the reasons you > mention - greater performance. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com
Gary - why not set up a trial system, which then will give data particular
to your setup. Minimise the POLLINGINTERVAL parameter and run the merge agent continuously then you can measure the latency by query. In transactional replication there are tracer tokens especially for this but in merge it's not too difficult to check the latency manually. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com
Yes.
"merge" is better. It just needs to tune its configuration a little. "updatable subscribers as immediate updating" also needs DTC. [quoted text, click to view] "Paul Ibison" wrote:
> Gary - why not set up a trial system, which then will give data particular > to your setup. Minimise the POLLINGINTERVAL parameter and run the merge > agent continuously then you can measure the latency by query. In > transactional replication there are tracer tokens especially for this but in > merge it's not too difficult to check the latency manually. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com > >
Don't see what you're looking for? Try a search.
|
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
October 2008
| home · blog · groups | Questions? Comments? Contact the dn |