Friday, 5 April 2013

Replication In SQL Server 2008 R2


What is replication?

In its simplest form, data in a master site are replicated to other sites in the form of snapshots. A snapshot does not have to contain all the master data—it can, for example, exclude certain columns from a table for security reasons.





What are different types of replication?


1.      Transactional Replication
              Transactional replication replicates the data on one server (the publisher) to another server (the subscriber) with less latency than log shipping.
              Transactional replication is implemented by the SQL Server Snapshot Agent, Log Reader Agent, and Distribution Agent. The Snapshot Agent 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 on the Distributor.
              The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database, which acts as a reliable store-and-forward queue. The Distribution Agent copies the initial snapshot files from the snapshot folder and the transactions held in the distribution database tables to Subscribers.
              Incremental changes made at the Publisher flow to Subscribers according to the schedule of the Distribution Agent, which can run continuously for minimal latency, or at scheduled intervals. Because changes to the data must be made at the Publisher (when transactional replication is used without immediate updating or queued updating options), update conflicts are avoided. Ultimately, all Subscribers will achieve the same values as the Publisher. If immediate updating or queued updating options are used with transactional replication, updates can be made at the Subscriber, and with queued updating, conflicts might occur.


2.       Merge Replication
                Merge was designed for more of a client-server environment where the client      sometimes syncs up with the server but is disconnected quite often.Changes are tracked by triggers instead of by the log reader agent, and latency isn't as low as it is with peer to peer.


3.      Snapshot  Replication
          Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

   Define below terms used in replication
·        Publisher
·        Subscriber
·        Distributor

Publisher:
Publisher is the server or database that sends its data to another server or database.
Publisher contain publication/publications. Publication is a collection of one or more articles that is sent to a subscriber server or database. Article is the basic unit of replication and can be a table or a subset of a table.

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.

Distributor
The Distributor is a database instance that acts as a store for replication specific data associated with one or more Publishers. Each Publisher is associated with a single database (known as a distribution database) at the Distributor. The distribution database stores replication status data, metadata about the publication, and, in some cases, acts as a queue for data moving from the Publisher to the Subscribers. In many cases, a single database server instance acts as both the Publisher and the Distributor. This is known as a local Distributor. When the Publisher and the Distributor are configured on separate database server instances, the Distributor is known as a remote Distributor.




What is the difference between Push and Pull Subscription?
Push and Pull, as named in the title of this article,are the two methods available for moving data from the Distributor to the Subscriber(s).  Under the Push method, the Distributor is responsible for queuing data from the Publisher,then propagating it to the Subscriber(s).  Under the Pull method, the Distributor is responsible for queuing data from the Publisher, and it is the job of each Subscriber to connect to the Distributor and grab all queued data ready for replication.  Selecting the incorrect method can lead to serious performance issues, especially at peak times of database use.



No comments:

Post a Comment

CPU queries in SQL server

-- Database Wise CPU Utilization report -- CPU_Time(Ms): CPU Time in Milliseconds -- CPUPercent: Let’s say this instance is using 50% CP...