{"id":2163,"date":"2020-02-06T16:53:22","date_gmt":"2020-02-06T21:53:22","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/jonathan\/?p=2163"},"modified":"2020-02-06T16:53:22","modified_gmt":"2020-02-06T21:53:22","slug":"understanding-sql-server-replication","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/understanding-sql-server-replication\/","title":{"rendered":"Understanding SQL Server Replication"},"content":{"rendered":"

This blog post is a part of a series on SQL Server Transactional Replication.\u00a0 If you want to find the other posts in the series, check out the main page for the series here<\/a>.<\/em><\/p>\n

SQL Server Replication is one of the components of SQL Server that often has a bad reputation for being difficult to manage and prone to errors and problems.\u00a0 However, when configured and managed properly, replication often just works and requires very little effort to maintain.\u00a0 Replication is really easy to use and configure once you understand the basics behind how it works and the various components involved, and it’s been a part of SQL Server for over 20 years with very minimal changes and many other features piggy back on the foundation provided by replication.<\/p>\n

There are at least three databases that participate in any replicated topology:<\/p>\n

    \n
  1. Publisher – the main copy of the database that reads and writes can be issued against<\/li>\n
  2. Distributor – the central database behind replication where log operations are written from the publisher and read for distribution to subscribers<\/li>\n
  3. Subscriber – the database that receives changes made on the articles configured for replication on the publisher<\/li>\n<\/ol>\n

    The terminology for replication is based on a publishing industry terminology for print media like a magazine or newspaper.<\/p>\n

      \n
    1. Publisher can produce one or many publications<\/li>\n
    2. Publications contain articles<\/li>\n
    3. Articles represent a specific object being published<\/li>\n
    4. Subscribers subscribe to a publication to receive the changes to all of the articles within that publication<\/li>\n<\/ol>\n

      There are a few more advanced features of replication that go outside of these basics, but for a majority of use cases for replication, just understanding these simple terms is all that is necessary to plan out and build a configuration that meets a majority of business requirements.<\/p>\n

      The primary functionality provided by replication is handled by three specific agents, that run on the different servers to perform specific tasks:<\/p>\n

        \n
      1. The Log Reader Agent reads the transaction log records from the publisher databases transaction log that are marked for replication and writes those commands into the distribution database.\u00a0 The great thing about replicated databases is that they do not require FULL recovery model to be able to publish the changes.\u00a0 The changes are marked for replication and until the log reader agent has successfully processed those commands to the distribution database they have to remain in the transaction log.<\/li>\n
      2. The Snapshot Agent generates a snapshot of the articles as an initialization point for replicating the articles to new subscribers.\u00a0 This is accomplished through BCP operations that write the contents of the tables out to files on disk that can then be used to create and load the data on new subscribers to initialize the data for further application of commands.<\/li>\n
      3. The Distribution Agent applies any snapshots required to initialize the subscriber and then executes commands stored in the distribution database to apply changes to the subscriber database to replicate the data changes happening at the publisher<\/li>\n<\/ol>\n

        Since replication is based off of reading the transaction log of the publisher, it is an entirely asynchronous process and has minimal impact to transactional throughput of the source system.\u00a0 However, this also means that there is latency between when a change is made, and when that change becomes available on a subscriber, but this is typically only a few seconds end-to-end.<\/p>\n","protected":false},"excerpt":{"rendered":"

        This blog post is a part of a series on SQL Server Transactional Replication.\u00a0 If you want to find the other posts in the series, check out the main page for the series here. SQL Server Replication is one of the components of SQL Server that often has a bad reputation for being difficult to […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[34],"tags":[],"class_list":["post-2163","post","type-post","status-publish","format-standard","hentry","category-replication"],"yoast_head":"\nUnderstanding SQL Server Replication - Jonathan Kehayias<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/understanding-sql-server-replication\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Understanding SQL Server Replication - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"This blog post is a part of a series on SQL Server Transactional Replication.\u00a0 If you want to find the other posts in the series, check out the main page for the series here. SQL Server Replication is one of the components of SQL Server that often has a bad reputation for being difficult to […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/understanding-sql-server-replication\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2020-02-06T21:53:22+00:00\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/understanding-sql-server-replication\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/understanding-sql-server-replication\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"Understanding SQL Server Replication\",\"datePublished\":\"2020-02-06T21:53:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/understanding-sql-server-replication\\\/\"},\"wordCount\":560,\"commentCount\":1,\"articleSection\":[\"Replication\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/understanding-sql-server-replication\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/understanding-sql-server-replication\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/understanding-sql-server-replication\\\/\",\"name\":\"Understanding SQL Server Replication - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"datePublished\":\"2020-02-06T21:53:22+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/understanding-sql-server-replication\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/understanding-sql-server-replication\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/understanding-sql-server-replication\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Replication\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/replication\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Understanding SQL Server Replication\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Understanding SQL Server Replication - Jonathan Kehayias","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/understanding-sql-server-replication\/","og_locale":"en_US","og_type":"article","og_title":"Understanding SQL Server Replication - Jonathan Kehayias","og_description":"This blog post is a part of a series on SQL Server Transactional Replication.\u00a0 If you want to find the other posts in the series, check out the main page for the series here. SQL Server Replication is one of the components of SQL Server that often has a bad reputation for being difficult to […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/understanding-sql-server-replication\/","og_site_name":"Jonathan Kehayias","article_published_time":"2020-02-06T21:53:22+00:00","author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/understanding-sql-server-replication\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/understanding-sql-server-replication\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"Understanding SQL Server Replication","datePublished":"2020-02-06T21:53:22+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/understanding-sql-server-replication\/"},"wordCount":560,"commentCount":1,"articleSection":["Replication"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/understanding-sql-server-replication\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/understanding-sql-server-replication\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/understanding-sql-server-replication\/","name":"Understanding SQL Server Replication - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"datePublished":"2020-02-06T21:53:22+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/understanding-sql-server-replication\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/understanding-sql-server-replication\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/understanding-sql-server-replication\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Replication","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/replication\/"},{"@type":"ListItem","position":3,"name":"Understanding SQL Server Replication"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/2163","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=2163"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/2163\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=2163"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=2163"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=2163"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}