SQL Server 2008 Replication

Here are some notes on “SQL Server 2008 Replication Overview” 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.

Replication

Metaphor

  • Publisher-Subscriber – like a magazine
  • Articles are tables, views, stored procedures
  • Publication is a set of articles
  • Publisher, Distributor and Subscriber

Replication scenarios

  • Scenario: Live Reporting Server - usually a read-only copy
  • Scenario: Data Locality, HA, Rolling upgrades – take individual server offline, keep running
  • Scenario: Point of sale application – download reference data, upload transactions, conflicts are rare, SQL CE
  • Scenario: Sales force automation – offline user, download reference data, upload transactions

Transactional Replication

  • Oldest type, started as read-only. 
  • Over time, improved scalability and availability
  • Later introduced immediately updating subscribers, bi-directional updates
  • Later introduced queued updating subscribers (should transition to Broker now)
  • Server to server, high throughput, based on the log
  • Scenarios: Reporting, reporting consolidation, read scale out, offload batch processing

Peer-to-Peer Replication

  • A form of transactional replication, bi-directional
  • Scenarios: HA, Scale Out, Upgrade
  • SQL Server 2008 added conflict detection system
  • SQL Server 2008 can add nodes on the fly

Merge Replication

  • Mobile Applications, Distributed Server apps with conflicts
  • Scenarios: Exchanging data with mobile users, consumer POS apps, Integration of data from multiple sites
  • Topologies: Central with nodes, Distributed with multiple levels (tree-like)
  • Changes are tracked by table, not as transactions in a set of tables
  • Consider performance and scale – based on triggers on tables
  • Merge agents does most of the work – needs to figure out which rows needs replicating
  • Conflict resolution

Snapshot Replication

  • Copy the entire data set every time
  • Used to create the initial data set for transactional or merge replication
  • Scenario: Complete refreshes of data, volume of data is low compared to transactions
  • Other ways to create initial set: backup/restore, transported via other means

Sync Services (ADO.NET)

  • Client-centric technology, part of Microsoft Sync Framework
  • Addressing common scenarios in mobile devices
  • Row-level conflict detection for offline updates, simple filtering, extensible design
  • SQL Server 2005 – Finds changes with Triggers
  • SQL Server 2008 – Finds changes with Change Tracking
  • It does all the plumbing work for offline applications
  • https://msdn.microsoft.com/en-us/library/cc281959.aspx

Sync Services – Demo

  • Create VB application with a form
  • Add sync object, connect to SDF database (SQL CE), configure tables for offline use
  • Option to use SQL Server Change Tracking
  • Creates a data source based on the sync object
  • Dragged data source to the form to create the UI
  • Add button to actually perform the sync operation (paste code example from sync object)
  • Takes care of the work to connect, sync, keep track of everything
  • Can set sync direction (server always win, client always win, bi-direction with conflict resolution)
  • Framework is pretty straightforward and ties nicely with SQL Server
  • Careful – SQL CE data types are not the same as full SQL Server

Heterogeneous Publishing

  • Oracle publishing introduced in SQL Server 2005
  • Previous support for DLL-based non-SQL Server publishing removed
  • RMO classes are quite generic

Agents

  • SQL Server Agent: Not a replication agent, but fires the replication jobs.
  • Agents: Can run via RMO or command line (lets you specify custom parameters)
  • Snapshot Agent: All types. Prepares schema and initial data. Stores snapshots, sync information.
  • Log Reader Agent: Transactional. Copies transactions into distribution database. One per published DB.
  • Distribution Agent: Transactional/Snapshot. Applies initial replica, transactions to the subscribers.
  • Merge Agent: Merge. One agent per subscriptions. Updates both (upload first by default, option for parallel)
  • Push/Push: Running Distribution/Mege Agent on distributor (Push) or Subscriber (Pull)
  • Anonymous vs. Named Subscriptions: Named keeps data until subscriber receives it, anonymous has a time limit

Other agents

  • Queue Reader Agent: Transaction with Queued update. Runs on distributor. Deprecated. Use broker instead.
  • Clean up Agents
  • Reinitialize subscriptions with Validation Failures. Detects failures, marks them for re-initialization.
  • Replication agents checkup Agent. Agent to check the agents.
  • Replication Monitor Refresher.
  • Replication Monitor: Configurable replication monitor grids, agent tab to view all agents and jobs

Agent profiles

Security Model

  • Last updated on