{"id":1153,"date":"2025-04-13T20:07:24","date_gmt":"2025-04-13T20:07:24","guid":{"rendered":"https:\/\/humtechno.com\/master\/?p=1153"},"modified":"2025-04-19T10:00:06","modified_gmt":"2025-04-19T10:00:06","slug":"configure-sql-server-transaction-replication-with-alwayson","status":"publish","type":"post","link":"https:\/\/humtechno.com\/master\/2025\/04\/13\/configure-sql-server-transaction-replication-with-alwayson\/","title":{"rendered":"Configure SQL SERVER Transaction Replicational with Alwayson"},"content":{"rendered":"\n\n\n\n\n<p><a href=\"https:\/\/humtechno.com\/master\/wp-content\/uploads\/2025\/04\/How To Configure Transaction Replication with Always on Cluster.pdf\" data-type=\"page\" data-id=\"946\">Download Complete Steps with Images<\/a><\/p>\n\n\n\n<p>Hub Sub Techno<br>Kamran Hussain<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<div class=\"nv-iframe-embed\"><iframe loading=\"lazy\" title=\"Step by Step how to configure SQL Server Transaction with Alwayson Availability Group\" width=\"1200\" height=\"675\" src=\"https:\/\/www.youtube.com\/embed\/QZ_8Q4m5yis?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/div>\n<\/div><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><br>Existing Configure<\/h2>\n\n\n\n<p><br>Domain: hst.com 192.168.179.141<br>Windows Cluster: sqlwcl 192.168.179.135<br>Always on Cluster: sqlcl 192.168.179.134<br>Always on Cluster Listener sqlli 192.168.179.136<br>3 Node Always on Cluster<br>sql1 192.168.179.131<br>sql2 192.168.179.132<br>sql3 192.168.179.133<br>DB1 = Primary DB for Transaction<br>DB1SUB= Primary DB Copy for Reporting<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><br>High Level Steps<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Create Distributor at Distributor Server<\/li>\n\n\n\n<li>Add Primary Publisher Name at Distributor Server<\/li>\n\n\n\n<li>Create Distributor at Primary Publisher Server<\/li>\n\n\n\n<li>Create Publisher with Always on Listener connection<\/li>\n\n\n\n<li>Create Subscriber Database and Add in Always on Cluster Group<\/li>\n\n\n\n<li>Create Subscriber with Always on Listener connection<\/li>\n\n\n\n<li>Add Secondary Node as Publisher Name at Distributor Server<\/li>\n\n\n\n<li>Create Distributor at Secondary Servers and Create Linked Server<\/li>\n\n\n\n<li>Define Publisher Re \u2013 Direct at Distributor<\/li>\n\n\n\n<li>Monitor Replication<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\">Create Distributor at Distributor Server<\/h2>\n\n\n\n<p>Create Share Folder \u201cC:\\Database\\Replication\\Snap\u201d<br>\\sqldistributor\\Replication\\Database\\Snap<\/p>\n\n\n\n<p>USE master;<br>GO<br>EXECUTE sys.sp_adddistributor<br>@distributor = &#8216;SQLDistributor&#8217;,<br>@password = &#8216;Rtf#fggf#sd234Df#&#8217;;<\/p>\n\n\n\n<p>USE master;<br>GO<br>EXECUTE sys.sp_adddistributiondb<br>@database = &#8216;distribution&#8217;,<br>@security_mode = 1;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Add Primary Publisher Name at Distributor Server<\/h2>\n\n\n\n<p><br>USE master;<br>GO<br>EXECUTE sys.sp_adddistpublisher<br>@publisher = &#8216;SQL1&#8217;,<br>@distribution_db = &#8216;distribution&#8217;,<br>@working_directory = &#8216;\\sqldistributor\\Replication\\Database\\Snap&#8217;,<br>@login = &#8216;SQLPUBLogin&#8217;,<br>@password = &#8216;Rtf#fggf#sd234Df#&#8217;;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Create Distributor at Primary Publisher Server<\/h2>\n\n\n\n<p><br>EXECUTE sys.sp_adddistributor<br>@distributor = &#8216;SQLDistributor&#8217;,<br>@password = &#8216;Rtf#fggf#sd234Df#&#8217;;<\/p>\n\n\n\n<p>USE master;<br>GO<br>EXECUTE sys.sp_replicationdboption<br>@dbname = &#8216;db1&#8217;,<br>@optname = &#8216;publish&#8217;,<br>@value = &#8216;true&#8217;;<br>EXECUTE sys.sp_replicationdboption<br>@dbname = &#8216;db1&#8217;,<br>@optname = &#8216;merge publish&#8217;,<br>@value = &#8216;true&#8217;;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Create Publisher with Always on Listener connection<\/h2>\n\n\n\n<h2 class=\"wp-block-heading\">Create Subscriber Database and Add in Always on Cluster Group<\/h2>\n\n\n\n<h2 class=\"wp-block-heading\">Create Subscriber with Always on Listener connection<\/h2>\n\n\n\n<h2 class=\"wp-block-heading\">Add Secondary Node as Publisher Name at Distributor Server<\/h2>\n\n\n\n<p><br>EXECUTE sys.sp_adddistpublisher<br>@publisher = &#8216;sql2&#8217;,<br>@distribution_db = &#8216;distribution&#8217;,<br>@working_directory = &#8216;\\sqldistributor\\Replication\\Database\\Snap&#8217;,<br>@login = &#8216;SQLPUBLogin&#8217;,<br>@password = &#8216;Rtf#fggf#sd234Df#&#8217;;<\/p>\n\n\n\n<p>EXECUTE sys.sp_adddistpublisher<br>@publisher = &#8216;sql3&#8217;,<br>@distribution_db = &#8216;distribution&#8217;,<br>@working_directory = &#8216;\\sqldistributor\\Replication\\Database\\Snap&#8217;,<br>@login = &#8216;SQLPUBLogin&#8217;,<br>@password = &#8216;Rtf#fggf#sd234Df#&#8217;;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Create Distributor at all Secondary Servers and Create Linked Server<\/h2>\n\n\n\n<p><br>EXECUTE sp_adddistributor<br>@distributor = &#8216;SQLDistributor&#8217;,<br>@password = &#8216;Rtf#fggf#sd234Df#&#8217;;<\/p>\n\n\n\n<p>EXECUTE sys.sp_addlinkedserver @server = &#8216;SQLLI&#8217;;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Define Publisher Re \u2013 Direct at Distributor<\/h2>\n\n\n\n<p><br>USE distribution;<br>GO<br>EXECUTE sys.sp_redirect_publisher<br>@original_publisher = &#8216;SQL1&#8217;,<br>@publisher_db = &#8216;DB1&#8217;,<br>@redirected_publisher = &#8216;SQLLI&#8217;;<\/p>\n\n\n\n<p>USE distribution;<br>GO<br>DECLARE @redirected_publisher AS sysname;<br>EXECUTE sys.sp_validate_replica_hosts_as_publishers<br>@original_publisher = &#8216;SQL1&#8217;,<br>@publisher_db = &#8216;DB1&#8217;,<br>@redirected_publisher = @redirected_publisher OUTPUT;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Monitor Replication<\/h2>\n\n\n\n<p><br>** Verify Table on Both Databases<\/p>\n\n\n\n<p>** Verify Agents<br>** Snapshot Agent should complete 100% for Copy all Tables first time to new Database<\/p>\n\n\n\n<p>Logs for Replication<\/p>\n\n\n\n<p>Replication Error<\/p>\n\n\n\n<p>SELECT TOP (1000) [id]<br>,[time]<br>,[error_type_id]<br>,[source_type_id]<br>,[source_name]<br>,[error_code]<br>,[error_text]<br>,[xact_seqno]<br>,[command_id]<br>,[session_id]<br>FROM [distribution].[dbo].[MSrepl_errors] order by time desc;<\/p>\n\n\n\n<p>Monitor Replication<\/p>\n\n\n\n<p>SELECT publisher,<br>publisher_db,<br>publication_id,<br>CASE publication_type<br>WHEN 0 then &#8216;0 &#8211; Transactional publication&#8217;<br>WHEN 1 then &#8216;1 &#8211; Snapshot publication&#8217;<br>WHEN 2 then &#8216;2 &#8211; Merge publication&#8217;<br>END AS publication_type_desc,<br>publication,<br>CASE agent_type<br>WHEN 1 then &#8216;1 &#8211; Snapshot Agent&#8217;<br>WHEN 2 then &#8216;2 &#8211; Log Reader Agent&#8217;<br>WHEN 3 then &#8216;3 &#8211; Distribution Agent&#8217;<br>WHEN 4 then &#8216;4 &#8211; Merge Agent&#8217;<br>WHEN 9 then &#8216;9 &#8211; Queue Reader Agent&#8217;<br>END AS agent_type,<br>agent_name,<br>CASE status<br>WHEN 1 THEN &#8216;1 &#8211; Started&#8217;<br>WHEN 2 THEN &#8216;2 &#8211; Succeeded&#8217;<br>WHEN 3 THEN &#8216;3 &#8211; In progress&#8217;<br>WHEN 4 THEN &#8216;4 &#8211; Idle&#8217;<br>WHEN 5 THEN &#8216;5 &#8211; Retrying&#8217;<br>WHEN 6 THEN &#8216;6 &#8211; Failed&#8217;<br>END AS agent_status,<br>RIGHT(&#8216;0&#8217; + CAST(cur_latency \/ 3600 AS VARCHAR),2) + &#8216;:&#8217; +<br>RIGHT(&#8216;0&#8217; + CAST((cur_latency \/ 60) % 60 AS VARCHAR),2) + &#8216;:&#8217; +<br>RIGHT(&#8216;0&#8217; + CAST(cur_latency % 60 AS VARCHAR),2) AS cur_latency,<br>RIGHT(&#8216;0&#8217; + CAST(worst_latency \/ 3600 AS VARCHAR),2) + &#8216;:&#8217; +<br>RIGHT(&#8216;0&#8217; + CAST((worst_latency \/ 60) % 60 AS VARCHAR),2) + &#8216;:&#8217; +<br>RIGHT(&#8216;0&#8217; + CAST(worst_latency % 60 AS VARCHAR),2) AS max_latency,<br>RIGHT(&#8216;0&#8217; + CAST(best_latency \/ 3600 AS VARCHAR),2) + &#8216;:&#8217; +<br>RIGHT(&#8216;0&#8217; + CAST((best_latency \/ 60) % 60 AS VARCHAR),2) + &#8216;:&#8217; +<br>RIGHT(&#8216;0&#8217; + CAST(best_latency % 60 AS VARCHAR),2) AS min_latency,<br>RIGHT(&#8216;0&#8217; + CAST(avg_latency \/ 3600 AS VARCHAR),2) + &#8216;:&#8217; +<br>RIGHT(&#8216;0&#8217; + CAST((avg_latency \/ 60) % 60 AS VARCHAR),2) + &#8216;:&#8217; +<br>RIGHT(&#8216;0&#8217; + CAST(avg_latency % 60 AS VARCHAR),2) AS avg_latency,<br>last_distsync AS last_time_dist_agent_run,<br>isagentrunningnow AS is_agent_running_now,<br>agentstoptime AS agent_stop_time,<br>CASE warning<br>WHEN 1 THEN &#8216;Expiration&#8217;<br>WHEN 2 THEN &#8216;Latency&#8217;<br>WHEN 4 THEN &#8216;Merge expiration &#8216;<br>WHEN 16 THEN &#8216;Merge slow run duration &#8216;<br>WHEN 32 THEN &#8216;Merge fast run speed &#8216;<br>WHEN 64 THEN &#8216;Merge slow run speed&#8217;<br>END AS warning,<br>CASE retention_period_unit<br>WHEN 1 THEN CAST(retention AS VARCHAR)+&#8217; Week&#8217;<br>WHEN 2 THEN CAST(retention AS VARCHAR)+&#8217; Month&#8217;<br>WHEN 3 THEN CAST(retention AS VARCHAR)+&#8217; Year&#8217;<br>END AS pub_retention_period,<br>distdb AS distribution_db<br>FROM distribution.dbo.MSreplication_monitordata<br>WHERE publisher_db = &#8216;db1&#8217;<br>AND publication IN (&#8216;ALL&#8217;,&#8217;your_publication_name&#8217;)<br>ORDER BY publisher,<br>agent_type,<br>publication;<\/p>\n\n\n\n<p>Alerts for Replication<\/p>\n\n\n\n<p>SELECT A.alert_id,<br>A.error_id,<br>A.time AS alert_time,<br>E.time AS error_time,<br>A.publisher,<br>A.publisher_db,<br>CASE A.publication_type<br>WHEN 0 THEN &#8216;Snapshot&#8217;<br>WHEN 1 THEN &#8216;Transactional&#8217;<br>WHEN 2 THEN &#8216;Merge&#8217;<br>END AS publication_type_desc,<br>A.publication as publication_name,<br>A.subscriber,<br>A.subscriber_db,<br>A.article,<br>A.source_object,<br>A.destination_object,<br>E.error_text,<br>A.alert_error_text,<br>A.agent_id,<br>CASE A.agent_type<br>WHEN 1 THEN &#8216;Snapshot Agent&#8217;<br>WHEN 2 THEN &#8216;Log Reader Agent&#8217;<br>WHEN 3 THEN &#8216;Distribution Agent&#8217;<br>WHEN 4 THEN &#8216;Merge Agent&#8217;<br>ELSE &#8216;Unknown&#8217;<br>END AS agent_type_desc,<br>COALESCE(S.name,L.name,D.name,M.name) AS agent_name,<br>E.session_id AS agent_session_id,<br>CASE status<br>WHEN 0 THEN &#8216;Unserviced&#8217;<br>WHEN 1 THEN &#8216;serviced&#8217;<br>END AS status_desc<br>FROM msdb.dbo.sysreplicationalerts AS A<br>LEFT JOIN distribution.dbo.MSrepl_errors AS E ON A.error_id = E.id<br>LEFT JOIN distribution.dbo.MSsnapshot_agents AS S ON S.id = A.agent_id<br>LEFT JOIN distribution.dbo.MSlogreader_agents AS L ON L.id = A.agent_id<br>LEFT JOIN distribution.dbo.MSdistribution_agents AS D ON D.id = A.agent_id<br>LEFT JOIN distribution.dbo.MSmerge_agents AS M ON M.id = A.agent_id<br>WHERE A.time &gt;= GETUTCDATE()-1<br>AND A.publisher_db = &#8216;db1&#8217;<br>AND A.publication = &#8216;sqlpubs&#8217;<br>AND A.subscriber_db = &#8216;db1sub&#8217;<br>ORDER BY A.alert_id DESC;<\/p>\n\n\n\n<p>Any Pending Commands for Replication<\/p>\n\n\n\n<p>SELECT<br>p.name as publisher_server,<br>da.publisher_db,<br>s.name as subscriber_server,<br>da.subscriber_db,<br>da.publication as publication,<br>a.destination_object as table_name,<br>ds.DelivCmdsInDistDB as delivered_commands_in_distribution_db,<br>ds.UndelivCmdsInDistDB as undelivered_commands_in_distribution_db<br>FROM distribution.dbo.MSdistribution_status ds<br>INNER JOIN distribution.dbo.MSdistribution_agents da ON da.id = ds.agent_id<br>INNER JOIN distribution.dbo.MSArticles a ON a.publisher_id = da.publisher_id AND a.publisher_db = da.publisher_db AND a.article_id = ds.article_id<br>INNER JOIN master.sys.servers s ON s.server_id = da.subscriber_id<br>INNER JOIN master.sys.servers p ON p.server_id = da.publisher_id<br>ORDER BY undelivered_commands_in_distribution_db DESC,<br>table_name ASC<\/p>\n\n\n\n<p>Agents History<\/p>\n\n\n\n<p>SELECT H.time AS message_log_time,<br>H.agent_id,<br>A.name AS agent_name,<br>H.runstatus,<br>CASE runstatus<br>WHEN 1 THEN &#8216;Start&#8217;<br>WHEN 2 THEN &#8216;Succeed&#8217;<br>WHEN 3 THEN &#8216;In progress&#8217;<br>WHEN 4 THEN &#8216;Idle&#8217;<br>WHEN 5 THEN &#8216;Retry&#8217;<br>WHEN 6 THEN &#8216;Fail&#8217;<br>END AS runstatus_desc,<br>H.start_time AS job_exec_start_time,<br>H.duration AS session_duration_in_sec,<br>H.comments AS message_text,<br>H.current_delivery_rate AS current_delivery_rate_per_sec,<br>H.current_delivery_latency\/1000 AS current_delivery_latency_in_sec,<br>H.delivery_rate AS delivery_rate_per_sec,<br>H.delivery_latency\/1000 AS delivery_latency_in_sec,<br>H.delivered_transactions AS transactions_delivered_in_session,<br>H.delivered_commands AS commands_delivered_in_session,<br>H.average_commands AS average_commands_in_session,<br>H.total_delivered_commands, &#8211;total commands delivered since subscription was created<br>A.publisher_db,<br>A.publication,<br>A.subscriber_db,<br>H.error_id,<br>E.time AS error_time,<br>E.error_code,<br>E.error_text<br>FROM distribution.dbo.MSdistribution_history AS H<br>INNER JOIN distribution.dbo.MSdistribution_agents AS A ON H.agent_id = A.id<br>LEFT JOIN distribution.dbo.MSrepl_errors AS E ON E.id = H.error_id<br>WHERE H.time &gt;= GETUTCDATE()-2<br>ORDER BY message_log_time desc;<\/p>\n\n\n\n<p>SELECT H.time AS message_log_time,<br>H.agent_id,<br>A.name AS agent_name,<br>H.runstatus,<br>CASE runstatus<br>WHEN 1 THEN &#8216;Start&#8217;<br>WHEN 2 THEN &#8216;Succeed&#8217;<br>WHEN 3 THEN &#8216;In progress&#8217;<br>WHEN 4 THEN &#8216;Idle&#8217;<br>WHEN 5 THEN &#8216;Retry&#8217;<br>WHEN 6 THEN &#8216;Fail&#8217;<br>END AS runstatus_desc,<br>H.start_time AS job_exec_start_time,<br>H.duration AS session_duration_in_sec,<br>H.comments AS message_text,<br>H.delivered_transactions AS transactions_delivered_in_session,<br>H.delivered_commands AS delivered_commands_per_sec,<br>H.delivery_rate AS delivery_rate_per_sec,<br>A.publisher_db,<br>A.publication,<br>H.error_id,<br>E.time AS error_time,<br>E.error_code,<br>E.error_text<br>FROM distribution.dbo.MSlogreader_history AS H<br>INNER JOIN distribution.dbo.MSlogreader_agents AS A ON H.agent_id = A.id<br>LEFT JOIN distribution.dbo.MSrepl_errors AS E ON E.id = H.error_id<br>WHERE H.time &gt;= GETUTCDATE()-7<br>ORDER BY message_log_time desc<\/p>\n\n\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_siteseo_robots_primary_cat":"17","pagelayer_contact_templates":[],"_pagelayer_content":"","neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[17],"tags":[18,23,19,20,21],"class_list":["post-1153","post","type-post","status-publish","format-standard","hentry","category-mssql","tag-sqlserver","tag-alwaysonavailabilitygroups","tag-sql","tag-sqlreplication","tag-transactionalreplication"],"_links":{"self":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts\/1153","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/comments?post=1153"}],"version-history":[{"count":5,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts\/1153\/revisions"}],"predecessor-version":[{"id":1177,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts\/1153\/revisions\/1177"}],"wp:attachment":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/media?parent=1153"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/categories?post=1153"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/tags?post=1153"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}