SQL Replication

SQL Replication

 

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. Using replication, you can 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.

Types of replication:

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.Concepts
Replication is the process whereby data is copied between databases on the same server or different servers connected by LANs, WANs, or the Internet. Microsoft SQL Server replication uses a publish, distributor and subscribe metaphor.

Publisher is the server or database that sends its data to another server or database.

Subscriber is the server or database that receives data from another server or database.

Distributor is the server that manages the flow of data through the replication system. This server contains the distribution database.

Publisher contain publication/publications. Publication is a collection of one or more articles that is sent to a subscriber server or database. Article is the basic unit of replication and can be a table or a subset of a table.

Subscription is the group of data that a server or database will receive. This can be one or more publications. There are push and pull subscriptions. Push subscription is subscription when the publishing server will periodically push transactions out to the subscribing server or database. Pull subscription is subscription when the subscribing server will periodically connect to the distribution database and pull information.

Microsoft SQL Server support the following replication topologies:

• Central publisher
• Central subscriber
• Central publisher with remote distributor
• Publishing subscriber

This is one of the most common replication topologies. One server is configured as Publisher and Distributor and another server/servers is configured as Subscriber/Subscribers.

Central publisher

 This is a common topology in data warehousing. Many servers or databases replicate their data to a single central server or database.

Central subscriber

 In this topology distribution database resides on the another server than publisher. This topology uses for performance reasons when the level of replication activity increases or the server or network resources become constrained.

Central publisher with remote distributor

This topology requires separate Microsoft SQL Server installations, one for the Publisher and one for the Distributor.

Publishing subscriber

This is a dual role topology. In this topology, the publishing server or database also subscribes to another server or database. In this scenario two servers publish the same data. This is useful when a Publisher must send data to Subscribers over a slow or expensive communications link.