Interview Questions.

Question 1. What Is Sql Server Replication?

Answer :

Replication is subset of SQL Server which could pass information and database gadgets in an automated manner from one database to some other database. This allows users to work with the equal records at exclusive places and modifications that are made are transferred to preserve the databases synchronized.

Question 2. What Are The Different Types Of Sql Server Replication?

Answer :

Snapshot replication - As the call implies photograph replication takes a snapshot of the posted gadgets and applies it to a subscriber. Snapshot replication completely overwrites the information on the subscriber on every occasion a snapshot is implemented. It is first-class ideal for fairly static statistics or if it's applicable to have statistics out of sync between replication intervals. A subscriber does not constantly want to be linked, so records marked for replication may be implemented the next time the subscriber is attached.  An instance use of photograph replication is to replace a listing of gadgets that only changes periodically.

Transactional replication - As the call implies, it replicates every transaction for the item being published. To installation transactional replication, a picture of the publisher or a backup is taken and implemented to the subscriber to synchronize the statistics. After that, when a transaction is written to the transaction log, the Log Reader Agent reads it from the transaction log and writes it to the distribution database and then to the subscriber. Only devoted transactions are replicated to make certain information consistency. Transactional replication is extensively carried out in which excessive latency isn't allowed, together with an OLTP system for a financial institution or a inventory trading firm, because you continually want real-time updates of coins or stocks. 

Merge replication - This is the maximum complex forms of replication which lets in changes to appear at both the writer and subscriber.  As the call implies, modifications are merged to maintain statistics consistency and a uniform set of facts. Just like transactional replication, an initial synchronization is executed by making use of photograph. When a transaction occurs on the Publisher or Subscriber, the alternate is written to alternate tracking tables. The Merge Agent tests these tracking tables and sends the transaction to the distribution database in which it receives propagated.  The merge agent has the capability of resolving conflicts that arise in the course of information synchronization.  An instance of the usage of merge replication can be a store with many branches where products may be centrally stored in inventory. As the overall stock is decreased it's far propagated to the alternative stores to preserve the databases synchronized. 

SQL Database Interview Questions
Question three. What Is The Difference Between Push And Pull Subscription?

Answer :

Push - As the call implies, a push subscription pushes data from writer to the subscriber. Changes can be pushed to subscribers on demand, constantly, or on a scheduled basis. 

Pull - As the call implies, a pull subscription requests changes from the Publisher.  This permits the subscriber to drag facts as needed.  This is beneficial for disconnected machines consisting of notebook computers that aren't constantly connected and after they join they are able to pull the facts. 

Question four. What Are Different Replication Agents And What's Their Purpose?

Answer :

Snapshot Agent- The Snapshot Agent is used with all forms of replication. It prepares the schema and the initial bulk copy documents of published tables and other items, shops the snapshot documents, and data data approximately synchronization in the distribution database. The Snapshot Agent runs on the Distributor. 
Log Reader Agent - The Log Reader Agent is used with transactional replication. It movements transactions marked for replication from the transaction log on the Publisher to the distribution database. Each database posted the use of transactional replication has its personal Log Reader Agent that runs at 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 image replication and transactional replication. It applies the initial image 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 on the Subscriber for pull subscriptions. 
Merge Agent - The Merge Agent is used with merge replication. It applies the preliminary picture to the Subscriber and moves and reconciles incremental statistics changes that arise. Each merge subscription has its very own Merge Agent that connects to both the Publisher and the Subscriber and updates both. The Merge Agent runs at both the Distributor for push subscriptions or the Subscriber for pull subscriptions. 
Queue Reader Agent - The Queue Reader Agent is used with transactional replication with the queued updating alternative. The agent runs at the Distributor and movements modifications made at the Subscriber returned to the Publisher. Unlike the Distribution Agent and the Merge Agent, simplest one example of the Queue Reader Agent exists to service all Publishers and guides for a given distribution database. 
SQL Database Tutorial
Question 5. Does A Specific Recovery Model Need To Be Used For A Replicated Database?

Answer :

Replication is not depending on any precise recuperation model. A database can take part in replication whether or not it's far in easy, bulk-logged, or complete. However how records is tracked for replication depends at the kind of replication used.

DB2 Using SQL Interview Questions
Question 6. What Type Of Locking Occurs During The Snapshot Generation?

Answer :

 Locking relies upon at the form of replication used:

In photo replication, the picture agent locks the item during the complete image generation technique.
In transactional replication, locks are obtained first of all for a totally brief time and then released. Normal operations on a database can hold after that.
In merge replication, no locks are acquired during the snapshot era procedure.
Question 7. What Options Are There To Delete Rows On The Publisher And Not On The Subscriber?

Answer :

One choice is to copy saved process execution in preference to the real DELETE command.  You can create  special versions of the stored strategies one at the publisher that does the delete and the other on the subscriber that doesn't do the delete.

Another option is to not mirror DELETE instructions.

DB2 Using SQL Tutorial Database Interview Questions
Question eight. Is It Possible To Run Multiple Publications And Different Type Of Publications From The Same Distribution Database?

Answer :

Yes this may be performed and there are not any restrictions on the wide variety or forms of courses that can use the same distribution database. One element to word even though is that each one guides from a Publisher should use the same Distributor and distribution database.

Question nine. Data Is Not Being Delivered To Subscribers, What Can Be The Possible Reasons?

Answer :

There are some of feasible reasons for records not being delivered to Subscribers:

The table is filtered, and there are no adjustments to supply to a given Subscriber.
One or more marketers are not jogging or are failing with an mistakes.
Data is deleted by a cause, or a cause includes a ROLLBACK declaration.
A transactional subscription was initialized with out a picture, and modifications have befell on the Publisher for the reason that booklet was created.
Replication of stored process execution for a transactional publication produces unique results on the Subscriber.
The INSERT stored manner used by a transactional article includes a situation that is not met.
Data is deleted with the aid of a consumer, a replication script, or some other application.
Database device standards Interview Questions
Question 10. Explain What Stored Procedure Sp_replcounters Is Used For?

Answer :

Sp_replcounters is a device stored procedure that returns facts about the transaction fee, latency, and first and final log collection variety (LSN) for each publication on a server. This is run at the publishing server. Running this stored manner on a server that is acting as the distributor or subscribing to guides from some other server will now not go back any records.

Database device concepts Tutorial
Question eleven. How Will You Monitor Replication Latency In Transactional Replication?

Answer :

Tracer tokens have been 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). 

SQL DBA Interview Questions
Question 12. 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 through using Transact-SQL or SQL Server Management Objects (SMO). When schema modifications are made in SQL Server Management Studio, Management Studio tries to drop and re-create the desk and because you can't drop a posted objects, the schema exchange will fail.

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

Answer :

Yes this will be finished the use of heterogeneous replication. In SQL Server 2000, publishing facts to other databases including DB2 or Oracle became supported; however, publishing facts from different databases become now not supported without custom programming. In SQL Server 2005 and later variations, Oracle databases can be directly replicated to SQL Server in a whole lot the identical way as trendy SQL Server replication.  

Database Testing Tutorial
Question 14. How Will You Monitor Replication Activity And Performance? What Privilege Do You Need To Use Replication Monitor?

Answer :

The simplest way to monitor replication activity and performance is to use replication monitor, however you can also use the below tools to monitor replication overall performance:  

T-SQL commands. For more info refer msdn article -  http://msdn.Microsoft.Com/en-us/library/ms147874.Aspx    
Microsoft SQL Server Management studio. For more information refer msdn article - http://msdn.Microsoft.Com/en-us/library/ms152763.Aspx    
To display 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 position in the distribution database. A system administrator can upload any person to the replmonitor position, which allows that user to view replication activity in Replication Monitor; however, the consumer can not administer replication.

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

Answer :

sys.Dm_repl_articles - Contains records about each article being published. It returns records from the database being posted and returns a row for every object being posted in every article.
Sys.Dm_repl_schemas - Contains statistics approximately every table and column being published. It returns statistics from the database being posted and returns one row for every column in each object being published
sys.Dm_repl_traninfo - Contains information approximately every transaction in a transactional replication
Database Testing Interview Questions