MS SQL Server - table snapshot?

Author

table snapshot?

tootsuite@gmail.com

2006-10-24, 6:38 pm

Hi,

I have a question about replication in SQL Server 2005, transactional
replication. I have an already existing publication, and I had to add
another table (article) to the publication. Obviously, this newly added
table on the publisher and subscriber will not automatically be in sync
unless I do something to make them in sync.

I have a tool that I use to sync the tables up so that I don't have to
apply a snapshot. However, I am wondering, is it possible to apply a
snapshot to a single table in a publication without applying the
snapshot to any other tables? I have turned off the snapshot agent job,
also, since we never use snapshots.

What are the ramifcations of applying a snapshot? Does it drop and
recreate the table? I spoke with a Microsoft tech person a while back,
and even he did not really explain to me what *really* happens when you
apply a snapshot... so, still wondering. He says it "synchronizes" the
tables but that really does not explain anything.

Thanks

Paul Ibison

2006-10-24, 6:38 pm

Correct - after you add the new table and subscribe to it, the snapshot
agent will just snapshot the new article, provided you don't have anonymous
subscribers set up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Hilary Cotter

2006-10-24, 6:38 pm

Yes it is possible to do this using sp_addarticle if you have named
subscriptions. Otherwise a complete snapshot will be run.

By default the snapshot will drop and recreate the table, but you have the
option to leave the table and the data intact, leave the table intact but
delete the data, or drop the table.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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

<tootsuite@gmail.com> wrote in message
news:1161280554.704392.151090@m7g2000cwm.googlegroups.com...
> Hi,
>
> I have a question about replication in SQL Server 2005, transactional
> replication. I have an already existing publication, and I had to add
> another table (article) to the publication. Obviously, this newly added
> table on the publisher and subscriber will not automatically be in sync
> unless I do something to make them in sync.
>
> I have a tool that I use to sync the tables up so that I don't have to
> apply a snapshot. However, I am wondering, is it possible to apply a
> snapshot to a single table in a publication without applying the
> snapshot to any other tables? I have turned off the snapshot agent job,
> also, since we never use snapshots.
>
>
> What are the ramifcations of applying a snapshot? Does it drop and
> recreate the table? I spoke with a Microsoft tech person a while back,
> and even he did not really explain to me what *really* happens when you
> apply a snapshot... so, still wondering. He says it "synchronizes" the
> tables but that really does not explain anything.
>
> Thanks
>

Sponsored Links