Replication is a very good option in SQL Server 2008, developed into a perfectly shaped, so that the planned strategy for databases and implemented on your production server. An even greater security of data from their loss, under the Backup strategy and implementation can be thought of replication.
Replication offers the best opportunity to preserve a copy of the data in another instance, server also in large geographical distance. These offers through replication components, architectures, services which will se below, the functioning of all these is shown in the figure.
As the main purpose of replication is to distribute data from the master database in one or more secondary databases. Because replication maintains duplicate copies of data in synchronization with the master copy.
The data to be replicated is defined by using three core components:
Articles is a set or block of data originating from the table. Articles replicates in one or more databases.
Publications are made by groups of the articles that define the replication set.
Filters among a various high-availability technologies replication in that has a ability to make only a portion of a database redundant. You can apply one or more filters to each set/block of data(articles) which are prepared from articles to replicated. Filters in set/block of data are possible in rows and columns.
You can configure databases and instances in three different roles:
Publisher it is the main unit of replication without it will not be able to think replication. It is the main source of data for replication, which consist of articles. Maintain the master copy of the data within a replication architecture.
Subscriber recipient of the article, receiving changes from the replication engine defined by the publication to which it is subscribing. Can receive changes from one or more publication.
Distributor is the main engine within a replication architecture, the distributor is the location in which all replications agents run by default. At the time of replication configuration and create the database named distributor for distributors.
Replication Topologies provides a process flow diagram of replication that describes how data flows within replication architecture.
- Central Publisher Topology most common used
- Central Subscriber Topology
- Other Topologies
Replication Agents services enable the realization of tasks from the beginning the preparation, development, distribution to the client application. Below are the services when they appear and in which methods of replication.
- Snapshot agent (snapshot, transactional and merge replication)
- Log Reader agent (transactional replication)
- Distribution agent (snapshot and transactional replication)
- Merge agent (merge replication)
- Queue Reader agent (updating for transactional and snapshot)
Replication Methods replication engine has three different methods that you can use to replicate data: snapshot, transactional and merge.
Snapshot replication takes the entire set/block of data and sends it during each cycle of the replication engine. This is a fully copy of the data that is applied to subscriber.
The publisher sends a snapshot of the published data to Subscribers at scheduled intervals. It incurs a high degree of latency and site autonomy, because the publications are only refreshed periodically. The snapshot method replicates sizable publications infrequently.
NOTE: The snapshot folder is a directory location that you specify when you configure replication, when you create a publication. Configure the snapshot folder, use network path to refer to this folder to support both push and pull subscription.
Transactional replication begins with a initial snapshot being applied to the subscriber to ensure that two databases is synchronized.
As a subsequent transactions are issued against the publisher, the replication engine applies to the subscriber.
The incremental transaction flow from publisher to subscriber makes transactional replication a good choice for maintaining a secondary copy of a database. The transactional technique updates Subscribers frequently with small changes. The most common configuration is in a server-to-server environment.
You can configure transactional replication with two optional modes:
Immediate updating subscribers
Queued updating subscribers
To sending transactions from a publisher to a subscriber, transactional replication can be deployed in two alternate architectures:
Bidirectional transactional replication
Peer-to-Peer you can take a set of tables and replicate them from DB1 to DB2 using transactional replication. You then create publication over the same set of tables on DB2 and replicate them back to DB1.
Peer-to-Peer has a very strict list of requirements:
Each peer must have its own distributor
The table structure must be exactly the same among all peers
Queued updating and immediate updating options are not available
No data conflicts can occur
Bidirectional is slightly different from peer-to-peer replication in the way you configure it. The set of table being replicated from DB1 to DB2 is the same set of tables being replicated from DB2 to DB1.
With bidirectional replication you get some performance advantages when you implement this type of replication:
You do not need a separate distributor
The table structure can be different
You must implement bidirectional architecture using code, not GUI.
Merge replication can be implemented to high-availability systems. Is designed primarily for mobile, disconnected processing. The publisher and subscriber are normally not connected at all times with this method of replication.
Unlike transactional replication, merge is designed to enable changes to be made at both the publisher and subscriber by default, then exchanges all changes between the publisher and subscriber during each cycle of the agent.
Can occur within any environment that enables distributed processing of transactions.
Types of conflicts:
- The insertion of duplicate primary key(PK), occurs when two user insert the same PK on both Publisher and Subscriber.
- A conflicting update, occurs when two users modify the same row of both Publisher and Subscriber.
- An update of nonexist row, occurs when one user updates a row on one side of the replication architecture and another user deletes the same row on the other side.
The replication engine is required to maintain a single coherent copy of the data between the publisher and subscriber.
The two most common conflict resolver are:
- The publisher always wins
- The subscriber always wins
Pontes, Herleson. The Real MCTS SQL Server 2008, ISBN 13: 978-1-59749-421-2