New Article on SQL CAT: Initializing a Transactional Replication Subscriber from an Array-Based Snapshot by Benjamin Wright-Jones
Preamble
I had the opportunity to perform a tech review of a very cool article by Benjamin Wright-Jones:
Initializing a Transactional Replication Subscriber from an Array-Based Snapshot
The array of geekly luminaries who contributed include SQL CAT members Prem Mehra & Mike Ruthruff as well as Ken England, Greg Yvkoff, Qun Guo, & Gopal Ashok.
Here’s a list of the reviewers: yours truly, Joe Sack, Paul Randal (SQLskills.com), Dennis Tighe, and Glenn Berry (SQL Server MVP).
What makes the article so cool is that heretofore initializing transactional replication of VLDBs or for that matter any reasonably sized SQL Server database was inordinately time consuming. Initializing transactional replication requires a full restore (potentially including log backups), so we had to move bits from a conventional dump from one backup medium (disk or tape) to disk. This can be tedious & monopolize hours or even days.
Article Intro
This article describes how to initialize a transactional replication Subscriber from an array-based snapshot rather than using the native SQL Server snapshot mechanism. Initializing the Subscriber using a SAN-based restore solution is particularly beneficial for very large databases. In this context, I use the term VLDB to mean a database that is typically multi-terabyte and requires specialized administration and management This is primarily because the standard transactional replication initialization process, which is typically restricted by either the network or storage I/O bandwidth, could take longer than the business service-level agreement (SLA) permits because of the time needed to initialize or recover the Subscriber. In contrast, initializing a Subscriber using an array-based snapshot utilizes the Virtual Device Interface (VDI) freeze and thaw mechanism, thereby minimizing recovery time. This procedure is also particularly beneficial in non-production environments that use transactional replication and require repeatable tests with large volumes of data.
Benjamin’s protocol provides us the ability to initialize via hardware snapshot. Leverage this to enhance uptime stats, expedite development, & mitigate sleepless nights.
Kudos to Benjamin for documenting this strategery!
Related Reference
BTW, SQL CAT has previously published a paper Gopal Ashok & Paul Randal on a related topic:
SQL Server Replication: Providing High-Availability using Database Mirroring
This white paper describes how to use database mirroring to increase the availability of the replication stream in a transactional environment. It covers setting up replication in a mirrored environment, the effect of mirroring partnership state changes, and the effect of mirroring failovers on replication. In addition, it describes how to use LSN-based initialization to recover from the failover of a mirrored subscriber database.
Administrivia
Jimmy May , MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
https://blogs.msdn.com/jimmymay
This post was written with the PracticeThis.com plugin for Windows Live Writer
There is no fate but what we make. —John Connor