sql server replication: Snapshot replication - can error result in empty tables? -- sql server replication

Snapshot replication - can error result in empty tables?

Learner
11/25/2004 4:01:03 AM

sql server replication:

I'm learning replication and am wanting to check if my understanding is
correct:
If snapshot replication is set up with 'delete all data' for article name
conflicts and the replication fails midway eg from connection failure (after
existing data is deleted and before new data is inserted), could the
subscriber be left with an empty table or would it automatically rollback to
the previous data?
The reason I ask is that I need to copy data from a couple of tables to
several databases every night. I will not know if the data has changed from
night to night (but is only few hundred rows anyway) so will have to copy all
the rows and can live with old data being present but NOT no data being
present.
Is snapshot replication the right way to go? I was also wondering about
creating a DTS package that I could use transactions in?

Re: Snapshot replication - can error result in empty tables?

Learner
11/25/2004 7:47:03 AM

Thanks for your answer. Unfortunately the tables do not have primary keys
(not my design!) so I can't use transactional replication. I believe DTS
packages are transactionally aware (package -> properties -> advanced) and I
can choose which tasks join a package's transaction so I think that is the
way I will have to go - happy to corrected on this point if I'm
misunderstanding DTS as it was my previous learning section so still not
completely up to speed!
Thanks again for replying

[quoted text, click to view]

Re: Snapshot replication - can error result in empty tables?

Hilary Cotter
11/25/2004 7:50:08 AM

when the snapshot is applied on the subscriber it truncates the data in the
table. There is no way to rollback to what was there before.

I think you should use transactional replication as only the changes are
replicated.

DTS is not transaction aware, so it is all or nothing. DTS will be faster
when you are sending a snapshot to a single subscriber. For more than one
subscriber, snapshot replication will be faster.

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server replication group.