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

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.