SQL Server Replication – Types of Replication – Merge Replication

Recently assisted one of my friend’s to choose technology to synchronous databases.   Came across number of options like Replication, Sync Framework etc.  Documenting those for future use.

http://channel9.msdn.com/posts/SQL-Server-Replication

Replication Model

Following are the main components for a SQL Server Replication Model:

Publisher

Distributor

Subscriber

Agents

Publications

Articles

Subscriptions

A replication topology defines the relationship between servers and copies of data and clarifies the logic that determines how data flows between servers.

There are several replication processes (referred to as agents) that are responsible for copying and moving data between the Publisher and Subscribers

Components of SQL Server Replication

Publisher – Publishers are the servers or SQL Server instance(s) that have the data you want to replicate.  The Publisher can have one or more publications, each defining a logically related set of objects and data to replicate.

Distributor – A Distributor you can say a central Database instance in replication which hosts the agents which are responsible for moving data from a publisher to subscriber(s).  A Distributor server also hosts a Distribution Database which is associated with Publisher.  This database stores metadata about the publication, replication status data etc.

Subscriber – A Subscriber is a database instance that receives replicated data.  A Subscriber can receive data from multiple Publishers and publications.  Depending on the type of replication chosen, the Subscriber can also pass data changes back to the Publisher or republish the data to other Subscribers.

Agents – There are multiple agents available in replication depending upon the type of replication we are using.  The main job of a replication agent is distributing data around the replication topology.   A replication agent is controlled by the SQL Server Agent.

Publication – A publication is a collection of one or more articles from one database.  The grouping of multiple articles into a publication makes it easier to specify a logically related set of database objects and data that are replicated as a unit.

Articles – An article is a smallest unit of a publication.  An article can be a table, view, stored procedure or a function.  Where an article is based on a table or view, it can contain all the data or just part of it.  We can also use filters while selecting articles depending on our requirement.

Subscription – A subscription is a copy of a publication which was either delivered at or requested by the Subscriber depending upon the type of subscription.  There are two types of subscriptions: Push and Pull.

Types of SQL Server Replication

Microsoft SQL Server provides three types of replication

Snapshot Replication – Snapshot replication involves copying the articles that make up the publication.  Normally, if they exist already on the subscriber, they are over-written, though this behavior can be changed.  Snapshot replication is more expensive in terms of overhead and network traffice and only takes place at intervals.  Because locks are held during snapshot replication, this can impact other users of the subscriber database.  It is therefore more suitable for static data and enumerations.

Transactional Replication – Transaction replication distributes data in one direction, but transactional replication does offer options that allow updates at the Subscriber.  Once a snapshot replication has synchronized the subscribers with the publisher, all committed transactions on the publisher are then propagated to the subscribers in sequence, via distributed transactions.  One can select a queued update or immediate, depending on requirements.  Transactional replication tracks changes through the SQL Server transaction log.  Transactional replication can have immediate or queued updatable subscribers.

Transactional Replication can be of two types:

– Peer-to-peer Transactional Replication – This is a special type of transactional replication in which every participant act both as a publisher and subscriber

– Bidirectional Transactional Replication – This is where two databases replicate the same articles to each other via a distributor.  There must be loopback detection.  Data conflicts aren’t handled and the replication must be implemented in code, since the GUI doesn’t support it.

Merge Replication – Merge replication is used when several Subscribers might need to update the same data at various times and propagate those changes back to the Publisher and hence to other Subscribers.  It is also required in applications that involve Subscribers receiving data, making changes offline, and finally reconnecting with the publisher to synchronize changes with the Publisher and other Subscribers.

Replication and types of Replication:

http://technet.microsoft.com/en-us/library/ms152531.aspx

http://technet.microsoft.com/en-us/library/ms151176.aspx

http://technet.microsoft.com/en-us/library/ms152746.aspx

http://technet.microsoft.com/en-us/library/ms151832.aspx

Step by Step:

http://sqldotnetsqldba.blogspot.in/2013/05/how-to-configure-merge-replication-in.html

http://blogs.technet.com/b/meamcs/archive/2011/01/06/merge-replication-step-by-step.aspx

http://sqlserverscribbles.com/2013/06/28/how-to-create-merge-replication-in-sql-server/

http://www.mssqlfix.com/2012/03/step-by-step-guide-to-creating-merge.html

How to choose type of replication

http://technet.microsoft.com/en-us/library/ms152565(v=sql.105).aspx

http://www.askaboutasp.net/_-_80_Can_we_use_Snapshot_2c_Transactional_or_Merge_SQL_replication_with_SharePoint_3f.aspx

http://dba.stackexchange.com/questions/18498/transactional-versus-merge-replication-restore-backup-after-downtime

http://www.replicationanswers.com/ReplicationTimesArticle.asp

Other Useful Links:

http://www.sswug.org/articles/viewarticle.aspx?id=19973

http://technet.microsoft.com/en-us/library/aa337457.aspx

http://technet.microsoft.com/en-us/library/ms161556.aspx

http://msftdbprodsamples.codeplex.com/releases/view/55330

http://sqlserverscribbles.com/2013/06/25/how-to-configure-distribution-for-replication/

http://sqlserverscribbles.com/2013/06/28/how-to-create-merge-replication-in-sql-server/

http://stackoverflow.com/questions/9599496/sql-server-2008-express-impossible-to-use-for-merge-replication

http://technet.microsoft.com/en-us/library/ms151819(v=sql.105).aspx

http://social.msdn.microsoft.com/Forums/en-US/c6cd837a-55ac-4858-961e-7b9496859f55/sql-server-2008-r2-express-edition-with-merge-replication

http://www.sqlrepl.com/sql-server/implementing-a-replication-agent-progess-bar/

http://technet.microsoft.com/en-us/library/ms151198.aspx

http://blogs.msdn.com/b/chrissk/archive/2010/02/01/sql-server-merge-replication-best-practices.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

TechFindings

BizTalk, WCF, ESB ToolKit, Windows Azure

danrosanova

Middleware, Services, and Integration

Steef-Jan Wiggers Blog

BizTalk, WCF, ESB ToolKit, Windows Azure

Journey Towards Destiny

Journal of IT Experience

Vijay Data Science

Random Rumblings related to DataScience

MSDN Magazine (en-us)

BizTalk, WCF, ESB ToolKit, Windows Azure

Sandro Pereira BizTalk Blog

My notes about BizTalk Server 2004, 2006, 2006 R2, 2009, 2010, 2013 and now also Windows Azure BizTalk Services.

BizTalk360

BizTalk, WCF, ESB ToolKit, Windows Azure

Ajay Solanki

BizTalk, WCF, ESB ToolKit, Windows Azure

Richard Seroter's Architecture Musings

Blog Featuring Code, Thoughts, and Experiences with Software and Services

%d bloggers like this: