1) What is Replication ?
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
2)Describe in brief
working of Replication.
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.
4) What are
different replication agents and what's their purpose?
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.
Queue 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.
5) Does a specific
recovery model need to be used for a replicated database?
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.
6) What type of
locking occurs during the Snapshot generation?
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.
7) What options are
there to delete rows on the publisher and not on the subscriber?
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.
8) Is it possible to
run multiple publications and different type of publications from the same
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.
9) Data is not being
delivered to Subscribers, what can be the possible reasons?
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.
10) Explain what
stored procedure sp_replcounters is used for?
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
11) How will you
monitor replication latency in transactional replication?
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
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?
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.
13) Is it possible
to replicate data from SQL Server to Oracle?
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
14) How will you
monitor replication activity and performance? What privilege do you need to use
replication monitor?
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.
15) 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 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
16) What is the best
way to update data between SQL Servers?
17)What are the
scenarios you will need multiple databases with schema?
18)How will you plan
your replication?
19)What are
publisher, distributor and subscriber in “Replication”?
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.
20)What is “Push”
and “Pull” subscription?
Pull Subscription :
In a pull subscription, changes to a subscriber
cannot be publicized 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 publicized without any request from subscriber. The
changes can be pushed either periodically or on demand.
21)Can a publication
support push and pull at one time?
22)What are
different models/types of replication?
There are 3 types of replication
1) Snap shot replication
2) Merge replication
3) Trasctinal Replication
23)What is Snapshot
replication?
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
24)What are the
advantages and disadvantages of using Snapshot replication?
25)What type of data
will qualify for “Snapshot replication”?
26)What is the
actual location where the distributor runs?
27)Can you explain
in detail how exactly “Snapshot Replication” works?
28)What is merge
replication?
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.
29)How does merge
replication works?
30)What are
advantages and disadvantages of Merge replication?
31)What is conflict
resolution in Merge replication?
32 )What is a
transactional 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.
33 )Can you explain
in detail how transactional replication works?
34 )What are data
type concerns during replications?
35) What are the
uses of Replication?
a) 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
b) Server to
Client Replication:-
1. Exchanging data with mobile users
2. Retail point of sale (POS) applications
3. Integrating data from multiple sites
36)What are the Agents used for Transactional Replication?
a) Snapshot Agent
b) Log Reader Agent
c) Distribution Agent
37)What are the Agents
used for Merge Replication?
a) Snapshot Agent
b) Merge Agent
38) What is the
Process of Transactional Replication?
Three Agents are doing the Process for Transactional
Replication
No comments:
Post a Comment
Tahnk u for visiting my blog.