Does Replication Agent locks the database?

Hi,
Does the first synchronization(Replication Agent) while setting up merge
replication locks both the publisher and subscriber databases or the
databases can be used while the first synchronization is taking place?
If first synchronization does not lock the databases, will the changes that
have taken place at both the ends be merged later on, when synchronization
is complete?
Thanks
Anukul
Anukul,
there is no exclusive lock on the database, if that is what you are
referring to. There is a shared lock on the database as there would be on
any connection. As far as I know, there aren't exclusive locks held on the
table during the snapshot generation in merge replication. There are
page-level shared locks which'll prevent updates while the snapshot of a
particular page is being generated, but concurrent reads are compatible. In
transactional there is the option to use concurrent snapshot processing
where concurrent changes can occur during the snapshot generation, but not
so in merge or snapshot replication.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul:
I am talking about Replication Agent, which runs after snapshot agent.
Replication Agnet does the syncronization.
Please suggest.
Thanks,
Anukul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OLV3bsFGFHA.3888@.TK2MSFTNGP12.phx.gbl...
> Anukul,
> there is no exclusive lock on the database, if that is what you are
> referring to. There is a shared lock on the database as there would be on
> any connection. As far as I know, there aren't exclusive locks held on the
> table during the snapshot generation in merge replication. There are
> page-level shared locks which'll prevent updates while the snapshot of a
> particular page is being generated, but concurrent reads are compatible.
In
> transactional there is the option to use concurrent snapshot processing
> where concurrent changes can occur during the snapshot generation, but not
> so in merge or snapshot replication.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||My understanding is that the merge agent works with
batches of records and each separate batch is treated as
a transaction, but the combined synchronization process
is not held under a global transaction. In this case, an
edit to a record on a batch already processed would be
acceptable and would enter MSmerge_contents.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Does adding a new column cause entire subscription to reinitialize?

Hello,
Some hopefully simple questions for the replication experts!
SQL Server 2000, transactional replication, one publisher, one
subscriber.
I have added a column to an article on the publisher and the publisher
is already published. Initially I tried to do this as follows:
sp_repladdcolumn@.source_object = 'tblA',
@.column = 'NewField',
@.typetext = 'int NULL',
@.publication_to_add = 'MyPublication',
@.force_reinit_subscription = 0
but this gave me an error message:
"Error 21381. Cannot add (drop) column to table 'tblA' because the
table belongs to publication(s) with an active updatable subscription.
Set @.force_reinit_subscription to 1 to force reinitialization"
I then re-ran the above with @.force_reinit_subscription=1. The command
worked and returned the message "subscription(s) reinitliazed" but the
new column hasn't appeared on the subscriber.
My questions are as follows:
1 - Do I have to re-run the Snapshot Agent?
2 - If I do re-run the Snapshot Agent, will it reinitialize all other
tables (and their data) or will it just add the new column?
3 - Is there a way I can view the reinitialization commands that are
queued at the publisher to see whether the queued command is to add the
new column or whether it is to reinitialize everything?
Thanks very much!
Pawel
For information: the snapshot agent ran in the evening. The snapshot
agent copied across the new field ALONG WITH all tables related to
'tblA', so it's just as well that I didn't kick it off manually during
the day.
I would still like to know if there is a way to view the commands which
the snapshot agent is due to execute. Does anyone know?
Thanks
Pawel
Pawel wrote:
> Hello,
> Some hopefully simple questions for the replication experts!
> SQL Server 2000, transactional replication, one publisher, one
> subscriber.
> I have added a column to an article on the publisher and the publisher
> is already published. Initially I tried to do this as follows:
> sp_repladdcolumn@.source_object = 'tblA',
> @.column = 'NewField',
> @.typetext = 'int NULL',
> @.publication_to_add = 'MyPublication',
> @.force_reinit_subscription = 0
> but this gave me an error message:
> "Error 21381. Cannot add (drop) column to table 'tblA' because the
> table belongs to publication(s) with an active updatable subscription.
> Set @.force_reinit_subscription to 1 to force reinitialization"
> I then re-ran the above with @.force_reinit_subscription=1. The command
> worked and returned the message "subscription(s) reinitliazed" but the
> new column hasn't appeared on the subscriber.
> My questions are as follows:
> 1 - Do I have to re-run the Snapshot Agent?
> 2 - If I do re-run the Snapshot Agent, will it reinitialize all other
> tables (and their data) or will it just add the new column?
> 3 - Is there a way I can view the reinitialization commands that are
> queued at the publisher to see whether the queued command is to add the
> new column or whether it is to reinitialize everything?
> Thanks very much!
> Pawel
|||Pawel,
as your test shows, if you are specifying @.force_reinit_subscription=1, the
snapshot will contain everything and if not, it won't. To see the commands
that the snapshot agent runs you can use sp_browsereplcmds. Also you can
look in the distribution working folder to see the actual files.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Just to clarify, I don't think the snapshot contained everything. It
looks like it only contained the tables which have a relationship with
'tblA', not all the tables in the published database.
Thanks for the note about sp_browsereplcmds.
Pawel
Paul Ibison wrote:
> Pawel,
> as your test shows, if you are specifying @.force_reinit_subscription=1, the
> snapshot will contain everything and if not, it won't. To see the commands
> that the snapshot agent runs you can use sp_browsereplcmds. Also you can
> look in the distribution working folder to see the actual files.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)