RSS

Category Archives: Replication

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.

 
Leave a comment

Posted by on April 2, 2015 in Replication

 

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.

Read the rest of this entry »

 
1 Comment

Posted by on March 28, 2015 in Replication

 

Tags: , , ,

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]

Read the rest of this entry »

 
2 Comments

Posted by on March 25, 2015 in Replication

 

Tags: , , , , , ,

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

 
Leave a comment

Posted by on January 5, 2013 in Replication

 

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

 

 
Leave a comment

Posted by on January 5, 2013 in Replication

 

Tags: , ,

Transactional Replication in SQL Server 2008R2

introduction

Today I will Explain How to Configure Replication With Replication type Transactional Replication then in another posts I will exaplain all Replication type and

How to manage the replication ,

how to manage the Replication Agent ,

How to monitor the replication ,

Hot to Check the Replication performance

Transactional Replication

in this Demo I install 2 virtual machine and 3 SQL Server instance

Virtual Machine : SQL-PC (Windows Server 2008R2 Enterprise Edition) With SQL Server (Default instance)

Virtual Machine : SQL2-PC (Windows Server 2008R2 Enterprise Edition) With SQL Server (Default instance , Named Instance = WitnessServer)

I will partition this Subject to three type :

1- How to install the Distribution Server. (SQL-PC)

2- How to install the Publication Server. (SQL-PC)

2- How to install the Subscription Server. (SQL2-PC)

How to install the Distribution Server

1- on machine Name SQL-PC open SQl Server Instance name SQL-PC Write Click on Replication then Select Configure Distribution

2- Select the Distributor Server : First option if you want SQL-PC is the Distributor Server .Second option if you won’t to Add another Distributor Server .

replic_2

3- Enter the Snapshot Folder : in this Step Create Folder in make it Share then take the Network path of this Folder and put it in the Snapshot folder path

replic_3

4- Create the distribution Database : this Database will be under the System Database like the master database

Replic_4

5- Select the Publisher Server : in this step you can add more than one Publisher Server but in my demo I will Add SQL-PC is publisherRepic_5

6- Finish the Configuration by Select between (Configure Wizard  & Create T-SQL)

Replic_6

7-Configuration Distribution Complete successfully

replic_7

8- Check the Distribution Database under the System Database

How to install the Publication Server

1- on machine Name SQL-PC open SQl Server Instance name SQL-PC Under Replication Select Load Publication the Wrote Click on it the Select New Publication

Replic_9

2-ٍSelect the publication Database

Replic_10

 3- Select the Publication Type

Replic_11

4-Select the object to publish it (Table , View , Stored , other objects)

Replic_12

5- if you have table in this table don’t have Primary ley you can’t publish it like this table

Replic_13

6- if you won’t to add filter on this table

Replic_15

Replic_16

7- Configure the snapshot agent

Replic_17

8- Add the user for the Agent Security

Replic_18

Replic_19

9-Write Name to Publication

Replic_20

10- Check now your new Publication

Replic_21

How to install the Subscription Server

Now let’s to Connect to another Server (SQL2-PC) to install the Subscription on two Instance

  • SQL2-PC
  • SQL-PC\WitnessServer

1- under Replication Select Load Subscription

Replic_22

2-  Selet the Publication Server

Replic23

3- Select Server name to run the Agent (run on Distributor Server SQL_PC) or run it on Subscription Server

Replic_24

4- Select the Subscription Server and add the Subscription Database

Replic_27

5- Add another Subscription Server (SQL2-PC\WitnessServer

Replic_25

6- Configure the Distribution Agent Secuirty Add the User name Agent

Replic_29

7- Synchronize Schedule

replic_30

8- ieinitialize Subscription

Replic_31

Now after this step you now finish the Configuration for SQL Server Transaction Replication .

Open SQL Server (SQL-PC) Publication then try to Edit any data in the Publication Database in table or View or Stored we Selected it on the Article and go to the Subscription Servers to Check this Data is transferred or no

Wait for me in another Replication posts

Regards ,

Mostafa Elmasry

 
Leave a comment

Posted by on January 5, 2013 in Replication

 

Replication Overview

1- What is SQL Replication?

SQL replication is a technology designed to allow storing identical data in multiple locations. First lets examine why replication may be useful and how it solves common data distribution problems. There are several classical examples in which SQL replication solves business problems. One of the most popular ones is the case when a business has mobile employees, who need to access data from their portable computers while they are away from they office. Another example is when the workforce of a business is distributed around the world and all employees need to access one and the same set of data, but network connectivity has poor quality. In both the above examples using SQL replication is the right thing to do. Replication is used in many other scenarios as well for example as a backup solution, and for offloading database intensive processing like reporting and data mining from main live databases.

2- Types of SQL Replication:

  • Snapshot replication

The publisher simply takes a snapshot of the entire replicated database and shares it with the subscribers.  Of course, this is a very time and resource-intensive process.  For this reason, most administrators don’t use snapshot replication on a recurring basis for databases that change frequently

  • Transactional replication

With transactional replication, the replication agent monitors the publisher for changes to the database and transmits those changes to the subscribers.  This transmission can take place immediately or on a periodic basis.

  • Merge replication

Merge replication, like transactional replication, typically starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.

3- Replication Overview

  • Publisher

Publisher Is database instance that’ allowed the Replicate data to the Subscribers Databases instances Publisher can sent to one or more Subscriber Database

  • Distributor

The Distributor is a database instance that acts as a store for replication specific data associated with one or more Publishers The distribution database stores replication status data, metadata about the publication, and, in some cases, acts as a queue for data moving from the Publisher to the Subscribers

  • Subscribers

A Subscriber is a database instance that receives replicated data. A Subscriber can receive data from multiple Publishers and publications. Depending on the type of replication chosen, the Subscriber can also pass data changes back to the Publisher or republish the data to other Subscribers.

  • Article

database object that is included in a publication this Publication Contain difference type like (table , View , Stored ).

********************************************************************************************************************************

I hope that this will be useful topic

Regards

Mostafa Elmasry

References

http://technet.microsoft.com/en-us/library/ms151314(v=sql.105).aspx

 
Leave a comment

Posted by on January 4, 2013 in Replication