|
|
You're in the
sql server replication
group:Transactional replication reliabiliy
sql server replication:
There is the option to initialize with backup which prevents any sort of
locking on the publisher, or there is the "Concurrent access during snapshot generation" option. Either way, there would be no problem on the publisher. That aside, once set up it is very robust and even schma changes are easily replicated to the subscriber in SQL Server 2005. The most likely problem is subscribers exceeding the retention period but you can set up alerts so you'll know about this well in advance. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com
I thinking of usage of transactional replication in one of projects. The
transaction replication will be used between several servers. Most of servers (TransDBs) will be used for entering data (high writing activity, lots of concurrent users on each of the servers), and data from the servers, for most of tables, will be replicated to other server, QueryDB. The QueryDB will have not only queries, but also will be used for writing, and data from tables which will be changed on QueryDB will be replicated by transactional replication to other servers. Any table will be either repliucated to QueryDB, or replicated from QueryDB, or not replicated at all, no tables which are replicated from QueryDB and back. QueryDB and other servers will have different set of tables, but all tables which are replicated from/to will have same structure. In order to prevent accidental changes, tables which are replicated to QueryDB will have triggers, not for replication, which will prevent any changes in the tables. Same for tables replicated from QueryDB to TransDB on TransDB. The question is - which reliability/availability I may expect from transactional replication? I understand what its relatively easy to fix problems caused by fallen replication (and when other simpler ways not works) - its recreate publication, apply snapshot etc. But since system is 24x6, I can not create publication during week, only on wekends - because creation of publication lock tables preventing concurrent updates. May I expect 99.99%
Creation of publication locking tables. You may check - add empty
transactional publication to DB, next in one of windows in Management Studio open transaction and write "delete from [tablename] where 1=0". It will place IX lock on table. Next execute sp_addarticle for the table. You will see what executing hanging, it attempts to place Sch_M lock on the table. So replication lock table, even in SQL2k5... - and if not that locking, planning for high replication availability may become much easier. About snapshot generation. I am not checked it for SQL2k5 yet, but in SQL2k, even with 'concurrent' snapshot option, snapshot generation locking tables - for shorter amount of time what with 'native', but locking. - And it is written in documentation. Is SQL2k5 snapshot generation with concurrent option lock tables or it is not lock it at all? [quoted text, click to view] "Paul Ibison" wrote:
> There is the option to initialize with backup which prevents any sort of > locking on the publisher, or there is the "Concurrent access during > snapshot generation" option. Either way, there would be no problem on the > publisher. > That aside, once set up it is very robust and even schma changes are easily > replicated to the subscriber in SQL Server 2005. The most likely problem is > subscribers exceeding the retention period but you can set up alerts so > you'll know about this well in advance. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com > >
If you are going for 5 9s availability, initialize with backup is the way to
go as Paul has already mentioned. That said, we do support a new sync_method = 'database snapshot' in SQL2005 EE SP2 which should incur lower locking contention at the publisher compared with concurrent snapshot (which is not to say locks aren't taken at all). -Raymond [quoted text, click to view] "andsm" <andsm@discussions.microsoft.com> wrote in message news:3B01CDE8-4847-4CC7-8367-58912539D0B3@microsoft.com... > Creation of publication locking tables. You may check - add empty > transactional publication to DB, next in one of windows in Management > Studio > open transaction and write "delete from [tablename] where 1=0". It will > place > IX lock on table. Next execute sp_addarticle for the table. You will see > what > executing hanging, it attempts to place Sch_M lock on the table. So > replication lock table, even in SQL2k5... - and if not that locking, > planning > for high replication availability may become much easier. > > About snapshot generation. I am not checked it for SQL2k5 yet, but in > SQL2k, > even with 'concurrent' snapshot option, snapshot generation locking > tables - > for shorter amount of time what with 'native', but locking. - And it is > written in documentation. Is SQL2k5 snapshot generation with concurrent > option lock tables or it is not lock it at all? > > "Paul Ibison" wrote: > >> There is the option to initialize with backup which prevents any sort of >> locking on the publisher, or there is the "Concurrent access during >> snapshot generation" option. Either way, there would be no problem on the >> publisher. >> That aside, once set up it is very robust and even schma changes are >> easily >> replicated to the subscriber in SQL Server 2005. The most likely problem >> is >> subscribers exceeding the retention period but you can set up alerts so >> you'll know about this well in advance. >> Cheers, >> Paul Ibison SQL Server MVP, www.replicationanswers.com >> >> >>
OK - I assumed you were talking exclusively about when the snapshot is
taken. The schema modification lock taken when sp_addarticle is executed is presumably taken to mark the object as replicated so I wouldn't expect much can be done about it, but it'll be a very short-term one. During initialization for concurrent snapshot generation the shared locks exist only for a few seconds - if this is too much have a look at "database snapshot" for the @sync_method. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com
Locks which I see during creation of transactional publication and genaration
of snapshot with @sync_option = 'database snapshot', on SQL2k5 SP2 EE: 1. During publication creation - it place Sch_M locks on all tables selected for replication, during execution of sp_addarticle. Bad, but not much - since it lock only one object at time. 2. During snapshot generation, with option 'database snapshot' - It place S locks on all tables which selected for replication. And its really bad and it means downtime for system, which should work 24x6. Are any ways to reduce locking and remove the downtime? If I will use initialization from backup, what will be with locks? [quoted text, click to view] "Paul Ibison" wrote:
> OK - I assumed you were talking exclusively about when the snapshot is > taken. The schema modification lock taken when sp_addarticle is executed is > presumably taken to mark the object as replicated so I wouldn't expect much > can be done about it, but it'll be a very short-term one. > During initialization for concurrent snapshot generation the shared locks > exist only for a few seconds - if this is too much have a look at "database > snapshot" for the @sync_method. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com > >
Regarding 2, the shared locks will only be held for as long as it takes to
create the database snapshot which should be a minimal amount of time. Of course, if that is still unacceptable, 'initialize with backup' is the way to go. -Raymond [quoted text, click to view] "andsm" <andsm@discussions.microsoft.com> wrote in message news:6C6E47B9-A989-4000-899F-7CAEBF9BA9C6@microsoft.com... > Locks which I see during creation of transactional publication and > genaration > of snapshot with @sync_option = 'database snapshot', on SQL2k5 SP2 EE: > > 1. During publication creation - it place Sch_M locks on all tables > selected > for replication, during execution of sp_addarticle. Bad, but not much - > since > it lock only one object at time. > > 2. During snapshot generation, with option 'database snapshot' - > It place S locks on all tables which selected for replication. And its > really bad and it means downtime for system, which should work 24x6. Are > any > ways to reduce locking and remove the downtime? If I will use > initialization > from backup, what will be with locks? > > "Paul Ibison" wrote: > >> OK - I assumed you were talking exclusively about when the snapshot is >> taken. The schema modification lock taken when sp_addarticle is executed >> is >> presumably taken to mark the object as replicated so I wouldn't expect >> much >> can be done about it, but it'll be a very short-term one. >> During initialization for concurrent snapshot generation the shared locks >> exist only for a few seconds - if this is too much have a look at >> "database >> snapshot" for the @sync_method. >> Cheers, >> Paul Ibison SQL Server MVP, www.replicationanswers.com >> >> >>
As a sort of academic exercise, if the snapshot agent has trouble acquiring
shared table locks when sync_method = 'database snapshot', I am a bit curious to know what is going on in the system that is blocking the snapshot agent. I know it is always theoretically possible to have such situations but it is better to wrap my head around a concrete example (or perhaps do something about it). -Raymond [quoted text, click to view] "Raymond Mak [MSFT]" <rmak@online.microsoft.com> wrote in message news:OdQJDZYeHHA.4188@TK2MSFTNGP02.phx.gbl... > Regarding 2, the shared locks will only be held for as long as it takes to > create the database snapshot which should be a minimal amount of time. Of > course, if that is still unacceptable, 'initialize with backup' is the way > to go. > > -Raymond > "andsm" <andsm@discussions.microsoft.com> wrote in message > news:6C6E47B9-A989-4000-899F-7CAEBF9BA9C6@microsoft.com... >> Locks which I see during creation of transactional publication and >> genaration >> of snapshot with @sync_option = 'database snapshot', on SQL2k5 SP2 EE: >> >> 1. During publication creation - it place Sch_M locks on all tables >> selected >> for replication, during execution of sp_addarticle. Bad, but not much - >> since >> it lock only one object at time. >> >> 2. During snapshot generation, with option 'database snapshot' - >> It place S locks on all tables which selected for replication. And its >> really bad and it means downtime for system, which should work 24x6. Are >> any >> ways to reduce locking and remove the downtime? If I will use >> initialization >> from backup, what will be with locks? >> >> "Paul Ibison" wrote: >> >>> OK - I assumed you were talking exclusively about when the snapshot is >>> taken. The schema modification lock taken when sp_addarticle is executed >>> is >>> presumably taken to mark the object as replicated so I wouldn't expect >>> much >>> can be done about it, but it'll be a very short-term one. >>> During initialization for concurrent snapshot generation the shared >>> locks >>> exist only for a few seconds - if this is too much have a look at >>> "database >>> snapshot" for the @sync_method. >>> Cheers, >>> Paul Ibison SQL Server MVP, >>> www.replicationanswers.com >>> >>> >>> > >
Interesting - and many thanks for posting up your findings. My understanding
(from reading only) was that 'database snapshot' results in lower locking than the concurrent option but your research shows that this is not necessarily the case. It looks like "initialization from backup" or a nosync initialization is your only option. If you have a couple of mins, please can you drop me an email me at Paul.Ibison@ReplicationAnswers.Com offline about this (- I'd email you directly but your account is an anonymous one). Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com
It is very simple - on the system snapshot agent will be unable to get
shared table locks because it constantly will be deadlocked. - The system have several thousands users at peak time and several hundreds users during time with minimal activity, high writing activity, OLTP, finance processing system. And I not see what can be done here - since in case if any transaction, due to the locks, will be delayed - affected users will be very unhappy and will start to complain to customer support because they may lose $$ due to the delays. [quoted text, click to view] "Raymond Mak [MSFT]" wrote:
> As a sort of academic exercise, if the snapshot agent has trouble acquiring > shared table locks when sync_method = 'database snapshot', I am a bit > curious to know what is going on in the system that is blocking the snapshot > agent. I know it is always theoretically possible to have such situations > but it is better to wrap my head around a concrete example (or perhaps do > something about it). > > -Raymond > > "Raymond Mak [MSFT]" <rmak@online.microsoft.com> wrote in message > news:OdQJDZYeHHA.4188@TK2MSFTNGP02.phx.gbl... > > Regarding 2, the shared locks will only be held for as long as it takes to > > create the database snapshot which should be a minimal amount of time. Of > > course, if that is still unacceptable, 'initialize with backup' is the way > > to go. > > > > -Raymond > > "andsm" <andsm@discussions.microsoft.com> wrote in message > > news:6C6E47B9-A989-4000-899F-7CAEBF9BA9C6@microsoft.com... > >> Locks which I see during creation of transactional publication and > >> genaration > >> of snapshot with @sync_option = 'database snapshot', on SQL2k5 SP2 EE: > >> > >> 1. During publication creation - it place Sch_M locks on all tables > >> selected > >> for replication, during execution of sp_addarticle. Bad, but not much - > >> since > >> it lock only one object at time. > >> > >> 2. During snapshot generation, with option 'database snapshot' - > >> It place S locks on all tables which selected for replication. And its > >> really bad and it means downtime for system, which should work 24x6. Are > >> any > >> ways to reduce locking and remove the downtime? If I will use > >> initialization > >> from backup, what will be with locks? > >> > >> "Paul Ibison" wrote: > >> > >>> OK - I assumed you were talking exclusively about when the snapshot is > >>> taken. The schema modification lock taken when sp_addarticle is executed > >>> is > >>> presumably taken to mark the object as replicated so I wouldn't expect > >>> much > >>> can be done about it, but it'll be a very short-term one. > >>> During initialization for concurrent snapshot generation the shared > >>> locks > >>> exist only for a few seconds - if this is too much have a look at > >>> "database > >>> snapshot" for the @sync_method. > >>> Cheers, > >>> Paul Ibison SQL Server MVP, > >>> www.replicationanswers.com > >>> > >>> > >>> > > > > > >
Thanks for the feedback. Given your scenario, snapshot processing definitely
(sync_method = 'database snapshot' or not) is *not* suitable as generating a snapshot incurs significant system resource contention even if there aren't any deadlocks or lock contention. That said, the snapshot agent does attempt to mutate the lock acquisition sequence upon encountering a deadlock so for a system with more or less consisent access patterns across different tables, the snapshot agent should eventually grab the table locks in a way that avoids deadlocks with any concurrent processes. I am guessing that there are probably too many "hot" tables in your system at any given time for the snapshot agent to converge to a deadlock-free lock acquisition sequence in a timely manner. Theoretically, you can try breaking up your publication into multiple publications with smaller number of articles to minimize the chance of deadlocks during snapshot generation. -Raymond [quoted text, click to view] "andsm" <andsm@discussions.microsoft.com> wrote in message news:156C8C6A-61A4-45A6-A364-08DE9746B6CA@microsoft.com... > It is very simple - on the system snapshot agent will be unable to get > shared table locks because it constantly will be deadlocked. - The system > have several thousands users at peak time and several hundreds users > during > time with minimal activity, high writing activity, OLTP, finance > processing > system. > > And I not see what can be done here - since in case if any transaction, > due > to the locks, will be delayed - affected users will be very unhappy and > will > start to complain to customer support because they may lose $$ due to the > delays. > > "Raymond Mak [MSFT]" wrote: > >> As a sort of academic exercise, if the snapshot agent has trouble >> acquiring >> shared table locks when sync_method = 'database snapshot', I am a bit >> curious to know what is going on in the system that is blocking the >> snapshot >> agent. I know it is always theoretically possible to have such situations >> but it is better to wrap my head around a concrete example (or perhaps do >> something about it). >> >> -Raymond >> >> "Raymond Mak [MSFT]" <rmak@online.microsoft.com> wrote in message >> news:OdQJDZYeHHA.4188@TK2MSFTNGP02.phx.gbl... >> > Regarding 2, the shared locks will only be held for as long as it takes >> > to >> > create the database snapshot which should be a minimal amount of time. >> > Of >> > course, if that is still unacceptable, 'initialize with backup' is the >> > way >> > to go. >> > >> > -Raymond >> > "andsm" <andsm@discussions.microsoft.com> wrote in message >> > news:6C6E47B9-A989-4000-899F-7CAEBF9BA9C6@microsoft.com... >> >> Locks which I see during creation of transactional publication and >> >> genaration >> >> of snapshot with @sync_option = 'database snapshot', on SQL2k5 SP2 EE: >> >> >> >> 1. During publication creation - it place Sch_M locks on all tables >> >> selected >> >> for replication, during execution of sp_addarticle. Bad, but not >> >> much - >> >> since >> >> it lock only one object at time. >> >> >> >> 2. During snapshot generation, with option 'database snapshot' - >> >> It place S locks on all tables which selected for replication. And its >> >> really bad and it means downtime for system, which should work 24x6. >> >> Are >> >> any >> >> ways to reduce locking and remove the downtime? If I will use >> >> initialization >> >> from backup, what will be with locks? >> >> >> >> "Paul Ibison" wrote: >> >> >> >>> OK - I assumed you were talking exclusively about when the snapshot >> >>> is >> >>> taken. The schema modification lock taken when sp_addarticle is >> >>> executed >> >>> is >> >>> presumably taken to mark the object as replicated so I wouldn't >> >>> expect >> >>> much >> >>> can be done about it, but it'll be a very short-term one. >> >>> During initialization for concurrent snapshot generation the shared >> >>> locks >> >>> exist only for a few seconds - if this is too much have a look at >> >>> "database >> >>> snapshot" for the @sync_method. >> >>> 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
| home · blog · groups | Questions? Comments? Contact the dn |