There a 4 type of replications: Snapshot replication Transactional replication Peer-to-Peer replication Merge replication
Peer-to-Peer Replication
Column/FK/Constraint changes are replicated but trigger/index changes are not replicated.
Changes are replicated almost instantly.
Table with timestamp column is not supported, no work around.
Identity column needs to be managed carefully. When setup replication, identity column will automatically have the NOT FOR REPLICATION option set to YES. Don’t set the option to NO (even though you can via script after replication setup). During data replication, replication engine is not going to set IDENTITY_INSERT ON so replication will fail as it can’t insert the identity value. Based on your environment, following are possible two workarounds:
scenario1: When all nodes can enter data simutaniously and the ID value order doesn’t matter, then assign different identity range for each nodes will help resolve conflicts. Periodically review the used identity values and readjust accordingly using DBCC CHECKIDENT (’table’, RESEED, new_seed_value)
scenario2: When data won’t be entered simutaniously and you want the ID values to increment in order, instead of assign different identity range for each node, you can setup SQL job to periodically run DBCC CHECKIDENT (’table’, RESEED) so that you won’t receive error that the ID value already in use as replication will replicate the value but not increment the seed value on the target node. Please note, this method WON’T prevent ID value conflict if one node start inserting data before receive updates from all other nodes. This is not a recommended method but might help in some cases.
Update trigger that involves certain data updates needs to be designed properly. If not, you will receive “A conflict of type ‘Update-Update’ was detected …” replication error. To avoid this error without disable the trigger, a dedicated SQL/Windows login to be used by replication. Logic needs to be added into such trigger so that it skip the trigger when the update is done by the replication login.
Merge Replication
Column/FK/Constraint changes are replicated but trigger/index changes are not replicated
Changes are replicated periodically based on schedule.
When setup merge replication, an identity column will be added into tables that doesn’t already have an identity column. Code that does not specify column list will fail on insert.
Prior SQL2022, merge conflict are resolved by the setting of the server priority. From SQL2022, merge conflict are resolved by latest change overwrites others.