REPLICATION

1.replication prerequisites:

1. Verify connectivity between publisher, distributor and subscriber. Ifyou are unable to access then have your network team to open firewall ruleso that these servers can communicate.

2. You need to be sysadmin in order to setup replication.

3. Primary Key for each table. That should be fine anyway as it alreadyhas replication setup.

4. Allocate enough space for Transaction log

5. Do not use explicit transactions in trigger at the subscriber if you

currently have because that can raise error sometimes.

2. Replication Agents Behaviour?

Replication Agents

The following are the replication agents and let us discuss each one of those in detail.

* Snapshot Agent – It is used for creating and propagating the snapshots used in snapshot replication. Each Published database has its own Snapshot Agent that runs on the distributor and connects to the publisher and takes a snapshot of the objects. It then updates the information in the distribution database. Snapshot Agents are used with all types of replication.

* Distribution Agent – It applies the data from snapshot replication or transactions from transactional replication to subscribers. It can run on the distributor or on subscribers. It runs on the distributor for push subscriptions and on subscriber for pull subscriptions. This agent is not used with merge replication.

* Merge Agent – The Merge agent is used in Merge replication and it merges the incremental changes that have occurred since the last reconciliation. In Merge replication there is no distribution or snapshot agent involved instead the Merge agent communicates with both the publisher and distributor. There is one Merge agent for each merge subscription.

* Log Reader – The Log reader is used for transactional replication. It moves the information from transaction log in the publisher to the distribution database. Each database that is using transaction replication has its own Log reader agent on the publisher.

* Queue Reader Agent – This is used in Transaction replication with queued updating option. It takes the changes that has occurred in the subscriber and applies them to the publisher. There is only one Queue reader agent per database.

2. Is it possible to truncate the table which is the part of replication ?

No it is not possible to truncate the table which is the part of replication. Because when you truncate the table replication broke and you are in trouble. So instead of truncating the table you can delete all the data. Truncate table command always reseed the identity column so in this case you can use reseed identity column.

3 Why can't I run TRUNCATE TABLE on a published table?

When we TRUNCATE any table it does not log any operation and it does not fire triggers. It is not permitted because replication cannot track the changes caused by the operation.
Transactional replication tracks changes through the transaction logged; merge replication tracks changes through triggers on published tables.

4 What is the difference between Push and Pull Subscription in Replication?

A Push - A push subscription pushes data from publisher to the subscriber. Changes can be pushed to subscribers on every transctions or on a scheduled basis.

Pull - A pull subscription requests changes from the Publisher. This allows the subscriber to pull data as needed. This is useful for disconnected machines such as notebook computers that are not always connected and when they connect they can pull the data.

5 What is SQL Server replication?

A Replication is subset of SQL Server that can move data and database objects from one database to another database in an automated way.

This allows users to work with the same data at different locations and changes that are made are transferred to keep the databases synchronized.

6. Types of replications?

A:

7. Replication Configuration?

A:

8. What is the difference between the Transactional Replication and the Snapshot Replication?

Both the Transactional replication and Snapshot replication start the initial synchronization by creating a full copy of the publication database articles and apply this copy to the subscription database.

In the Transaction replication, all changes that occurred at the Publisher side after taking the snapshot will be copied and applied to the Subscriber side continuously.

This is not the case with the Snapshot replication, where the next synchronization will occur when a new snapshot is taken from the Publisher side to be applied to the Subscriber again.

9. What is special with the Merge Replication?

In the Merge replication, the Subscriber will download a copy of the Publisher database data and objects at first connect. When the Subscriber connects to the network again, it will upload all changes to the Publisher database then download all changes from the Publisher again, that are performed by all Subscribers, to keep the data synchronized and consistent.

10. What is the difference between the Peer to Peer replication and the Bidirectional replication?

Both Peer to Peer and Bidirectional replications are special types of Transactional replication.

In the Peer to Peer replication, the Publisher distributes the changes to more than one Subscriber at the same time.

But in the Bidirectional replication, the two servers can exchange data between each other.

11. What is common between all replication types?

The initial synchronization in all SQL replication types starts by taking a snapshot from the Publisher database then copy and apply it to the Subscriber database.

12. List the four SQL Replication agents and what is the purpose of each one?

• Replication Snapshot Agent, responsible for generating a copy of the Publisher database that will be used for the initial synchronization in all SQL replication types
• Log Reader Agent, responsible for monitoring the changes of the Publisher databases and copying the transactions from the transaction log into the distribution database
• Distribution Agent, responsible for applying the initial snapshot and the continuous data changes to the Subscriber database
• Merge Agent, responsible for synchronizing the changes from the Publisher and the Subscriber in both ways

13. What permissions required for the account that is used to create the replication site and run the replication agents?

• The account used to enable the Distributor, Publisher and Subscriber should be a member of the sysadmin fixed server role
• The account used to create the publication should be a member of the db_owner on the publication database
• The account used to create the subscription should be a member of the db_owner on both the publication and subscription databases

14. What is the difference between the Push and Pull subscriptions?

• Push subscription: All replication agents will run at the Distributor side, providing centralized administration for the replication site
• Pull subscription: Each agent will run at its own Subscriber, reducing the overhead caused by the agents’ operations, where this overhead will be distributed over the subscribers

15. How could we fix a replication issue when trying to update a record that is deleted from the subscription database mistakenly?

By inserting this record again to the subscription database or ignore this consistency issue, based on the project requirements, by configuring the subscription agent profile to continue synchronizing if a data consistency issue is detected.

16. What does “Uninitialized Subscription” error means?

This indicates that the current subscription is pending to be initialized and provided a new initial synchronization snapshot or the subscription is expired due to synchronization failure for multiple days.

17. How could we troubleshoot that the user configured to write on the subscription database does not have permission on the subscription database?

The records will be read from the Publisher to the Distributor with no issue, but it will be stuck while trying to write it to the Subscriber. Where we will see that there is a subscription error message from the Replication Monitor, and the error message can be checked from the Agent Activity Monitor under the Log Reader Agent job history.

We can fix this issue by providing the db_owner permission for the account that is configured for the Subscriber or use an authorized account.

18. Can we rename a Subscriber database? If yes, describe the steps.

Yes, we can rename a subscriber database by running an ALTER statement, but we should follow some additional steps post renaming the subscriber database.

The command below can be used to rename a subscriber database.

USE master;

GO

ALTER DATABASE DBNAME Modify Name = DBNAME_NEW;

Once you rename the subscriber database you might see errors like “The process could not access the database” in the replication monitor in the 'Distributor to Subscriber History'. To fix this error you need to create another subscription for this renamed database and drop the older subscription.

19. Can we configure SQL Server Replication for a database that is running in SIMPLE recovery model?

Yes, you can configure Replication for any database irrespective of their recovery model. The recovery model is not a prerequisite to configure replication for a database.

20. How can we add an article to an existing SQL Server publication?

Follow the below processes to add an article to existing publication.

Launch Publication Properties then choose your Publication in which you want to add the identified article. Uncheck the “Show only objects”. Now you will be able to see all database objects from the publication database. Here, you can select the check box next to the object that you want to add in to existing publication. Then click OK to proceed. Similarly, you can uncheck the box if you want to remove any article from a publication.