Replication

Replication is a type of configuration used to copy data and database objects from one database instance to another. A database administrator has to configure a server for replication, and monitor it in order to detect and resolve issues regarding replication.

Requirements for replication: The following roles are required for replication configuration:

• Distributor: The server that is configured to the Distributor role distributes data to Subscribers. It creates tables and files to store the data for replication. The computer to be used as a Distributor should have a large amount of storage space.
• Publisher: The server that is configured to the Publisher role stores data for replication. It detects data changes and maintains information about all publications.
• Subscriber: The server that is configured to the Subscriber role receives data published by either Publisher or Distributor. The Subscriber configuration depends on the type of replication chosen to replicate data. Each replication type should have a different configuration for a Subscriber.

Replication objects: After the configuration of Publisher, Distributor, and Subscriber servers, various objects are needed to replicate the data. These objects are as follows:

• Article: Article is an object that includes various database objects such as tables, views, stored procedures, etc. Each of the database objects can be selected as an article. All the articles are combined to create a publication.
• Publication: Publication is a combination of articles. The data to be received by the Subscriber is sent as a publication. An article must be placed in a publication in order to send the data to the Subscriber.
• Subscription: Subscription receives the publication sent by the Publisher or Distributor. Subscription is of the following two types:
  • Push subscription: A push subscription is created at the Publisher. Data is pushed to the Subscribers by the Merge or the Distribution agent.
  • Pull subscription: A pull subscription is created at the Subscriber. Data is pulled by the Merge or the Distribution agent from the Publisher.

Replication Models: A DBA has to decide the type of replication model before replicating the data. He has to decide the configuration for the Publisher, Distributor, and Subscriber databases. The following replication models can be applied before data replication:

• Peer-to-Peer: It is a type of replication model in which a single database is hosted by multiple servers. The purpose of this model is to maintain a database and manage database failures. If a server containing the database is unavailable, the applications can access the data through other servers hosting the database.
• Central Publisher: The Central Publisher is a replication model in which the Publisher and the Distributor roles are maintained on the same SQL Server 2005 computer. The Subscriber role is configured on other servers. The maintenance of the server is easy, but it adds additional load on the server.
• Central Publisher with Remote Distributor: The Central Publisher with Remote Distributor is a replication model in which the Publisher and the Distributor roles are maintained on separate SQL Server 2005 computers. The Subscriber role is configured on other servers. The workload of the Publisher server is distributed, but an additional Distributor server is to be maintained.
• Central Subscriber: Central Subscriber is a replication model in which the Subscriber role is configured on a single server. It is used to collect data from several Publisher servers. It can also be configured to send data to other servers.
• Publishing Subscriber: Publishing Subscriber is a replication model in which data is obtained from a Publisher and transferred to other Subscribers. This replication model can be used with other replication models for the replication of data.

Types of replication: There are different types of replication and a DBA has to choose which type of replication he needs to configure. Some applications need only a snapshot of the database, whereas others need incremental data changes. The different types of replication are as follows:

• Transactional Replication: Transactional replication starts with a snapshot of the Publication database objects and data. The modifications made in the data and in the schema are delivered to the Subscriber database, simultaneously. Transactional replication is used in a server-to-server environment in the following conditions:
  • When a Publisher or Subscriber is a non-SQL Server database, such as Oracle.
  • When incremental changes are to be made at the Subscriber.
  • When an application requires low latency.
  • When an application requires access to data changes.
  • When the Publisher has a high volume of insert, update, or delete operations.
• Merge Replication: Merge replication starts with a snapshot of the Publication database. Triggers track the modifications in the data and the schema at the Publisher or the Subscriber server. Merge replication is performed in a server-to-client environment. It should be performed in the following conditions:
  • When same data is to be updated by multiple Subscribers.
  • When data changes are to be propagated to the Publisher and other Subscribers.
  • When offline data changes are to be synchronized on the Publisher and other Subscribers.
  • When conflicts are to be detected and resolved.
  • When each Subscriber needs different partitions of data.

  Merge replication does not take place if a Publisher or Subscriber is a non-SQL Server database.

• Snapshot Replication: Snapshot replication distributes data at a specific moment. It does not monitor the modifications in data or schema. The entire snapshot is sent to Subscribers when synchronization occurs. The snapshot replication can be performed in the following conditions:
  • When data changes infrequently.
  • When small volumes of data are to be replicated.
  • When large volumes of changes occur over a short period of time.

  It does not take place when a Publisher or a Subscriber is a non-SQL Server database.

Replication Agents: Replication agents are used during the replication of data. They store data published by a Publisher. They also initialize and monitor the replication. Replication agents are as follows:

• Queue Reader Agent: The Queue Reader agent runs with a transactional replication. It runs at the Distributor database and transfers the changes from the Subscribers to the Publisher database. Only one instance of this agent exists to serve all the Publishers.
• Snapshot Agent: The Snapshot agent is used with all types of replication. It is used to prepare snapshot files that contain the schema and the data of published tables and database objects in the publication database. It also records synchronization jobs in the distribution database. It runs at the Distributor database instance.
• Log Reader Agent: The Log Reader agent is used with a transactional replication. It is used to monitor the transaction log of each database configured for transactional replication. It copies the transactions that are marked for replication to the Distribution database.
• Distribution Agent: The Distribution agent is used with transactional and snapshot replications. It is used in a transactional replication to move transaction files and data to the Subscribers database. It runs at the Subscribers database for pull subscriptions or at the Distributors database for push subscriptions.
• Merge Agent: The Merge agent is used with the merge replication. It applies the snapshot to the Subscriber database instance, and the changes in the Subscriber are reflected in the Publisher database. The Merge agent uploads data changes from Subscribers to Publishers, and downloads data from Publishers to Subscribers.

• Download free practice test for mcitp MCITP exam.
• Pass Microsoft mcitp-database-administrator - MCITP Database Administrator
• Become Microsoft MCITP Database Administrator SQL Server certified.
• Get certified in first attempt download mcitp-database-admin-sql-server-2005 - MCITP Database Administrator SQL Server 2005 simulation.
• Pass MCITP Database Developer SQL Server 2008 in first attampt.
• Become Microsoft MCITP Microsoft SQL Server certified.
• Get certified in first attempt download mcp - MCP simulation.
• Pass MCTS SQL Server 2005 in first attampt.
• Get certified in first attempt download 70-443 - SQL Server 2005 Design simulation.
• Get certified in first attempt download 70-444 - SQL Server 2005 Optimizing and Maintaining simulation.
• Pass Microsoft 70-447 - SQL Server 2005 Design

• Previous Entry: What is the mirror role?
• Next Entry: How to create a database diagram?