Top 100+ Database Replication Interview Questions And Answers

Question 1. What Is Replication?

Answer :

Replication is the procedure of distributing information from one database to another at the equal server or servers related via LAN or the internet. Replication is used to synchronize facts among databases that may be at far flung place.

Question 2. Describe In Brief Working Of Replication?

Answer :

At first information and item is synchronized between writer and subscribers. The snapshot is created on the publisher and transmitted to subscribers. All the following changes on the writer are stored in distribution database. Subscriber receives the records either the usage of push or pull mechanism as configured from distribution database.

Sybase Interview Questions
Question 3. What Are Different Replication Agents And What's Their Purpose?

Answer :

Snapshot Agent:- The Snapshot Agent is used with all kinds of replication. It prepares the schema and the preliminary bulk copy files of posted tables and other items, shops the image files, and information information about synchronization inside the distribution database. The Snapshot Agent runs on the Distributor.

Log Reader Agent:-

The Log Reader Agent is used with transactional replication. It moves transactions marked for replication from the transaction go browsing the Publisher to the distribution database. Each database.
Published using transactional replication has its own Log Reader Agent that runs at the Distributor and connects to the Publisher (the Distributor can be at the equal laptop because the Publisher).
Distribution Agent:- The Distribution Agent is used with snapshot replication and transactional replication. It applies the initial picture to the Subscriber and actions transactions held in the distribution database to Subscribers. The Distribution Agent runs at both 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 photo to the Subscriber and movements and reconciles incremental information adjustments that occur. Each merge subscription has its very own Merge Agent that connects to each the Publisher and the Subscriber and updates each. 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 alternative. The agent runs at the Distributor and moves changes made at the Subscriber returned to the Publisher. Unlike the Distribution Agent and the Merge Agent, handiest one example of the Queue Reader Agent exists to carrier all Publishers and guides for a given distribution database.

Question 4. Does A Specific Recovery Model Need To Be Used For A Replicated Database?

Answer :

Replication is not dependent on any precise recuperation model. A database can take part in replication whether or not it's miles in easy, bulk-logged, or full. However how facts is tracked for replication depends at the type of replication used.

MongoDB Tutorial
Question five. What Type Of Locking Occurs During The Snapshot Generation?

Answer :

Locking relies upon at the kind of replication used:

In image replication, the snapshot agent locks the object at some point of the complete picture technology process.
In transactional replication, locks are obtained to start with for a totally brief time after which released. Normal operations on a database can preserve after that.
In merge replication, no locks are received during the snapshot era method.
SSRS(SQL Server Reporting Services) Interview Questions
Question 6. What Options Are There To Delete Rows On The Publisher And Not On The Subscriber?

Answer :

One choice is to duplicate saved process execution instead of the real DELETE command.  You can create two distinct versions of the stored tactics one at the publisher that does the delete and the alternative on the subscriber that does not do the delete.

Another option is to no longer mirror DELETE instructions.

Question 7. Is It Possible To Run Multiple Publications And Different Type Of Publications From The Same Distribution Database?

Answer :

Yes this may be carried out and there are not any restrictions on the range or kinds of courses that may use the same distribution database. One element to word although is that each one courses from a Publisher need to use the identical Distributor and distribution database.

Oracle Data Integrator (ODI) Interview Questions
Question 8. Data Is Not Being Delivered To Subscribers, What Can Be The Possible Reasons?

Answer :

There are a number of feasible reasons for statistics not being brought to Subscribers:

The table is filtered, and there are no changes to deliver to a given Subscriber.
One or more retailers aren't going for walks or are failing with an errors.
Data is deleted by using a trigger, or a cause consists of a ROLLBACK assertion.
A transactional subscription became initialized with out a picture, and adjustments have came about at the Publisher because the e-book was created.
Replication of stored system execution for a transactional ebook produces distinctive consequences on the Subscriber.
The INSERT stored technique used by a transactional article includes a situation that is not met.
Data is deleted with the aid of a user, a replication script, or another software.
Question 9. Explain What Stored Procedure Sp_replcounters Is Used For?

Answer :

Sp_replcounters is a system saved manner that returns information about the transaction price, latency, and primary and closing log series number (LSN) for every ebook on a server. This is administered on the publishing server. Running this stored method on a server this is acting because the distributor or subscribing to guides from another server will now not return any information.

Oracle RAC/ASM Interview Questions
Question 10. How Will You Monitor Replication Latency In Transaction Replication?

Answer :

Tracer tokens have been brought with SQL Server 2005 transactional replication as a way to monitor the latency of handing over transactions from the writer to the distributor and from the distributor to the subscriber(s). For information, please refer tip to this tip: Monitor SQL Server replication latency the use of tracer tokens.

Question 11. If I Create A Publication With One Table As An Article, And Then Change The Schema Of The Published Table (for Example, By Adding A Column To The Table), Will The New Schema Ever Be Applied At The Subscribers?

Answer :

Yes. Schema modifications to tables should be made with the aid of the use of Transact-SQL or SQL Server Management Objects (SMO). When schema adjustments are made in SQL Server Management Studio, Management Studio attempts to drop and re-create the desk and since you can not drop a published objects, the schema exchange will fail.

Database Administration Interview Questions
Question 12. Is It Possible To Replicate Data From Sql Server To Oracle?

Answer :

Yes this will be performed the usage of heterogeneous replication. In SQL Server 2000, publishing information to different databases which include DB2 or Oracle became supported; however, publishing facts from other databases turned into now not supported without custom programming. In SQL Server 2005 and later versions, Oracle databases may be without delay replicated to SQL Server in tons the same manner as standard SQL Server replication.  For more information, please examine tip: Introduction to SQL Server Heterogeneous Replication.

Sybase Interview Questions
Question thirteen. How Will You Monitor Replication Activity And Performance? What Privilege Do You Need To Use Replication Monitor?

Answer :

The easiest way to display replication interest and overall performance is to apply replication display. To monitor replication, a consumer ought to be a member of the sysadmin fixed server role on the Distributor or a member of the replmonitor fixed database role in the distribution database. A gadget administrator can upload any consumer to the replmonitor role, which permits that person to view replication interest in Replication Monitor; but, the person can not administer replication.

Question 14. Can You Tell Me Some Of The Common Replication Dmv's And Their Use?

Answer :

sys.Dm_repl_articles - Contains data approximately each article being posted. It returns information from the database being published and returns a row for every object being posted in every article.
Sys.Dm_repl_schemas - Contains information approximately every desk and column being published. It returns statistics from the database being posted and returns one row for each column in each object being published.
Sys.Dm_repl_traninfo - Contains statistics approximately every transaction in a transactional replication.
Question 15. What Are Publisher, Distributor And Subscriber In “replication”?

Answer :

Publisher: A publisher is any database unit that makes records to be had to different resources through replication. The publisher can have more than one guides which defines the records to copy.

Subscriber: A subscriber is any database unit that gets the replicated statistics. It can acquire facts from multiple publishers or guides. Data may be republished to the alternative subscriber.

Distributor: A distributor is any database unit that acts as a store for the information to be replicated. Each writer is related to a single database called as distribution database. Replication status data is saved in distribution database. A distributor can be neighborhood or faraway.

Oracle GoldenGate Interview Questions
Question 16. What Is “push” And “pull” Subscription?

Answer :

Pull Subscription:  In a pull subscription, adjustments to a subscriber cannot be published without any request from subscriber. This permits the person on the Subscriber to determine while the records adjustments are synchronized.

Push Subscription: In a push subscription, adjustments to a subscriber may be posted with none request from subscriber. The changes can be pushed either periodically or on demand.

Question 17. What Are Different Models/types Of Replication?

Answer :

There are 3 sorts of replication:

Snap shot replication
Merge replication
Trasctinal Replication
MongoDB Interview Questions
Question 18. What Is Snapshot Replication?

Answer :

Snapshot of entire information is copied from publisher to the subscriber's database on everyday interval.
This kind of replication is used for replicating facts that doesn’t trade frequently.
It is used wherein the source can manipulate without of date facts.
This replication is taken into consideration when quantity of records to be replicated is small.
SSRS(SQL Server Reporting Services) Interview Questions
Question 19. What Is Merge Replication?

Answer :

This allows each publisher and subscriber to work independently, on-line or offline and merges the changes later.
Here changes are music on both publisher and subscriber and then merged.
Question 20. What Is A Transaction Replication?

Answer :

It is used whilst modifications are frequent. The replication agent monitors the adjustments on the publisher and distributes the adjustments to the subscribers. Transactional Replication is needed in which updated facts is needed.

Oracle Data Guard Interview Questions
Question 21. What Are The Uses Of Replication?

Answer :

Server to Server Replication:-

Improving scalability and availability
Data warehousing and reporting
Integrating information from a couple of websites
Integrating heterogeneous facts
Offloading batch processing
Sverer to Client Replication:-

Exchanging data with cell customers
Retail point of sale (POS) programs
Integrating statistics from a couple of sites
Question 22. What Are The Agents Used For Transactional Replication?

Answer :

Snapshot Agent
Log Reader Agent
Distribution Agent
Question 23. What Are The Agents Used For Merge Replication?

Answer :

Snapshot Agent
Merge Agent
Sql Server Dba Interview Questions