Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
SQL Server High Availability for Developers, Part 4
Utilize snapshot, merge, or transactional replication to achieve high availability for SQL Server
Michael K. Campbell
March 1, 2012
6 Min Read
In the first part of this series, we looked at core high-availability concepts and clustering as a solution for achieving high availability. In the second part, we discussed log shipping as another solution for achieving high availability. In the third part, we took a look at database mirroring and SQL Server 2012's AlwaysOn Availability Groups. In this fourth and final article in this series, we'll take a look at replication, which is a set of SQL Server technologies that provide capabilities above the types of redundancy that we've looked at previously.
An Overview of Replication
As I like to tell my SQL Server consulting clients, high availability is more than just creating redundant data—it's about making data more available. Although database mirroring, clustering, AlwaysOn Availability Groups, and transaction log shipping all provide redundancy, these solutions aren't intended to provide end users with access when they go offline. Also, none of these redundancy technologies allow for scale-out scenarios in which apps and users can modify data against multiple masters.
For these kinds of problems, you need replication. SQL Server has three primary kinds of replication available to it. These forms of replication adhere to a publication metaphor: Replication works by defining publications, which are collections of articles (e.g., tables, views, stored procedures) that can be distributed and subscribed to (think of magazines or newspapers). However, each form of replication is implemented much differently and has its own set of pros and cons.
Snapshot Replication
Snapshot replication is nothing more than a wrapper around SQL Server's native bulk-copy functionality. This functionality lets DBAs easily publish snapshots from one server to another by means of scripting objects (such as tables, views, and stored procedures) and data into a set of snapshot files that can be scheduled for regular delivery to subscribers.
Snapshot replication has a variety of benefits, including:
Setup and maintenance. Snapshot replication is easy to set up and manage.
Scale out. Snapshot replication is designed primarily for scale-out scenarios in which stale data can be replicated for read-only operations such as reporting.
No required code changes. Because snapshot replication merely scripts objects and data from one location to another, no code changes are needed.
This form of replication does have a few disadvantages, including:
Static/non-vectoring. In addition to replicating stale data, snapshot replication doesn't vector changes or data. Snapshot replication essentially restarts from scratch each time it runs, making the operation dependent on size. In other words, the larger your publication, the longer the snapshot replication will take to run.
Failover. Snapshot replication doesn't support failover unless you're comfortable with the potential for lots of lost data.
Transactional Replication
Transactional replication uses snapshot replication for initialization in order to mirror data between a publisher and one or more subscribers. After this step, transactional replication employs a special log-reader agent that monitors the transaction log for any changes that are made to articles that have been replicated in order to keep subscribers synchronized.
Various benefits for utilizing transaction replication include:
Synchronization. This method can be used to keep multiple subscribers synchronized in real time. Synchronization can be done across large distances, but it might require tuning.
Scale out. Transactional replication is excellent for scenarios in which read-only data can be scaled out for reporting purposes or to enable e-commerce scalability (such as providing multiple copies of product catalogs).
There are a few disadvantages to consider before utilizing transaction replication, including:
Schema changes/failover. Transactional subscribers require several schema changes that impact foreign keys and impose other constraints. Schema changes aren't too hard to manage or set up, but it makes subscriber failover very difficult.
Performance. Large-scale operations or changes at the publisher might require a long time to reach subscribers. Operations and changes can also result in significant tuning and tweaking by DBAs.
Merge Replication
Merge replication also uses snapshot replication as the primary means for initialization: Two databases are immediately synchronized so that subsequent modifications can be mirrored between merge-replicated servers. Unlike transactional replication, merge replication uses triggers to capture changes to data (and replicated schema) along with specialized stored procedures. These changes are pushed up to the distributor so that they can then be published to all other merge-replicated databases participating in the replication topology.
Merge replication intrinsically supports the notion of multiple masters that can accept changes, which are pushed out to other servers as needed. In practice there are limitations and there's the possibility of conflicts, but these details can be managed and configured to truly enable scenarios in which multiple master databases can run simultaneously. Furthermore, each of these master databases is designed to work in a disconnected fashion, where it retains changes and then marshals changes up as needed when connectivity is returned.
Merge replication offers some great features and options that aren't available with other forms of replication, including:
Multi-master architecture. Although merge replication is designed primarily to address bygone days when sales folks would head out into the field with a disconnected copy of their product databases on a laptop, merge replication does allow multiple master databases. These databases can manage their own copies of data and marshal those changes as needed between other members of a replication topology. This makes it a great option for large organizations or scenarios in which multiple disconnected offices and sites need access to local data for performance or availability purposes.
Disconnected architecture. Merge replication is also natively built to endure periods of no connectivity, meaning that it can send and receive changes after communication is restored.
Availability. With effort on the part of the developers, merge-replicated databases can be used to achieve excellent scale-out and redundancy options.
Merge replication comes with some disadvantages, including:
Schema changes. Merge replication requires the existence of a specialized GUID column per replicated table. The GUID column ensures the uniqueness of data on a row-by-row basis for tracking purposes. By default, merge replication adds the GUID column to tables when it doesn't exist, but this operation is dependent on the size of the data, which can cause performance problems.
Complexity. Although merge replication provides powerful options and capabilities, it's harder to manage compared to other high-availability solutions. Merge replication is harder to manage because merge replication needs to address the possibility for conflicts and manage operations between multiple subscribers.
Replication Resources
In addition to the forms of replication covered here, SQL Server provides variant forms of transactional replication that let subscribers provide updates, effectively blurring the lines between merge replication and transactional replication. Accordingly, if replication sounds like it might solve some of your application needs or problems, see the MSDN article "SQL Server Replication." This resource provides some great information about several forms of replication along with their pros and cons. Another resource, the Microsoft Sync Framework, isn't a SQL Server technology, but it provides additional options for making data more readily available instead of merely redundant.
Otherwise, when it comes to high-availability solutions and architectures for developers, the best thing to do is make sure that you aggressively plan and test whatever solution you put into place to make sure it accurately meets your needs.
About the Author(s)
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
_(1).png?width=700&auto=webp&quality=80&disable=upscale)
.png?width=700&auto=webp&quality=80&disable=upscale)