concurrent access during snapshot generation

We are using SQL 2K with sp4 and a push transaction subscription.
In the Publication properties, under the snapshot tab, there is a check box
for 'Concurrent access during snapshot gneration', the default is not
checked.
1. I am thinking about checking the box because the benefit is great, but
what is the consequence? I am curious of why it isn't default to 'checked' box
2. If leaving it unchecked, say I have 100 tables in the snapshot, the
system only locks a table one at a time during the process, they shouldn't
lock all the tables at once, am I right?
wingman
For the first part, the methodology is different. Transactions made during
the snapshot time are sent to the distribution database and will be
synchronised after the snapshot. This is possible in transactional because
it is transaction-based, but not in merge. However, in some cases it might
be desirable to quiesce the articles while they are being snapshotted, so as
to limit the transactions getting queued up. I'm not too sure if your
assumption is correct about the second part and will check it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the explanation. Just so I understand it correctly because I
don't quite know the word 'quiesce' means.
If the box is unchecked, the snaplock process will stop any transactions
being sent to the distribution database until the snapshot is done.
If the box is checked, any transactions will be sent to distribution
database, which in turns sends them to the subscriber database while snapshot
is going on.
Your answer prompts me a new question or a clarification of what I thought I
understand. When that check box indicates "Do Not lock tables during
snapshot generation.....'. What does it mean? Does it lock the tables in
publisher database or in subscriber database?
"Paul Ibison" wrote:

> For the first part, the methodology is different. Transactions made during
> the snapshot time are sent to the distribution database and will be
> synchronised after the snapshot. This is possible in transactional because
> it is transaction-based, but not in merge. However, in some cases it might
> be desirable to quiesce the articles while they are being snapshotted, so as
> to limit the transactions getting queued up. I'm not too sure if your
> assumption is correct about the second part and will check it.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Wingman,
it's referring to a table-lock on the publisher. The tables usually don't
exist on the subscriber, or if they do, the default is to drop them anyway.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Sorry I am still not quite clear. Please pattern me and clarify the following
So if I unchecked the box(tables will be locked), when the snapshot is
happening, which of the following scenario is happening:
1. the snapshot process will stop any transactions being sent to the
distribution database until the snapshot is done. This means any changes to
any tables are sitting in the queue of the distribution database. During the
snapshot process, the tables involved are locked.
2. the snapshot process will only stop transactions related to the tables
involved in the snapshot and these transactions will be held in the queue of
the distribution database. This means changes are still allowed to be made
to other tables except the tables involved in the snapshot. During the
snapshot process, the tables involved are locked.
If the box is checked, are the above situations reversed? For example,
there will be no queueing in the distribution database and the tables
involved in the snapshot are not locked.
Again, thank for your time and patience very much.
"Paul Ibison" wrote:

> Wingman,
> it's referring to a table-lock on the publisher. The tables usually don't
> exist on the subscriber, or if they do, the default is to drop them anyway.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||With the box unchecked, it is the tables on the Publisher that are locked.
Other processes trying to update these tables will be blocked until the lock
is removed. This should be verifiable by testing and simultaneously
profiling or using the current activity window.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)