We are using snapshot replication as a standby to replicate prod data for a month from 2012 to 2008R2 after migration from 8R2 to 2012, just as a rollback process.

Using snapshot replication for 10 databases to replicate on old sql instance. For one of the db (2 TB) it takes almost 32 hours for distribution agent job to complete. On old sql that database is set to simple recovery model to prevent log growth and db there no needs to go through log backups. Snapshot job is scheduled to run daily 12 PM for a month or 2 to monitor for any issues on newly migrated one.

Is there a way this 32 hours can be made faster? Will changing to bulk recovery model help? Data type are blob and xml within that db ..

  • So when you do a snapshot it's literally sending over your whole entire database to the subscriber. You're basically performing a backup / restore. The're some factors that can play into why it's taking so long. Server configurations, local publisher versus a remote publisher. Blocking while it's generating the snapshot. Why can't you use transactional replication? Script out a backup restore process with compression that dumps the .bak file to a shared network location. I perform a scenario like that here monthly on a database that is about 1TB, and the process takes about 45 minutes. – H.79 Sep 25 '18 at 16:43
  • We cannot use backup restore as backup db would be of higher version than the one where its going to be restored – BeginnerDBA Sep 25 '18 at 17:47
  • True.... I looked over that tid bit. You could always write a BCP process that runs on the OS side of things. It's very very efficient, and puts minimal load on your SQL engine. Think outside box and develop a solution that works best for you. We have a powershell script process that we use to BCP TBs of data to our remote datamart servers, on a daily / hourly basis. The big push runs in about 2 hours. – H.79 Sep 25 '18 at 21:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.