SQL Server DMV Replication Monitoring Part 4


cloud-monitoring

Hello my followers  in the previous post i published part two in the series of (SQL Server DMV Replication Monitoring) and i explained very interested point for any DBA working on replication solutions by simple DMV depend on dynamic Query technique and on SQL Server cursor technology.

Summary of the previous posts

First Post :

  1. Publication Status
  2. Subscription Status
  3. Send Email by the Current status

Second Post :

  1. Replication information for the Complete setup.
  2. Find publication article.

Third Post :

How we can Check Distributor AGENT.

Replication in SQL Server Series  1 2 3 4 

DMV Replication Monitoring Series  1 2 3 4

Today i will Explain one new point it is very helpful and attractive , How we can Check LogReader AGENT Status

by the below Stored procedure we can Execute it from SQL Server AGENT to send to us notification mail if the LogReader JOB(s) FAILING OR STOPPED.

SOURCE POST : WWW.MostafaElmasry.WordPress.Com

[sql]

USE [MSDB]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[replLogReaderStatusGet] 
@pRecipients varchar(255) = 'SQLGULF-dbconsultant@outlook.com' 
AS 

 
 SET NOCOUNT ON; 
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
 
 DECLARE @is_sysadmin INT 
 DECLARE @job_owner sysname 
 DECLARE @job_id uniqueidentifier 
 DECLARE @job_name sysname 
 DECLARE @running int 
 DECLARE @cnt int 
 DECLARE @msg varchar(8000) 
 DECLARE @msg_header varchar(1000) 
 DECLARE @categoryid int 
 
 
select la.name,la.publisher_db, 
 case lh.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' 
 else 'Unknown' 
 end as runstatus 
 , lh.time, lh.comments 
from distribution..MSlogreader_history lh 
 inner join distribution..MSlogreader_agents la on lh.agent_id = la.id 
 inner join ( 
 select lh.agent_id, max(lh.time) as LastTime 
 from distribution..MSlogreader_history lh 
 inner join distribution..MSlogreader_agents la on lh.agent_id = la.id 
 group by lh.agent_id) r 
 on r.agent_id = lh.agent_id 
 and r.LastTime = lh.time 
where lh.runstatus not in (3,4) 
 
if @@rowcount > 0 
 
BEGIN 
 SELECT @job_owner = SUSER_SNAME() 
 ,@is_sysadmin = 1 
 ,@running = 0 
 ,@categoryid = 13 
 
 CREATE TABLE #job (job_id UNIQUEIDENTIFIER NOT NULL, 
 last_run_date INT , 
 last_run_time INT , 
 next_run_date INT , 
 next_run_time INT , 
 next_run_schedule_id INT , 
 requested_to_run INT , 
 request_source INT , 
 request_source_id sysname COLLATE database_default NULL, 
 running int , 
 current_step INT , 
 current_retry_attempt INT , 
 job_state INT) 
 
 INSERT INTO #job 
 EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner 
 
 SELECT @running = isnull(sum(j.running),-1),@cnt = count(*) 
 FROM #job j 
 join msdb..sysjobs s on j.job_id = s.job_id 
 where category_id = @categoryid 
 
 if @running <> @cnt 
 BEGIN 
 SELECT @msg_header = 'LogReader job(s) FAILING OR STOPPED. Please check replication job(s) ASAP.' 
 SELECT @msg_header = @msg_header + char(10) 
 SELECT @msg_header = @msg_header + '**************************************************************' 

 set @msg = '' 
 SELECT @msg = @msg + char(10)+'"' + s.[name] + '" - '+ convert(varchar, isnull(j.running,-1)) 
 FROM #job j 
 join msdb..sysjobs s on j.job_id = s.job_id 
 where category_id = @categoryid 
 and isnull(j.running,-1) <> 1 
 
 SELECT @msg = @msg_header + char(10) + nullif(@msg,'') 
 

 exec msdb.dbo.sp_send_dbmail 
 @recipients= @pRecipients , 
 @subject= 'Production Replication LogReader Alert', 
 @body = @msg 
END 
END 
GO
[/sql]

Now create new SQL Server agent job and execute this Stored procedure on it

sql]
USE [msdb]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[replLogReaderStatusGet]
@pRecipients = N'SQLGULF-dbconsultant@outlook.com'
SELECT 'Return Value' = @return_value
GO
[/sql]

Follow me to know more SQL Server DMV in Replication Monitoring

Follow  the Author :

View all my tips , LinkedIn Website Slideshare ,Youtube Channel.

SQL Server DMV Replication Monitoring Part 2


cloud-monitoring

 

Hello my followers  in the previous post i started my new series in (SQL Server DMV Replication Monitoring) and i explained very interested point for any DBA WORKING on replication solutions How we can know the current status of our Publication and subscription is it Active , In Active by simple DMV depend on dynamic Query technique and on SQL Server cursor TECHNOLOGY .

 

Summary of the previous post:
1- Publication Status
2- Subscription Status
3- Send Email by the Current status

 Replication in SQL Server Series  

1     2      3       4 

DMV Replication Monitoring Series  1     2

Today is  will complete this series to be this part is part two and i will add 2 new point very helpful for our daily WORK :

  • Replication information for the Complete setup.
  • Find publication article.

Continue reading “SQL Server DMV Replication Monitoring Part 2”

SQL Server DMV Replication Monitoring Part 1


IC216462

 

Hello My followers  today i will took in very important subject for any DBA How we can monitor our Replications !! for any replication i have publication and subscription , How i can now is it working or it is active or down or what is the status of it now , based on that i created one DMV build with technique of Dynamic Query and using SQL Server cursor but before you go for Monitoring (advanced level) you should know some points at the beginning :

 

 

 

 

Replication in SQL Server Part 1

  • What is SQL Replication?
  • Types of SQL Replication
  • Replication Overview

Replication in SQL Server Part 2

  • How to create Transaction Replication

Replication in SQL Server Part 3

  • How to Add New Publisher Server to the Distributor Server

Replication in SQL Server Part 4

  • Disable Publishing and Distribution Servers

Now after we explained the main highlights for the replication lets see How we can monitor the status of the publication and subscription? and How we can receive email if any one of them is InActive :

  • First i return all databases used in replication
[sql]

SELECT
 MSA.publisher_db AS 'Database Name'
FROM
 distribution.DBO.MSarticles AS MSA
OPEN db_cursor
[/sql]

Continue reading “SQL Server DMV Replication Monitoring Part 1”

Disable Publishing and Distribution Servers


1- Open MSSQL Distribution Server >>> mangment Studio >>> Replication >>> Write Click >>>> Disable Publishing and Distribution

Disable PDisable ublishing and Distribution

2- Disable this  Distribution and Publishing in this Server or Disable the Publishing and let the Distribution

Replic_5

3- Confirm Disabling Remote publisher this step if you add Remote Publisher

Confirm Remote Publisher

4- Confirm Disable is Wizerd or T-SQL

Replic_7

Now if you Checked the Distrubiter Database under the System Database you will not Found it also if you make write Clikc on Replication you will See Configure Distribution option that’s meaning this server without Distribution

Check

Wait for me in another Replication posts

Regards ,

Mostafa Elmasry

How to Add New Publisher Server to the Distributor Server (MSSQL Replication)


now if i need to Add new Publisher on my Distributor Server i will do this steps in SQL Server Also if you need to know hot to Setup the Cnfiguration of Distributor Server see this link :http://wp.me/p1Oidq-et or Check the replication categories :https://mostafaelmasry.wordpress.com/category/sql-server-2008/replication/

1- Connect to the MSSQL Distributor Server >>> mangement Studio >>> Replication >>> Write Click >>> Distributor properties

Replic_1

2- Add SQL publisher

Add SQL publisher

3- Add password to Connect the Publisher with this password to the Distributor

replic_3

Now if you Check the Distributor Server you will See 2 Publisher Server

Wait for me in another Replication posts

Regards ,

Mostafa Elmasry