| Groups | Blog | Home |
sql server replication : Transaction replication question
instantaneous updating to the subscriber. I only have two servers. Should I place the distribution database on the same server as the publisher or the subscriber? Is it even possible to place the distribution database on the subscriber server?
You can place the distribution database anywhere that you want to. If it is
on the publisher, you will be adding processing overhead on that server. If it is on the subscriber, you will be adding processing overhead on that server. Either way, you have overhead. Since you are processing changes on both sides, it really doesn't matter. One very major point, there is no such thing as instantaneous. The replication engine will only propagate a change when it has been committed. This means there is a latency that will always exist before the servers. Ifyou need instantaneous, then you need to upgrade to SQL Server 2005 and implement database mirroring in which case you can only process changes on 1 of the machines. -- Mike Mentor Solid Quality Learning http://www.solidqualitylearning.com [quoted text, click to view] "SSM" <SSM@discussions.microsoft.com> wrote in message news:6F24F043-32F0-4D8B-A6FD-C5032C2A8EDC@microsoft.com... > My goal is to create a warm standby using transaction replication with > instantaneous updating to the subscriber. I only have two servers. > Should I > place the distribution database on the same server as the publisher or the > subscriber? Is it even possible to place the distribution database on the > subscriber server? > > Thanks.
It should be on the publisher. If the subscriber goes down your tlogs will
get very large on the publisher waiting for the log reader to read them. If they get too large you may have a hard time restarting them. -- 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] "SSM" <SSM@discussions.microsoft.com> wrote in message news:6F24F043-32F0-4D8B-A6FD-C5032C2A8EDC@microsoft.com... > My goal is to create a warm standby using transaction replication with > instantaneous updating to the subscriber. I only have two servers. > Should I > place the distribution database on the same server as the publisher or the > subscriber? Is it even possible to place the distribution database on the > subscriber server? > > Thanks.
The normal setup is to have the distribution database on the publisher. In
the case of a very heavy workload, the distribution database can be placed on another server (but usually an independant one). Log shipping may also be used inthis scenario, and this article helps detail the differences: http://www.replicationanswers.com/Standby.asp Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
Uhmm, I am not so sure I agree with you Mike. Yes there is processing over
head on what ever server you choose to be the distribution server, however generally the norm seems to be the Distribution is usually on the same server as the publsiher. Without knowing more about the environment at the publisher, it is difficult to give a more informed answer to the question. If as he says he is looking to implement Transactional Replication, I would assume that the source server is pretty busy and would not want to be burdened with handling the addtional overhaed of a log reader role as well as a Distribution Database work load. I would err on the side of putting the distribution db on a different server, and the subscribing server would not be my first choice. Recall the writer says the objective is a standby server, which impleas and I assume that this standby server should be prepared and could be called upon to carry the production lload in case of some form of service iterruption to the primary production server. What then happens in a scenario like this? Now we have the "new primary" server also hosting the distribution DB, something we do not have in production. Not so sure about your comment "If it is on the subscriber, you will be adding processing overhead on that server. Either way, you have overhead. Since you are processing changes on both sides, it really doesn't matter." I cannot speak much on Mirroring as I am still testing it with production volume in our labs here. Finally, I think my answer now is we need more information like the transaction volume and whether there is the option of a separate Distribution server, Synch with backups, High speed interconnect between publisher and distributor etc. Happy New Year All. [quoted text, click to view] "Michael Hotek" <mike@solidqualitylearning.com> wrote in message news:eejgLIWDGHA.528@TK2MSFTNGP09.phx.gbl... > You can place the distribution database anywhere that you want to. If it is > on the publisher, you will be adding processing overhead on that server. If > it is on the subscriber, you will be adding processing overhead on that > server. Either way, you have overhead. Since you are processing changes on > both sides, it really doesn't matter. One very major point, there is no > such thing as instantaneous. The replication engine will only propagate a > change when it has been committed. This means there is a latency that will > always exist before the servers. Ifyou need instantaneous, then you need to > upgrade to SQL Server 2005 and implement database mirroring in which case > you can only process changes on 1 of the machines. > > -- > Mike > Mentor > Solid Quality Learning > http://www.solidqualitylearning.com > > > "SSM" <SSM@discussions.microsoft.com> wrote in message > news:6F24F043-32F0-4D8B-A6FD-C5032C2A8EDC@microsoft.com... > > My goal is to create a warm standby using transaction replication with > > instantaneous updating to the subscriber. I only have two servers. > > Should I > > place the distribution database on the same server as the publisher or the > > subscriber? Is it even possible to place the distribution database on the > > subscriber server? > > > > Thanks. > >
That was exactly the point. With the information that has been given, you
can put it wherever you want to put it. Wherever the dirstibutor is located is where the majority of resources are going to be consumed. -- Mike Mentor Solid Quality Learning http://www.solidqualitylearning.com [quoted text, click to view] "Patrick Ikhifa" <patisi@pacbell.net> wrote in message news:u3J8qh2DGHA.3828@TK2MSFTNGP14.phx.gbl... > Uhmm, I am not so sure I agree with you Mike. Yes there is processing over > head on what ever server you choose to be the distribution server, however > generally the norm seems to be the Distribution is usually on the same > server as the publsiher. Without knowing more about the environment at the > publisher, it is difficult to give a more informed answer to the question. > > If as he says he is looking to implement Transactional Replication, I > would > assume that the source server is pretty busy and would not want to be > burdened with handling the addtional overhaed of a log reader role as > well > as a Distribution Database work load. I would err on the side of putting > the > distribution db on a different server, and the subscribing server would > not > be my first choice. Recall the writer says the objective is a standby > server, which impleas and I assume that this standby server should be > prepared and could be called upon to carry the production lload in case of > some form of service iterruption to the primary production server. > > What then happens in a scenario like this? Now we have the "new primary" > server also hosting the distribution DB, something we do not have in > production. Not so sure about your comment "If it is on the subscriber, > you will be adding processing overhead on that server. Either way, you > have > overhead. Since you are processing changes on both sides, it really > doesn't > matter." I cannot speak much on Mirroring as I am still testing it with > production volume in our labs here. > Finally, I think my answer now is we need more information like the > transaction volume and whether there is the option of a separate > Distribution server, Synch with backups, High speed interconnect between > publisher and distributor etc. Happy New Year All. > > > "Michael Hotek" <mike@solidqualitylearning.com> wrote in message > news:eejgLIWDGHA.528@TK2MSFTNGP09.phx.gbl... >> You can place the distribution database anywhere that you want to. If it > is >> on the publisher, you will be adding processing overhead on that server. > If >> it is on the subscriber, you will be adding processing overhead on that >> server. Either way, you have overhead. Since you are processing changes > on >> both sides, it really doesn't matter. One very major point, there is no >> such thing as instantaneous. The replication engine will only propagate >> a >> change when it has been committed. This means there is a latency that > will >> always exist before the servers. Ifyou need instantaneous, then you need > to >> upgrade to SQL Server 2005 and implement database mirroring in which case >> you can only process changes on 1 of the machines. >> >> -- >> Mike >> Mentor >> Solid Quality Learning >> http://www.solidqualitylearning.com >> >> >> "SSM" <SSM@discussions.microsoft.com> wrote in message >> news:6F24F043-32F0-4D8B-A6FD-C5032C2A8EDC@microsoft.com... >> > My goal is to create a warm standby using transaction replication with >> > instantaneous updating to the subscriber. I only have two servers. >> > Should I >> > place the distribution database on the same server as the publisher or > the >> > subscriber? Is it even possible to place the distribution database on > the >> > subscriber server? >> > >> > Thanks. >> >> > >
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
| home · blog · groups | Questions? Comments? Contact the dn |