Interview Questions.

Q1. What Is The Difference Between Push And Pull Subscription?

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

Pull - As the name implies, a pull subscription requests changes from the Publisher.  This allows the subscriber to drag information as needed.  This is useful for disconnected machines including pocket book computers that are not continually connected and once they connect they can pull the facts. 

Q2. How Will You Monitor Replication Latency In Transactional Replication?

Tracer tokens were brought with SQL Server 2005 transactional replication as a manner to reveal the latency of turning in transactions from the writer to the distributor and from the distributor to the subscriber(s). 

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

There are a number of possible reasons for information now not being introduced to Subscribers:

The desk is filtered, and there aren't any changes to supply to a given Subscriber.

One or more retailers are not jogging or are failing with an mistakes.

Data is deleted by using a cause, or a cause consists of a ROLLBACK declaration.

A transactional subscription changed into initialized with out a photograph, and adjustments have came about at the Publisher because the book become created.

Replication of saved technique execution for a transactional e-book produces unique effects at the Subscriber.

The INSERT stored method utilized by a transactional article includes a circumstance that isn't met.

Data is deleted by means of a user, a replication script, or every other application.

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

Replication isn't dependent on any unique restoration version. A database can participate in replication whether it's far in easy, bulk-logged, or full. However how facts is tracked for replication relies upon on the sort of replication used.

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

Yes this could be executed and there are no restrictions at the quantity or forms of publications which can use the identical distribution database. One aspect to word though is that all guides from a Publisher ought to use the same Distributor and distribution database.

Q6. Explain What Stored Procedure Sp_replcounters Is Used For?

Sp_replcounters is a gadget stored procedure that returns information approximately the transaction rate, latency, and primary and final log sequence wide variety (LSN) for every publication on a server. This is administered at the publishing server. Running this stored procedure on a server this is appearing as the distributor or subscribing to publications from another server will now not return any facts.

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

Snapshot Agent- The Snapshot Agent is used with all sorts of replication. It prepares the schema and the initial bulk copy documents of posted tables and other gadgets, shops the photo files, and statistics statistics about synchronization within the distribution database. The Snapshot Agent runs on the Distributor. 

Log Reader Agent - The Log Reader Agent is used with transactional replication. It actions 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 may be on the identical laptop as the Publisher) 

Distribution Agent - The Distribution Agent is used with photograph replication and transactional replication. It applies the initial picture to the Subscriber and movements transactions held inside 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 preliminary photo to the Subscriber and movements and reconciles incremental statistics adjustments 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. 

Queue Reader Agent - The Queue Reader Agent is used with transactional replication with the queued updating alternative. The agent runs on the Distributor and actions modifications made at the Subscriber returned to the Publisher. Unlike the Distribution Agent and the Merge Agent, best one instance of the Queue Reader Agent exists to provider all Publishers and guides for a given distribution database. 

Q8. How Will You Monitor Replication Activity And Performance? What Privilege Do You Need To Use Replication Monitor?

The easiest manner to display replication interest and overall performance is to apply replication monitor, however you can also use the below equipment to monitor replication overall performance:  

T-SQL commands. For extra info refer msdn article -  http://msdn.Microsoft.Com/en-us/library/ms147874.Aspx    

Microsoft SQL Server Management studio. For greater details refer msdn article - http://msdn.Microsoft.Com/en-us/library/ms152763.Aspx    

To display replication, a person ought to be a member of the sysadmin fixed server position at the Distributor or a member of the replmonitor fixed database position inside the distribution database. A machine administrator can add any user to the replmonitor role, which permits that person to view replication activity in Replication Monitor; however, the user can not administer replication.

Q9. What Options Are There To Delete Rows On The Publisher And Not On The Subscriber?

One alternative is to replicate stored technique execution instead of the actual DELETE command.  You can create  exceptional versions of the saved tactics one at the publisher that does the delete and the other on the subscriber that does not do the delete.

Another choice is to not mirror DELETE commands.

Q10. What Are The Different Types Of Sql Server Replication?

Snapshot replication - As the name implies image replication takes a image of the posted gadgets and applies it to a subscriber. Snapshot replication absolutely overwrites the facts on the subscriber on every occasion a photo is carried out. It is satisfactory proper for pretty static facts or if it is appropriate to have records out of sync between replication periods. A subscriber does now not continually need to be connected, so data marked for replication can be implemented the following time the subscriber is hooked up.  An example use of image replication is to replace a listing of items that simplest changes periodically.

Transactional replication - As the call implies, it replicates each transaction for the article being published. To set up transactional replication, a image of the publisher or a backup is taken and implemented to the subscriber to synchronize the information. After that, while 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 dedicated transactions are replicated to make certain facts consistency. Transactional replication is broadly carried out wherein high latency isn't always allowed, including an OLTP gadget for a bank or a inventory buying and selling firm, because you usually need actual-time updates of coins or stocks. 

Merge replication - This is the most complex forms of replication which permits adjustments to occur at each the publisher and subscriber.  As the name implies, modifications are merged to hold data consistency and a uniform set of facts. Just like transactional replication, an preliminary synchronization is accomplished by means of making use of picture. When a transaction occurs at the Publisher or Subscriber, the trade is written to change monitoring tables. The Merge Agent exams 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 all through facts synchronization.  An instance of using merge replication can be a shop with many branches where merchandise can be centrally stored in inventory. As the general stock is decreased it's miles propagated to the other stores to keep the databases synchronized. 

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

Sys.Dm_repl_articles - Contains information about each article being published. It returns statistics from the database being published and returns a row for each object being published in each article.

Sys.Dm_repl_schemas - Contains information approximately each desk and column being posted. It returns statistics from the database being published and returns one row for each column in each object being posted

sys.Dm_repl_traninfo - Contains statistics about each transaction in a transactional replication

Q12. 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 Sub

Yes. Schema changes to tables must be made by using 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 desk and because you cannot drop a posted gadgets, the schema change will fail.

Q13. What Type Of Locking Occurs During The Snapshot Generation?

 Locking relies upon on the sort of replication used:

In image replication, the snapshot agent locks the object during the entire picture generation process.

In transactional replication, locks are obtained to begin with for a totally short time after which launched. Normal operations on a database can continue after that.

In merge replication, no locks are obtained during the photo technology procedure.

Q14. What Is Sql Server Replication?

Replication is subset of SQL Server that may move facts and database items in an automated way from one database to another database. This permits users to work with the same statistics at exceptional locations and changes which can be made are transferred to preserve the databases synchronized.

Q15. Is It Possible To Replicate Data From Sql Server To Oracle?

Yes this will be completed the use of heterogeneous replication. In SQL Server 2000, publishing facts to other databases including DB2 or Oracle became supported; however, publishing information from other databases changed into no longer supported with out custom programming. In SQL Server 2005 and later versions, Oracle databases can be without delay replicated to SQL Server in lots the identical manner as standard SQL Server replication.