SQL Server 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.
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 and Windows 8.
REPLICATION AGENTS
- We have discussed that replication process works in the background with the help of jobs.
- These jobs are also called as agents. These jobs internally uses respective .exe files present in …………….. \110\COM folder.
- All the agents information is present in Distribution db in the following tables.
- dbo.MSxxx_agents
- dbo.MSxxx_history
Snapshot Agent:
- The Snapshot Agent is typically used
- It is an executable file that prepares snapshot files containing schema and data of published tables and db objects.
- It stores the files in the snapshot folder, and records synchronization jobs in the distribution database.
- The Snapshot Agent runs at the Distributor.
Distribution Agent
- It is used with snapshot and transactional replication.
- It applies the initial snapshot to the Subscriber and moves transactions held in the Distribution db to Subscribers.
- It runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions.
Log Reader Agent
- It is used with transactional replication, which moves transactions marked for replication from the transaction log on the publisher to the distribution db.
- Each db has its own Log Reader Agent that runs on the Distributor and connects to the Publisher.
Merge Agent
- It is used with merge replication.
- It applies the initial snapshot to the Subscriber and moves incremental data changes that occur.
- Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both.
- It captures changes using triggers.
Queue Reader Agent
- It is used with transactional replication with the queued updating option.
- It runs at the Distributor and moves changes made at the Subscriber back to the Publisher.
- Unlike Distribution Agent and Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given distribution db.
REPLICATION TYPES
- Snapshot Replication
- Transactional Replication
- Transaction Replication with Updatable subscribers
- Merge Replication
- Peer-to-peer Replication
1. Snapshot Replication
- The snapshot process is commonly used to provide the initial set of data and database objects for transactional and merge publications.
- It copies and distributes data and database objects exactly as they appear at the current moment of time.
- 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 (BOL).
- Scenarios
- When the data is not changing frequently.
- If we want to replicate small amount of data.
- To replicate Look-up tables which are not changing frequently.
- It is acceptable to have copies of data that are out of date with respect to the publisher for a period of time
For example, if a sales organization maintains a product price list and the prices are all updated at the same time once or twice each year, replicating the entire snapshot of data after it has changed is recommended.
Snapshot Replication Architecture
Source: BOL
How it Works?
- Snapshot Agent establishes a connection from distributor to publisher and generates fresh snapshot into snapshot folder by placing locks.
- Snapshot agent writes copy of the table schema for each article to .sch file.
- Copies data from published table at the Publisher and writes data to the snapshot folder in the form of .bcp file.
- Appends rows to the Msrepl_commands and Msrepl_transactions.
- Releases any locks on published tables.
Configuring Replication
- Configuring distributor
- Configuring publisher
- Creating publication of required type
- Creating subscription
