USE [Distribution]
GO
/****** Object: Table [dbo].[ReplicationStatus] Script Date: 6/24/2016 2:09:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ReplicationStatus](
[PublisherServername] [varchar](250) NULL,
[SubscriberServerName] [varchar](250) NULL,
[PublisherDB] [varchar](250) NULL,
[SubscriberDB] [varchar](250) NULL,
[DistributionStatus] [varchar](250) NULL,
[LogReaderStatus] [varchar](250) NULL,
[DateInserted] [datetime] NOT NULL DEFAULT (getdate())
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [Distribution]
GO
/****** Object: StoredProcedure [dbo].[CheckReplicationStatus] Script Date: 6/24/2016 1:45:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Proc [dbo].[CheckReplicationStatus]
as
BEGIN
---SQl1
BEGIN
INSERT INTO Distribution.dbo.ReplicationStatus
SELECT
'FPVI-SQL4' as Servername,
dl.subscriber_server,
dl.publisher_db,
dl.subscriber_db,
CASE dl.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 DistributionRunStatus,
CASE rl.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 LogReaderRunStatus,
GETDATE()
FROM (SELECT agent_id,
publisher_db,
Upper(ss.datasource) AS subscriber_server,
subscriber_db AS subscriber_db,
[name] AS distribution_agent,
DH.runstatus,
CASE
WHEN DH.runstatus = 1 THEN Datediff(SS, DH.TIME, Getdate())
WHEN DH.runstatus = 3 THEN DH.delivery_latency
ELSE 0
END AS distribution_latency
FROM (SELECT DHMAX.*
FROM distribution.dbo.MSdistribution_history DHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS TIME
FROM distribution.dbo.MSdistribution_history DH WITH(NOLOCK)
GROUP BY agent_id) MTDH
ON DHMAX.agent_id = MTDH.agent_id
AND DHMAX.TIME = MTDH.TIME) DH
JOIN distribution.dbo.MSdistribution_agents DA WITH(NOLOCK)
ON DH.agent_id = DA.id
JOIN MASTER.dbo.sysservers ss WITH(NOLOCK)
ON da.subscriber_id = ss.srvid) AS DL
LEFT OUTER JOIN (SELECT agent_id,
la.publisher_db,
lh.runstatus,
CASE
WHEN lh.runstatus = 1 THEN Datediff(SS, LH.TIME, Getdate())
WHEN lh.runstatus = 3 THEN lh.delivery_latency
ELSE 0
END AS reader_latency
FROM (SELECT LHMAX.*
FROM distribution.dbo.MSlogreader_history LHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS [time]
FROM distribution.dbo.MSlogreader_history WITH(NOLOCK)
GROUP BY agent_id) MTLH
ON LHMAX.agent_id = MTLH.agent_id
AND LHMAX.TIME = MTLH.TIME) LH
JOIN distribution.dbo.mslogreader_agents LA WITH(NOLOCK)
ON lH.agent_id = lA.id) AS RL
ON DL.publisher_db = RL.publisher_db
ORDER BY dl.publisher_db,
dl.distribution_agent
END
BEGIN
---sql1
INSERT INTO Distribution.dbo.ReplicationStatus
SELECT
'FPSQL1' as Servername,
dl.subscriber_server,
dl.publisher_db,
dl.subscriber_db,
CASE dl.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 DistributionRunStatus,
CASE rl.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 LogReaderRunStatus,
GETDATE()
FROM (SELECT agent_id,
publisher_db,
Upper(ss.datasource) AS subscriber_server,
subscriber_db AS subscriber_db,
[name] AS distribution_agent,
DH.runstatus,
CASE
WHEN DH.runstatus = 1 THEN Datediff(SS, DH.TIME, Getdate())
WHEN DH.runstatus = 3 THEN DH.delivery_latency
ELSE 0
END AS distribution_latency
FROM (SELECT DHMAX.*
FROM [FPMC-DIST1].distribution.dbo.MSdistribution_history DHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS TIME
FROM [FPMC-DIST1].distribution.dbo.MSdistribution_history DH WITH(NOLOCK)
GROUP BY agent_id) MTDH
ON DHMAX.agent_id = MTDH.agent_id
AND DHMAX.TIME = MTDH.TIME) DH
JOIN [FPMC-DIST1].distribution.dbo.MSdistribution_agents DA WITH(NOLOCK)
ON DH.agent_id = DA.id
JOIN [FPMC-DIST1]. MASTER.dbo.sysservers ss WITH(NOLOCK)
ON da.subscriber_id = ss.srvid) AS DL
LEFT OUTER JOIN (SELECT agent_id,
la.publisher_db,
lh.runstatus,
CASE
WHEN lh.runstatus = 1 THEN Datediff(SS, LH.TIME, Getdate())
WHEN lh.runstatus = 3 THEN lh.delivery_latency
ELSE 0
END AS reader_latency
FROM (SELECT LHMAX.*
FROM [FPMC-DIST1].distribution.dbo.MSlogreader_history LHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS [time]
FROM [FPMC-DIST1].distribution.dbo.MSlogreader_history WITH(NOLOCK)
GROUP BY agent_id) MTLH
ON LHMAX.agent_id = MTLH.agent_id
AND LHMAX.TIME = MTLH.TIME) LH
JOIN [FPMC-DIST1]. distribution.dbo.mslogreader_agents LA WITH(NOLOCK)
ON lH.agent_id = lA.id) AS RL
ON DL.publisher_db = RL.publisher_db
ORDER BY dl.publisher_db,
dl.distribution_agent
END
BEGIN
----fpvi-sql5
INSERT INTO Distribution.dbo.ReplicationStatus
SELECT
'FPVI-SQL5' as Servername,
dl.subscriber_server,
dl.publisher_db,
dl.subscriber_db,
CASE dl.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 DistributionRunStatus,
CASE rl.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 LogReaderRunStatus
,GETDATE()
FROM (SELECT agent_id,
publisher_db,
Upper(ss.datasource) AS subscriber_server,
subscriber_db AS subscriber_db,
[name] AS distribution_agent,
DH.runstatus,
CASE
WHEN DH.runstatus = 1 THEN Datediff(SS, DH.TIME, Getdate())
WHEN DH.runstatus = 3 THEN DH.delivery_latency
ELSE 0
END AS distribution_latency
FROM (SELECT DHMAX.*
FROM [fpvi-sql5].distribution.dbo.MSdistribution_history DHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS TIME
FROM [fpvi-sql5].distribution.dbo.MSdistribution_history DH WITH(NOLOCK)
GROUP BY agent_id) MTDH
ON DHMAX.agent_id = MTDH.agent_id
AND DHMAX.TIME = MTDH.TIME) DH
JOIN [fpvi-sql5].distribution.dbo.MSdistribution_agents DA WITH(NOLOCK)
ON DH.agent_id = DA.id
JOIN [fpvi-sql5]. MASTER.dbo.sysservers ss WITH(NOLOCK)
ON da.subscriber_id = ss.srvid) AS DL
LEFT OUTER JOIN (SELECT agent_id,
la.publisher_db,
lh.runstatus,
CASE
WHEN lh.runstatus = 1 THEN Datediff(SS, LH.TIME, Getdate())
WHEN lh.runstatus = 3 THEN lh.delivery_latency
ELSE 0
END AS reader_latency
FROM (SELECT LHMAX.*
FROM [fpvi-sql5].distribution.dbo.MSlogreader_history LHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS [time]
FROM [fpvi-sql5].distribution.dbo.MSlogreader_history WITH(NOLOCK)
GROUP BY agent_id) MTLH
ON LHMAX.agent_id = MTLH.agent_id
AND LHMAX.TIME = MTLH.TIME) LH
JOIN [fpvi-sql5]. distribution.dbo.mslogreader_agents LA WITH(NOLOCK)
ON lH.agent_id = lA.id) AS RL
ON DL.publisher_db = RL.publisher_db
ORDER BY dl.publisher_db,
dl.distribution_agent
END
BEGIN
---fpvisql6
INSERT INTO Distribution.dbo.ReplicationStatus
SELECT
'FPVI-SQL6' as Servername,
dl.subscriber_server,
dl.publisher_db,
dl.subscriber_db,
CASE dl.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 DistributionRunStatus,
CASE rl.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 LogReaderRunStatus
,GETDATE()
FROM (SELECT agent_id,
publisher_db,
Upper(ss.datasource) AS subscriber_server,
subscriber_db AS subscriber_db,
[name] AS distribution_agent,
DH.runstatus,
CASE
WHEN DH.runstatus = 1 THEN Datediff(SS, DH.TIME, Getdate())
WHEN DH.runstatus = 3 THEN DH.delivery_latency
ELSE 0
END AS distribution_latency
FROM (SELECT DHMAX.*
FROM [fpvi-sql6].distribution.dbo.MSdistribution_history DHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS TIME
FROM [fpvi-sql6].distribution.dbo.MSdistribution_history DH WITH(NOLOCK)
GROUP BY agent_id) MTDH
ON DHMAX.agent_id = MTDH.agent_id
AND DHMAX.TIME = MTDH.TIME) DH
JOIN [fpvi-sql6].distribution.dbo.MSdistribution_agents DA WITH(NOLOCK)
ON DH.agent_id = DA.id
JOIN [fpvi-sql6]. MASTER.dbo.sysservers ss WITH(NOLOCK)
ON da.subscriber_id = ss.srvid) AS DL
LEFT OUTER JOIN (SELECT agent_id,
la.publisher_db,
lh.runstatus,
CASE
WHEN lh.runstatus = 1 THEN Datediff(SS, LH.TIME, Getdate())
WHEN lh.runstatus = 3 THEN lh.delivery_latency
ELSE 0
END AS reader_latency
FROM (SELECT LHMAX.*
FROM [fpvi-sql6].distribution.dbo.MSlogreader_history LHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS [time]
FROM [fpvi-sql6].distribution.dbo.MSlogreader_history WITH(NOLOCK)
GROUP BY agent_id) MTLH
ON LHMAX.agent_id = MTLH.agent_id
AND LHMAX.TIME = MTLH.TIME) LH
JOIN [fpvi-sql6]. distribution.dbo.mslogreader_agents LA WITH(NOLOCK)
ON lH.agent_id = lA.id) AS RL
ON DL.publisher_db = RL.publisher_db
ORDER BY dl.publisher_db,
dl.distribution_agent
END
END
GO