SQL SERVER Replication

Which type(s) of replication do you need?

http://www.cryer.co.uk/brian/sqlserver/howtoreplication.htm#TypeNeed

The three different types of replication (merge, snapshot and transaction) cannot be mixed within a publication, i.e. for each publication only one type of replication can be used. However, given that there is no limit on the number of publications that can be defined this should not be a problem (if you need a table to participate in a different type of replication from other tables simply place it in another publication).

Each type of replication is suited to different requirements, as illustrated below:

	Merge	Snapshot	Transaction
Edit anywhere	Yes	No	No
Only changes replicated	Yes	No	Yes
Suited for large quantities of data	Yes	No	Yes
Replicate stored procedures	No	Yes	Yes
Read only at subscriber	No	Yes	Yes
Consistency guaranteed	No	Yes	Yes
Complete refresh of data	No	Yes	No

It would be wise to plan for each article/table which form of replication would be most appropriate. As a general rule,

I would recommend

merge replication for tables that need to be updateable at any server and

transaction replication where updates are only to be made at the publisher.

 

SQL Server 2000 provides three types of replication:
• Snapshot replication.
• Transactional replication.
• Merge replication.

SQL Server 2005 includes a new replication method:
• Peer-to-peer transactional replication.

 

Peer to Peer Transactional Replication

http://www.exforsys.com/tutorials/sql-server-2005/sql-server-configuring-replication.html

The concept of Peer to peer transactional replication is used to increase the server scale out capabilities. All nodes within the system are treated as peers and can be updated simultaneously. Each node has its own Log Reader Agent and Distribution Agent. Therefore, a node can be taken offline for scheduled maintenance without impact on performance and put online again and logs from other nodes can be applied to it .

 

--for more inforation about how to setup a p2p replication, please refre to : http://www.databasejournal.com/features/mssql/article.php/3814591/Peer-to-Peer-Replication-in-SQL-Server-2008--Configure-a-two-node-topology.htm

 

 

Terminologies before getting started:

http://www.codeproject.com/KB/database/sql2005-replication.aspx

Microsoft SQL Server 2000 supports the following types of replication

Publisher is a server that makes the data available for subscription to other servers. In addition to that, publisher also identifies what data has changed at the subscriber during the synchronizing process. Publisher contains publication(s) .

Subscriber is a server that receives and maintains the published data . Modifications to the data at subscriber can be propagated back to the publisher.

Distributor is the serve r that manages the flow of data through the replication system. Two types of distributors are present, one is remote distributor and the other one local distributor . Remote distributor is separate from publisher and is configured as distributor for replication. Local distributor is a server that is configured as publisher and distributor.

Agents are the processes that are responsible for copying and distributing data between publisher and subscriber. There are different types of agents supporting different types of replication.

Snapshot Agent is an executable file that prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database.

 

 

Replication Types

Microsoft SQL Server 2005 supports the following types of replication:

• Snapshot Replication
• Transactional Replication
• Merge Replication

Snapshot Replication

• Snapshot replication is also known as static replication . Snapshot replication copies and distributes data and database objects exactly as they appear at the current moment in time.
• Subscribers are updated with complete modified data and not by individual transactions , and are not continuous in nature.
• This type is mostly used when the amount of data to be replicated is small and data/DB objects are static or does not change frequently.

Transactional Replication

• Transactional replication is also known as dynamic replication . In transactional replication, modifications to the publication at the publisher are propagated to the subscriber incrementally.
• Publisher and the subscriber are always in synchronization and should always be connected.
• This type is mostly used when subscribers always need the latest data for processing .

Merge replication

It allows making autonomous changes to replicated data on the Publisher and on the Subscriber. With merge replication, SQL Server captures all incremental data changes in the source and in the target databases, and reconciles conflicts according to rules you configure or using a custom resolver you create. Merge replication is best used when you want to support autonomous changes on the replicated data on the Publisher and on the Subscriber .

Replication agents involved in merge replication are snapshot agent and merge agent.

Implement merge replication if, changes are made constantly at the publisher and subscribing servers, and must be merged in the end.

By default, the publisher wins all conflicts that it has with subscribers because it has the highest priority. Conflict resolver can be customized

 

 

Configuring Replication(2 steps)

Step 1 : Add new publication

http://www.exforsys.com/tutorials/sql-server-2005/sql-server-configuring-replication.html

1. In Object Explorer , expand the Replication folder.
2. Right-click Publications , and then click New Publication.

 

 

....All the way NEXT (refer to http://www.exforsys.com/tutorials/sql-server-2005/sql-server-configuring-replication.html)

 

 

 

Type in windows account - database server host windows log in account

 

 

 

....All the way NEXT (refer to http://www.exforsys.com/tutorials/sql-server-2005/sql-server-configuring-replication.html)

 

 

Step 2 : Add new Subscriptions

 

 

...

 

 

 

Questions & Answers:

1. What's the difference between peer-to-peer replication and merge replication using SQL Server?

• Peer-to-Peer Transactional Replication is used to support applications that distribute read operations across a number of server nodes.

  Although read operations can be spread across multiple nodes , write operations are applied to a single node and propogated out to the other nodes . The application needs to be aware of the network topology in order to direct your write activity.

  Peer-To-Peer Replicaiton

• Merge Replication is bi-directional i.e. read and wrtie operations are propogated to and from all nodes . Merge replication often requires the implementation of conflict resolution .

  See: How Merge Replication Works