Explore topic-wise InterviewSolutions in Current Affairs.

• SNAPSHOT AGENT
• MERGE Agent

• SNAPSHOT AGENT
• LOG READER Agent
• DISTRIBUTION Agent

Server to Server REPLICATION:-

1. IMPROVING scalability and availability
2. Data WAREHOUSING and reporting
3. Integrating data from multiple sites
4. Integrating heterogeneous data
5. OFFLOADING batch processing

Sverer to Client Replication:-

1. EXCHANGING data with mobile users
2. Retail point of sale (POS) applications
3. Integrating data from multiple sites

Server to Server Replication:-

Sverer to Client Replication:-

It is used when CHANGES are frequent. The replication agent MONITORS the changes at the publisher and DISTRIBUTES the changes to the subscribers. Transactional Replication is required where up to DATE DATA is required.

It is used when changes are frequent. The replication agent monitors the changes at the publisher and distributes the changes to the subscribers. Transactional Replication is required where up to date data is required.

• This ALLOWS both publisher and subscriber to work INDEPENDENTLY, online or OFFLINE and merges the CHANGES later.
• Here changes are track on both publisher and subscriber and then merged.

• Snapshot of entire DATA is copied from publisher to the subscriber's database on regular interval.
• This kind of REPLICATION is used for replicating data that doesn’t change FREQUENTLY.
• It is used where the source can manage with out of date data.
• This replication is considered when amount of data to be REPLICATED is SMALL.

There are 3 TYPES of replication:

1. Snap SHOT replication
2. MERGE replication
3. Trasctinal Replication

There are 3 types of replication:

Pull Subscription: In a pull subscription, CHANGES to a subscriber cannot be published without any request from subscriber. This allows the user at the Subscriber to determine when the data changes are SYNCHRONIZED.

PUSH Subscription: In a push subscription, changes to a subscriber can be published without any request from subscriber. The changes can be PUSHED either periodically or on DEMAND.

Pull Subscription: In a pull subscription, changes to a subscriber cannot be published without any request from subscriber. This allows the user at the Subscriber to determine when the data changes are synchronized.

Push Subscription: In a push subscription, changes to a subscriber can be published without any request from subscriber. The changes can be pushed either periodically or on demand.

Publisher: A publisher is any database UNIT that makes data available to other sources via replication. The publisher can have MULTIPLE publications which defines the data to replicate.

Subscriber: A subscriber is any database unit that receives the REPLICATED data. It can receive data from multiple publishers or publications. Data can be republished to the other subscriber.

Distributor: A distributor is any database unit that acts as a store for the data to be replicated. Each publisher is associated with a SINGLE database called as distribution database. Replication status data is stored in distribution database. A distributor can be local or remote.

Publisher: A publisher is any database unit that makes data available to other sources via replication. The publisher can have multiple publications which defines the data to replicate.

Subscriber: A subscriber is any database unit that receives the replicated data. It can receive data from multiple publishers or publications. Data can be republished to the other subscriber.

Distributor: A distributor is any database unit that acts as a store for the data to be replicated. Each publisher is associated with a single database called as distribution database. Replication status data is stored in distribution database. A distributor can be local or remote.

• sys.dm_repl_articles - Contains INFORMATION about each article being published. It RETURNS data from the DATABASE being published and returns a row for each object being published in each article.
• sys.dm_repl_schemas - Contains information about each table and column being published. It returns data from the database being published and returns ONE row for each column in each object being published.
• sys.dm_repl_traninfo - Contains information about each transaction in a TRANSACTIONAL replication.

The easiest way to monitor REPLICATION activity and performance is to USE replication monitor. To monitor replication, a user must be a member of the SYSADMIN fixed server role at the Distributor or a member of the replmonitor fixed database role in the distribution database. A system administrator can add any user to the replmonitor role, which allows that user to view replication activity in Replication Monitor; however, the user cannot ADMINISTER replication.

The easiest way to monitor replication activity and performance is to use replication monitor. To monitor replication, a user must be a member of the sysadmin fixed server role at the Distributor or a member of the replmonitor fixed database role in the distribution database. A system administrator can add any user to the replmonitor role, which allows that user to view replication activity in Replication Monitor; however, the user cannot administer replication.

Yes this can be done using heterogeneous replication. In SQL Server 2000, publishing data to other databases such as DB2 or Oracle was SUPPORTED; however, publishing data from other databases was not supported without custom programming. In SQL Server 2005 and later versions, Oracle databases can be directly replicated to SQL Server in MUCH the same WAY as standard SQL Server replication. For more INFORMATION, please read tip: Introduction to SQL Server Heterogeneous Replication.

Yes this can be done using heterogeneous replication. In SQL Server 2000, publishing data to other databases such as DB2 or Oracle was supported; however, publishing data from other databases was not supported without custom programming. In SQL Server 2005 and later versions, Oracle databases can be directly replicated to SQL Server in much the same way as standard SQL Server replication. For more information, please read tip: Introduction to SQL Server Heterogeneous Replication.

Yes. SCHEMA changes to tables must be MADE by using Transact-SQL or SQL Server Management OBJECTS (SMO). When schema changes are made in SQL Server Management Studio, Management Studio attempts to drop and re-create the table and since you cannot drop a PUBLISHED objects, the schema change will fail.

Yes. Schema changes to tables must be made by using Transact-SQL or SQL Server Management Objects (SMO). When schema changes are made in SQL Server Management Studio, Management Studio attempts to drop and re-create the table and since you cannot drop a published objects, the schema change will fail.

Tracer tokens were introduced with SQL Server 2005 transactional REPLICATION as a way to monitor the latency of delivering transactions from the publisher to the distributor and from the distributor to the subscriber(s). For DETAILS, PLEASE REFER tip to this tip: Monitor SQL Server replication latency USING tracer tokens.

Tracer tokens were introduced with SQL Server 2005 transactional replication as a way to monitor the latency of delivering transactions from the publisher to the distributor and from the distributor to the subscriber(s). For details, please refer tip to this tip: Monitor SQL Server replication latency using tracer tokens.

Sp_replcounters is a system stored procedure that returns INFORMATION about the TRANSACTION rate, latency, and first and last log sequence number (LSN) for each publication on a server. This is run on the publishing server. Running this stored procedure on a server that is ACTING as the DISTRIBUTOR or subscribing to publications from another server will not return any data.

Sp_replcounters is a system stored procedure that returns information about the transaction rate, latency, and first and last log sequence number (LSN) for each publication on a server. This is run on the publishing server. Running this stored procedure on a server that is acting as the distributor or subscribing to publications from another server will not return any data.

There are a number of possible causes for data not being delivered to SUBSCRIBERS:

• The table is filtered, and there are no changes to deliver to a GIVEN Subscriber.
• One or more AGENTS are not running or are failing with an error.
• Data is deleted by a trigger, or a trigger includes a ROLLBACK statement.
• A transactional subscription was initialized WITHOUT a snapshot, and changes have occurred on the Publisher SINCE the publication was created.
• Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.
• The INSERT stored procedure used by a transactional article includes a condition that is not met.
• Data is deleted by a user, a replication script, or another application.

There are a number of possible causes for data not being delivered to Subscribers:

Yes this can be done and there are no restrictions on the number or types of publications that can USE the same distribution DATABASE. One thing to note THOUGH is that all publications from a Publisher MUST use the same DISTRIBUTOR and distribution database.

Yes this can be done and there are no restrictions on the number or types of publications that can use the same distribution database. One thing to note though is that all publications from a Publisher must use the same Distributor and distribution database.

ONE OPTION is to replicate stored PROCEDURE execution INSTEAD of the actual DELETE command. You can create two different versions of the stored procedures one on the publisher that does the delete and the other on the subscriber that does not do the delete.

Another option is to not replicate DELETE commands.

One option is to replicate stored procedure execution instead of the actual DELETE command. You can create two different versions of the stored procedures one on the publisher that does the delete and the other on the subscriber that does not do the delete.

Another option is to not replicate DELETE commands.

Locking depends on the TYPE of replication used:

• In snapshot replication, the snapshot agent locks the object during the entire snapshot generation process.
• In transactional replication, locks are acquired initially for a very brief TIME and then released. NORMAL operations on a database can continue after that.
• In MERGE replication, no locks are acquired during the snapshot generation process.

Locking depends on the type of replication used:

Replication is not dependent on any PARTICULAR recovery MODEL. A database can PARTICIPATE in replication whether it is in simple, bulk-logged, or full. However how DATA is tracked for replication depends on the type of replication USED.

Replication is not dependent on any particular recovery model. A database can participate in replication whether it is in simple, bulk-logged, or full. However how data is tracked for replication depends on the type of replication used.

Snapshot Agent:- The Snapshot Agent is used with all types of replication. It prepares the schema and the initial bulk copy files of published tables and other OBJECTS, stores the snapshot files, and records information about synchronization in the distribution database. The Snapshot Agent runs at the Distributor.

Log Reader Agent:-

• The Log Reader Agent is used with transactional replication. It moves transactions marked for replication from the transaction log on the Publisher to the distribution database. Each database.
• Published using transactional replication has its own Log Reader Agent that runs on the Distributor and connects to the Publisher (the Distributor can be on the same computer as the Publisher).

Distribution Agent:- The Distribution Agent is used with snapshot replication and transactional replication. It applies the initial snapshot to the Subscriber and moves transactions held in the distribution database to Subscribers. The Distribution Agent runs at EITHER the Distributor for push subscriptions or at the Subscriber for pull subscriptions.

Merge Agent:- The Merge Agent is used with merge replication. It applies the initial snapshot to the Subscriber and moves and reconciles incremental data changes that occur. Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both. The Merge Agent runs at either the Distributor for push subscriptions or the Subscriber for pull subscriptions.

QUEUES Reader Agent:- The Queue Reader Agent is used with transactional replication with the queued updating OPTION. The agent runs at the Distributor and moves changes made at the Subscriber back to the Publisher. Unlike the Distribution Agent and the Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given distribution database.

Snapshot Agent:- The Snapshot Agent is used with all types of replication. It prepares the schema and the initial bulk copy files of published tables and other objects, stores the snapshot files, and records information about synchronization in the distribution database. The Snapshot Agent runs at the Distributor.

Log Reader Agent:-

Distribution Agent:- The Distribution Agent is used with snapshot replication and transactional replication. It applies the initial snapshot to the Subscriber and moves transactions held in the distribution database to Subscribers. The Distribution Agent runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions.

Merge Agent:- The Merge Agent is used with merge replication. It applies the initial snapshot to the Subscriber and moves and reconciles incremental data changes that occur. Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both. The Merge Agent runs at either the Distributor for push subscriptions or the Subscriber for pull subscriptions.

Queues Reader Agent:- The Queue Reader Agent is used with transactional replication with the queued updating option. The agent runs at the Distributor and moves changes made at the Subscriber back to the Publisher. Unlike the Distribution Agent and the Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given distribution database.

At first data and object is synchronized between PUBLISHER and subscribers. The snapshot is created on the publisher and transmitted to subscribers. All the subsequent CHANGES on the publisher are stored in distribution database. SUBSCRIBER receives the data either using push or PULL mechanism as configured from distribution database.

At first data and object is synchronized between publisher and subscribers. The snapshot is created on the publisher and transmitted to subscribers. All the subsequent changes on the publisher are stored in distribution database. Subscriber receives the data either using push or pull mechanism as configured from distribution database.

Replication is the PROCESS of DISTRIBUTING data from one database to ANOTHER on the same server or servers connected through LAN or the internet. Replication is used to synchronize data between DATABASES that may be at remote LOCATION.

Replication is the process of distributing data from one database to another on the same server or servers connected through LAN or the internet. Replication is used to synchronize data between databases that may be at remote location.