This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What should be your general steps to Implementing any type of Replication?
- Configuring Replication: Identify the Publisher, Distributor, and Subscribers in your topology.
- Publishing Data and Database Objects: Create the publication and define the data and database object articles in the publication, and apply any necessary filters to data that will be published.
- Subscribing to Publications: Create push, pull, or anonymous subscriptions to indicate what publications need to be propagated to individual Subscribers and when.
- Generating the Initial Snapshot: Indicate where to save snapshot files, whether they are compressed, and scripts to run before or after applying the initial snapshot.
- Applying the Initial Snapshot: Apply the snapshot automatically by synchronizing the subscription using the Distribution Agent or the Merge Agent.
- Synchronizing Data: Synchronizing data occurs when the Snapshot Agent, Distribution Agent, or Merge Agent runs and updates are propagated between Publisher and Subscribers.
What are your general steps to configure a Replication?
- Identifying a Distributor.
- Creating a distribution database on the Distributor.
- Enabling Publishers that will use the Distributor.
- Enabling publication databases.
- Enabling Subscribers that will receive published data.
What is called a Subscription Process?
A subscription is the request for data or database objects to be published to a specific Subscriber. A Subscriber can have several subscriptions to different publications.
A subscription defines what publication will be replicated, where and when. A subscription can be created either at the Publisher or at the Subscriber.
What is a Push Subscription?
With a push subscription, the Publisher propagates changes to a Subscriber without a request from the Subscriber. Changes can be pushed to Subscribers on demand, continuously, or on a scheduled basis. By default, the Distribution Agent or Merge Agent runs at the Distributor.
Because a Subscriber must explicitly be enabled at the Publisher to receive a push subscription, push subscriptions are known as named subscriptions.
What is a Pull Subscription?
With a pull subscription, the Subscriber requests changes made at the Publisher. Pull subscriptions allow the user to determine when the data changes are synchronized. By default, the Distribution Agent or the Merge Agent runs at the Subscriber.
Because a Subscriber must explicitly be enabled at the Publisher to receive a push subscription, pull subscriptions are known as named subscriptions.
What is an Anonymous Subscription?
An anonymous subscription is a type of pull subscription. Detailed information about the subscription and the Subscriber is not stored at the Publisher when using an anonymous subscription.
When we should use Push Subscription?
- Data will typically be synchronized on demand or on a frequently recurring schedule.
- Publications require near real-time movement of data without polling.
- The higher processor overhead at a Publisher using a local Distributor does not affect performance.
- You need easier administration from a centralized location (the Distributor).
When we should use Pulll Subscription?
- Administration of the subscription will take place at the Subscriber.
- The publication has a large number of Subscribers, and when it would be too resource-intensive to run all the agents at one site or all at the Distributor.
- Subscribers are autonomous, disconnected, and/or mobile. Subscribers will determine when they will connect to the Publisher/Distributor and synchronize changes.
When we should use Anonymous Subscriptions?
- Applications have a very large number of Subscribers.
- You do not want the overhead of maintaining extra information at the Publisher or Distributor.
- If Subscribers use the Internet to access publications.
Can we apply filters on a table to be published?
Yes, we can apply different filters on a table which is going to publish.
Different types of filter likes, Row filters, Column filter, Dynamic filter, Static filter, Join filter.
Can you modify the data of Subscriber?
Yes, With snapshot replication or transactional replication, replicated data is by default read only; however, you have the ability to modify replicated data at the Subscriber by using updatable subscriptions.
What do you know about Immediate Updating?
Immediate updating allows snapshot replication and transactional replication Subscribers to update the replicated data at the Subscriber and send those changes back to the Publisher and to other Subscribers.
What do you know about Queued Updating?
Queued updating allows snapshot replication and transactional replication Subscribers to modify published data without requiring an active network connection to the Publisher.
When you create a publication with the queued updating option enabled and a Subscriber performs INSERT, UPDATE, or DELETE statements on published data, the changes are stored in a queue. The queued transactions are applied asynchronously at the Publisher when network connectivity is restored.
Can we use Data Transformation Service (DTS Package) for publishing a data?
Yes, we can create a DTS package for replication either using the replication wizards or programmatically, such as using Microsoft Visual Basic.
What do you know about Alternate Synchronizing Partner?
Subscribers to merge publications can synchronize with servers other than the Publisher at which the subscription originated. Synchronizing with alternate partners provides the ability for a Subscriber to synchronize data even if the primary Publisher is unavailable.
This feature is also useful when mobile Subscribers have access to a faster or more reliable network connection with an alternate synchronization partner.
What is error 20598 for DELETEs?
- A error 20598 is due to a missing row on the subscriber and there are two scenarios that can cause this error:
- An UPDATE command cannot be replicated, because there is no record that matches the update condition on the subscriber.
- A DELETE command cannot be replicated, because there is no record that matches the delete condition on the subscriber.
How to fix error 20598 for DELETEs?
- On the subscriber, insert a dummy record with the exact primary key value of the deleted row. This PK value can be retrieved using sp_browsereplcmds.
- The other option is to modify the sp_MSDel_<schema><table_name> to comment out the error emitting codes when @@rowcount = 0.
