Concurrent snapshot processing

How can i check if i have concurrent snapshot processing enabled thru TSQL ?
I dont see an option to set it during the initial setup using the GUI
Hasan,
have a look at sp_helppublication (synchronization method). This can be set
after finishing the publication wizard, and before you set up a subscriber.
Rgds,
Paul Ibison

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)

Concurrent

I want to use what I believe would be called concurrent snapshot replication
and by that I mean that any changes to one of the articles will immediately
(or very shortly) be pushed over to any subscribers. Here are a few
"feasibility" type of questions:
1. Do you have to have a key on all articles/tables that need to be
replicated? (I read that having a key on a clustered index is recommended,
but I want to know if a key is a hard requirement.)
2. Can you do this from one 7.0 server to another 7.0 server? (Something
in BOL led me to believe it can only be done 2000 to 7.0 for concurrent
replication but I want to veryify.)
3. Is there a definitive list of "gotchas" somewhere, i.e things that I
should check first to see if it will work? (For example, I read that
triggers on the subscriber I believe could cause issues.)
I think you mean transactional replication rather than "concurrent
snapshot". Concurrent in the context or replication means that for
transactional replication the snapshot is generated by holding brief locks
instead of locking all the tables.
To answer your questions
1) yes
2) yes. The concurrent snapshot option for SQL Server is only available with
transactional replication in SQL 2000
3) No. With defaults the publisher will set up the tables on the subscriber
they way they want them. IE the triggers will be whacked.
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
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:106827F6-D03B-4A2F-B745-32F7FADE665E@.microsoft.com...
> I want to use what I believe would be called concurrent snapshot
replication
> and by that I mean that any changes to one of the articles will
immediately
> (or very shortly) be pushed over to any subscribers. Here are a few
> "feasibility" type of questions:
> 1. Do you have to have a key on all articles/tables that need to be
> replicated? (I read that having a key on a clustered index is
recommended,
> but I want to know if a key is a hard requirement.)
> 2. Can you do this from one 7.0 server to another 7.0 server? (Something
> in BOL led me to believe it can only be done 2000 to 7.0 for concurrent
> replication but I want to veryify.)
> 3. Is there a definitive list of "gotchas" somewhere, i.e things that I
> should check first to see if it will work? (For example, I read that
> triggers on the subscriber I believe could cause issues.)