1746 lines
160 KiB
Transact-SQL
1746 lines
160 KiB
Transact-SQL
USE [HCITools]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[Replication_Check] Script Date: 16.04.2024 07:40:11 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
ALTER PROCEDURE [dbo].[Replication_Check]
|
||
@in_debug int = null,
|
||
@in_CheckType smallint = 0,
|
||
@in_Email smallint
|
||
AS
|
||
/*=============================================================================
|
||
|
||
Explication du traitement realise par la SP
|
||
-------------------------------------------
|
||
Cette SP sert à vérifier un ensemble de points critiques sur la réplication
|
||
|
||
Contexte d'utilisation
|
||
----------------------
|
||
Cette SP est appelée par le job DR92160 - Replication Check
|
||
|
||
Parametres
|
||
----------
|
||
@in_debug : non utilisé
|
||
@in_CheckType : 0 = Toutes les vérifications
|
||
1 = Check des jobs de maintenance de la réplication
|
||
2 = Check des articles présents dans la publication
|
||
3 = Check des linked servers
|
||
4 = Liste des postes non abonnées (manquants)
|
||
5 = Check de la disponibilité du share
|
||
6 = Check du mode d'authentification des souscriptions
|
||
7 = Check de l'etat des souscriptions pour chaque poste
|
||
8 = Check All Agents Status
|
||
9 = Check jobs de réplication à double
|
||
10 = Vérifications des commandes non distribuées
|
||
@in_Email : 1 = Envoie d'email à HCI_DBA
|
||
2 = Pas d'email, uniquement enregisté dans la base de données
|
||
|
||
Creation : 28.08.17 / FLA
|
||
|
||
Modification : 12.07.17 / FLA Ajout de la gestion des environnements pour executer que sur N+1 et PROD
|
||
24.04.18 / RTC Ajout du test des commandes non distribuées et mise à jour du schedule
|
||
21.11.18 / SPE Ajout d'une vérification d'état des linked server des postes (job replication stoppé depuis plus de 2 minutes)
|
||
20.04.20 / SPE Create alert for monitoring replication after MSI deployment
|
||
19.05.20 / SPE Envoi de mail seulement si erreur critique
|
||
04.09.20 / RTC Adapt sp after shared file config changes
|
||
29.10.20 / FLA Manage agent error with more than one replication / manage others errors
|
||
23.09.20 / RTC TFS : 62550 Update version check to avoid wrong error message.
|
||
26.02.21 / SPE TFS : 63953 Adapt stored procedure [dbo].[CheckActivePosPublication] to new settings
|
||
01.04.21 / SPE TFS : 60931 Remove undist commands fix after 2014
|
||
04.05.21 / RTC TFS : 64808 Update check D91030 - Backup ActivePos_Read
|
||
04.05.21 / RTC TFS : 65443 Update check replication performance
|
||
17.03.22 / FLA : Change DBA mail
|
||
24.11.22 / TSC : OCTPDBA-412 Replace ArizonaCash.dbo.pr_point_of_sales with arizona.dbo.point_of_sale
|
||
27.01.23 / SPE : OCTPDBA-478: Resolve SQL 2019 compatibility issue when getting SQL replication subscription info
|
||
03.02.23 / RTC : OCTPDBA-485: Remove check for windows authentication in the replication monitoring
|
||
03.06.29 / RTC : OCTPDBA-666: Update replication jobs check
|
||
16.04.24 / TSC : TPDT-456 Remove job "'D91030 - Backup ActivePos_Read" from the checks for job present or disabled
|
||
=============================================================================*/
|
||
|
||
SET NOCOUNT ON;
|
||
|
||
/*------------------- Declaration des variables --------------------*/
|
||
DECLARE @result_sp int,
|
||
@CheckJob int,
|
||
@Status int,
|
||
@Rate int,
|
||
@AlertID int,
|
||
@errfound bit,
|
||
@isProd bit,
|
||
@countok tinyint,
|
||
@countko tinyint,
|
||
@counttot tinyint,
|
||
@percent tinyint,
|
||
@LksrvPos sysname,
|
||
@CheckDate datetime2(0),
|
||
@tsql nvarchar(max),
|
||
@Message varchar(max),
|
||
@query varchar(max),
|
||
@fileName varchar(400),
|
||
@errmsg varchar(255),
|
||
@JobName varchar(100),
|
||
@server varchar(100),
|
||
@Article varchar(50),
|
||
@POS varchar(50),
|
||
@dbVersion varchar(20),
|
||
@versionNumberSrv varchar(17),
|
||
@versionNumberPos varchar(17),
|
||
@ouCode varchar(15),
|
||
@distribution_db sysname,
|
||
@publication sysname,
|
||
@publisher_db sysname,
|
||
@subscriber sysname,
|
||
@Agent_type sysname,
|
||
@Agent_id int,
|
||
@EndTime datetime2(0),
|
||
@comments varchar(250),
|
||
@TableSys sysname,
|
||
@AlertKey varchar(20)
|
||
|
||
/*------------ Affectation des parametres aux variables ------------*/
|
||
SET @message = ''
|
||
SET @result_sp = 0
|
||
SET @errfound = 0
|
||
SET @CheckDate = getdate()
|
||
|
||
SELECT @counttot = count(*)
|
||
FROM [Arizona].[dbo].[Bmc_application_key] [bak] WITH (NOLOCK)
|
||
JOIN [Arizona].[dbo].[Bmc_application_default] [bad] WITH (NOLOCK)
|
||
ON [bad].[BAPD_bmc_application_key] = [bak].[Bmc_application_key_ID]
|
||
JOIN [Arizona].[dbo].[Organizational_unit] OUA WITH (NOLOCK)
|
||
ON OUA.[Organizational_unit_ID] = [bad].[BAPD_value]
|
||
JOIN [Arizona].[dbo].[Organizational_unit] [ou] WITH(NOLOCK)
|
||
ON [ou].[OU_code] = [OUA].[OU_code]
|
||
JOIN [Arizona].[dbo].[Point_of_sale] [pos]
|
||
ON [ou].[Organizational_unit_ID] = [pos].[POS_organizational_unit]
|
||
WHERE [bak].[BAPK_key] = 'cvCurrentOrganizationalUnit'
|
||
AND [pos].[POS_active] = 1
|
||
AND [pos].[POS_number] <= 39
|
||
AND [pos].[POS_type] IN (1,2) /* 1=POS, 2=Backoffice, 3=SAFE */
|
||
--AND OU.CROU_locally_used = 1
|
||
|
||
/*-------------------------- Traitement ---------------------------*/
|
||
IF @in_Email NOT IN (0, 1)
|
||
BEGIN
|
||
SET @errmsg = 'Probleme avec le paramètre @in_Email';
|
||
RAISERROR (@errmsg,16,1);
|
||
END
|
||
|
||
/* Check replications only if POS are registred on this pharmacy */
|
||
IF @counttot > 0
|
||
BEGIN
|
||
|
||
/* Exclude all N+2 */
|
||
IF EXISTS (SELECT *
|
||
FROM [master].[cfg].[InstanceContext]
|
||
WHERE Type in ('VALI','ACCE','PROD')
|
||
AND Business in ('TPCENT','TPUCENT','TPPHAR')
|
||
AND Behavior = 'Enable')
|
||
BEGIN
|
||
|
||
/* Alerts are critical or warning only if PROD pharmacie */
|
||
IF EXISTS (SELECT 1 FROM [master].[cfg].[InstanceContext] WHERE Type = 'PROD')
|
||
BEGIN
|
||
SET @isProd = 1
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
SET @isProd = 0
|
||
END
|
||
|
||
CREATE TABLE #LinkedServerPb(posname varchar(255))
|
||
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
/* 1: Check des jobs de maintenance de la réplication */
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
|
||
BEGIN TRY
|
||
IF (@in_CheckType = 1 or @in_CheckType = 0)
|
||
BEGIN
|
||
|
||
SET @message = ''
|
||
|
||
SELECT @CheckJob = COUNT(*) from msdb.dbo.sysjobs with (nolock) where name = 'Agent history clean up: distribution' AND enabled = 1
|
||
IF @CheckJob <> 1
|
||
SET @message = @message + '- Agent history clean up: distribution'+CHAR(13)+CHAR(10)
|
||
|
||
SELECT @CheckJob = COUNT(*) from msdb.dbo.sysjobs with (nolock) where name = 'Distribution clean up: distribution' AND enabled = 1
|
||
IF @CheckJob <> 1
|
||
SET @message = @message + '- Distribution clean up: distribution'+CHAR(13)+CHAR(10)
|
||
|
||
SELECT @CheckJob = COUNT(*) from msdb.dbo.sysjobs with (nolock) where name = 'Expired subscription clean up' AND enabled = 1
|
||
IF @CheckJob <> 1
|
||
SET @message = @message + '- Expired subscription clean up'+CHAR(13)+CHAR(10)
|
||
|
||
SELECT @CheckJob = COUNT(*) from msdb.dbo.sysjobs with (nolock) where name = 'Reinitialize subscriptions having data validation failures' AND enabled = 1
|
||
IF @CheckJob <> 1
|
||
SET @message = @message + '- Reinitialize subscriptions having data validation failures'+CHAR(13)+CHAR(10)
|
||
|
||
SELECT @CheckJob = COUNT(*) from msdb.dbo.sysjobs with (nolock) where name = 'Replication agents checkup' AND enabled = 1
|
||
IF @CheckJob <> 1
|
||
SET @message = @message + '- Replication agents checkup'+CHAR(13)+CHAR(10)
|
||
|
||
--SELECT @CheckJob = COUNT(*) from msdb.dbo.sysjobs with (nolock) where name = 'D91030 - Backup ActivePos_Read'
|
||
--IF @CheckJob <> 1
|
||
-- SET @message = @message + '- D91030 - Backup ActivePos_Read'+CHAR(13)+CHAR(10)
|
||
|
||
SELECT @CheckJob = COUNT(*) from msdb.dbo.sysjobs with (nolock) where name = 'D00480 - ActivePos_read Snapshot' AND enabled = 1
|
||
IF @CheckJob <> 1
|
||
SET @message = @message + '- D00480 - ActivePos_read Snapshot'+CHAR(13)+CHAR(10)
|
||
|
||
SELECT @CheckJob = COUNT(*) from msdb.dbo.sysjobs with (nolock) where name = 'DR00470 - ActivePos_read Log reader' AND enabled = 1
|
||
IF @CheckJob <> 1
|
||
SET @message = @message + '- DR00470 - ActivePos_read Log reader'+CHAR(13)+CHAR(10)
|
||
|
||
IF @message <> ''
|
||
BEGIN
|
||
SET @errfound = 1
|
||
SET @message = 'Jobs de maintenance de la réplication manquants ou désactivés : '+CHAR(13)+CHAR(10)+@message
|
||
SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'MaintJobs'
|
||
IF @isProd = 1
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 1, @CheckDate, @message)
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 0, @CheckDate, @message)
|
||
END
|
||
|
||
/* S'il y a une erreur, on envoie un mail aux DBA */
|
||
IF @in_Email = 1
|
||
BEGIN
|
||
EXEC [dbo].[aps_Send_Mail_with_template]
|
||
@in_param_varchar_2 = 'DBA_operator;',
|
||
@in_job_type = 3, /* 3 = warning */
|
||
@in_param_varchar_3 = @Message
|
||
END
|
||
END
|
||
END
|
||
END TRY
|
||
BEGIN CATCH
|
||
/* Traitement des erreurs (avec RaiseError) */
|
||
EXEC dbo.get_Error_Info @in_RaiseError = 1
|
||
END CATCH
|
||
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
/* 2: Check des articles présents dans la publication */
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
|
||
BEGIN TRY
|
||
IF (@in_CheckType = 2 or @in_CheckType = 0)
|
||
BEGIN
|
||
|
||
SET @message = ''
|
||
|
||
DECLARE c_Articles CURSOR LOCAL FORWARD_ONLY STATIC FOR
|
||
SELECT NAME
|
||
FROM ActivePos_read.sys.objects AROT WITH (NOLOCK)
|
||
WHERE type = 'U'
|
||
AND is_ms_shipped = 0
|
||
AND NOT EXISTS (SELECT name
|
||
FROM ActivePos_read.sys.objects AROA WITH (NOLOCK)
|
||
WHERE type = 'U'
|
||
AND is_published = 1
|
||
AND is_ms_shipped = 0
|
||
AND AROT.name = AROA.name)
|
||
|
||
OPEN c_Articles
|
||
FETCH NEXT FROM c_Articles
|
||
INTO @Article
|
||
|
||
WHILE @@fetch_status <> -1
|
||
BEGIN
|
||
|
||
SET @message = @message + '- '+@Article+CHAR(13)+CHAR(10)
|
||
|
||
FETCH NEXT FROM c_Articles
|
||
INTO @Article
|
||
END
|
||
|
||
IF @message <> ''
|
||
BEGIN
|
||
SET @errfound = 1
|
||
SET @message = 'Liste des articles manquants dans la publication :'+CHAR(13)+CHAR(10)+@message
|
||
SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'MissArt'
|
||
IF @isProd = 1
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 2, @CheckDate, @message)
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 0, @CheckDate, @message)
|
||
END
|
||
|
||
/* S'il y a une erreur, on envoie un mail aux DBA */
|
||
IF @in_Email = 1
|
||
BEGIN
|
||
EXEC [dbo].[aps_Send_Mail_with_template]
|
||
@in_param_varchar_2 = 'DBA_operator;',
|
||
@in_job_type = 3, /* 3 = warning */
|
||
@in_param_varchar_3 = @Message
|
||
END
|
||
END
|
||
|
||
CLOSE c_Articles
|
||
DEALLOCATE c_Articles
|
||
|
||
END
|
||
END TRY
|
||
BEGIN CATCH
|
||
/* Traitement des erreurs (avec RaiseError) */
|
||
EXEC dbo.get_Error_Info @in_RaiseError = 1
|
||
END CATCH
|
||
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
/* 3: Check des linked servers */
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
|
||
BEGIN TRY
|
||
IF (@in_CheckType = 3 or @in_CheckType = 0)
|
||
BEGIN
|
||
|
||
SET @message = ''
|
||
SET @countok = 0
|
||
SET @countko = 0
|
||
SET @percent = 0
|
||
|
||
IF EXISTS ( SELECT *
|
||
FROM distribution.sys.tables
|
||
WHERE name = 'msreplservers')
|
||
BEGIN
|
||
EXEC ('SELECT POS.[POS_hostname]
|
||
INTO ##POS
|
||
FROM arizona.dbo.bmc_application_key WITH (NOLOCK)
|
||
JOIN arizona.dbo.bmc_application_default WITH (NOLOCK)
|
||
ON BAPD_bmc_application_key = Bmc_application_key_ID
|
||
JOIN arizona.dbo.organizational_unit OUA WITH (NOLOCK)
|
||
ON [OUA].[Organizational_unit_ID] = bapd_value
|
||
JOIN [Arizona].[dbo].[Organizational_unit] [ou] WITH(NOLOCK)
|
||
ON [ou].[OU_code] = [OUA].[OU_code]
|
||
JOIN [Arizona].[dbo].[Point_of_sale] [pos]
|
||
ON [ou].[Organizational_unit_ID] = [pos].[POS_organizational_unit]
|
||
WHERE BAPK_key = ''cvCurrentOrganizationalUnit''
|
||
AND [pos].[POS_active] = 1
|
||
AND [pos].[POS_number] <= 39
|
||
AND [pos].[POS_type] IN (1,2) /* 1=POS, 2=Backoffice, 3=SAFE */
|
||
--AND OU.CROU_locally_used = 1
|
||
AND EXISTS (select distinct [ss].[srvname]
|
||
FROM distribution.dbo.MSreplservers ss WITH (NOLOCK)
|
||
INNER JOIN distribution.dbo.MSsubscriptions ms WITH (NOLOCK)
|
||
ON [ss].[srvid] = [ms].[subscriber_id]
|
||
WHERE [ss].[srvname] = POS.[POS_hostname])');
|
||
END;
|
||
ELSE
|
||
BEGIN
|
||
EXEC ('SELECT POS.[POS_hostname]
|
||
INTO ##POS
|
||
FROM arizona.dbo.bmc_application_key WITH (NOLOCK)
|
||
JOIN arizona.dbo.bmc_application_default WITH (NOLOCK)
|
||
ON BAPD_bmc_application_key = Bmc_application_key_ID
|
||
JOIN arizona.dbo.organizational_unit OUA WITH (NOLOCK)
|
||
ON [OUA].[Organizational_unit_ID] = bapd_value
|
||
JOIN [Arizona].[dbo].[Organizational_unit] [ou] WITH(NOLOCK)
|
||
ON [ou].[OU_code] = [OUA].[OU_code]
|
||
JOIN [Arizona].[dbo].[Point_of_sale] [pos]
|
||
ON [ou].[Organizational_unit_ID] = [pos].[POS_organizational_unit]
|
||
WHERE BAPK_key = ''cvCurrentOrganizationalUnit''
|
||
AND [pos].[POS_active] = 1
|
||
AND [pos].[POS_number] <= 39
|
||
AND [pos].[POS_type] IN (1,2) /* 1=POS, 2=Backoffice, 3=SAFE */
|
||
--AND OU.CROU_locally_used = 1
|
||
AND EXISTS (select distinct [ss].[name]
|
||
FROM sys.servers ss WITH (NOLOCK)
|
||
INNER JOIN distribution.dbo.MSsubscriptions ms WITH (NOLOCK)
|
||
ON [ss].[server_id] = [ms].[subscriber_id]
|
||
WHERE [ss].[name] = POS.[POS_hostname])');
|
||
END;
|
||
|
||
DECLARE c_Subscriptions CURSOR LOCAL FORWARD_ONLY STATIC FOR
|
||
SELECT *
|
||
FROM ##POS
|
||
|
||
SELECT TOP 1 @versionNumberSrv = [VersionNumber] FROM [ActivePos_read].[upd].[DatabaseHistory] ORDER BY [DatabaseHistoryId] DESC
|
||
|
||
OPEN c_Subscriptions
|
||
|
||
FETCH NEXT FROM c_Subscriptions
|
||
INTO @POS
|
||
|
||
WHILE @@fetch_status <> -1
|
||
BEGIN
|
||
|
||
SET @LksrvPos = @POS;
|
||
BEGIN TRY
|
||
EXEC @result_sp = sys.sp_testlinkedserver @LksrvPos
|
||
END TRY
|
||
BEGIN CATCH
|
||
SET @result_sp = sign(@@error);
|
||
END CATCH;
|
||
|
||
IF @result_sp <> 0
|
||
BEGIN
|
||
SET @countko = @countko + 1
|
||
SET @message = @message + '- '+@POS+ ' - Timeout' +CHAR(13)+CHAR(10)
|
||
INSERT INTO #LinkedServerPb(posname)
|
||
VALUES(@POS)
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
SET @countok = @countok + 1
|
||
END
|
||
|
||
FETCH NEXT FROM c_Subscriptions
|
||
INTO @POS
|
||
END
|
||
|
||
IF @message <> ''
|
||
BEGIN
|
||
|
||
SET @errfound = 1
|
||
SET @message = 'Liste des linked servers avec des problèmes de connexion:'+CHAR(13)+CHAR(10)+@message
|
||
|
||
SET @countok = @counttot - @countko
|
||
|
||
SELECT @percent = (@countko * 100 / (@counttot))
|
||
SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'POSLinked'
|
||
|
||
IF @percent <= 29
|
||
BEGIN
|
||
IF @isProd = 1
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 1, @CheckDate, @message)
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 0, @CheckDate, @message)
|
||
END
|
||
END
|
||
IF @percent >= 30
|
||
BEGIN
|
||
IF @isProd = 1
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 2, @CheckDate, @message)
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 0, @CheckDate, @message)
|
||
END
|
||
END
|
||
|
||
/* S'il y a une erreur, on envoie un mail aux DBA */
|
||
IF @in_Email = 1
|
||
BEGIN
|
||
IF @percent >= 30 /* Envoi de mail seulement si erreur critique */
|
||
BEGIN
|
||
EXEC [dbo].[aps_Send_Mail_with_template]
|
||
@in_param_varchar_2 = 'DBA_operator;',
|
||
@in_job_type = 3, /* 3 = warning */
|
||
@in_param_varchar_3 = @Message
|
||
END
|
||
END
|
||
END
|
||
|
||
CLOSE c_Subscriptions
|
||
DEALLOCATE c_Subscriptions
|
||
|
||
DROP TABLE ##POS;
|
||
|
||
END
|
||
END TRY
|
||
BEGIN CATCH
|
||
/* Traitement des erreurs (avec RaiseError) */
|
||
EXEC dbo.get_Error_Info @in_RaiseError = 1
|
||
END CATCH
|
||
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
/* 4: Liste des postes non abonnées (manquants) */
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
|
||
BEGIN TRY
|
||
IF (@in_CheckType = 4 or @in_CheckType = 0)
|
||
BEGIN
|
||
|
||
SET @message = ''
|
||
SET @countok = 0
|
||
SET @countko = 0
|
||
|
||
IF EXISTS ( SELECT *
|
||
FROM distribution.sys.tables
|
||
WHERE name = 'msreplservers')
|
||
BEGIN
|
||
EXEC ('SELECT POS.[POS_hostname]
|
||
INTO ##POS
|
||
FROM arizona.dbo.bmc_application_key WITH (NOLOCK)
|
||
JOIN arizona.dbo.bmc_application_default WITH (NOLOCK)
|
||
ON BAPD_bmc_application_key = Bmc_application_key_ID
|
||
JOIN arizona.dbo.organizational_unit OUA WITH (NOLOCK)
|
||
ON [OUA].[Organizational_unit_ID] = bapd_value
|
||
JOIN [Arizona].[dbo].[Organizational_unit] [ou] WITH(NOLOCK)
|
||
ON [ou].[OU_code] = [OUA].[OU_code]
|
||
JOIN [Arizona].[dbo].[Point_of_sale] [pos]
|
||
ON [ou].[Organizational_unit_ID] = [pos].[POS_organizational_unit]
|
||
WHERE BAPK_key = ''cvCurrentOrganizationalUnit''
|
||
AND [pos].[POS_active] = 1
|
||
AND [pos].[POS_number] <= 39
|
||
AND [pos].[POS_type] IN (1,2) /* 1=POS, 2=Backoffice, 3=SAFE */
|
||
--AND OU.CROU_locally_used = 1
|
||
AND NOT EXISTS (select distinct [ss].[srvname]
|
||
FROM distribution.dbo.MSreplservers ss WITH (NOLOCK)
|
||
INNER JOIN distribution.dbo.MSsubscriptions ms WITH (NOLOCK)
|
||
ON [ss].[srvid] = [ms].[subscriber_id]
|
||
WHERE [ss].[srvname] = POS.[POS_hostname])');
|
||
END;
|
||
ELSE
|
||
BEGIN
|
||
EXEC ('SELECT POS.[POS_hostname]
|
||
INTO ##POS
|
||
FROM arizona.dbo.bmc_application_key WITH (NOLOCK)
|
||
JOIN arizona.dbo.bmc_application_default WITH (NOLOCK)
|
||
ON BAPD_bmc_application_key = Bmc_application_key_ID
|
||
JOIN arizona.dbo.organizational_unit OUA WITH (NOLOCK)
|
||
ON [OUA].[Organizational_unit_ID] = bapd_value
|
||
JOIN [Arizona].[dbo].[Organizational_unit] [ou] WITH(NOLOCK)
|
||
ON [ou].[OU_code] = [OUA].[OU_code]
|
||
JOIN [Arizona].[dbo].[Point_of_sale] [pos]
|
||
ON [ou].[Organizational_unit_ID] = [pos].[POS_organizational_unit]
|
||
WHERE BAPK_key = ''cvCurrentOrganizationalUnit''
|
||
AND [pos].[POS_active] = 1
|
||
AND [pos].[POS_number] <= 39
|
||
AND [pos].[POS_type] IN (1,2) /* 1=POS, 2=Backoffice, 3=SAFE */
|
||
--AND OU.CROU_locally_used = 1
|
||
AND NOT EXISTS (select distinct [ss].[name]
|
||
FROM sys.servers ss WITH (NOLOCK)
|
||
INNER JOIN distribution.dbo.MSsubscriptions ms WITH (NOLOCK)
|
||
ON [ss].[server_id] = [ms].[subscriber_id]
|
||
WHERE [ss].[name] = POS.[POS_hostname])');
|
||
END;
|
||
|
||
DECLARE c_Subscriptions CURSOR LOCAL FORWARD_ONLY STATIC FOR
|
||
SELECT *
|
||
FROM ##POS
|
||
|
||
SELECT TOP 1 @versionNumberSrv = [VersionNumber] FROM [ActivePos_write].[upd].[DatabaseHistory] ORDER BY [DatabaseHistoryId] DESC
|
||
|
||
OPEN c_Subscriptions
|
||
|
||
FETCH NEXT FROM c_Subscriptions
|
||
INTO @POS
|
||
|
||
WHILE @@fetch_status <> -1
|
||
BEGIN
|
||
|
||
SET @LksrvPos = @POS;
|
||
BEGIN TRY
|
||
IF EXISTS(SELECT 1 FROM #LinkedServerPb WHERE posname = @POS)
|
||
BEGIN
|
||
SET @result_sp = 1
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
EXEC @result_sp = sys.sp_testlinkedserver @LksrvPos
|
||
END
|
||
END TRY
|
||
BEGIN CATCH
|
||
SET @result_sp = sign(@@error);
|
||
END CATCH;
|
||
IF @result_sp = 0
|
||
BEGIN
|
||
SET @tsql = N'SELECT TOP 1 @versionNumberPos = [VersionNumber] FROM [' + @POS + '].[ActivePos_write].[upd].[DatabaseHistory] order by [DatabaseHistoryId] desc'
|
||
EXEC [sys].[sp_executesql] @tsql, N'@versionNumberPos varchar(17) OUTPUT', @versionNumberPos OUTPUT
|
||
|
||
IF @versionNumberSrv != @versionNumberPos
|
||
BEGIN
|
||
SET @message = @message + '- '+@POS+ ' Version missmatch: Pos ' +@versionNumberPos + '<> Server ' + @versionNumberSrv +CHAR(13)+CHAR(10)
|
||
SET @countko = @countko + 1
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
SET @message = @message + '- '+@POS+ ' Version OK' +CHAR(13)+CHAR(10)
|
||
SET @countok = @countok + 1
|
||
END
|
||
END
|
||
|
||
FETCH NEXT FROM c_Subscriptions
|
||
INTO @POS
|
||
END
|
||
|
||
IF @message <> ''
|
||
BEGIN
|
||
|
||
SET @errfound = 1
|
||
SET @message = 'Liste des postes non abonnés à la publication :'+CHAR(13)+CHAR(10)+@message
|
||
|
||
SELECT @percent = (@countko * 100 / (@counttot))
|
||
SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'SubMiss'
|
||
|
||
|
||
IF @percent <= 29
|
||
BEGIN
|
||
IF @isProd = 1
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 1, @CheckDate, @message)
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 0, @CheckDate, @message)
|
||
END
|
||
END
|
||
IF @percent >= 30
|
||
BEGIN
|
||
IF @isProd = 1
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 2, @CheckDate, @message)
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 0, @CheckDate, @message)
|
||
END
|
||
END
|
||
|
||
/* S'il y a une erreur, on envoie un mail aux DBA */
|
||
IF @in_Email = 1
|
||
BEGIN
|
||
EXEC [dbo].[aps_Send_Mail_with_template]
|
||
@in_param_varchar_2 = 'DBA_operator;',
|
||
@in_job_type = 3, /* 3 = warning */
|
||
@in_param_varchar_3 = @Message
|
||
END
|
||
END
|
||
|
||
CLOSE c_Subscriptions
|
||
DEALLOCATE c_Subscriptions
|
||
|
||
DROP TABLE ##POS;
|
||
|
||
END
|
||
END TRY
|
||
BEGIN CATCH
|
||
/* Traitement des erreurs (avec RaiseError) */
|
||
EXEC dbo.get_Error_Info @in_RaiseError = 1
|
||
END CATCH
|
||
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
/* 5: Check de la disponibilité du share */
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
|
||
BEGIN TRY
|
||
IF (@in_CheckType = 5 or @in_CheckType = 0)
|
||
BEGIN
|
||
|
||
SET @message = ''
|
||
|
||
SELECT @dbVersion = ActivePos_write.upd.DatabaseVersion()
|
||
|
||
SELECT TOP 1 @ouCode = ou_code
|
||
FROM ActivePos_server.dbo.GetCurrentOU()
|
||
|
||
SELECT @fileName = CONVERT(VARCHAR(400),SettingValue) + '\ActivePos_read.' + @dbVersion + '.bak'
|
||
FROM ActivePos_server.dbo.Settings WITH (NOLOCK)
|
||
WHERE SettingId = 'Values.Pharmacy.DbInitializationBackupPath'
|
||
|
||
IF @fileName is null
|
||
BEGIN
|
||
SELECT @fileName = CONVERT(VARCHAR(400), SettingValue) + '\ActivePos_read.' + @dbVersion + '.bak'
|
||
FROM ActiveSystemServer.cfg.Settings WITH (NOLOCK)
|
||
WHERE SettingId = 'Values.Modules.Replication.DbInitializationBackupPath'
|
||
END
|
||
|
||
SELECT @result_sp = HCITools.dbo.aps_File_Exists(@filename)
|
||
|
||
IF @result_sp <> 1
|
||
BEGIN
|
||
|
||
SET @errfound = 1
|
||
SET @message = 'Share Replinibackup injoignable pour la réplication SQL'
|
||
SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'Replini'
|
||
IF @isProd = 1
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 1, @CheckDate, @message)
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 0, @CheckDate, @message)
|
||
END
|
||
|
||
/* S'il y a une erreur, on envoie un mail aux DBA */
|
||
IF @in_Email = 1
|
||
BEGIN
|
||
EXEC [dbo].[aps_Send_Mail_with_template]
|
||
@in_param_varchar_2 = 'DBA_operator;',
|
||
@in_job_type = 3, /* 3 = warning */
|
||
@in_param_varchar_3 = @Message
|
||
END
|
||
END
|
||
|
||
END
|
||
END TRY
|
||
BEGIN CATCH
|
||
/* Traitement des erreurs (avec RaiseError) */
|
||
EXEC dbo.get_Error_Info @in_RaiseError = 1
|
||
END CATCH
|
||
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
/* 7: Check de l'etat des souscriptions pour chaque poste */
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
|
||
BEGIN TRY
|
||
IF (@in_CheckType = 7 or @in_CheckType = 0)
|
||
BEGIN
|
||
|
||
SET @message = ''
|
||
|
||
SET @countok = 0
|
||
SET @countko = 0
|
||
SET @percent = 0
|
||
|
||
IF EXISTS ( SELECT *
|
||
FROM distribution.sys.tables
|
||
WHERE name = 'msreplservers')
|
||
BEGIN
|
||
EXEC ('SELECT DISTINCT [Sub].[srvname] INTO ##POS FROM distribution.dbo.MSSubscriptions s
|
||
JOIN distribution.dbo.MSreplservers Sub
|
||
ON [Sub].[srvid] = [s].[subscriber_id]
|
||
WHERE [s].[status] <> 2
|
||
ORDER BY [Sub].[srvname]');
|
||
END;
|
||
ELSE
|
||
BEGIN
|
||
EXEC ('SELECT DISTINCT [Sub].[name] INTO ##POS FROM distribution.dbo.MSSubscriptions s
|
||
JOIN master.sys.servers Sub
|
||
ON [Sub].[server_id] = [s].[subscriber_id]
|
||
WHERE [s].[status] <> 2
|
||
ORDER BY [Sub].[name]');
|
||
END;
|
||
|
||
DECLARE c_Subscriptions3 CURSOR LOCAL FORWARD_ONLY STATIC FOR
|
||
SELECT * FROM ##POS
|
||
|
||
SELECT TOP 1 @versionNumberSrv = [VersionNumber] FROM [ActivePos_read].[upd].[DatabaseHistory] ORDER BY [DatabaseHistoryId] DESC
|
||
|
||
OPEN c_Subscriptions3
|
||
|
||
FETCH NEXT FROM c_Subscriptions3
|
||
INTO @POS
|
||
|
||
WHILE @@fetch_status <> -1
|
||
BEGIN
|
||
|
||
SET @LksrvPos = @POS;
|
||
BEGIN TRY
|
||
IF EXISTS(SELECT 1 FROM #LinkedServerPb WHERE posname = @POS)
|
||
BEGIN
|
||
SET @result_sp = 1
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
EXEC @result_sp = sys.sp_testlinkedserver @LksrvPos
|
||
END
|
||
END TRY
|
||
BEGIN CATCH
|
||
SET @result_sp = sign(@@error);
|
||
END CATCH;
|
||
IF @result_sp = 0
|
||
BEGIN
|
||
SET @tsql = N'SELECT TOP 1 @versionNumberPos = [VersionNumber] FROM [' + @POS + '].[ActivePos_read].[upd].[DatabaseHistory] order by [DatabaseHistoryId] desc'
|
||
|
||
EXEC [sys].[sp_executesql] @tsql, N'@versionNumberPos varchar(17) OUTPUT', @versionNumberPos OUTPUT
|
||
|
||
IF @versionNumberSrv != @versionNumberPos
|
||
BEGIN
|
||
SET @message = @message + '- '+@POS+ ' Version missmatch' +CHAR(13)+CHAR(10)
|
||
SET @countko = @countko + 1
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
SET @message = @message + '- '+@POS+ ' Version OK' +CHAR(13)+CHAR(10)
|
||
SET @countok = @countok + 1
|
||
END
|
||
END
|
||
|
||
FETCH NEXT FROM c_Subscriptions3
|
||
INTO @POS
|
||
END
|
||
|
||
IF @message <> ''
|
||
BEGIN
|
||
|
||
SET @errfound = 1
|
||
SET @message = 'Liste des postes avec un problème de souscription :'+CHAR(13)+CHAR(10)+@message
|
||
|
||
SELECT @percent = (@countko * 100 / (@counttot))
|
||
SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'SubCheck'
|
||
|
||
IF @percent <= 29
|
||
BEGIN
|
||
IF @isProd = 1
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 1, @CheckDate, @message)
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 0, @CheckDate, @message)
|
||
END
|
||
END
|
||
IF @percent >= 30
|
||
BEGIN
|
||
IF @isProd = 1
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 2, @CheckDate, @message)
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 0, @CheckDate, @message)
|
||
END
|
||
END
|
||
|
||
/* S'il y a une erreur, on envoie un mail aux DBA */
|
||
IF @in_Email = 1
|
||
BEGIN
|
||
EXEC [dbo].[aps_Send_Mail_with_template]
|
||
@in_param_varchar_2 = 'DBA_operator;',
|
||
@in_job_type = 3, /* 3 = warning */
|
||
@in_param_varchar_3 = @Message
|
||
END
|
||
END
|
||
|
||
CLOSE c_Subscriptions3
|
||
DEALLOCATE c_Subscriptions3
|
||
|
||
DROP TABLE ##POS
|
||
|
||
END
|
||
END TRY
|
||
BEGIN CATCH
|
||
/* Traitement des erreurs (avec RaiseError) */
|
||
EXEC dbo.get_Error_Info @in_RaiseError = 1
|
||
END CATCH
|
||
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
/* 8: Check All Agents Status */
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
|
||
BEGIN TRY
|
||
IF (@in_CheckType = 8 or @in_CheckType = 0)
|
||
BEGIN
|
||
|
||
SET @message = ''
|
||
|
||
CREATE TABLE #ReplMonitorAgents
|
||
(
|
||
agent sysname,
|
||
dbname sysname NULL,
|
||
name nvarchar(100) NULL,
|
||
status int NULL,
|
||
message nvarchar(255) NULL,
|
||
publisher sysname NULL,
|
||
publisher_db sysname NULL,
|
||
publication sysname NULL,
|
||
subscriber sysname NULL,
|
||
subscriber_db sysname NULL,
|
||
subscription_type int NULL,
|
||
start_time nvarchar(24) NULL,
|
||
time nvarchar(24) NULL,
|
||
duration int NULL,
|
||
comments nvarchar(255) NULL,
|
||
delivery_time int NULL,
|
||
delivered_transactions int NULL,
|
||
delivered_commands int NULL,
|
||
average_commands int NULL,
|
||
transactions_processed int NULL,
|
||
commands_processed int NULL,
|
||
download_inserts int NULL,
|
||
download_updates int NULL,
|
||
download_deletes int NULL,
|
||
publisher_conficts int NULL,
|
||
upload_inserts int NULL,
|
||
upload_updates int NULL,
|
||
upload_deletes int NULL,
|
||
subscriber_conficts int NULL,
|
||
delivery_rate float NULL,
|
||
delivery_latency int NULL,
|
||
error_id INT NULL,
|
||
job_id binary(16) NULL,
|
||
local_job bit NULL,
|
||
profile_id int NULL,
|
||
agent_id int NULL,
|
||
local_timestamp binary(8) NULL,
|
||
offload_enabled bit NULL,
|
||
offload_server sysname NULL,
|
||
subscriber_type tinyint NULL,
|
||
dynamic_filter_login sysname NULL,
|
||
dynamic_filter_hostname sysname NULL
|
||
|
||
)
|
||
|
||
INSERT INTO #ReplMonitorAgents (agent,
|
||
dbname,
|
||
name,
|
||
status,
|
||
publisher ,
|
||
publisher_db,
|
||
publication,
|
||
start_time,
|
||
time,
|
||
duration,
|
||
comments,
|
||
delivered_transactions,
|
||
delivered_commands,
|
||
delivery_rate,
|
||
error_id,
|
||
job_id,
|
||
local_job,
|
||
profile_id,
|
||
agent_id,
|
||
local_timestamp,
|
||
dynamic_filter_login,
|
||
dynamic_filter_hostname)
|
||
SELECT 'Snapshot',
|
||
dbname,
|
||
name,
|
||
status,
|
||
publisher ,
|
||
publisher_db,
|
||
publication,
|
||
start_time,
|
||
time,
|
||
duration,
|
||
comments,
|
||
delivered_transactions,
|
||
delivered_commands,
|
||
delivery_rate,
|
||
error_id,
|
||
job_id,
|
||
local_job,
|
||
profile_id,
|
||
agent_id,
|
||
local_timestamp,
|
||
dynamic_filter_login,
|
||
dynamic_filter_hostname
|
||
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','SET FMTONLY OFF EXEC sp_MSenum_replication_agents @type = 1 with result sets
|
||
((
|
||
dbname sysname NULL,
|
||
name nvarchar(100) NULL,
|
||
status int NULL,
|
||
publisher sysname NULL,
|
||
publisher_db sysname NULL,
|
||
publication sysname NULL,
|
||
start_time nvarchar(24) NULL,
|
||
time nvarchar(24) NULL,
|
||
duration int NULL,
|
||
comments nvarchar(255) NULL,
|
||
delivered_transactions int NULL,
|
||
delivered_commands int NULL,
|
||
delivery_rate float NULL,
|
||
error_id INT NULL,
|
||
job_id binary(16) NULL,
|
||
local_job bit NULL,
|
||
profile_id int NULL,
|
||
agent_id int NULL,
|
||
local_timestamp binary(8) NULL,
|
||
dynamic_filter_login sysname NULL,
|
||
dynamic_filter_hostname sysname NULL
|
||
))')
|
||
|
||
|
||
INSERT INTO #ReplMonitorAgents (agent,
|
||
dbname,
|
||
name,
|
||
status,
|
||
publisher ,
|
||
publisher_db,
|
||
start_time,
|
||
time,
|
||
duration,
|
||
comments,
|
||
delivery_time,
|
||
delivered_transactions,
|
||
delivered_commands,
|
||
average_commands,
|
||
delivery_rate,
|
||
delivery_latency,
|
||
error_id,
|
||
job_id,
|
||
local_job,
|
||
profile_id,
|
||
agent_id,
|
||
local_timestamp)
|
||
SELECT 'Logreader',
|
||
dbname,
|
||
name,
|
||
status,
|
||
publisher ,
|
||
publisher_db,
|
||
start_time,
|
||
time,
|
||
duration,
|
||
comments,
|
||
delivery_time,
|
||
delivered_transactions,
|
||
delivered_commands,
|
||
average_commands,
|
||
delivery_rate,
|
||
delivery_latency,
|
||
error_id,
|
||
job_id,
|
||
local_job,
|
||
profile_id,
|
||
agent_id,
|
||
local_timestamp
|
||
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','SET FMTONLY OFF EXEC sp_MSenum_replication_agents @type = 2 with result sets
|
||
((
|
||
dbname sysname NULL,
|
||
name nvarchar(100) NULL,
|
||
status int NULL,
|
||
publisher sysname NULL,
|
||
publisher_db sysname NULL,
|
||
start_time nvarchar(24) NULL,
|
||
time nvarchar(24) NULL,
|
||
duration int NULL,
|
||
comments nvarchar(255) NULL,
|
||
delivery_time int NULL,
|
||
delivered_transactions int NULL,
|
||
delivered_commands int NULL,
|
||
average_commands int NULL,
|
||
delivery_rate float NULL,
|
||
delivery_latency int NULL,
|
||
error_id INT NULL,
|
||
job_id binary(16) NULL,
|
||
local_job bit NULL,
|
||
profile_id int NULL,
|
||
agent_id int NULL,
|
||
local_timestamp binary(8) NULL
|
||
))')
|
||
|
||
|
||
INSERT INTO #ReplMonitorAgents (agent,
|
||
dbname,
|
||
name,
|
||
status,
|
||
publisher ,
|
||
publisher_db,
|
||
publication,
|
||
subscriber,
|
||
subscriber_db,
|
||
subscription_type,
|
||
start_time,
|
||
time,
|
||
duration,
|
||
comments,
|
||
delivery_time,
|
||
delivered_transactions,
|
||
delivered_commands,
|
||
average_commands,
|
||
delivery_rate,
|
||
delivery_latency,
|
||
error_id,
|
||
job_id,
|
||
local_job,
|
||
profile_id,
|
||
agent_id,
|
||
local_timestamp,
|
||
offload_enabled,
|
||
offload_server,
|
||
subscriber_type)
|
||
SELECT 'Distribution',
|
||
dbname,
|
||
name,
|
||
status,
|
||
publisher ,
|
||
publisher_db,
|
||
publication,
|
||
subscriber,
|
||
subscriber_db,
|
||
subscription_type,
|
||
start_time,
|
||
time,
|
||
duration,
|
||
comments,
|
||
delivery_time,
|
||
delivered_transactions,
|
||
delivered_commands,
|
||
average_commands,
|
||
delivery_rate,
|
||
delivery_latency,
|
||
error_id,
|
||
job_id,
|
||
local_job,
|
||
profile_id,
|
||
agent_id,
|
||
local_timestamp,
|
||
offload_enabled,
|
||
offload_server,
|
||
subscriber_type
|
||
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','SET FMTONLY OFF EXEC sp_MSenum_replication_agents @type = 3 with result sets
|
||
((
|
||
dbname sysname NULL,
|
||
name nvarchar(100) NULL,
|
||
status int NULL,
|
||
publisher sysname NULL,
|
||
publisher_db sysname NULL,
|
||
publication sysname NULL,
|
||
subscriber sysname NULL,
|
||
subscriber_db sysname NULL,
|
||
subscription_type int NULL,
|
||
start_time nvarchar(24) NULL,
|
||
time nvarchar(24) NULL,
|
||
duration int NULL,
|
||
comments nvarchar(255) NULL,
|
||
delivery_time int NULL,
|
||
delivered_transactions int NULL,
|
||
delivered_commands int NULL,
|
||
average_commands int NULL,
|
||
delivery_rate float NULL,
|
||
delivery_latency int NULL,
|
||
error_id INT NULL,
|
||
job_id binary(16) NULL,
|
||
local_job bit NULL,
|
||
profile_id int NULL,
|
||
agent_id int NULL,
|
||
local_timestamp binary(8) NULL,
|
||
offload_enabled bit NULL,
|
||
offload_server sysname NULL,
|
||
subscriber_type tinyint NULL
|
||
))')
|
||
|
||
|
||
INSERT INTO #ReplMonitorAgents (agent,
|
||
dbname,
|
||
name,
|
||
status,
|
||
publisher ,
|
||
publisher_db,
|
||
publication,
|
||
subscriber,
|
||
subscriber_db,
|
||
subscription_type,
|
||
start_time,
|
||
time,
|
||
duration,
|
||
comments,
|
||
delivery_rate,
|
||
download_inserts,
|
||
download_updates,
|
||
download_deletes,
|
||
publisher_conficts,
|
||
upload_inserts,
|
||
upload_updates,
|
||
upload_deletes,
|
||
subscriber_conficts,
|
||
error_id,
|
||
job_id,
|
||
local_job,
|
||
profile_id,
|
||
agent_id,
|
||
local_timestamp,
|
||
offload_enabled,
|
||
offload_server,
|
||
subscriber_type)
|
||
SELECT 'Merge',
|
||
dbname,
|
||
name,
|
||
status,
|
||
publisher ,
|
||
publisher_db,
|
||
publication,
|
||
subscriber,
|
||
subscriber_db,
|
||
subscription_type,
|
||
start_time,
|
||
time,
|
||
duration,
|
||
comments,
|
||
delivery_rate,
|
||
download_inserts,
|
||
download_updates,
|
||
download_deletes,
|
||
publisher_conficts,
|
||
upload_inserts,
|
||
upload_updates,
|
||
upload_deletes,
|
||
subscriber_conficts,
|
||
error_id,
|
||
job_id,
|
||
local_job,
|
||
profile_id,
|
||
agent_id,
|
||
local_timestamp,
|
||
offload_enabled,
|
||
offload_server,
|
||
subscriber_type
|
||
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','SET FMTONLY OFF EXEC sp_MSenum_replication_agents @type = 4 with result sets
|
||
((
|
||
dbname sysname NULL,
|
||
name nvarchar(100) NULL,
|
||
status int NULL,
|
||
publisher sysname NULL,
|
||
publisher_db sysname NULL,
|
||
publication sysname NULL,
|
||
subscriber sysname NULL,
|
||
subscriber_db sysname NULL,
|
||
subscription_type int NULL,
|
||
start_time nvarchar(24) NULL,
|
||
time nvarchar(24) NULL,
|
||
duration int NULL,
|
||
comments nvarchar(255) NULL,
|
||
delivery_rate float NULL,
|
||
download_inserts int NULL,
|
||
download_updates int NULL,
|
||
download_deletes int NULL,
|
||
publisher_conficts int NULL,
|
||
upload_inserts int NULL,
|
||
upload_updates int NULL,
|
||
upload_deletes int NULL,
|
||
subscriber_conficts int NULL,
|
||
error_id INT NULL,
|
||
job_id binary(16) NULL,
|
||
local_job bit NULL,
|
||
profile_id int NULL,
|
||
agent_id int NULL,
|
||
local_timestamp binary(8) NULL,
|
||
offload_enabled bit NULL,
|
||
offload_server sysname NULL,
|
||
subscriber_type tinyint NULL
|
||
))')
|
||
|
||
INSERT INTO #ReplMonitorAgents (agent,
|
||
dbname,
|
||
name,
|
||
status,
|
||
start_time,
|
||
time,
|
||
duration,
|
||
comments,
|
||
transactions_processed,
|
||
commands_processed,
|
||
average_commands,
|
||
delivery_rate,
|
||
delivery_latency,
|
||
error_id,
|
||
job_id,
|
||
profile_id,
|
||
agent_id,
|
||
local_timestamp)
|
||
SELECT 'Queue reader',
|
||
dbname,
|
||
name,
|
||
status,
|
||
start_time,
|
||
time,
|
||
duration,
|
||
comments,
|
||
transactions_processed,
|
||
commands_processed,
|
||
average_commands,
|
||
delivery_rate,
|
||
delivery_latency,
|
||
error_id,
|
||
job_id,
|
||
profile_id,
|
||
agent_id,
|
||
local_timestamp
|
||
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','SET FMTONLY OFF EXEC sp_MSenum_replication_agents @type = 9 with result sets
|
||
((
|
||
dbname sysname NULL,
|
||
name nvarchar(100) NULL,
|
||
status int NULL,
|
||
start_time nvarchar(24) NULL,
|
||
time nvarchar(24) NULL,
|
||
duration int NULL,
|
||
comments nvarchar(255) NULL,
|
||
transactions_processed int NULL,
|
||
commands_processed int NULL,
|
||
average_commands int NULL,
|
||
delivery_rate float NULL,
|
||
delivery_latency int NULL,
|
||
error_id INT NULL,
|
||
job_id binary(16) NULL,
|
||
profile_id int NULL,
|
||
agent_id int NULL,
|
||
local_timestamp binary(8) NULL
|
||
))')
|
||
|
||
DECLARE CursorLastHistory CURSOR FOR
|
||
SELECT agent,dbname,name,status,time,comments,agent_id,publication,publisher_db,subscriber
|
||
FROM #ReplMonitorAgents
|
||
ORDER BY publisher_db,publication desc,subscriber desc
|
||
|
||
OPEN CursorLastHistory
|
||
|
||
FETCH NEXT FROM CursorLastHistory
|
||
INTO @Agent_type,@distribution_db,@Jobname,@status,@EndTime,@Comments,@Agent_id,@publication,@publisher_db,@subscriber
|
||
|
||
WHILE @@FETCH_STATUS = 0
|
||
BEGIN
|
||
SET @message = ''
|
||
|
||
SELECT @AlertKey = CASE @Agent_type WHEN 'Snapshot' THEN 'SnapAgt'
|
||
WHEN 'Logreader' THEN 'LogReadAgt'
|
||
WHEN 'Distribution' THEN 'DistAgt'
|
||
WHEN 'Merge' THEN 'MergeAgt'
|
||
WHEN 'Queue reader' THEN 'QreaderAgt'
|
||
END
|
||
|
||
IF @status > 4
|
||
BEGIN
|
||
|
||
IF @subscriber IS NOT NULL AND @publication <> 'publ_Ceres_Arizona_Gaia_Tran'
|
||
BEGIN
|
||
BEGIN TRY
|
||
IF EXISTS(SELECT 1 FROM #LinkedServerPb WHERE posname = @subscriber)
|
||
BEGIN
|
||
SET @result_sp = 1
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
EXEC @result_sp = sys.sp_testlinkedserver @subscriber
|
||
END
|
||
END TRY
|
||
BEGIN CATCH
|
||
SET @result_sp = @@error;
|
||
END CATCH;
|
||
END
|
||
ELSE
|
||
SET @result_sp = 0
|
||
|
||
IF @result_sp = 0
|
||
SELECT @message = 'L''agent '+ @Agent_type +' '+ @Jobname +' de la ' + CASE WHEN @publication IS NOT NULL THEN 'publication '+@publication
|
||
WHEN @publication IS NULL THEN 'base '+@publisher_db
|
||
END +
|
||
CASE WHEN @subscriber IS NOT NULL THEN ' de l''abonné '+@subscriber
|
||
ELSE ''
|
||
END + ' est en erreur : ' + @Comments
|
||
|
||
IF @message <> ''
|
||
BEGIN
|
||
|
||
SET @errfound = 1
|
||
|
||
SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = @AlertKey
|
||
|
||
IF @isProd = 1
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 1, @CheckDate, @message)
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 0, @CheckDate, @message)
|
||
END
|
||
|
||
/* S'il y a une erreur, on envoie un mail aux DBA */
|
||
IF @in_Email = 1
|
||
BEGIN
|
||
EXEC [dbo].[aps_Send_Mail_with_template]
|
||
@in_param_varchar_2 = 'DBA_operator;',
|
||
@in_job_type = 3, /* 3 = warning */
|
||
@in_param_varchar_3 = @Message
|
||
END
|
||
END
|
||
|
||
END
|
||
ELSE IF @status = 2 AND DATEDIFF(SECOND,@EndTime,GETDATE()) > 120
|
||
BEGIN
|
||
|
||
IF @subscriber IS NOT NULL AND @publication <> 'publ_Ceres_Arizona_Gaia_Tran'
|
||
BEGIN
|
||
BEGIN TRY
|
||
IF EXISTS(SELECT 1 FROM #LinkedServerPb WHERE posname = @subscriber)
|
||
BEGIN
|
||
SET @result_sp = 1
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
EXEC @result_sp = sys.sp_testlinkedserver @subscriber
|
||
END
|
||
END TRY
|
||
BEGIN CATCH
|
||
SET @result_sp = @@error;
|
||
END CATCH;
|
||
END
|
||
ELSE IF @Agent_type = 'Snapshot'
|
||
SET @result_sp = 1
|
||
ELSE
|
||
SET @result_sp = 0
|
||
|
||
IF @result_sp = 0
|
||
SELECT @message = 'L''agent '+ @Agent_type +' '+ @Jobname +' de la ' + CASE WHEN @publication IS NOT NULL THEN 'publication '+@publication
|
||
WHEN @publication IS NULL THEN 'base '+@publisher_db
|
||
END +
|
||
CASE WHEN @subscriber IS NOT NULL THEN ' de l''abonné '+@subscriber
|
||
ELSE ''
|
||
END + ' est stoppé depuis plus de 2 minutes'
|
||
IF @message <> ''
|
||
BEGIN
|
||
SET @errfound = 1
|
||
|
||
SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'StopJobs'
|
||
IF @isProd = 1
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 2, @CheckDate, @message)
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 0, @CheckDate, @message)
|
||
END
|
||
|
||
/* S'il y a une erreur, on envoie un mail aux DBA */
|
||
IF @in_Email = 1
|
||
BEGIN
|
||
EXEC [dbo].[aps_Send_Mail_with_template]
|
||
@in_param_varchar_2 = 'DBA_operator;',
|
||
@in_job_type = 3, /* 3 = warning */
|
||
@in_param_varchar_3 = @Message
|
||
END
|
||
END
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
|
||
SELECT @TableSys = CASE @Agent_type WHEN 'Snapshot' THEN 'MSsnapshot_history'
|
||
WHEN 'Logreader' THEN 'MSlogreader_history'
|
||
WHEN 'Distribution' THEN 'MSdistribution_history'
|
||
WHEN 'Merge' THEN 'MSmerge_history'
|
||
WHEN 'Queue reader' THEN 'MSqreader_history'
|
||
END
|
||
|
||
|
||
SET @tsql = 'IF EXISTS (SELECT 1
|
||
FROM (SELECT TOP 3
|
||
MH.agent_id,
|
||
MH.error_id
|
||
FROM '+ @distribution_db +'.dbo.'+ @TableSys +' as MH
|
||
WHERE MH.agent_id = @Agent_id ORDER BY MH.start_time DESC, MH.time desc) as T1
|
||
WHERE T1.error_id > 0)
|
||
BEGIN
|
||
SELECT @message = ''A ''+CONVERT(nvarchar(30), T1.start_time, 120)+'' Il y a eu une erreur ''+CAST(T1.error_id as varchar(10))+'' sur l''''agent ''+ @Agent_type +'' ''+ @Jobname +'' de la '' + CASE WHEN @publication IS NOT NULL THEN ''publication ''+@publication
|
||
WHEN @publication IS NULL THEN ''base ''+@publisher_db
|
||
END +
|
||
CASE WHEN @subscriber IS NOT NULL THEN '' de l''''abonné ''+@subscriber
|
||
ELSE ''''
|
||
END + '' : '' + @Comments
|
||
FROM (SELECT TOP 3
|
||
start_time,
|
||
error_id
|
||
FROM '+ @distribution_db +'.dbo.'+ @TableSys +'
|
||
WHERE agent_id = @Agent_id ORDER BY start_time DESC, time desc) as T1
|
||
WHERE T1.error_id > 0
|
||
END'
|
||
|
||
EXEC [sys].[sp_executesql] @tsql, N'@Agent_id int,@Agent_type sysname,@JobName varchar(100),@publication sysname,@publisher_db sysname,@subscriber sysname,@comments varchar(250),@Message varchar(max) OUTPUT',@Agent_id=@Agent_id,@Agent_type=@Agent_type,@JobName=@JobName,@publication=@publication,@publisher_db=@publisher_db,@subscriber=@subscriber,@comments=@comments, @message = @message OUTPUT
|
||
|
||
IF @Message like '%No replicated transactions are available%' or @Message like '%transaction(s) with%command(s) were delivered%'
|
||
BEGIN
|
||
SET @Message = ''
|
||
END
|
||
|
||
IF @Message <> ''
|
||
BEGIN
|
||
SET @errfound = 1
|
||
SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = @AlertKey
|
||
|
||
IF @isProd = 1
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 1, @CheckDate, @message)
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 0, @CheckDate, @message)
|
||
END
|
||
|
||
/* S'il y a une erreur, on envoie un mail aux DBA */
|
||
IF @in_Email = 1
|
||
BEGIN
|
||
EXEC [dbo].[aps_Send_Mail_with_template]
|
||
@in_param_varchar_2 = 'DBA_operator;',
|
||
@in_job_type = 3, /* 3 = warning */
|
||
@in_param_varchar_3 = @Message
|
||
END
|
||
END
|
||
END
|
||
|
||
FETCH NEXT FROM CursorLastHistory
|
||
INTO @Agent_type,@distribution_db,@Jobname,@status,@EndTime,@Comments,@Agent_id,@publication,@publisher_db,@subscriber
|
||
|
||
END
|
||
|
||
CLOSE CursorLastHistory
|
||
DEALLOCATE CursorLastHistory
|
||
|
||
IF OBJECT_ID('tempdb..#ReplMonitorAgents') IS NOT NULL
|
||
DROP TABLE #ReplMonitorAgents
|
||
|
||
END
|
||
END TRY
|
||
BEGIN CATCH
|
||
/* Traitement des erreurs (avec RaiseError) */
|
||
EXEC dbo.get_Error_Info @in_RaiseError = 1
|
||
END CATCH
|
||
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
/* 9: Check jobs de réplication à double */
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
BEGIN TRY
|
||
IF (@in_CheckType = 9 or @in_CheckType = 0)
|
||
BEGIN
|
||
|
||
SET @message = ''
|
||
|
||
DECLARE c_pos CURSOR LOCAL FORWARD_ONLY STATIC FOR
|
||
SELECT SUBSTRING(name,CHARINDEX('ActivePosTran',name,0)+14,CHARINDEX('-',name,CHARINDEX('ActivePosTran',name,0)+14)-(CHARINDEX('ActivePosTran',name,0)+14))
|
||
FROM msdb.dbo.sysjobs
|
||
WHERE name like '%ActivePos_read-ActivePosTran%'
|
||
AND SUBSTRING(name,CHARINDEX('ActivePosTran',name,0)+14,8) like 'W%'
|
||
GROUP BY SUBSTRING(name,CHARINDEX('ActivePosTran',name,0)+14,CHARINDEX('-',name,CHARINDEX('ActivePosTran',name,0)+14)-(CHARINDEX('ActivePosTran',name,0)+14))
|
||
HAVING COUNT(*) > 1
|
||
|
||
OPEN c_pos
|
||
|
||
FETCH NEXT FROM c_pos
|
||
INTO @POS
|
||
|
||
WHILE @@fetch_status = 0
|
||
BEGIN
|
||
|
||
SET @message = @Message + 'Il y a trop de jobs pour le poste : '+@POS+CHAR(13)+CHAR(10)
|
||
|
||
FETCH NEXT FROM c_pos
|
||
INTO @POS
|
||
END
|
||
|
||
CLOSE c_pos
|
||
DEALLOCATE c_pos
|
||
|
||
IF @message <> ''
|
||
BEGIN
|
||
|
||
SET @errfound = 1
|
||
SET @message = 'Liste des jobs en double :'+CHAR(13)+CHAR(10)+@message
|
||
SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'DuplicJobs'
|
||
IF @isProd = 1
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 1, @CheckDate, @message)
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 0, @CheckDate, @message)
|
||
END
|
||
|
||
/* S'il y a une erreur, on envoie un mail aux DBA */
|
||
IF @in_Email = 1
|
||
BEGIN
|
||
EXEC [dbo].[aps_Send_Mail_with_template]
|
||
@in_param_varchar_2 = 'DBA_operator;',
|
||
@in_job_type = 3, /* 3 = warning */
|
||
@in_param_varchar_3 = @Message
|
||
END
|
||
END
|
||
END
|
||
END TRY
|
||
BEGIN CATCH
|
||
/* Traitement des erreurs (avec RaiseError) */
|
||
EXEC dbo.get_Error_Info @in_RaiseError = 1
|
||
END CATCH
|
||
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
/* 10: Verifications performances et des commandes non distribuées */
|
||
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
|
||
BEGIN TRY
|
||
IF (@in_CheckType = 10 or @in_CheckType = 0)
|
||
BEGIN
|
||
|
||
SET @message = ''
|
||
SET @countko = 0
|
||
SET @percent = 0
|
||
|
||
SELECT RQH_subscriber_db, RQH_records_in_queue, RQH_catch_up_time, RQH_log_date
|
||
INTO #LastHourStatus
|
||
FROM HciTools.[mon].[Replication_queue_history]
|
||
WHERE RQH_records_in_queue > 1000 and RQH_catch_up_time > 60
|
||
AND RQH_log_date > DATEADD(hh, -1, getdate())
|
||
ORDER BY RQH_log_date DESC
|
||
|
||
/* Separate check for TriaFin replication, only ont subscription registered */
|
||
IF EXISTS (SELECT RQH_subscriber_db, count(*) FROM #LastHourStatus
|
||
GROUP BY RQH_subscriber_db
|
||
HAVING RQH_subscriber_db like '%GALCTP' and count(*) >= 3)
|
||
BEGIN
|
||
SELECT @Message = @Message + CHAR(13) + CHAR(10) + '-' + RQH_subscriber_db + ' : ' + cast(RQH_records_in_queue as varchar(20)) +': '
|
||
+ cast(RQH_catch_up_time as varchar (10)) + '(s) : '
|
||
+ convert(varchar(20),RQH_log_date, 120) + CHAR(13) + CHAR(10)
|
||
FROM #LastHourStatus
|
||
WHERE RQH_subscriber_db like '%GALCTP'
|
||
END
|
||
|
||
/* Suiz percentage check for perf, need to see caisse mall, pos covid and out f site pos (ex chantepoulet) single perf issue
|
||
SELECT @countko = count(distinct RQH_subscriber_db)
|
||
FROM #LastHourStatus
|
||
WHERE RQH_subscriber_db not like '%GALCTP'
|
||
|
||
SELECT @percent = (@countko * 100 / (@counttot))
|
||
|
||
IF @percent < 50 and @message = ''
|
||
Return
|
||
*/
|
||
|
||
|
||
|
||
IF EXISTS ( SELECT *
|
||
FROM distribution.sys.tables
|
||
WHERE name = 'msreplservers')
|
||
BEGIN
|
||
EXEC ('SELECT REPLACE([POS].[POS_hostname],''-'','''') as POS_hostname
|
||
INTO ##POS
|
||
FROM arizona.dbo.bmc_application_key WITH (NOLOCK)
|
||
JOIN arizona.dbo.bmc_application_default WITH (NOLOCK)
|
||
ON BAPD_bmc_application_key = Bmc_application_key_ID
|
||
JOIN arizona.dbo.organizational_unit OUA WITH (NOLOCK)
|
||
ON [OUA].[Organizational_unit_ID] = bapd_value
|
||
JOIN [Arizona].[dbo].[Organizational_unit] [ou] WITH(NOLOCK)
|
||
ON [ou].[OU_code] = [OUA].[OU_code]
|
||
JOIN [Arizona].[dbo].[Point_of_sale] [pos]
|
||
ON [ou].[Organizational_unit_ID] = [pos].[POS_organizational_unit]
|
||
WHERE BAPK_key = ''cvCurrentOrganizationalUnit''
|
||
AND [pos].[POS_active] = 1
|
||
AND [pos].[POS_number] <= 39
|
||
AND [pos].[POS_type] IN (1,2) /* 1=POS, 2=Backoffice, 3=SAFE */
|
||
--AND OU.CROU_locally_used = 1
|
||
AND EXISTS (SELECT DISTINCT [ss].[srvname]
|
||
FROM distribution.dbo.MSreplservers ss WITH (NOLOCK)
|
||
INNER JOIN distribution.dbo.MSsubscriptions ms WITH (NOLOCK)
|
||
ON [ss].[srvid] = [ms].[subscriber_id]
|
||
WHERE [ss].[srvname] = POS.[POS_hostname])');
|
||
END;
|
||
ELSE
|
||
BEGIN
|
||
EXEC ('SELECT REPLACE([POS].[POS_hostname],''-'','''') as POS_hostname
|
||
INTO ##POS
|
||
FROM arizona.dbo.bmc_application_key WITH (NOLOCK)
|
||
JOIN arizona.dbo.bmc_application_default WITH (NOLOCK)
|
||
ON BAPD_bmc_application_key = Bmc_application_key_ID
|
||
JOIN arizona.dbo.organizational_unit OUA WITH (NOLOCK)
|
||
ON [OUA].[Organizational_unit_ID] = bapd_value
|
||
JOIN [Arizona].[dbo].[Organizational_unit] [ou] WITH(NOLOCK)
|
||
ON [ou].[OU_code] = [OUA].[OU_code]
|
||
JOIN [Arizona].[dbo].[Point_of_sale] [pos]
|
||
ON [ou].[Organizational_unit_ID] = [pos].[POS_organizational_unit]
|
||
WHERE BAPK_key = ''cvCurrentOrganizationalUnit''
|
||
AND [pos].[POS_active] = 1
|
||
AND [pos].[POS_number] <= 39
|
||
AND [pos].[POS_type] IN (1,2) /* 1=POS, 2=Backoffice, 3=SAFE */
|
||
--AND OU.CROU_locally_used = 1
|
||
AND EXISTS (SELECT DISTINCT [ss].[name]
|
||
FROM sys.servers ss WITH (NOLOCK)
|
||
INNER JOIN distribution.dbo.MSsubscriptions ms WITH (NOLOCK)
|
||
ON [ss].[server_id] = [ms].[subscriber_id]
|
||
WHERE [ss].[name] = POS.[POS_hostname])');
|
||
END;
|
||
|
||
/* Online subscription having undistributed transaction with latency in last hour */
|
||
DECLARE c_Subscriptions4 CURSOR LOCAL FORWARD_ONLY STATIC FOR
|
||
SELECT * FROM ##POS
|
||
|
||
OPEN c_Subscriptions4
|
||
|
||
FETCH NEXT FROM c_Subscriptions4
|
||
INTO @POS
|
||
|
||
WHILE @@fetch_status <> -1
|
||
BEGIN
|
||
/* Select only POS having more or equal than 4 entries ko in last hour */
|
||
IF NOT EXISTS(SELECT 1 FROM #LinkedServerPb WHERE posname = @POS)
|
||
AND EXISTS (SELECT RQH_subscriber_db, count(*)
|
||
FROM #LastHourStatus
|
||
GROUP BY RQH_subscriber_db
|
||
HAVING RQH_subscriber_db = @pos and count(*) >= 4)
|
||
BEGIN
|
||
SELECT @Message = @Message + '-' + RQH_subscriber_db + ' : ' + cast(RQH_records_in_queue as varchar(20)) + ': '+ cast(RQH_catch_up_time as varchar (10)) + '(s) : '
|
||
+ convert(varchar(20),RQH_log_date, 120) + CHAR(13) + CHAR(10)
|
||
FROM #LastHourStatus
|
||
WHERE RQH_subscriber_db = @pos
|
||
|
||
SELECT @Message = @Message + CHAR(13) + CHAR(10)
|
||
|
||
END
|
||
FETCH NEXT FROM c_Subscriptions4
|
||
INTO @POS
|
||
END
|
||
|
||
DROP TABLE #LastHourStatus
|
||
|
||
IF @message <> ''
|
||
BEGIN
|
||
|
||
SET @errfound = 1
|
||
SET @message = 'Subscription(s) with undistributed commands and catch up time :' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + @message
|
||
|
||
/* S'il y a une erreur, on envoie un mail aux DBA */
|
||
IF @in_Email = 1
|
||
BEGIN
|
||
EXEC [dbo].[aps_Send_Mail_with_template]
|
||
@in_param_varchar_2 = 'DBA_operator;',
|
||
@in_job_type = 3, /* 3 = warning */
|
||
@in_param_varchar_3 = @Message
|
||
END
|
||
END
|
||
|
||
CLOSE c_Subscriptions4
|
||
DEALLOCATE c_Subscriptions4
|
||
|
||
DROP TABLE ##POS
|
||
|
||
END
|
||
|
||
END TRY
|
||
BEGIN CATCH
|
||
/* Traitement des erreurs (avec RaiseError) */
|
||
EXEC dbo.get_Error_Info @in_RaiseError = 1
|
||
END CATCH
|
||
|
||
/* Status OK si pas d'erreur */
|
||
IF @errfound = 0
|
||
BEGIN
|
||
SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'OK'
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 0, @CheckDate, '')
|
||
END
|
||
|
||
DROP TABLE #LinkedServerPb
|
||
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
/* N+2 Sites = OK */
|
||
SELECT @AlertID = DBA_monitoring_alerts_ID FROM [HCITools].[mon].[DBA_monitoring_alerts] WHERE DBAMA_key = 'OK'
|
||
INSERT INTO [HCITools].[mon].[DBA_monitoring_alerts_history] (DBAMAH_monitoring_alerts, DBAMAH_criticity_level, DBAMAH_event_date, DBAMAH_long_message)
|
||
VALUES (@AlertID, 0, @CheckDate, '')
|
||
END
|
||
END
|