Hi, I hope someone can point me in the right direction here.
We have a larger (1T and growing) application database (mostly xml data store) that we replicate for reporting and analysis purposes. Due to the significantly different needs of the reporting and analysis, we apply several layers of transformation to the data on its way to the subscriber (convert UTF-8 varchar(max) to UTF-16 xml (don't ask), add some persisted calculated columns to extract data from xml, add indexing, etc).
As a result of all of these modifications to the data it can take an extended period of time (about a week the last time we did it) to fully rebuild replication.
We have occasional high risk situations (application version upgrades) where our standard practice has always been to take the system to single user mode, back up the database, perform the upgrade and roll back to the backup if necessary. Unfortunately in my searches I have been unable to find any clear way to restore both the publisher and subscriber and get replication back up and running at a point in time.
Has anyone had any experience with restoring replication (both the publisher and subscriber) and getting everything back in sync? If so could you provide some feedback or links to documents discussing some how and why? so that we can build out an internal process?
any help that anyone could provide would be much appreciated.