Database Design

Pisgah Forest, NC, North Carolina, Microsoft Access Consulting

SQL Server Replication

SQL Server replication gives you a powerful way to move and synchronize data. Leveraging the built-in replication capabilities SQL Server provides, you can construct sophisticated systems. Whether you need near-real-time performance or you need to automatically synchronize data between infrequently-connected machines, replication is a compelling technology to consider.

With the ease-of-use the replication wizards provide, establishing replication appears deceptively simple. In fact, except for the most simple applications, the wizards are best considered a head start and a learning tool. A thorough understanding of the underlying replication technology is of much greater importance. 

With Power Comes Responsibility

Replication fundamentally alters the structure of a database. Tables require special design considerations. Bandwidth consumed as records replicate can be a crucial factor, especially over slow network links or in complex replication topologies. As the number of users increases, the importance of careful up-front design grows.

Snapshot Replication - A Picture in Time

Snapshot replication is perhaps the least-often used form of replication offered by SQL Server. A "snapshot" of the replicated data is taken at a point in time and stored for future use. When applied to another database, this snapshot appears on the new database exactly as it was on the original database at the time the snapshot was taken. This is best used for infrequently-changing, read-only data.

Merge Replication - A Bidirectional Workhorse

Merge replication begins with applying a snapshot, but any similarity to snapshot replication ends there. Special triggers create metadata in replication tables, identifying all inserts, updates, and deletes performed at all members of the merge replication topology since the replication initialized. This metadata can exist for several weeks or more, allowing even infrequently-connected members to send and receive all database changes long after the transactions originate.

A publisher / subscriber metaphor describes the relationship between the databases participating in the merge replication. Articles of data are published and subscribed to. The merge replication technology ensures that all data changes eventually converge at all locations throughout the replication topology. However, many different types of adverse conditions can exist, and must be understood. This knowledge can at times be difficult to obtain due to sparse documentation on some of the technology's finer points.

Transactional Replication - Synchronizing Databases

Unlike merge replication, transactional replication does not use metadata to track data changes. Rather, transactional replication uses the transaction log. While many of the same data synchronizing benefits are present, transactional replication works best when the synchronization occurs reliably and frequently, such as over a high-speed LAN connection.

Particularly with high-volume databases, replication should not be delayed because the contents of the transaction log will not be removed until the replication occurs.

Pre-project Planning is Key

So many replication considerations exist. For a SQL Server replication project to succeed, a thorough understanding of the technology is a must. Sufficient planning is vital to avoid performance bottlenecks, deadlocks, and data management issues. Preventing and resolving data conflicts takes advance planning. Get the facts from an expert, and get your staff quickly up to speed on SQL Server replication.

Learn More

Call today to schedule a detailed consultation with Productive Technology's experienced SQL Server replication consultant. You need all the facts; we will get you started on the right track.