SQL Server Replication

Applies to: SQL Server Azure SQL Managed Instance

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Use replication to distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Transactional replication is typically used in server-to-server scenarios that require high throughput, including: improving scalability and availability; data warehousing and reporting; integrating data from multiple sites; integrating heterogeneous data; and offloading batch processing. Merge replication is primarily designed for mobile applications or distributed server applications that have possible data conflicts. Common scenarios include: exchanging data with mobile users; consumer point of sale (POS) applications; and integration of data from multiple sites. Snapshot replication is used to provide the initial data set for transactional and merge replication; it can also be used when complete refreshes of data are appropriate. With these three types of replication, SQL Server provides a powerful and flexible system for synchronizing data across your enterprise. Replication to SQLCE 3.5 and SQLCE 4.0 is supported on both Windows Server 2012 R2 and Windows 8.

What's new

• SQL Server 2022 hasn't introduced significant new features to SQL Server replication.
• SQL Server 2019 hasn't introduced significant new features to SQL Server replication.
• SQL Server 2017 hasn't introduced significant new features to SQL Server replication.
• SQL Server 2016 hasn't introduced significant new features to SQL Server replication.

For backward compatibility information see, Replication Backward Compatibility

Replication security

• View and Modify Replication Security Settings
• Manage Logins in the Publication Access List

Publishing and Distribution

• Configure Publishing and Distribution
• View and Modify Publication Properties
• Disable Publishing and Distribution

Publications and Articles

• Create a Publication
• Define an Article
• View and Modify Publication Properties
• View and Modify Article Properties
• Delete a Publication
• Delete an Article
• Create a Publication from an Oracle Database
• Set the Expiration Period for Subscriptions
• Specify Schema Options
• Replicate Schema Changes
• Manage Identity Columns
• Set the Compatibility Level for Merge Publications

Snapshot Options

• Configure Snapshot Properties
• Deliver a Snapshot Through FTP

Filter Data

• Define and Modify a Column Filter
• Define and Modify a Static Row Filter
• Define and Modify a Parameterized Row Filter for a Merge Article
• Optimize Parameterized Row Filters
• Define and Modify a Join Filter Between Merge Articles

Transactional Replication Options

• Set the Propagation Method for Data Changes to Transactional Articles
• Enable Updating Subscriptions for Transactional Publications

Merge Replication Options

• Define a Logical Record Relationship Between Merge Table Articles
• Specify Merge replication properties
• Specify a Merge Article Resolver

Manage Subscriptions

• Create a Pull Subscription
• View and Modify Pull Subscription Properties
• Delete a Pull Subscription
• Create a Push Subscription
• View and Modify Push Subscription Properties
• Delete a Push Subscription
• Specify Synchronization Schedules
• Create an Updatable Subscription to a Transactional Publication
• Create a Subscription for a Non-SQL Server Subscriber

Synchronize Subscriptions

• Create and Apply the Initial Snapshot
• Create a Snapshot for a Merge Publication with Parameterized Filters
• Initialize a Transactional Subscription from a Backup (Replication Transact-SQL Programming)
• Initialize a Subscription Manually
• Synchronize a Pull Subscription
• Synchronize a Push Subscription
• Reinitialize a Subscription
• Execute Scripts During Synchronization (Replication Transact-SQL Programming)
• Implement a Business Logic Handler for a Merge Article
• Debug a Business Logic Handler (Replication Programming)
• Control the Behavior of Triggers and Constraints During Synchronization (Replication Transact-SQL Programming)
• Implement a Custom Conflict Resolver for a Merge Article

Administration

• Work with Replication Agent Profiles
• Validate Data at the Subscriber
• Manage Partitions for a Merge Publication with Parameterized Filters
• Bulk-Load Data into Tables in a Merge Publication (Replication Transact-SQL Programming)
• Clean Up Merge Metadata (Replication Transact-SQL Programming)
• Perform a Dummy Update for a Merge Article (Replication Transact-SQL Programming)
• View Replicated Commands and Other Information in the Distribution Database (Replication Transact-SQL Programming)
• Enable Coordinated Backups for Transactional Replication (Replication Transact-SQL Programming)
• Administer a Peer-to-Peer Topology (Replication Transact-SQL Programming)
• Quiesce a Replication Topology (Replication Transact-SQL Programming)
• Configure the Transaction Set Job for an Oracle Publisher (Replication Transact-SQL Programming)
• Upgrade Replication Scripts (Replication Transact-SQL Programming)

Monitor

• Allow Non-Administrators to Use Replication Monitor
• Programmatically Monitor Replication
• View Replicated Commands and Other Information in the Distribution Database (Replication Transact-SQL Programming)
• View Conflict Information for Merge Publications (Replication Transact-SQL Programming)
• Measure Latency and Validate Connections for Transactional Replication