June 14, 2011 at 7:07 am
I have a client with a backend office DB and web based client facing DB. To do this they setup a simple one way transactional replication from thier server in the office to the hosted database server on Rackspace. The database is around 30Gb due to image files being stored in a couple of the tables. Last week the replication failed and required a re-initialise, this forced a new snapshot to be generated and it then proceeded to upload the snapshot - clear down the existing data and insert from the bcp files.
This took nearly 7 days to complete as they have a pretty poor internet connection. Is there anyway I can get the replication to re-initialise, but start from a backup? It only takes a couple of hours to courier a HDD with the database on over to the hosts and get it restored.
Thanks in advance for any advice on how to speed up this process.
June 14, 2011 at 8:11 am
Yes.
Restore backup, and populate table.
Recreate the publication and subscription and make sure to use the NoSync initialization.
I have done this for columns added to large tables in replication.
Bing search "no-sync sql server transaction replication"
http://www.replicationanswers.com/NoSyncOn2005.asp
I tried to submt an article on this to SQLServerCentral and MsSqlTipa, but neither ever posted it.
Apparently, I do not write good articles.
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
June 14, 2011 at 1:13 pm
Thanks for the info, much appreciated.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply