Users of Microsoft SQL Server databases employ SQL replication to duplicate and distribute data and database objects from one database to another database or to a data warehouse or data mart. SQL replication can also be used to maintain data consistency between a source database and target database(s). SQL Server can support data replication out-of-the-box. Many firms also use separate replication tools to flexibly managereplication involving both SQL Server and non-SQL Server sources and targets.

Types of SQL Replication Supported by SQL Server

In addition to SQL Server change tracking and SQL change data capture, SQL Server supports three types of SQL replication: snapshot, merge, and transactional replication.

Snapshot replication copies the entire dataset from your source system, essentially providing you with a full replica of your database. Because this unidirectional type of SQL database replication is resource-intensive, it is generally used as part of the initialization process for other types of replication. Snapshot also is appropriate when data is changed only infrequently, only small volumes need to be replicated, and/or sources and targets can be out of sync for a period of time.

Merge replication is a type of bidirectional SQL replication used to keep two or more databases in sync. Users start with a snapshot of the source database objects and data, then track data and schema changes at both source and target with triggers. Merge replicationis often employed in scenarios in which source and target systems cannot be continuously connected, and multiple end points are receiving data updates concurrently.

Transactional replication is the most common form of SQL Server data replication and suitable for a wide variety of scenarios because of its versatility,small footprint and ability to keep up with frequent data updates. It can be used to copy and move a select dataset from one server to one or more servers in near real time—for data warehousing or to offload query workloads, for example—and to synchronize source and target datasets as incremental changes occur.

Considerations When Using SQL Server's Built-In SQL Replication Capabilities

Depending on your edition of SQL Server, you may find that its built-in data replication capabilities are adequate for your needs. But if your teams are time-strapped and/or unfamiliar with the technology or your firm uses non-SQL Server databases, you may discover that a third-party SQL replication solution is a better fit for your organization in the long run. Some find that SQL Server replication is time-consuming to maintain and troubleshoot.

Qlik Replicate (formerly Attunity Replicate): A Simpler, Faster Way to Execute and Manage SQL Replication

If like most medium to large-size firms, your company uses different types of databases, you may want to consider Qlik Replicate (formerly Attunity Replicate), a universal data replication platform that can help you replicate, distribute, synchronize, consolidate, and ingest data across a wide range of databases, data warehouses, Hadoop distributions, and cloud storage platforms. Whether it's MS SQL replication or MySQL replication software you need, our platform has you covered.

Our Click-2-Replicate designer makes setting up and managing replication jobs intuitive and painless, and our unique change data capture technology minimizes impact on your source systems while enabling real-time data integration. With Qlik (Attunity), there's no need to install intrusive agents on your source or target systems. And because our architecture is optimized for superior scalability, our platform can continue to support you as your business grows and replication needs change.