Download Complete Steps with Images

Hub Sub Techno
Kamran Hussain

Existing Configure

Domain: hst.com 192.168.179.141
Windows Cluster: sqlwcl 192.168.179.135
Always on Cluster: sqlcl 192.168.179.134
Always on Cluster Listener sqlli 192.168.179.136
3 Node Always on Cluster
sql1 192.168.179.131
sql2 192.168.179.132
sql3 192.168.179.133
DB1 = Primary DB for Transaction
DB1SUB= Primary DB Copy for Reporting

High Level Steps

1. Create Distributor at Distributor Server
2. Add Primary Publisher Name at Distributor Server
3. Create Distributor at Primary Publisher Server
4. Create Publisher with Always on Listener connection
5. Create Subscriber Database and Add in Always on Cluster Group
6. Create Subscriber with Always on Listener connection
7. Add Secondary Node as Publisher Name at Distributor Server
8. Create Distributor at Secondary Servers and Create Linked Server
9. Define Publisher Re – Direct at Distributor
10. Monitor Replication

Create Distributor at Distributor Server

Create Share Folder “C:\Database\Replication\Snap”
\sqldistributor\Replication\Database\Snap

USE master;
GO
EXECUTE sys.sp_adddistributor
@distributor = 'SQLDistributor',
@password = 'Rtf#fggf#sd234Df#';

USE master;
GO
EXECUTE sys.sp_adddistributiondb
@database = 'distribution',
@security_mode = 1;

Add Primary Publisher Name at Distributor Server

USE master;
GO
EXECUTE sys.sp_adddistpublisher
@publisher = 'SQL1',
@distribution_db = 'distribution',
@working_directory = '\sqldistributor\Replication\Database\Snap',
@login = 'SQLPUBLogin',
@password = 'Rtf#fggf#sd234Df#';

Create Distributor at Primary Publisher Server

EXECUTE sys.sp_adddistributor
@distributor = 'SQLDistributor',
@password = 'Rtf#fggf#sd234Df#';

USE master;
GO
EXECUTE sys.sp_replicationdboption
@dbname = 'db1',
@optname = 'publish',
@value = 'true';
EXECUTE sys.sp_replicationdboption
@dbname = 'db1',
@optname = 'merge publish',
@value = 'true';

Create Publisher with Always on Listener connection

Create Subscriber Database and Add in Always on Cluster Group

Create Subscriber with Always on Listener connection

Add Secondary Node as Publisher Name at Distributor Server

EXECUTE sys.sp_adddistpublisher
@publisher = 'sql2',
@distribution_db = 'distribution',
@working_directory = '\sqldistributor\Replication\Database\Snap',
@login = 'SQLPUBLogin',
@password = 'Rtf#fggf#sd234Df#';

EXECUTE sys.sp_adddistpublisher
@publisher = 'sql3',
@distribution_db = 'distribution',
@working_directory = '\sqldistributor\Replication\Database\Snap',
@login = 'SQLPUBLogin',
@password = 'Rtf#fggf#sd234Df#';

Create Distributor at all Secondary Servers and Create Linked Server

EXECUTE sp_adddistributor
@distributor = 'SQLDistributor',
@password = 'Rtf#fggf#sd234Df#';

EXECUTE sys.sp_addlinkedserver @server = 'SQLLI';

Define Publisher Re – Direct at Distributor

USE distribution;
GO
EXECUTE sys.sp_redirect_publisher
@original_publisher = 'SQL1',
@publisher_db = 'DB1',
@redirected_publisher = 'SQLLI';

USE distribution;
GO
DECLARE @redirected_publisher AS sysname;
EXECUTE sys.sp_validate_replica_hosts_as_publishers
@original_publisher = 'SQL1',
@publisher_db = 'DB1',
@redirected_publisher = @redirected_publisher OUTPUT;

Monitor Replication

** Verify Table on Both Databases

** Verify Agents
** Snapshot Agent should complete 100% for Copy all Tables first time to new Database

Logs for Replication

Replication Error

SELECT TOP (1000) [id]
,[time]
,[error_type_id]
,[source_type_id]
,[source_name]
,[error_code]
,[error_text]
,[xact_seqno]
,[command_id]
,[session_id]
FROM [distribution].[dbo].[MSrepl_errors] order by time desc;

Monitor Replication

SELECT publisher,
publisher_db,
publication_id,
CASE publication_type
WHEN 0 then '0 - Transactional publication'
WHEN 1 then '1 - Snapshot publication'
WHEN 2 then '2 - Merge publication'
END AS publication_type_desc,
publication,
CASE agent_type
WHEN 1 then '1 - Snapshot Agent'
WHEN 2 then '2 - Log Reader Agent'
WHEN 3 then '3 - Distribution Agent'
WHEN 4 then '4 - Merge Agent'
WHEN 9 then '9 - Queue Reader Agent'
END AS agent_type,
agent_name,
CASE status
WHEN 1 THEN '1 - Started'
WHEN 2 THEN '2 - Succeeded'
WHEN 3 THEN '3 - In progress'
WHEN 4 THEN '4 - Idle'
WHEN 5 THEN '5 - Retrying'
WHEN 6 THEN '6 - Failed'
END AS agent_status,
RIGHT('0' + CAST(cur_latency / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((cur_latency / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(cur_latency % 60 AS VARCHAR),2) AS cur_latency,
RIGHT('0' + CAST(worst_latency / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((worst_latency / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(worst_latency % 60 AS VARCHAR),2) AS max_latency,
RIGHT('0' + CAST(best_latency / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((best_latency / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(best_latency % 60 AS VARCHAR),2) AS min_latency,
RIGHT('0' + CAST(avg_latency / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((avg_latency / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(avg_latency % 60 AS VARCHAR),2) AS avg_latency,
last_distsync AS last_time_dist_agent_run,
isagentrunningnow AS is_agent_running_now,
agentstoptime AS agent_stop_time,
CASE warning
WHEN 1 THEN 'Expiration'
WHEN 2 THEN 'Latency'
WHEN 4 THEN 'Merge expiration '
WHEN 16 THEN 'Merge slow run duration '
WHEN 32 THEN 'Merge fast run speed '
WHEN 64 THEN 'Merge slow run speed'
END AS warning,
CASE retention_period_unit
WHEN 1 THEN CAST(retention AS VARCHAR)+' Week'
WHEN 2 THEN CAST(retention AS VARCHAR)+' Month'
WHEN 3 THEN CAST(retention AS VARCHAR)+' Year'
END AS pub_retention_period,
distdb AS distribution_db
FROM distribution.dbo.MSreplication_monitordata
WHERE publisher_db = 'db1'
AND publication IN ('ALL','your_publication_name')
ORDER BY publisher,
agent_type,
publication;

Alerts for Replication

SELECT A.alert_id,
A.error_id,
A.time AS alert_time,
E.time AS error_time,
A.publisher,
A.publisher_db,
CASE A.publication_type
WHEN 0 THEN 'Snapshot'
WHEN 1 THEN 'Transactional'
WHEN 2 THEN 'Merge'
END AS publication_type_desc,
A.publication as publication_name,
A.subscriber,
A.subscriber_db,
A.article,
A.source_object,
A.destination_object,
E.error_text,
A.alert_error_text,
A.agent_id,
CASE A.agent_type
WHEN 1 THEN 'Snapshot Agent'
WHEN 2 THEN 'Log Reader Agent'
WHEN 3 THEN 'Distribution Agent'
WHEN 4 THEN 'Merge Agent'
ELSE 'Unknown'
END AS agent_type_desc,
COALESCE(S.name,L.name,D.name,M.name) AS agent_name,
E.session_id AS agent_session_id,
CASE status
WHEN 0 THEN 'Unserviced'
WHEN 1 THEN 'serviced'
END AS status_desc
FROM msdb.dbo.sysreplicationalerts AS A
LEFT JOIN distribution.dbo.MSrepl_errors AS E ON A.error_id = E.id
LEFT JOIN distribution.dbo.MSsnapshot_agents AS S ON S.id = A.agent_id
LEFT JOIN distribution.dbo.MSlogreader_agents AS L ON L.id = A.agent_id
LEFT JOIN distribution.dbo.MSdistribution_agents AS D ON D.id = A.agent_id
LEFT JOIN distribution.dbo.MSmerge_agents AS M ON M.id = A.agent_id
WHERE A.time >= GETUTCDATE()-1
AND A.publisher_db = 'db1'
AND A.publication = 'sqlpubs'
AND A.subscriber_db = 'db1sub'
ORDER BY A.alert_id DESC;

Any Pending Commands for Replication

SELECT
p.name as publisher_server,
da.publisher_db,
s.name as subscriber_server,
da.subscriber_db,
da.publication as publication,
a.destination_object as table_name,
ds.DelivCmdsInDistDB as delivered_commands_in_distribution_db,
ds.UndelivCmdsInDistDB as undelivered_commands_in_distribution_db
FROM distribution.dbo.MSdistribution_status ds
INNER JOIN distribution.dbo.MSdistribution_agents da ON da.id = ds.agent_id
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
INNER JOIN master.sys.servers s ON s.server_id = da.subscriber_id
INNER JOIN master.sys.servers p ON p.server_id = da.publisher_id
ORDER BY undelivered_commands_in_distribution_db DESC,
table_name ASC

Agents History

SELECT H.time AS message_log_time,
H.agent_id,
A.name AS agent_name,
H.runstatus,
CASE runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS runstatus_desc,
H.start_time AS job_exec_start_time,
H.duration AS session_duration_in_sec,
H.comments AS message_text,
H.current_delivery_rate AS current_delivery_rate_per_sec,
H.current_delivery_latency/1000 AS current_delivery_latency_in_sec,
H.delivery_rate AS delivery_rate_per_sec,
H.delivery_latency/1000 AS delivery_latency_in_sec,
H.delivered_transactions AS transactions_delivered_in_session,
H.delivered_commands AS commands_delivered_in_session,
H.average_commands AS average_commands_in_session,
H.total_delivered_commands, --total commands delivered since subscription was created
A.publisher_db,
A.publication,
A.subscriber_db,
H.error_id,
E.time AS error_time,
E.error_code,
E.error_text
FROM distribution.dbo.MSdistribution_history AS H
INNER JOIN distribution.dbo.MSdistribution_agents AS A ON H.agent_id = A.id
LEFT JOIN distribution.dbo.MSrepl_errors AS E ON E.id = H.error_id
WHERE H.time >= GETUTCDATE()-2
ORDER BY message_log_time desc;

SELECT H.time AS message_log_time,
H.agent_id,
A.name AS agent_name,
H.runstatus,
CASE runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS runstatus_desc,
H.start_time AS job_exec_start_time,
H.duration AS session_duration_in_sec,
H.comments AS message_text,
H.delivered_transactions AS transactions_delivered_in_session,
H.delivered_commands AS delivered_commands_per_sec,
H.delivery_rate AS delivery_rate_per_sec,
A.publisher_db,
A.publication,
H.error_id,
E.time AS error_time,
E.error_code,
E.error_text
FROM distribution.dbo.MSlogreader_history AS H
INNER JOIN distribution.dbo.MSlogreader_agents AS A ON H.agent_id = A.id
LEFT JOIN distribution.dbo.MSrepl_errors AS E ON E.id = H.error_id
WHERE H.time >= GETUTCDATE()-7
ORDER BY message_log_time desc