How to reinitialize a table in transactional publication.

I have set-up replication using transactional publication and I have
configured subscription to be near continuous.
I have this issue. When setting up subscription I have specified that the
subscription database already had data. So when the snapshot agent is
initialized it does not recreate the tables on the subscription database.
This is ok. But, now if I was to re-initialize only one of the tables, how
do I achieve this without breaking replication.
-Nags
You can't really do this.
If you start with a no-sync, you can't re-initialize. Only subscriptions
that were set for automatic synchronization can be re-initialized.
What I do in situations like this is to drop the article, and then recreate
it in a new publication.
"Nags" <nags@.DontSpamMe.com> wrote in message
news:eQMRViYIEHA.828@.TK2MSFTNGP10.phx.gbl...
> I have set-up replication using transactional publication and I have
> configured subscription to be near continuous.
> I have this issue. When setting up subscription I have specified that the
> subscription database already had data. So when the snapshot agent is
> initialized it does not recreate the tables on the subscription database.
> This is ok. But, now if I was to re-initialize only one of the tables,
how
> do I achieve this without breaking replication.
> -Nags
>
|||Can we drop an article once the tables are published and also subscribed ?
I thought that once there is a subscription on a publication (at least in
transactional publication) we cannot drop the article.
-Nags
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OIi$glcIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> You can't really do this.
> If you start with a no-sync, you can't re-initialize. Only subscriptions
> that were set for automatic synchronization can be re-initialized.
> What I do in situations like this is to drop the article, and then
recreate[vbcol=seagreen]
> it in a new publication.
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:eQMRViYIEHA.828@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
database.
> how
>
|||You will have to drop the subscription for both automatic and nosync
subscriptions, then you can drop the article.
"Nags" <nags@.DontSpamMe.com> wrote in message
news:uSGXCLhIEHA.3440@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Can we drop an article once the tables are published and also subscribed ?
> I thought that once there is a subscription on a publication (at least in
> transactional publication) we cannot drop the article.
> -Nags
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OIi$glcIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> recreate
> the
> database.
tables,
>
|||But, I do not want to break replication
-Nags
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eg$wjYkIEHA.3536@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> You will have to drop the subscription for both automatic and nosync
> subscriptions, then you can drop the article.
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:uSGXCLhIEHA.3440@.TK2MSFTNGP09.phx.gbl...
?[vbcol=seagreen]
in[vbcol=seagreen]
subscriptions[vbcol=seagreen]
that[vbcol=seagreen]
is
> tables,
>
|||Nags wrote:
> I have set-up replication using transactional publication and I have
> configured subscription to be near continuous.
> I have this issue. When setting up subscription I have specified that the
> subscription database already had data. So when the snapshot agent is
> initialized it does not recreate the tables on the subscription database.
> This is ok. But, now if I was to re-initialize only one of the tables, how
> do I achieve this without breaking replication.
> -Nags
>
You are not very well..
However, there are several tricks you can try:
a) you can afford having no transaction on the source table for a
certain time
=> put a trigger for insert / update / delete on the table which
always raise error and rollback
=> wait the log reader agent and the distribution agent have purged
any pending transaction on this table
=> bulk copy your table from publisher to subscriber
=> remove the trigger
b) you can also create a new publication on the table ( I never tried to
publish twice a table on the same database, but you can always create a
"mirror" table on the publishing database that you keep in sync with
triggers )
On the subscriber, subscribe to this new publication and wait for
synchronisation
Now, here is the trick:
On the publisher you have articles A and A2 which you know are in sync
On the subscriber you have tables Arep and A2rep and you know that A2rep
is in sync
So, stop the log reader agent.
Wait long enough so that the distributing agents tell "no more transactions"
Copy A2rep into Arep
restart log reader agent.
Now Arep is sync'ed and you can drop all the A2 stuff.
If you are not familiar with these operations, you might make a
rehearsal on a test server before trying your production server!