Replication is a mechanism to copy or distribute data and database objects among servers at different locations and then synchronizing between these servers to maintain consistency.
The purpose of replication may be varied and hence different approaches of replications are used for different scenarios. In this article we will mainly talk about Replication in SQL server (2008 to be specific but it is applicable to 2005 as well).
Below we will shortly discuss different types of replication in SQL and their purposes. And then talk about how to setup Peer-to-Peer replication between two SQL servers.
Before going to the types of replication let me mention the usual metaphors used for various participants in Replication:
The metaphor used here is of a Magazine publication and subscription.
Publisher – Publisher (like for a Magazine) published DB objects and data
Subscriber – Subscriber (like for a Magazine) subscribes to a publisher changes.
Article – A set of tables, SPs, views (A publisher can publish more than one article. A subscriber can subscribe to more than one article from each and more than one publisher.
Distributor – An agent (a process) which is responsible for relaying the changes from publisher to subscriber.
Following are the main types of Replication in SQL server.
As the name suggests when the synchronization occurs, entire snapshot of data and schema at a moment is copied to the subscriber without need to monitor updates to data. A snapshot of the current schema of the publisher articles is created by an Agent called “Snapshot Agent”. This snapshot is created in form for snapshot files in a snapshot folder. The “Distributor Agent” relays these files to the subscriber at specified intervals. This type of replication typically copies the entire data set every time.
Note that Snapshot agent is used by other replication types also for the purpose of creating initial snapshot of Database to be initialized in each participating server.
It is suitable to use Snapshot replication where
Latency can be tolerated.
Data changes infrequently.
Large volume of data is changed in a small instant.
A transactional replication has all the transactions at the Publisher read and logged by a process called “Log reader agent”. These transactions are relayed to the subscriber by the Distributor agent. An important point to note is transactional replication uses Snapshot agent to replicate initial schema and data to the subscribers.
Transactional Replication is suitable where
Latency cannot be tolerated or at least low latency is desired.
An insert, update, delete transactions occur at high frequency.
The publisher or subscriber is a non-SQL database such as Oracle.
There is one more aspect to Transactional replication i.e. Updatable Subscriptions. It means the ability for the subscriber database to be changed and possible replayed back to publisher (peer-to-peer).
Note that with updatable subscriber and latency of the publisher can cause conflicts which need to be handled. We are not discussing conflict resolution here.
Merge replication again (as Transactional replication) starts with a Snapshot agent synchronizing the initial snapshot. But the subsequent data changes to schema and data are tracked by a process called “Merge Agent”.
Merge Replication is suitable where
Subscribers may have to go offline e.g. Mobile users etc.
Each subscriber may work on different partition of data.
Ok, now we describe how we setup a Peer-to-peer replication which is nothing but a Transactional replication with updatable Subscription.
Note that we only try to configure two servers. However it can be extended to any number of servers
Configure Databases at each server.
Lets Identify two servers and call them MASTERSERVER and SLAVESERVER as follows.
Let the database be MyDB_Master and MyDB_Slave. (You can have same names to the database also.)
At MasterServer (MyDB_Master)
Run MyDB_Master script to initializeMaster DB
Configure user id/password for SlaveAgentID (chepub in sceenshots) at Master. Give permissions of dB owner to this id for MyDB_Master
At SlaveServer (MyDB_Slave)
Run MyDB_Slave scriptto initializa slave DB
You typically use snapshot agent to replicate the initial database and schema at the subscriber. But in this case I have to chosen not to use Snapshot agent for this.
Configure user id/password for MasterAgentID (elipub in screenshots) at Slave. Give permissions of dB owner to this id for MyDB_Slave
Configure Distributor at both Nodes. Below is described for Master node.
Right click on “Replication” and select Configure Distributor
Specify the host server itself as Distributor agent server.
Specify the Distributor Name and further on select default settings and “Finish”
Repeat the same in Slave server but with different Distributor Name
Configuring Publisher at Master
Right click on Publications and select New Publications
Select 2nd option – Transactional Publication
Select the tables to be replicated.
Uncheck both check boxes.
Use SQL server agent service account
Sepcify the Publication name and “Finish”
Go to MasterPublications -> right click -> proeprties -> Supcription Options -> Peer to Peer = True.
Configure Peer 2 Peer
Go to MasterPublication, right click, select Configure Peer to Peer
Right clisk on Surface and select Add New Peer node
Select SlaveServer and connect using MasterAgentID (configured earlier on SlaveServer.
Db = MyDB_Slave , Connect to all nodes = true, Originator Id =2
Loag reader agent – security – SQL server agnt.
While setting subscribers, specify SQL server agent for Ditrtributor agent in both rows.
(refer pic Config P2P 5 (Subscriber agent sec settings))
For Subscriber agent for CIMS_TEST, agent id is MasterAgentID
For Subscriber agent for MyDB_Slave, agent id is SlaveAgentID
DB Initialization select first option i.e. no restoration required.
Replication setup is done.
Right click on Replication and select “Launch Replication Monitor”