SQL Server 2008 Merge Replication

  • Article

Here are some notes on “SQL Server 2008 Merge Replication” I took while attending an advanced class on SQL Server taught by Greg Low (from https://sqlblog.com/blogs/greg_low/ and https://www.sqldownunder.com/).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

Merge Replication

Changes to Database

  • Snapshot agent will make changes to table
  • Needs a row uniquely identified (ROWGUIDCOL) – Does not have to be called rowguid
  • Wizard will add rowguid column if none there with ROWGUIDCOL property
  • Triggers added: Insert/Update/Delete triggers: MSmerge_xxx_GUID
  • Multiple triggers – you probably want the merge trigger to run last
  • SPs added: insert/update/delete
  • Views: created to manage insert/update/delete/filtering
  • Conflict tables: dbo.MSmerge_conflict_<pub>_<article>
  • Conflict resolvers come with it, you can create your own
  • See https://msdn.microsoft.com/en-us/library/ms151769.aspx

Agents

  • Agents: Snapshot, Merge (merges changes, detects and resolves conflicts)
  • SQL Server Agent: Needs account , many use SQL Server authentication
  • One initially sets publication, then others subscribes
  • Very little ends up in distribution database

Snapshot

  • Start: Snapshot, as usual, needs a location. Parameter (for filter) included in path.

  • Files in snapshot - schema, constraints and indexes, triggers, system, conflict

  • 20 system tables, 8 system views (MSMerge prefix, optimize views)

Filtering

  • Consider redesigning for it
  • Static filters are efficient
  • Join filter – join tables at top level, avoid too much depth. Consider denormalization to avoid joins.
  • Parameterized if necessary – Less efficient, avoid if possible
  • Sp_MSsetupbelongs – Key SP that determines which rows needs to be processed
  • You can use SQL Profiler to trace that SP, filter for the SP and look at statements
  • HOST_NAME() commonly used for filtering
  • See https://msdn.microsoft.com/en-us/library/ms151226.aspx

NOT FOR REPLICATION

Changes

  • Incremental changes: Data, Schema, tracked with triggers
  • Retention – Cleanup of metadata is importante. Republishers cannot exceed original publisher retention.
  • DDL – ALTER for TABLE/VIEW/PROCEDURE/FUNCTION/TRIGGER (not DDL triggers)
  • DDL - Don’t do for non-SQL subscribers
  • DDL – Compatibility level must be 90 or higher, careful with data types
  • Schema changes can lead to reinitialization
  • Identity columns – Ideally NOT FOR REPLICATION. Use ranges as required.
  • Identity columns – Automatic management in SQL Server 2005+ - ranges of numbers auto assigned
  • Timestamp – a.k.a. RowVersion. Like a serial number for the row. Varbinary, changes on INSERT/UPDATE.
  • Timestamp – Ideallly NOT FOR REPLICATION to avoid change on destination, endless loop.

Conflicts

  • Detection of lineage in MSmerge_contents table
  • Row level or column level. You need to figure out what you really need
  • View using sp_showreplicainfo
  • Merge agent launches conflict resolver if conflict is detected
  • Last-in wins commonly used. Can use priority. You can write your own. Should you?
  • Extra care with Identity columns, constraint, type remapping, LOB
  • Calculated columns – If not deterministic, propaged as non-calculate
  • Consider disabling schema replication – it’s in the wizard, also option in script
  • See https://msdn.microsoft.com/en-us/library/ms151749.aspx

SET FMTONLY

  • Discussion on SET FMTONLY when SP has multiple code paths
  • “SELECT … WHERE @key=key” does not handle NULLs
  • You can fix with “SELECT … WHERE (@key=key) OR (@key IS NULL AND KEY IS NULL)”
  • Or you could use “IF @key IS NULL (SELECT … WHERE key IS NULL) ELSE (SELECT … WHERE @key=key)”
  • See https://msdn.microsoft.com/en-us/library/ms173839.aspx

Merge - Demo

  • Wizard – Asks what type of subscribers (2000/2005/2005Mob/2008) – changes how the data is generated
  • Article properties – very similar to transactional – make sure you select a single article to get all details
  • Identity – Automatic management?, Publisher Range Size, Subscriber Range Size, Threshold Percentage
  • Filtering – Basically writing a WHERE clause
  • Immediately or Schedule – 14 days is default
  • Create script, look at the script
  • Sp_addmergepublication, Sp_addpublication_snaphot, Sp_appmergearticle

Other

  • Periodic data validation – sp_validatemergepublication - row count or checksum – publication or subscription
  • See https://msdn.microsoft.com/en-us/library/ms152758.aspx
  • Backup and restore – What and where to backup
  • Restoring – publisher, subscriber (you can also re-initalize if in retention period)
  • Threshold and Alters – Imminent expiration, latency, sync time, not processing enough rows
  • Cleanup metadata – quite a lot of MSmerge metadata - system tables, triggers, columns
  • Expired subscription – cannot upload changes. Recreate (don’t apply snapshot) plus dummy update (sp_mergedummyupdate).
  • See https://msdn.microsoft.com/en-us/library/ms146905.aspx
  • Server names – be careful when you change

Performance

  • Database design is #1 issue
  • Don’t merge everything, just what you need
  • Distributor – retention period
  • Publication – watch your options, filter design
  • Maintenance – plan it, don’t make any assumptions
  • Avoid long retention periods – tables can grow too big
  • Avoid depth in join filters
  • SQL Profiler – optimize statements – consider indexes
  • See https://msdn.microsoft.com/en-us/library/ms152770.aspx