Help Changing a transactional replication Publication

[RayG]

We have a fairly large publication involving about 15 Gigs of data to
which I would like to add articles from time to time withouth having
to re-apply the complete refreshed snapshot to the subscriber. (just
creating the snapshot has adverse effects on our oltp).

I was wondering which of these objects can be added without having to
completeley re-initialize the subscriber database from the snapshot?

Adding a Table Article
Adding / removing Indexes
Changes to Article Table columns

I was hoping that adding an article to a publication and then adding
the article to the subscription would result in some kind of DELTA
processing that would simply add that table and data to the subscriber
(withouth the full re-build of the Subscriber database as happens when
you use the wizards).

But then again I don't understand the underlying stuff as well as I'd
like to.

I can understand that if I make a change to an Article such as
including all indexes or changing the column list that this might not
be implementable as a delta. I can see FKs being a lot of fun too
which is probably why I didn't include them.

Not thrilled about reading books on-line about thsi stuff as the
information is not clear and concise. Any other effective sources to
recommend?

Maybe its not a good idea to bring Indexes along as part fo teh
replication but to apply those separately to the Subscriber?

Thanks for your help

...Ray

[Ben Thul]

I think I understand what you want to do, but let me restate it just
to make sure. Let's say that you have a publication with 10 articles
and you want to add two more. When you run the snapshot agent, you
want it to only generate a snapshot for the two new articles.

If that describes your situation, it is indeed possible. You'll need
to set a couple of properties on the publication. The immediate_sync
and allow_anonymous properties need to be set to false. They can be
changed with sp_changepublication. Once you do this, you should be
able to run the snapshot agent whenever you want and as long as there
are no new subscriptions, it'll find nothing to do. Only when you
have uninitialized subscriptions (because you're adding a new
subscriber database and/or adding new articles) will it do anything.
Try it out in a non-prod environment first, but you should be good to
go.

HTH,
Ben

Post by RayG
We have a fairly large publication involving about 15 Gigs of data to
which I would like to add articles from time to time withouth having
to re-apply the complete refreshed snapshot to the subscriber.  (just
creating the snapshot has adverse effects on our oltp).
I was wondering which of these objects can be added without having to
completeley re-initialize the subscriber database from the snapshot?
Adding a Table Article
Adding / removing Indexes
Changes to Article Table columns
I was hoping that adding an article to a publication and then adding
the article to the subscription would result in some kind of DELTA
processing that would simply add that table and data to the subscriber
(withouth the full re-build of the Subscriber database as happens when
you use the wizards).
But then again I don't understand the underlying stuff as well as I'd
like to.
I can understand that if I make a change to an Article such as
including all indexes or changing the column list that this might not
be implementable as a delta.  I can see FKs being a lot of fun too
which is probably why I didn't include them.
Not thrilled about reading books on-line about thsi stuff as the
information is not clear and concise.  Any other effective sources to
recommend?
Maybe its not a good idea to bring Indexes along as part fo teh
replication but to apply those separately to the Subscriber?
Thanks for your help
...Ray