Reduce Transactional Replication Synchronization Time By Applying Snapshots Manually

<div><p>I've had to take some big transactional snapshots before - 15 million rows\10 GB big. Applying the snapshot to a subscriber sitting in the same facility over a local network can take upwards of an hour...so imagine how long it takes to apply the snapshot to a subscriber sitting in another data center. Even at the best of speeds it can take 6-8 hours or more.</p> <p>You can intervene and reduce the time it takes to apply the snapshot. Here's how:</p> <p></p> <ol> <li>Create the subscription to the publication and start the snapshot agent. Before the snapshot agent completes stop the distribution agent for the subscriber. When the snapshot agent finishes it will have written several files for each article to the snapshot folder including: IDX, PRE, SCH, XPP, and BCP files along with a copy of any pre and post snapshot scripts that were configured for the publication. <br /></li> <li>Using your text editor of preference (Notepad works) open the pre-snapshot script in the snapshot folder, copy and paste the commands into Management Studio, and execute them against the subscriber. Do the same for the PRE, SCH, XPP, and IDX files (respectively) for each article. <br /></li> <li>Using your compression tool of choice, compress each BCP file and transfer it to the subscriber. I use <a href="http://www.info-zip.org/" target="_blank">Info-Zip</a> because I can compress all files individually on the command line using a FOR command like so: <br /> <blockquote><font face="Courier New">FOR %F in ("{snapshot folder path}\*.bcp") DO zip -j "%~nF.zip" "%F"</font></blockquote> </li> <li>At the subscriber, uncompress each BCP file and use the bcp utility to import the files into their respective tables. There are two options that must be provided for the file to be imported correctly; They are <strong>-n</strong> for "native type" and <strong>-k</strong> for "keep null values". Other options can be provided as necessary for authentication, server name, database name, and table. I created a batch script that I named "unzip and bcp.bat" to process each compressed file. Here's what the script looks like: <blockquote><font face="Courier New">pushd "%~dp1" <br />unzip -j %1 <br />bcp "%2" in "%~dpn1.bcp" -n -T -k >> "%~dp1%2.bcplog.txt" <br />del "%~dpn1.bcp" <br />popd <p></p> </font></blockquote> The script takes two arguments - the full path of the compressed file (%1 in the script) and the qualified name of the table (i.e. database.owner.table) to bulk insert into (%2 in the script). A log is written in the same directory as the compressed file in order to review for any errors that might have occurred during the bulk copy. The log file is the name of the table (second argument) with ".bcplog.txt" added on at the end. Since the script works with one file at a time and there are multiple files to process, I use the FOR command again like so: <blockquote><font face="Courier New">FOR %F in ("{compressed file path}\{table name}*.zip") DO "unzip and bcp.bat" "%F" {db}.{owner}.{table}</font></blockquote> I always make it a point to check the log files for any errors that occurred (search for the word "error") when the bcp in is done. I also select the top 500 rows from each table to ensure the data looks good and didn't get mangled up somewhere along the way. <br /></li> <li>At the distributor, replace each file in the snapshot folder with an empty file with the same name. An easy way to do this is create an empty file called "blank.txt" and use the FOR command: <br /> <blockquote><font face="Courier New">FOR %F in ("{snapshot folder path}\*.*") DO copy /Y "blank.txt" "%F"</font></blockquote> </li> <li>Start the distribution agent. When you view the agent history it will look like it's going through every article in the publication and applying the snapshot, but in reality it's not doing anything because all of the files are empty. After it "applies" the snapshot it will start delivering any transactions that have been read by the log reader agent. Violà!</li> </ol> <p> </p> <p>Note that you don't have to do this for every article in your publication - if only one of them is big you can work with just the files generated for that article and leave the others alone. When the distribution agent runs it will do what it normally does to the other articles and "apply" the blank files for the article you transferred manually.</p> <p>I hope this saves you time the next time you need to transfer a large snapshot!</p> </div>

Share This

• Twitter
• Google+
• Linked In
• Facebook

Related Posts

Replication Tips