Interview Questions.

Q1. Explain What Stored Procedure Sp_replcounters Is Used For?

Sp_replcounters is a machine saved method that returns facts approximately the traction rate, latency, and first and final log series variety (LSN) for every e-book on a server. This is run at the publishing server. Running this stored method on a server this is performing as the distributor or subscribing to guides from some other server will no longer return any information.

Q2. What Are The Agents Used For Merge Replication?

Snapshot Agent

Merge Agent

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

One option is to duplicate stored technique execution in place of the real DELETE command.  You can create  different versions of the stored strategies one on the writer that does the delete and the other at the subscriber that doesn't do the delete.

Another alternative is to now not reflect DELETE instructions.

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

Replication isn't dependent on any unique recuperation version. A database can participate in replication whether or not it's far in easy, bulk-logged, or full. However how statistics is tracked for replication depends on the kind of replication used.

Q5. How Will You Monitor Replication Latency In Traction Replication?

Tracer tokens were brought with SQL Server 2005 tractional replication as a way to reveal the latency of delivering tractions 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 the usage of tracer tokens.

Q6. Describe In Brief Working Of Replication?

At first records and item is synchronized between writer and subscribers. The picture is created at the publisher and trmitted to subscribers. All the subsequent adjustments at the publisher are stored in distribution database. Subscriber gets the information both the usage of push or pull mechanism as configured from distribution database.

Q7. What Are The Uses Of Replication?

Server to Server Replication:-

Improving scalability and availability

Data warehousing and reporting

Integrating facts from a couple of web sites

Integrating heterogeneous facts

Offloading batch processing

Sverer to Client Replication:-

Exchanging facts with cellular users

Retail factor of sale (POS) packages

Integrating data from a couple of sites

Q8. What Are Different Models/types Of Replication?

There are 3 forms of replication:

Snap shot replication

Merge replication

Trasctinal Replication

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

Yes this may be done and there are not any restrictions at the number or types of publications that can use the same distribution database. One issue to notice even though is that all courses from a Publisher have to use the equal Distributor and distribution database.

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

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

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

Sys.Dm_repl_traninfo - Contains statistics about every traction in a tractional replication.

Q11. What Is Replication?

Replication is the method of dispensing records from one database to another on the identical server or servers connected thru LAN or the net. Replication is used to synchronize records between databases that can be at far off area.

Q12. What Are The Agents Used For Tractional Replication?

Snapshot Agent

Log Reader Agent

Distribution Agent

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

The easiest way to reveal replication hobby and overall performance is to use replication display. To reveal replication, a consumer need to 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 gadget administrator can add any user to the replmonitor position, which allows that user to view replication activity in Replication Monitor; but, the user can not administer replication.

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

There are a number of viable reasons for information now not being brought to Subscribers:

The desk is filtered, and there are no adjustments to supply to a given Subscriber.

One or extra dealers aren't jogging or are failing with an errors.

Data is deleted by means of a trigger, or a trigger consists of a ROLLBACK statement.

A tractional subscription changed into initialized with out a photograph, and adjustments have happened on the Publisher since the guide became created.

Replication of stored method execution for a tractional booklet produces unique effects on the Subscriber.

The INSERT stored system used by a tractional article consists of a condition that isn't always met.

Data is deleted with the aid of a user, a replication script, or some other utility.

Q15. What Is Merge Replication?

This permits both writer and subscriber to paintings independently, on-line or offline and merges the adjustments later.

Here modifications are tune on each publisher and subscriber and then merged.

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

Locking depends at the sort of replication used:

In picture replication, the snapshot agent locks the item during the entire photo generation technique.

In tractional replication, locks are received to start with for a completely brief time after which launched. Normal operations on a database can retain after that.

In merge replication, no locks are acquired for the duration of the image era technique.

Q17. What Is A Traction Replication?

It is used while adjustments are common. The replication agent video display units the modifications at the writer and distributes the modifications to the subscribers. Tractional Replication is required where up to date facts is required.

Q18. What Is Snapshot Replication?

Snapshot of complete information is copied from writer to the subscriber's database on regular c program languageperiod.

This form of replication is used for replicating data that doesn’t alternate frequently.

It is used in which the supply can manage without of date statistics.

This replication is considered whilst amount of statistics to be replicated is small.

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

Yes this will be performed the usage of heterogeneous replication. In SQL Server 2000, publishing records to other databases inclusive of DB2 or Oracle become supported; but, publishing records from different databases became not supported with out custom programming. In SQL Server 2005 and later versions, Oracle databases may be at once replicated to SQL Server in tons the same way as widespread SQL Server replication.  For more data, please examine tip: Introduction to SQL Server Heterogeneous Replication.

Q20. 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 have to be made by way of using Tract-SQL or SQL Server Management Objects (SMO). When schema adjustments are made in SQL Server Management Studio, Management Studio tries to drop and re-create the table and because you cannot drop a published items, the schema exchange will fail.

Q21. 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 different objects, shops the photo files, and records information approximately synchronization in the distribution database. The Snapshot Agent runs at the Distributor.

Log Reader Agent:-

The Log Reader Agent is used with tractional replication. It actions tractions marked for replication from the traction log on the Publisher to the distribution database. Each database.

Published using tractional replication has its personal Log Reader Agent that runs at the Distributor and connects to the Publisher (the Distributor can be on the equal pc because the Publisher).

Distribution Agent:- The Distribution Agent is used with photograph replication and tractional replication. It applies the preliminary picture to the Subscriber and movements tractions 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 preliminary image to the Subscriber and movements and reconciles incremental statistics modifications that arise. Each merge subscription has its own Merge Agent that connects to each the Publisher and the Subscriber and updates each. The Merge Agent runs at both the Distributor for push subscriptions or the Subscriber for pull subscriptions.

Queues Reader Agent:- The Queue Reader Agent is used with tractional replication with the queued updating choice. The agent runs at the Distributor and moves adjustments made on the Subscriber lower back to the Publisher. Unlike the Distribution Agent and the Merge Agent, handiest one instance of the Queue Reader Agent exists to provider all Publishers and courses for a given distribution database.