{"id":479,"date":"2012-08-22T06:50:44","date_gmt":"2012-08-22T06:50:44","guid":{"rendered":"\/blogs\/joe\/post\/Transactional-Replication-Publications-and-Availability-Groups.aspx"},"modified":"2014-01-18T12:09:16","modified_gmt":"2014-01-18T20:09:16","slug":"transactional-replication-publications-and-availability-groups","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/transactional-replication-publications-and-availability-groups\/","title":{"rendered":"Transactional Replication Publications and Availability Groups"},"content":{"rendered":"<p>Books Online documents a few scenarios regarding Replication and Availability Group interoperability.\u00a0 Today I tested out the process detailed here:<\/p>\n<blockquote><p><a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/hh710046\" target=\"_blank\">Configure Replication for AlwaysOn Availability Groups (SQL Server)<\/a><\/p><\/blockquote>\n<p>It worked as advertised and I tested this on a five replica AG topology with three synchronous replicas (including the primary) and two asynchronous replicas.\u00a0 I won\u2019t rehash the BOL steps \u2013 but I did want to mention a few observations about the process:<\/p>\n<ul>\n<li>One of my AG replicas was also the same SQL Server instance as my subscription database (non-AG database), so I skipped the sys.sp_addlinkedserver step for that particular SQL Server instance.\u00a0 Collocation of the primary replica and subscriber worked fine.<\/li>\n<li>While it is possible to make one of your participating replica SQL Server instances the distributor, it doesn\u2019t make sense to do so from an HA\/DR perspective.\u00a0 But if your distributor is indeed remote and not collocated with the AG replicas, think about FCIs for providing HA.<\/li>\n<li>The publications show up in SQL Server Management Studio under the Replication\\Local Publications folder.\u00a0 Hovering over the publication from a secondary replica will still show a yellow (tooltips-like) dialog box showing the original SQL Server instance where you created the publication \u2013 even if that replica is currently a secondary.<\/li>\n<li>The New Publication Wizard doesn\u2019t stop you from creating a Peer-to-Peer publication for an availability database, even though this <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/hh710046\" target=\"_blank\">combo is not supported by Microsoft<\/a>.\u00a0 I didn\u2019t finish P2P configuration \u2013 but now I\u2019m curious if it actually works (even though it wouldn\u2019t have support).<\/li>\n<li>Deleting a publication for an availability database raises the error 18752 \u201cOnly one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time\u201d.\u00a0 This error was repeatable with or without existing subscribers.\u00a0 The error also gets followed up with a \u201cchange database context to\u201d message. Even after the message, the publication does indeed get removed.\u00a0 This message is seen both with the GUI and with sp_droppublication.\u00a0 I\u2019ll likely put out a Connect item on this one (I didn\u2019t see one that matched my scenario).<\/li>\n<\/ul>\n<p>Why consider replication when you have AG readable secondaries?\u00a0 There are several use-cases that I could think of \u2013 for example if you want to have a sub-set of the overall data and use customized indexing on the subscriber.\u00a0 Another case would be to have access to replicated data if there is an outage of the AG.<\/p>\n<p>I\u2019m going to write about testing the AG subscriber scenario in another post.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Books Online documents a few scenarios regarding Replication and Availability Group interoperability.\u00a0 Today I tested out the process detailed here: Configure Replication for AlwaysOn Availability Groups (SQL Server) It worked as advertised and I tested this on a five replica AG topology with three synchronous replicas (including the primary) and two asynchronous replicas.\u00a0 I won\u2019t [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[30],"tags":[],"class_list":["post-479","post","type-post","status-publish","format-standard","hentry","category-replication"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Transactional Replication Publications and Availability Groups - Joe Sack<\/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\/joe\/transactional-replication-publications-and-availability-groups\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Transactional Replication Publications and Availability Groups - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"Books Online documents a few scenarios regarding Replication and Availability Group interoperability.\u00a0 Today I tested out the process detailed here: Configure Replication for AlwaysOn Availability Groups (SQL Server) It worked as advertised and I tested this on a five replica AG topology with three synchronous replicas (including the primary) and two asynchronous replicas.\u00a0 I won\u2019t [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/transactional-replication-publications-and-availability-groups\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2012-08-22T06:50:44+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-01-18T20:09:16+00:00\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/transactional-replication-publications-and-availability-groups\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/transactional-replication-publications-and-availability-groups\/\",\"name\":\"Transactional Replication Publications and Availability Groups - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2012-08-22T06:50:44+00:00\",\"dateModified\":\"2014-01-18T20:09:16+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/transactional-replication-publications-and-availability-groups\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/transactional-replication-publications-and-availability-groups\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/transactional-replication-publications-and-availability-groups\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Replication\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/replication\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Transactional Replication Publications and Availability Groups\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Transactional Replication Publications and Availability Groups - Joe Sack","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\/joe\/transactional-replication-publications-and-availability-groups\/","og_locale":"en_US","og_type":"article","og_title":"Transactional Replication Publications and Availability Groups - Joe Sack","og_description":"Books Online documents a few scenarios regarding Replication and Availability Group interoperability.\u00a0 Today I tested out the process detailed here: Configure Replication for AlwaysOn Availability Groups (SQL Server) It worked as advertised and I tested this on a five replica AG topology with three synchronous replicas (including the primary) and two asynchronous replicas.\u00a0 I won\u2019t [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/transactional-replication-publications-and-availability-groups\/","og_site_name":"Joe Sack","article_published_time":"2012-08-22T06:50:44+00:00","article_modified_time":"2014-01-18T20:09:16+00:00","author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/transactional-replication-publications-and-availability-groups\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/transactional-replication-publications-and-availability-groups\/","name":"Transactional Replication Publications and Availability Groups - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2012-08-22T06:50:44+00:00","dateModified":"2014-01-18T20:09:16+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/transactional-replication-publications-and-availability-groups\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/transactional-replication-publications-and-availability-groups\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/transactional-replication-publications-and-availability-groups\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Replication","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/replication\/"},{"@type":"ListItem","position":3,"name":"Transactional Replication Publications and Availability Groups"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/479","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=479"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/479\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=479"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=479"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=479"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}