|
|
You're in the
sql server replication
group:replication performance
sql server replication:
of on the secondary hence eliminating ad hoc querying on the live OLTP server Problem with log shipping is obvious..Users are kicked off when logs are being restored. So if we do use replication for each and every table out there and all OLTP databases, are there any performance hits on these publishers.. either from a CPU/IO/Mem ...We would like to setup transactional replication. Like to know if this is worth implementing or should we just use log shipping if there are performance concerns to using replication on the publishers Thanks
Hi
With replication, you will experience the issue of locking and blocking. Every 1/5/15 minutes (or whatever your interval is), you will get a flurry of activity on your reporting DB as the changes are applied. This performance hit spills back into the distribution DB, and might have an impact on your production DB. How often do you need to refresh your data? Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland IM: mike@epprecht.net MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ [quoted text, click to view] "Hassan" <fatima_ja@hotmail.com> wrote in message news:#cXJipwvEHA.4048@TK2MSFTNGP15.phx.gbl... > We currently use log shipping as a means of reporting for users to query off > of on the secondary hence eliminating ad hoc querying on the live OLTP > server > > Problem with log shipping is obvious..Users are kicked off when logs are > being restored. > > So if we do use replication for each and every table out there and all OLTP > databases, are there any performance hits on these publishers.. either from > a CPU/IO/Mem ...We would like to setup transactional replication. Like to > know if this is worth implementing or should we just use log shipping if > there are performance concerns to using replication on the publishers > > Thanks > >
Since the log reader agent reads off the log file, I am assuming that
replication never touches any base tables on the publisher directly ..right ? This is transactional replication and if I did want to use it, my frequency would be in continuous mode...Please advice.. [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:%23Oi%23gR2vEHA.3416@TK2MSFTNGP09.phx.gbl... > The performance impact is a function of your hardware, the frequency and > polling interval of your log reader and distribution agents, the number of > transactions and how many rows are affected by each of these transactions. > > Set up correctly there will be minimal locking/blocking while using > replication. > > "Hassan" <fatima_ja@hotmail.com> wrote in message > news:%23cXJipwvEHA.4048@TK2MSFTNGP15.phx.gbl... > > We currently use log shipping as a means of reporting for users to query > > off > > of on the secondary hence eliminating ad hoc querying on the live OLTP > > server > > > > Problem with log shipping is obvious..Users are kicked off when logs are > > being restored. > > > > So if we do use replication for each and every table out there and all > > OLTP > > databases, are there any performance hits on these publishers.. either > > from > > a CPU/IO/Mem ...We would like to setup transactional replication. Like to > > know if this is worth implementing or should we just use log shipping if > > there are performance concerns to using replication on the publishers > > > > Thanks > > > > > >
The performance impact is a function of your hardware, the frequency and
polling interval of your log reader and distribution agents, the number of transactions and how many rows are affected by each of these transactions. Set up correctly there will be minimal locking/blocking while using replication. [quoted text, click to view] "Hassan" <fatima_ja@hotmail.com> wrote in message news:%23cXJipwvEHA.4048@TK2MSFTNGP15.phx.gbl... > We currently use log shipping as a means of reporting for users to query > off > of on the secondary hence eliminating ad hoc querying on the live OLTP > server > > Problem with log shipping is obvious..Users are kicked off when logs are > being restored. > > So if we do use replication for each and every table out there and all > OLTP > databases, are there any performance hits on these publishers.. either > from > a CPU/IO/Mem ...We would like to setup transactional replication. Like to > know if this is worth implementing or should we just use log shipping if > there are performance concerns to using replication on the publishers > > Thanks > >
That depends on what you mean by touching the tables.
While your snapshot is generated the tables will be read. You can use the concurrent option which will minimize locking while the snapshot is been generated. Other than that the base tables are not modified or read. There may be some blocking/locking if your server is under heavy load, which might be what Mike Epprecht is referring to. In this case you need to use a remote distributor to minimize it. [quoted text, click to view] "Hassan" <fatima_ja@hotmail.com> wrote in message news:eyNVrf3vEHA.2596@TK2MSFTNGP10.phx.gbl... > Since the log reader agent reads off the log file, I am assuming that > replication never touches any base tables on the publisher directly > ..right > ? This is transactional replication and if I did want to use it, my > frequency would be in continuous mode...Please advice.. > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:%23Oi%23gR2vEHA.3416@TK2MSFTNGP09.phx.gbl... >> The performance impact is a function of your hardware, the frequency and >> polling interval of your log reader and distribution agents, the number >> of >> transactions and how many rows are affected by each of these >> transactions. >> >> Set up correctly there will be minimal locking/blocking while using >> replication. >> >> "Hassan" <fatima_ja@hotmail.com> wrote in message >> news:%23cXJipwvEHA.4048@TK2MSFTNGP15.phx.gbl... >> > We currently use log shipping as a means of reporting for users to >> > query >> > off >> > of on the secondary hence eliminating ad hoc querying on the live OLTP >> > server >> > >> > Problem with log shipping is obvious..Users are kicked off when logs >> > are >> > being restored. >> > >> > So if we do use replication for each and every table out there and all >> > OLTP >> > databases, are there any performance hits on these publishers.. either >> > from >> > a CPU/IO/Mem ...We would like to setup transactional replication. Like > to >> > know if this is worth implementing or should we just use log shipping >> > if >> > there are performance concerns to using replication on the publishers >> > >> > Thanks >> > >> > >> >> > >
But that blocking/locking if any is on the distributor right and not on the
publishing dbs ? [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:uBF4mA5vEHA.2804@TK2MSFTNGP14.phx.gbl... > That depends on what you mean by touching the tables. > > While your snapshot is generated the tables will be read. You can use the > concurrent option which will minimize locking while the snapshot is been > generated. > > Other than that the base tables are not modified or read. > > There may be some blocking/locking if your server is under heavy load, which > might be what Mike Epprecht is referring to. > > In this case you need to use a remote distributor to minimize it. > > "Hassan" <fatima_ja@hotmail.com> wrote in message > news:eyNVrf3vEHA.2596@TK2MSFTNGP10.phx.gbl... > > Since the log reader agent reads off the log file, I am assuming that > > replication never touches any base tables on the publisher directly > > ..right > > ? This is transactional replication and if I did want to use it, my > > frequency would be in continuous mode...Please advice.. > > > > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > > news:%23Oi%23gR2vEHA.3416@TK2MSFTNGP09.phx.gbl... > >> The performance impact is a function of your hardware, the frequency and > >> polling interval of your log reader and distribution agents, the number > >> of > >> transactions and how many rows are affected by each of these > >> transactions. > >> > >> Set up correctly there will be minimal locking/blocking while using > >> replication. > >> > >> "Hassan" <fatima_ja@hotmail.com> wrote in message > >> news:%23cXJipwvEHA.4048@TK2MSFTNGP15.phx.gbl... > >> > We currently use log shipping as a means of reporting for users to > >> > query > >> > off > >> > of on the secondary hence eliminating ad hoc querying on the live OLTP > >> > server > >> > > >> > Problem with log shipping is obvious..Users are kicked off when logs > >> > are > >> > being restored. > >> > > >> > So if we do use replication for each and every table out there and all > >> > OLTP > >> > databases, are there any performance hits on these publishers.. either > >> > from > >> > a CPU/IO/Mem ...We would like to setup transactional replication. Like > > to > >> > know if this is worth implementing or should we just use log shipping > >> > if > >> > there are performance concerns to using replication on the publishers > >> > > >> > Thanks > >> > > >> > > >> > >> > > > > > >
Yes, that is my experience.
Replication will put a load on your system as a whole. Typically the load will not be significant. You can get an idea of the added load by stopping your distribution and log reader agents and see how CPU usage (for one) declines. -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html [quoted text, click to view] "Hassan" <fatima_ja@hotmail.com> wrote in message news:e6e3NDIwEHA.3288@TK2MSFTNGP14.phx.gbl... > But that blocking/locking if any is on the distributor right and not on the > publishing dbs ? > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:uBF4mA5vEHA.2804@TK2MSFTNGP14.phx.gbl... > > That depends on what you mean by touching the tables. > > > > While your snapshot is generated the tables will be read. You can use the > > concurrent option which will minimize locking while the snapshot is been > > generated. > > > > Other than that the base tables are not modified or read. > > > > There may be some blocking/locking if your server is under heavy load, > which > > might be what Mike Epprecht is referring to. > > > > In this case you need to use a remote distributor to minimize it. > > > > "Hassan" <fatima_ja@hotmail.com> wrote in message > > news:eyNVrf3vEHA.2596@TK2MSFTNGP10.phx.gbl... > > > Since the log reader agent reads off the log file, I am assuming that > > > replication never touches any base tables on the publisher directly > > > ..right > > > ? This is transactional replication and if I did want to use it, my > > > frequency would be in continuous mode...Please advice.. > > > > > > > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > > > news:%23Oi%23gR2vEHA.3416@TK2MSFTNGP09.phx.gbl... > > >> The performance impact is a function of your hardware, the frequency > and > > >> polling interval of your log reader and distribution agents, the number > > >> of > > >> transactions and how many rows are affected by each of these > > >> transactions. > > >> > > >> Set up correctly there will be minimal locking/blocking while using > > >> replication. > > >> > > >> "Hassan" <fatima_ja@hotmail.com> wrote in message > > >> news:%23cXJipwvEHA.4048@TK2MSFTNGP15.phx.gbl... > > >> > We currently use log shipping as a means of reporting for users to > > >> > query > > >> > off > > >> > of on the secondary hence eliminating ad hoc querying on the live > OLTP > > >> > server > > >> > > > >> > Problem with log shipping is obvious..Users are kicked off when logs > > >> > are > > >> > being restored. > > >> > > > >> > So if we do use replication for each and every table out there and > all > > >> > OLTP > > >> > databases, are there any performance hits on these publishers.. > either > > >> > from > > >> > a CPU/IO/Mem ...We would like to setup transactional replication. > Like > > > to > > >> > know if this is worth implementing or should we just use log shipping > > >> > if > > >> > there are performance concerns to using replication on the publishers > > >> > > > >> > 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 |