{"id":16029,"date":"2023-01-26T05:24:45","date_gmt":"2023-01-26T05:24:45","guid":{"rendered":"https:\/\/rax-prod-wp-aws.com\/blogs\/microsoft-sql-server-add-articles-to-existing-replication-without-reinitializing-snapshot\/"},"modified":"2026-11-04T15:16:30","modified_gmt":"2026-11-04T15:16:30","slug":"microsoft-sql-server-add-articles-to-existing-replication-without-reinitializing-snapshot","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/de\/blogs\/microsoft-sql-server-add-articles-to-existing-replication-without-reinitializing-snapshot\/","title":{"rendered":"Microsoft SQL Server &#8211; Add Articles to Existing Replication Without Reinitializing Snapshot"},"content":{"rendered":"<p>Replication can be defined as the process via which Data and database objects are copied and distributed between databases, and then synchronized to ensure consistency. Transactional replication is typically used in scenarios requiring high server throughput, Reporting and data warehouses; improving scalability and availability; integrating data from disparate sources. Batch processing can be offloaded, heterogeneous data can be integrated, and multiple sites can be integrated.<\/p>\n<p>Now a days, a major issue for every client is related to database performance. This blog explains how to tune memory in database which result in high performance in databases. The following are the various kinds of memory tuning methods that are available: OS level process tuning, CPU tuning, RAM tuning, database tuning etc. In this blog I will be discussing about the memory tuning in databases.<\/p>\n<p><strong>What we are trying to achieve:<\/strong><\/p>\n<p>Adding a fresh article to a replication is always a tedious task and specially when publisher is big in size or we have customizations in the subscription level. We don\u2019t want to spend hours on reinitializing entire subscription or lose all customization happening on the subscriber for business needs. For such cases we have a option to add article in exiting publication without generating the whole snapshot. Let\u2019s see how we can accomplish this.<\/p>\n<p><strong>Existing Replication details:-<\/strong><\/p>\n<ul>\n<li>Publisher Instance:- LG4WX8Y2<\/li>\n<li>Publisher Database:- [AdventureWorks2019]<\/li>\n<li>Publisher Name :- AdvWorks-Publication<\/li>\n<li>Article Count :- 13<\/li>\n<\/ul>\n<ul>\n<li>Subscriber Instance:- LG4WX8Y2<\/li>\n<li>Subscriber Database:- [AdventureWorksReporting]<\/li>\n<li>Article Count :- 13<\/li>\n<\/ul>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-body-SQL-Replication-1.png\" alt=\"SQL-Replication-1\" \/><\/p>\n<p>****************** **Steps to add articles in to existing replication ** **************************<\/p>\n<p>1. Ensure exiting replication is healthy.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-body-SQL-Replication-2.png\" alt=\"SQL-Replication-2\" \/><\/p>\n<p>2. First, change the allow_anonymous property of the publication to FALSE<\/p>\n<p><strong>Note:<\/strong>&nbsp;Anonymous subscriptions can be created for the given publication, and immediate_sync must also be true. This cannot be changed for peer-to-peer publications.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-body-SQL-Replication-3.png\" alt=\"SQL-Replication-3\" \/><\/p>\n<p>3. Next, disable change immediate_sync<\/p>\n<p><strong>Note:<\/strong>&nbsp;Immediate_sync feature instructs replication to maintain Snapshot BCP files and distributed transactions in the Distribution database. New Subscribers (or reinitialize) would not need to have a \u201cfresh\u201d snapshot generated.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-body-SQL-Replication-4.png\" alt=\"SQL-Replication-4\" \/><\/p>\n<p>4. Add article and invalidate the snapshot (New articles :- [Production].[Product] and [Person].[Person])<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-body-SQL-Replication-5.png\" alt=\"SQL-Replication-5\" \/><\/p>\n<p>5. Refresh Subscription so that we can generate new snapshot<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-body-SQL-Replication-6.png\" alt=\"SQL-Replication-6\" \/><\/p>\n<p><strong>Note:<\/strong>&nbsp;\/*When using pull subscription use below command<br \/>\n*\/\u2013EXEC sp_refreshsubscriptions @publication = N\u2019Adventureworks2016-Pub&#8217;<\/p>\n<p>6. Now, start Snapshot Agent using Replication monitor for publisher \u201cAdvWorks-Publication\u201d<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-body-SQL-Replication-7.png\" alt=\"SQL-Replication-7\" \/><\/p>\n<p><strong>NOTE:-<\/strong>&nbsp;You should notice that bulk-insert statements are created only for 2 article instead of all articles.<\/p>\n<p>7. Check if log reader agent is running and replicating transactions.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-body-SQL-Replication-8.png\" alt=\"SQL-Replication-8\" \/><\/p>\n<p>8. Ensure articles are added to publisher \u2013 Now you can see 15 articles in the list.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-body-SQL-Replication-9.png\" alt=\"SQL-Replication-9\" \/><\/p>\n<p>9. First, change the immediate_sync property of the publication to true<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-body-SQL-Replication-10.png\" alt=\"SQL-Replication-10\" \/><\/p>\n<p>10. Next, enable change allow_anonymous<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-body-SQL-Replication-11.png\" alt=\"SQL-Replication-11\" \/><\/p>\n<p>11. Monitor the replication for some time.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-body-SQL-Replication-12.png\" alt=\"SQL-Replication-12\" \/><\/p>\n<p>Scripts Used for Demo:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-body-SQL-Replication-code1.png\" alt=\"SQL-Replication-code1\" \/><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-body-SQL-Replication-code2.png\" alt=\"SQL-Replication-code2\" \/><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-body-SQL-Replication-code3.png\" alt=\"SQL-Replication-code3\" \/><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-body-SQL-Replication-code4.png\" alt=\"SQL-Replication-code4\" \/><\/p>\n<p><a href=\"https:\/\/www.rackspace.com\/data\/managed-sql\">Learn about Rackspace Managed SQL Databases&nbsp;<strong>\u2192<\/strong><\/a><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Microsoft SQL Server &#8211; Add Articles to Existing Replication Without Reinitializing Snapshot<\/p>\n","protected":false},"author":2,"featured_media":16396,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"25667","content_type":"blog","created_by":"11058","authored_on":"2023-06-23T09:54:45+05:30","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Varun Jha, Database Support Manager, Rackspace Technology","seo_description":"Learn how to add articles to existing replication in Microsoft SQL Server without reinitializing the snapshot.","field_featured_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-insights_image-database-technical-blog-1-insights.jpg","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/25667-insights_image-database-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"","product":"","publisheddate":"2023-01-26T05:24:45","rxt_solutions":"","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Microsoft SQL Server - Add Articles to Existing Replication ","field_topics":"","field_slug":"microsoft-sql-server-add-articles-to-existing-replication-without-reinitializing-snapshot\/","footnotes":""},"categories":[],"tags":[204],"class_list":["post-16029","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/16029","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/comments?post=16029"}],"version-history":[{"count":2,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/16029\/revisions"}],"predecessor-version":[{"id":16031,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/16029\/revisions\/16031"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/media\/16396"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/media?parent=16029"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/categories?post=16029"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/tags?post=16029"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}