{"id":623,"date":"2024-06-18T08:00:00","date_gmt":"2024-06-18T13:00:00","guid":{"rendered":"https:\/\/www.sqltabletalk.com\/?p=623"},"modified":"2024-06-17T10:18:56","modified_gmt":"2024-06-17T15:18:56","slug":"mastering-partition-switching-in-transactional-replication","status":"publish","type":"post","link":"https:\/\/www.sqltabletalk.com\/?p=623","title":{"rendered":"Mastering Partition Switching in Transactional Replication"},"content":{"rendered":"<h2>Introduction<\/h2>\n<p>Partition switching is a powerful feature in SQL Server that allows for efficient data management and movement. However, when it comes to transactional replication, there are specific properties that must be managed to ensure consistency and performance. In this blog post, we\u2019ll explore how to control the behavior of partition switching in a replicated environment.<\/p>\n<h3>Understanding the Properties<\/h3>\n<p>Two key properties govern partition switching in transactional publications:<\/p>\n<ul>\n<li><strong>@allow_partition_switch<\/strong>: When set to true, this property enables the execution of the SWITCH PARTITION command against the publication database.<\/li>\n<li><strong>@replicate_partition_switch<\/strong>: This property decides if the SWITCH PARTITION DDL statement should be replicated to Subscribers. It\u2019s only applicable if <strong>@allow_partition_switch<\/strong> is true.<\/li>\n<\/ul>\n<p>These properties can be set using <code>sp_addpublication<\/code> when creating the publication or <code>sp_changepublication<\/code> after the publication\u2019s creation.<\/p>\n<h3>The Case with Merge Replication<\/h3>\n<p>It\u2019s important to note that merge replication does not support partition switching. If you need to execute <code>SWITCH PARTITION<\/code> on a table that is part of merge replication, you must first remove the table from the publication.<\/p>\n<h3>The Benefits and Challenges<\/h3>\n<p>Table partitioning\u2019s main advantage is the swift and efficient movement of data subsets between partitions, typically done using the <code>SWITCH PARTITION<\/code> command. By default, replication blocks <code>SWITCH PARTITION<\/code> operations to prevent inconsistencies between the Publisher and Subscriber, especially if the Subscriber lacks the table or has a different partitioned table definition.<\/p>\n<h3>Enabling Partition Switching<\/h3>\n<p>Despite the challenges, you can enable partition switching for transactional replication. Before doing so, ensure that all involved tables exist at both the Publisher and Subscriber and that the table and partition definitions match.<\/p>\n<p>When the partitions share the same scheme at the Publisher and Subscriber, you can activate <strong>@allow_partition_switch<\/strong> along with <strong>@replicate_partition_switch<\/strong>. This setup replicates only the partition switch statement to the Subscriber.<\/p>\n<p>Alternatively, you can enable <strong>@allow_partition_switch<\/strong> without replicating the DDL, which is useful for rolling out old data from the partition while maintaining the replicated partition at the Subscriber for backup purposes or when you have a different partition scheme on the Subscriber.<\/p>\n<h3>Unsupported Scenarios<\/h3>\n<p>The following scenarios are not supported when using replication with partition switching:<\/p>\n<ul>\n<li><strong>Peer-to-peer replication<\/strong>: Peer-to-peer replication is not supported with partition switching.<\/li>\n<li><strong>Use of variables with partition switching<\/strong>: Using variables with partition switching on tables published with transactional replication or Change Data Capture (CDC) is not supported for the <code>ALTER TABLE ... SWITCH TO ... PARTITION ...<\/code> statement.<\/li>\n<li><strong>Foreign Keys with CHECK CONSTRAINT<\/strong>: Tables with foreign keys using <code>WITH CHECK CHECK CONSTRAINT<\/code> cannot use partition switching. Options include:\n<ul>\n<li>Disabling the foreign key check to enable table switching via <code>ALTER TABLE [SourceTable] SWITCH TO [TargetTable]<\/code>.<\/li>\n<li>Changing the foreign key to <code>NOCHECK CONSTRAINT<\/code>. Running <code>ALTER TABLE &lt;table&gt; WITH CHECK CHECK CONSTRAINT &lt;constraint&gt;<\/code> will alter it back, but this can take time and cause blocking on foreign keys referencing larger tables.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h3>Conclusion<\/h3>\n<p>Partition switching can significantly enhance data management in a replicated environment. By carefully setting the appropriate properties and ensuring consistency between Publisher and Subscriber, you can leverage this feature to its full potential.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to effectively manage partition switching in SQL Server transactional replication environments. This guide covers key properties such as @allow_partition_switch and @replicate_partition_switch, the benefits and challenges of using partition switching, and important considerations for ensuring consistency and performance. We also discuss unsupported scenarios and provide best practices for setting up partition switching in your replicated databases.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[94,5,149],"tags":[151,131,52,150],"class_list":["post-623","post","type-post","status-publish","format-standard","hentry","category-change-data-capture","category-performance","category-replication","tag-partition-switching","tag-sql-server","tag-sql-server-performance","tag-transactional-replication"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.1.1 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Mastering Partition Switching in Transactional Replication - SQL Table Talk<\/title>\n<meta name=\"description\" content=\"Learn how to effectively manage partition switching in SQL Server transactional replication environments. This guide covers key properties such as @allow_partition_switch and @replicate_partition_switch, the benefits and challenges of using partition switching, and important considerations for ensuring consistency and performance. We also discuss unsupported scenarios and provide best practices for setting up partition switching in your replicated databases.\" \/>\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.sqltabletalk.com\/?p=623\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Mastering Partition Switching in Transactional Replication - SQL Table Talk\" \/>\n<meta property=\"og:description\" content=\"Learn how to effectively manage partition switching in SQL Server transactional replication environments. This guide covers key properties such as @allow_partition_switch and @replicate_partition_switch, the benefits and challenges of using partition switching, and important considerations for ensuring consistency and performance. We also discuss unsupported scenarios and provide best practices for setting up partition switching in your replicated databases.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqltabletalk.com\/?p=623\" \/>\n<meta property=\"og:site_name\" content=\"SQL Table Talk\" \/>\n<meta property=\"article:published_time\" content=\"2024-06-18T13:00:00+00:00\" \/>\n<meta name=\"author\" content=\"Yvonne Vanslageren\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Yvonne Vanslageren\" \/>\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.sqltabletalk.com\/?p=623#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=623\"},\"author\":{\"name\":\"Yvonne Vanslageren\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/68bb31b454bafe9e139183ed4f3e9082\"},\"headline\":\"Mastering Partition Switching in Transactional Replication\",\"datePublished\":\"2024-06-18T13:00:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=623\"},\"wordCount\":483,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"keywords\":[\"Partition Switching\",\"SQL Server\",\"SQL Server performance\",\"Transactional Replication\"],\"articleSection\":[\"Change Data Capture\",\"Performance\",\"Replication\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=623#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=623\",\"url\":\"https:\/\/www.sqltabletalk.com\/?p=623\",\"name\":\"Mastering Partition Switching in Transactional Replication - SQL Table Talk\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\"},\"datePublished\":\"2024-06-18T13:00:00+00:00\",\"description\":\"Learn how to effectively manage partition switching in SQL Server transactional replication environments. This guide covers key properties such as @allow_partition_switch and @replicate_partition_switch, the benefits and challenges of using partition switching, and important considerations for ensuring consistency and performance. We also discuss unsupported scenarios and provide best practices for setting up partition switching in your replicated databases.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=623#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=623\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=623#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqltabletalk.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Mastering Partition Switching in Transactional Replication\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\",\"url\":\"https:\/\/www.sqltabletalk.com\/\",\"name\":\"SQL Table Talk\",\"description\":\"Breaking Down SQL Server, One Post at a Time.\",\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqltabletalk.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\",\"name\":\"Stephen Planck\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"caption\":\"Stephen Planck\"},\"logo\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\"},\"sameAs\":[\"https:\/\/dexterwiki.com\",\"https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI\",\"https:\/\/www.youtube.com\/linuxmate\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/68bb31b454bafe9e139183ed4f3e9082\",\"name\":\"Yvonne Vanslageren\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/22c274a7a354b81a0a8dc5b23e8e0be9bdd81a6bc0541474cfb6b954d6bb2089?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/22c274a7a354b81a0a8dc5b23e8e0be9bdd81a6bc0541474cfb6b954d6bb2089?s=96&d=mm&r=g\",\"caption\":\"Yvonne Vanslageren\"},\"url\":\"https:\/\/www.sqltabletalk.com\/?author=2\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Mastering Partition Switching in Transactional Replication - SQL Table Talk","description":"Learn how to effectively manage partition switching in SQL Server transactional replication environments. This guide covers key properties such as @allow_partition_switch and @replicate_partition_switch, the benefits and challenges of using partition switching, and important considerations for ensuring consistency and performance. We also discuss unsupported scenarios and provide best practices for setting up partition switching in your replicated databases.","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.sqltabletalk.com\/?p=623","og_locale":"en_US","og_type":"article","og_title":"Mastering Partition Switching in Transactional Replication - SQL Table Talk","og_description":"Learn how to effectively manage partition switching in SQL Server transactional replication environments. This guide covers key properties such as @allow_partition_switch and @replicate_partition_switch, the benefits and challenges of using partition switching, and important considerations for ensuring consistency and performance. We also discuss unsupported scenarios and provide best practices for setting up partition switching in your replicated databases.","og_url":"https:\/\/www.sqltabletalk.com\/?p=623","og_site_name":"SQL Table Talk","article_published_time":"2024-06-18T13:00:00+00:00","author":"Yvonne Vanslageren","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Yvonne Vanslageren","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqltabletalk.com\/?p=623#article","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/?p=623"},"author":{"name":"Yvonne Vanslageren","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/68bb31b454bafe9e139183ed4f3e9082"},"headline":"Mastering Partition Switching in Transactional Replication","datePublished":"2024-06-18T13:00:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqltabletalk.com\/?p=623"},"wordCount":483,"commentCount":0,"publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"keywords":["Partition Switching","SQL Server","SQL Server performance","Transactional Replication"],"articleSection":["Change Data Capture","Performance","Replication"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqltabletalk.com\/?p=623#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqltabletalk.com\/?p=623","url":"https:\/\/www.sqltabletalk.com\/?p=623","name":"Mastering Partition Switching in Transactional Replication - SQL Table Talk","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/#website"},"datePublished":"2024-06-18T13:00:00+00:00","description":"Learn how to effectively manage partition switching in SQL Server transactional replication environments. This guide covers key properties such as @allow_partition_switch and @replicate_partition_switch, the benefits and challenges of using partition switching, and important considerations for ensuring consistency and performance. We also discuss unsupported scenarios and provide best practices for setting up partition switching in your replicated databases.","breadcrumb":{"@id":"https:\/\/www.sqltabletalk.com\/?p=623#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqltabletalk.com\/?p=623"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqltabletalk.com\/?p=623#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqltabletalk.com\/"},{"@type":"ListItem","position":2,"name":"Mastering Partition Switching in Transactional Replication"}]},{"@type":"WebSite","@id":"https:\/\/www.sqltabletalk.com\/#website","url":"https:\/\/www.sqltabletalk.com\/","name":"SQL Table Talk","description":"Breaking Down SQL Server, One Post at a Time.","publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqltabletalk.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0","name":"Stephen Planck","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","caption":"Stephen Planck"},"logo":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/dexterwiki.com","https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI","https:\/\/www.youtube.com\/linuxmate"]},{"@type":"Person","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/68bb31b454bafe9e139183ed4f3e9082","name":"Yvonne Vanslageren","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/22c274a7a354b81a0a8dc5b23e8e0be9bdd81a6bc0541474cfb6b954d6bb2089?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/22c274a7a354b81a0a8dc5b23e8e0be9bdd81a6bc0541474cfb6b954d6bb2089?s=96&d=mm&r=g","caption":"Yvonne Vanslageren"},"url":"https:\/\/www.sqltabletalk.com\/?author=2"}]}},"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/623","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=623"}],"version-history":[{"count":2,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/623\/revisions"}],"predecessor-version":[{"id":627,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/623\/revisions\/627"}],"wp:attachment":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=623"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=623"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=623"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}