Reduce Transactional SQL Server Replication Synchronization Time
Originally published on KendalVanDyke.com.
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 for database synchronization.
You can intervene and reduce the time it takes to apply the snapshot. Here’s how:
- 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.
- 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.
- Using your compression tool of choice, compress each BCP file and transfer it to the subscriber. I use Info-Zip because I can compress all files individually on the command line using a FOR command like so:
FOR %F in ("{snapshot folder path}\*.bcp") DO zip -j "%~nF.zip" "%F" - 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 -n for “native type” and -k 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:
pushd "%~dp1" unzip -j %1 bcp "%2" in "%~dpn1.bcp" -n -T -k >> "%~dp1%2.bcplog.txt" del "%~dpn1.bcp" popd
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:
FOR %F in ("{compressed file path}\{table name}*.zip") DO "unzip and bcp.bat" "%F" {db}.{owner}.{table}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.
- 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:
FOR %F in ("{snapshot folder path}\*.*") DO copy /Y "blank.txt" "%F" - 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à!
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.
I hope this saves you time the next time you need to transfer a large snapshot!
About the Author
Kendal Van Dyke
UpSearch Alum Kendal Van Dyke is a database strategist, community advocate, public speaker and blogger. He is passionate about helping leaders use Microsoft's SQL Server to solve complex problems that protect, unlock and optimize data's value.
Since 1999, Kendal has specialized in SQL Server database management solutions and provided IT strategy consulting. Kendal excels at disaster recovery, high availability planning/implementation and debugging/troubleshooting mission critical SQL Server environments.
Kendal Van Dyke served the SQL Server community as Founder and President of MagicPass, the Orlando, FL based chapter of the Professional Association for SQL Server (PASS). In 2012, Kendal served as a member of the PASS Board of Directors.
Kendal remains active in the SQL Server community as a speaker and blogger. He teaches SQL Server enthusiast and technology leaders how to protect, unlock and optimize data’s value. Since 2008, Kendal has operated a SQL Server focused blog at http://www.kendalvandyke.com/.
Microsoft acknowledged Kendal for his support and outstanding contributions to the SQL Server community by awarding him Microsoft MVP (2011-15). Learn more about Kendal Van Dyke https://upsearch.com/kendal-van-dyke/.
About UpSearch
UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.
