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

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

What is the use of XACT_ABORT Parameter?

XACT_ABORT ON ensures that any errors encountered during execution at the Publisher cause the entire execution to be rolled back, avoiding the Distribution Agent failure.

If XACT_ABORT is set to OFF, and an error occurs during execution of the procedure at the Publisher, the same error will occur at the Subscriber, causing the Distribution Agent to fail.

What is a Merge Replication?

Merge replication, like transactional replication, typically starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.

Merge replication allows various sites to work autonomously and later merge updates into a single, uniform result. Because updates are made at more than one node, the same data may have been updated by the Publisher and by more than one Subscriber.

In which situations, Merge Replication is suitable?

• Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers.
• Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.
• Each Subscriber requires a different partition of data.
• Conflicts might occur and, when they do, you need the ability to detect and resolve them.
• The application requires net data change rather than access to intermediate data states.

What should be your best practice for tracking Merge Replication?

To track changes, merge replication must be able to uniquely identify every row in every published table. To accomplish this merge replication adds the column rowguid to every table.

What is a Snapshot Replication?

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

Snapshot replication can be used by itself, but the snapshot process is also commonly used to provide the initial set of data and database objects for transactional and merge publications.

How Snapshot replication is differ than other type of replication and In which situation, it is recommended?

Data is mostly static and does not change often. When it does change, it makes more sense to publish an entirely new copy to Subscribers.

It is acceptable to have copies of data that are out of date for a period of time.

Replicating small volumes of data in which an entire refresh of the data is reasonable.

Snapshot replication is mostly appropriate when you need to distribute a read-only copy of data, but it also provides the option to update data at the Subscriber.

Snapshot replication has a lower continuous overhead on the Publisher than transactional replication, because incremental changes are not tracked. However, if the dataset set being replicated is very large, it will require substantial resources to generate and apply the snapshot. Consider the size of the entire data set and the frequency of changes to the data when evaluating whether to utilize snapshot replication.

Would you please explain basic, how Snapshot works?

Snapshots can be generated and applied either immediately after the subscription is created or according to a schedule set at the time the publication is created. The Snapshot Agent prepares snapshot files containing the schema and data of published tables and database objects, stores the files in the snapshot folder for the Publisher, and records tracking information in the distribution database on the Distributor.

You specify a default snapshot folder when you configure a Distributor, but you can specify an alternate location for a publication instead of or in addition to the default.

What do you require to plan before setup a Snapshot Replication?

• Transferring and storing snapshot files:
• You have the option of storing snapshot files in a location other than or in addition to the default location, which is often located on the Distributor. Alternate locations can be on another server, on a network drive, or on removable media.
• Scheduling snapshots:
• To plan the optimum schedule for running the Snapshot Agent, estimate the length of time it takes the Snapshot Agent to complete the snapshot. Because the snapshot is created using bcp, perform a test bulk copy of your data set and time how long it takes to complete. If your data set is very large, perform the bulk copy on a sample of the data set and extrapolate the lapse time to the entire data set.

Before Transaction Replication, what should be your planning?

• Transaction log space.
• Disk space for the distribution database.
• Primary keys for each table to be published.
• Immediate updating and queued updating.
• Transforming replicated data.
• text and image data types in transactional replication.
• Identity ranges.
• Constraints and NOT FOR REPLICATION.

Before Merge Replication, what should be your planning?

• Timestamp columns.
• Identity ranges.
• Data integrity.
• Primary keys.
• Synchronizing with alternate synchronization partners.
• Row-level tracking and column-level tracking.
• Triggers and business rules.
• text and image data types in merge replication.
• Conflict resolution.
• Occassional maintenance for online/offline applications

Before planning to Replication, What should be your consideration?

• Whether replicated data needs to be updated, and by whom.
• Your data distribution needs regarding consistency, autonomy, and latency.
• The replication environment, including business users, technical infrastructure, network and security, and data characteristics.
• Types of replication and replication options.
• Replication topologies and how they align with the types of replication.

Why you should use a separate disk drive for all databases which are involved in Replication?

This applies to the publication database, the distribution database, and the subscription database. You can decrease the time it takes to write transactions by storing the log files on a disk drive different than the one used to store the database. You can mirror that drive, using  a Redundant Array of Inexpensive Disks (RAID)-1, if you require fault tolerance.

Use RAID 0 or 0+1 (depending on your need for fault tolerance) for other database files. This is a good practice regardless of whether or not replication is being used.

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.

I have transactional replication configured in my production environment with multiple subscribers.  The business team has requested that one of the subscriptions be reinitialized, because they think there is some missing data.

What are the different option to reinitialized subscriptions?

Option 1: Using SQL Server Management Studio (SSMS).

In SSMS go to Replication -> Local Publications -> Locate your publication and expand it.

To do this, right click that subscription and select the ‘reinitialize’ option.

Option 2: Using Replication Monitor

In SSMS go to Replication -> right click on replication and select ‘Launch Replication Monitor’.

On the left pane, under ‘My publishers’, click on the publisher node and expand to get a list of the subscriptions.

In the ‘All Subscriptions’ tab, you need to select the appropriate subscription and click on ‘Reinitialize Subscription’.

How you can solve Error: Could not find stored procedure error and how to recover.

How you can recreate the missing stored procedures?

We can use sp_scriptpublicationcustomprocs system stored procedure which has been available since SQL Server 2000 Service Pack 1.

The only parameter for the sp_scriptpublicationcustomprocs is the publication name and this stored procedure must be executed on the published database.

1	EXEC sp_scriptpublicationcustomprocs @publication='Publication_Name';

The output from this stored procedure will be the creation code of INSERT, UPDATE, and DELETE stored procedures for each article that belongs to the ‘Publication_Name’ publication.

T-SQL script to check status of Replication.