SQL Server: Database Replication Interview Questions and Answers (Day-4)

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Why we require to manage IDENTITY column and Why data cannot replicate because of IDENTITY column.

In replication topologies, where a publication contains an identity column and new rows can be inserted at Subscribers, additional configuration may be necessary to ensure that no duplicate identity values or constraint violations occur.

If successful, two different rows with a same identity value will exist on each replica. As a result,  each published article will contain multiple rows with the same identity values. If the identity column was defined as a primary key, or with a unique constraint, the data will not replicate.

Can we skip IDENTITY in the Replication?

Yes, we can use NOT FOR REPLICATION. The “Not For Replication” setting for identity columns allows replication to pass the ID value from the publisher to the subscriber without the subscriber creating a new ID.  Since identity columns will always generate a new number when records are inserted, this setting allows you to ensure the values on both your publisher and subscribers stay in synch.

How we should manage the IDENTITY column in Replicaton?

The simplest way to use automatic identity range handling, we must first enable the feature at the time the publication is created, assign a set of initial Publisher and Subscriber identity range values, and then assign a threshold value that determines when a new identity range is created.

Another one is, Using a primary key other than the identity column (for example, a composite key or a rowguid column), if an identity column is not necessary. This strategy eliminates the overhead of managing identity columns on the replicated data.

How to enable Replication Monior?

In Enterprise Manager, right-click on a SQL Server, and click Properties.

On the Replication tab, select Show Replication Monitor Group. Optionally, you can also select to add this server as a Distributor in the Replication Monitor Group.

Why is primary key needed in Transactional replication?

The reason is in the subscriber, rows are updated/deleted one-by-one using primary key.

Which all database objects can be included in replication?

• Tables
• Partitioned Tables
• Stored Procedures – Definition (Transact-SQL and CLR)
• Stored Procedures – Execution (Transact-SQL and CLR)
• Views
• Indexed Views
• Indexed Views as Tables
• User-Defined Types (CLR)
• User-Defined Functions (Transact-SQL and CLR)
• Alias Data Types
• Full text indexes
• Schema Objects

Can we rename a database used in Publication or subscription

No: we would need to drop the publications, rename the database and re-configure replication all over again.

Have you tried to replicate Database User Login information?

No: directly it is not possible, We require to create a DTS\SSIS package to transfer logins and passwords from a Publisher to one or more Subscribers.

Are tables locked during snapshot generation?

• The length of time that the locks are taken depends on the type of replication used:
• For merge publications, the Snapshot Agent does not take any locks.
• For transactional publications, by default the Snapshot Agent takes locks only during the initial phase of snapshot generation.
• For snapshot publications the Snapshot Agent takes locks during the entire snapshot generation process.

How do I manage constraints on published tables?

The NOT FOR REPLICATION option is specified by default for foreign key constraints and check constraints, it skips by the Replication Agent operations.

Can multiple publications use the same distribution database?

Yes. There are no restrictions on the number or types of publications that can use the same distribution database.

If you have multiple publications, you can configure multiple distribution databases at the Distributor to ensure that the data flowing through each distribution database is from a single publication.

How do I add or change indexes on publication and subscription databases?

Indexes can be added at the Publisher or Subscribers with no special considerations for replication.

How do I move or rename files for databases involved in replication?

A moving or renaming database files required detaching and reattaching the database. Because a replicated database cannot be detached, replication had to be removed from these databases first.

Data is not being delivered to Subscribers, what can be the possible reasons?

• 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.

T-SQL script to check status of Replication Distribution Agents.

T-SQL script to find Replication errors.