Morning!
Here’s a good one for you!
Pub: SQL2000 SP3
Dist: SQL2000 SP4
Sub: SQL2000 SP3
Transactional Replication (Pull Sub – Anonymous)
I’m at a client site looking at the best way to get them up and
running should there be some form of failure in the transactional
replication and assuming that there has not been any form of schema
change/upgrade on the system.
My theory for when there is some form of log reader error is as
follows:
1) Stop the log reader
2) List and copy over the commands to the subscriber of non-
distributed transactions (using sp_replshowcmds).
3) Execute the commands manually (See below)
4) Clear down the Non-Replciated transactions on the publisher
(sp_repldone @xactid = null, @xact_seqno = null, @reset = 1 and then
sp_replflush)
5) Start the log reader
Some points/questions:
(i) Can you execute the non-replicated transactions (the one’s I’ve
dynamically copied over) manually and if so, how? It doesn’t appear
obvious.
(ii) I note that by clearing down the non-replicated transactions
(sp_repldone) is will cause inconsistency in the publisher/subscriber
relationship but the aim is to avoid carrying out a snapshot during
office hours.
(iii) Communicate to users that transactions in the subscription/
reporting envrionment might have been lost in the last 15 or so
minutes.
(iv) We would manually re-initialise the snapshot at the weekend.
Now it’s open to the floor – I’d love some feedback and any questions
you might have in order to come up with a decent solution.
Thanks
Simon