SQL Server Replication: Have it Your Way with Free Subscriptions!

This week, we'll take a look at part 4 of our series on application high availability and disaster recovery (HA/DR) for SQL Server: replication.

Part 1: Application High Availability and Disaster Recovery for SQL Server
Part 2: Poor Man's Disaster Recovery: Use Log Shipping for SQL Server (at Your Own Risk)
Part 3: Database, Database on My Server, Do I Mirror Thee, Sync, or Async?!

SQL replication is a publication-subscription model. The publishers (source servers) define the data or database objects (i.e., articles/publications) to publish and the subscribers (target servers) decide any or all data (i.e., subscriptions) to receive.

SQL Server supports three types of replications:

• Snapshot – generated and applied immediately after a subscription is created, or according to a schedule set at the time the publication is created.
• Transactional – committed transactions made at the publisher can be distributed and applied immediately at the subscriber or at scheduled intervals. Near real-time data availability can be achieved.
• Merge – bi-directional replication where incremental updates at both subscriber and publisher are synchronized and merged.

To configure replications, you can use the following references:

1. Selecting the Appropriate Type of Replication
2. Designing and Implementing: Walkthroughs (Replication)

There are many advantages and disadvantages with using replication compared to other HA/DR solutions. Let's take a closer look at some considerations:

• Depending on your appetite. You decide what database objects to publish and subscribe. The dataset (i.e., article) can be as small as a single row in a table or as big as all the tables, views, stored procedures, user functions, etc. in the database.
• Whenever you want. You decide when the data should be published and applied to the target servers. For near real-time replicated data, you can use transactional replication to push out the changes to target servers. If it's one way replicated data, you can periodically do a snapshot of the data and push it to the target servers.
• Push or pull. Unlike other HA/DR solutions, replication can be configured to either push or pull changes from the publisher to subscribers. A pull topology is best when bandwidth is limited and you have a fixed maintenance window. You only pull changes on a per-need basis.
• Big fan (subscriber) base. You can have as many as subscribers as you'd like. As long as your network bandwidth is capable of handling reasonable data transfer rates between the servers, you can have yourself all the fans in the enterprise.
• No automatic failover. There is no coordination for an automatic failover or failback. Each server in the replication model is an independent server with its own set of users. If you use merge replication or transactional replication with updating subscriptions then the replicated data can be made consistent across all servers. It is still a manual process to switch the connection between servers.
• Data loss or corruption. Depending on when the changes are replicated or synchronized on the servers, data loss can occur. Also, there is no protection for the replicated data. Every change made at the publisher is logged in the form of a DDL or DML command in the distribution database, to be delivered to the subscribers. If proper rules and filters are not in place, you could certainly corrupt your data on the subscribers (i.e., invalid updates, inserts, or deletes).
• Real-time, really. Replication is never really real-time. The Log Reader Agent monitors the transaction log for the articles defined for your publication, and when it finds changes it translates them into appropriate insert, update, delete commands and logs them in the distribution database. These commands are then applied at the subscribers to make the data consistent. There is a cost in this lookup, command translation, and delivery; thus, replication is never real-time.
• Bloated distributor. Old commands logged in the distribution database can bloat the distributor. You should consider setting rules to clean up these old commands. It is really a challenge to keep the distributor lean and mean.
• Publishers and subscribers can be any HW/SW class. The servers are completely independent of each other and do not need to be hosting the same applications, even if they share the same replicated data. The publisher and subscriber can even be running different database software. For example, a SQL Server publisher and Oracle subscriber (or vice versa).
• Servers can be anywhere. Your mileage varies depending on the amount of data to be replicated. As with any OLTP system, WAN or latency always has major impact on performance. You may consider creating and publishing many small publications instead of some large ones.

Replication is probably one of the most flexible HA/DR solutions in term of defining and synchronizing data between servers. You can basically decide what to publish and what to subscribe to. Any objects within a database system (SQL Server, Oracle, DB2, etc.) is fair game for replication. But without the built-in page fault recovery, conflict resolution rules and policies, and automatic failover, you have to take extra special care to ensure data validity and consistency across your systems. Also, if you need protection at the instance or server level, replication is not the answer and you will have to check out other HA/DR solutions.

I hope you find the replication information above useful and will join me again in the next installment of the Application High Availability and Disaster Recovery (HA/DR) for SQL Server series, where I will discuss another HA/DR solution.

Do you have a specific question about HA/DR solutions? If so, please let me know and I'll try to provide insight and solutions. Cheers!