We have a SQL2005 3 server Push Transactional Replication environment. A Publisher, Distributor and Subscriber servers, this was initially setup using “initialize with backup” and the db was considerably smaller. The Pub db is currently 230gb. We are getting ready to do a vendor application upgrade which will cause us to totally redo the replication setup. This is the only replication environment we have and followed the vendor document for the initial setup. The process was to backup on the Pub, copy the backup to the Sub, Restore on Sub, then initialize on the Pub pointing to the local Pub backup.
Now with the db larger time is a factor to follow the same steps. Using the current process it is taking 2hr to do local backup, 4hr copy to Sub, 2 hr restore on Sub. The user staff wants to be back on the Pub as soon as possible. I am hoping to get some guidance for a streamlined process. This is a clinical system used for patient medical tracking.
1) I tested doing a backup on the Pub with a smaller db directly to the Sub (skipping the local backup/copy to Sub) and it appears that I can initialize point to the share on the Sub. My concerns how is the backup used in the initialization? If I understand correctly it appears to read some header info, for it to do this will it have to pull the whole backup on the Pub if I am sitting on the Pub to initial the subscription?
2) What would be in impact if the users are let back on the Pub after the backup done BUT before the rest of the process is complete? I would assume the Pub db tlog would continue to grow until the subscription is complete. ANYTHING else to be consider with? Network traffic?