Merge Replication Initial Snapshot

SQL 2000.
I have a merge replication system running in a 24/7 environment that
cannot be brought offline or locked up. When i try to run my snapshot
agent, it locks up the primary DB (there are many tables in this that
are very large, like hundreds of millions of rows). is there a way
around the initial snapshot, or a way to speed it up? I have read in
BOL that it may be possible with a backup and removable storage, or
using BCP.
Has anyone been able to get merge replication working without locking
up the db or bringing it offline? If so, how?
Thanks in advance!!
No, AFAIK - there is no way to do this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"colbycrane" <gstarnes@.gmail.com> wrote in message
news:1104268201.081080.154550@.z14g2000cwz.googlegr oups.com...
> SQL 2000.
> I have a merge replication system running in a 24/7 environment that
> cannot be brought offline or locked up. When i try to run my snapshot
> agent, it locks up the primary DB (there are many tables in this that
> are very large, like hundreds of millions of rows). is there a way
> around the initial snapshot, or a way to speed it up? I have read in
> BOL that it may be possible with a backup and removable storage, or
> using BCP.
> Has anyone been able to get merge replication working without locking
> up the db or bringing it offline? If so, how?
> Thanks in advance!!
>
|||Backup and restore can be used for a nosync initialization, but the data
shouldn't be changed on the publisher while this takes place so effectively
it's the same. In transactional replication there is the option to have
concurrent snapshot bgeneration, but this is not possible for merge. So, if
you must use merge, essentially you'll need some system downtime.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||But the problem with a backup restore is that the rowguid column still has
to be added, preferably before the backup is done. This involves an alter
table and locking.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OqLd1pZ7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Backup and restore can be used for a nosync initialization, but the data
> shouldn't be changed on the publisher while this takes place so
> effectively
> it's the same. In transactional replication there is the option to have
> concurrent snapshot bgeneration, but this is not possible for merge. So,
> if
> you must use merge, essentially you'll need some system downtime.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Good point
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||We employ a fairly complicated ETL routine to add rows to tables without
locking the db. This can also be used to migrate the data to the
subscriber db. Heres how it works:
Say tableA needs a new column (tableA has 60m rows). We create TableB,
which is TableA + the new column. We insert / select with (nolock) to
migrate the data. This may introduce dirty reads, thus bad data to
tableB so, at the same time, we put an on insert, update, delete trigger
on table that feeds any changes to TableC. After the inital insert /
select is finished, we run a daemon that takes from TableC and applies
it to tableA. Ultimately, we run the daemon repeatedly until TableC
only has a few records (6 second delay is acceptable), so we then put
into a transaction the final 'reconciliation' to lock tableA, push the
final changes from tableC to tableB, do an sp_rename, and off we go...
If we were able to do this, would the initialization of Merge
replication still read all the rows out of TableA, thus still locking
it?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Greg,
If I read you correctly, in your example, you're saying that you have a
method of making an identical copy of a table without taking a shared lock
on it, or at least the lock used at the final step is minimal and probably
involves a few rows. If you can make such a copy on the subscriber then you
could indeed do a noinit initialization. The guid column with rowguid
attribute could be added using your current methodology. During
initialization, you need to be sure that there are no changes to the data at
the publisher, but this could last as little as 30 secs.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)