SQL Server - Types of replication in simple terms that relates to architecture
I tend to get emails that relates to SQL Server questions from the user, by default I suggest them to post on the forums to get quicker response and do reply in simple terms. One of the them I got recently asking about how can I explain the replication models in simple terms, as the originator got confused with the documentation in BOL.
It is evident that SQL Server Replication Architecture is an extensive process to meet most of the data needs which requires careful consideration on configuration of system by choosing a right model to suffice the business needs. Tons of material on web & Microsoft refers that to make the replication go smoothly, you should do a bit of planning, which involves selecting a specific replication model and performing any necessary preliminary tasks before you start configuring replication.
That leaves the major part of decision to the user in selecting appropriate replication model that relates to physical layout of the publisher, distributor, and subscriber databases. Its a common to have multiple versioned SQL environment and whereby the immediate question will be does replication support replicating data from/to different versions of SQL Server? The answer is SQL Server 2005 replication version onwards supports replicating data to different versions of SQL Server. Just a note about using SQL Server 2005 Express edition as per BOL notes:
Microsoft SQL Server 2005 Express Edition can serve as a Subscriber for all types of replication, providing a convenient way to distribute data to client applications that use this edition of SQL Server. When using SQL Server 2005 Express Edition in a replication topology, keep the following considerations in mind:
- SQL Server 2005 Express Edition cannot serve as a Publisher or Distributor. However, merge replication allows changes to be replicated in both directions between a Publisher and Subscriber.
- Replication does not support the Run As Normal User (RANU) feature of SQL Server Express. For more information about this feature, see the SQL Server 2005 Express Edition online documentation.
- SQL Server 2005 Express Edition does not include SQL Server Agent, which is typically used to run replication agents. If you use a pull subscription (in which agents run at the Subscriber), you must synchronize the subscription using Windows Synchronization Manager or RMO.
Additional to this multiple version usage, again notes from BOL to refer:
SQL Server version 7.0 service pack 4 (SP4) is the minimum version required to participate in a replication topology with SQL Server 2005. If you use SQL Server 2000, service pack 3 (SP3) is required. If you have more than one version of SQL Server in a replication topology:
- A Distributor can be any version as long as it is greater than or equal to the Publisher version (in many cases the Distributor is the same instance as the Publisher).
- A Publisher can be any version as long as it less than or equal to the Distributor version.
- Subscriber version depends on the type of publication:
- A read-only Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example: a SQL Server version 7.0 Publisher can have SQL Server 2005 Subscribers; and a SQL Server 2005 Publisher can have SQL Server version 7.0 Subscribers.
- An updatable Subscriber to a SQL Server 2005 transactional publication can be any version equal to or greater than Microsoft SQL Server 2000 SP3.
- A Subscriber to a merge publication can be any version less than or equal to the Publisher version.
Bear in mind that when you are choosing the replication between different versions of SQL Server, you are 'mostly limited to the functionality of the earliest version used. This is due to the architectural differences between earlier version and existing version, think about deprecated & discontinued functionalities and for instanceif you upgrade a Distributor to an instance of SQL Server 2005, but you have a Publisher running an instance of SQL Server 2000, and a Subscriber running an instance of SQL Server version 7.0, you are limited to the general functionality and replication functionality of SQL Server version 7.0. Then relating the same to SQL Server 2005 publisher capabilities, compatibility level is not connected to the database compatibility level and the default compatibility level is set to 80RTM. If you create a publication in the New Publication Wizard, the compatibility level is determined based on the options chosen on the Subscriber Types page of the wizard. Also in SQL Server 2005 you must manually set the compatibility level to 90RTM before enabling functionality that requires that compatibility level. This is the reason during the ugprade process it is recommended to stop the REPLICATION completely and re-define the model afresh. Further this is a larger subject to discuss about mapping SQL Server 2005 data types for previous versions, restoring a replicated database from a previous version and compatibility level for merge publications.
Coming to the original question of getting common replication models that you might want to use are as follows:
-
Peer-to-peer model, a new introduction to SQL Server 2008 version which allows replication between identical participants in the topology. The advantage of this model is that it permits roles to move between replicated nodes dynamically for maintenance or failure management. The disadvantage is the additional administration overhead involved with moving roles.
-
Central publisher model, a common deployed model which maintains the publisher and distributor databases on the same server, with one or more subscribers configured on other servers. The advantages of this model are manageability and ease of maintenance, only issue which slows down the optimization with an extra workload and resource usage on the publication server.
-
Central publisher with remote distributor model is useful when the organisation is spred across the globe to manage the date which maintains the publisher and distributor databases on different servers, with one or more subscribers configured on other servers. The advantage of this model is that the workload is more evenly distributed and only note is that you have to maintain an additional server.
-
Central subscriber model, a good one to manage a single subscriber database that collects data from several publishers. For example, if you have ServerA, ServerB, and ServerC, ServerA and ServerB act as central publishers and ServerC acts as the central subscriber. In this configuration, when updates are distributed from ServerA and ServerB, they are collected on ServerC. A central subscriber can then republish the combined data to other servers. To use this model, all tables used in replication must have a unique primary key; otherwise, the replication model will not work properly.
-
Publishing subscriber model which is similar to central subscriber model but that relays the distribution of data to other subscribers; you can use this with any of the other models. For example, if you have two geographically separated sites, a publisher can replicate data to servers at site A and then have a publishing subscriber at site B that distributes the data to servers at site B.