Franz  Bosco

Franz Bosco

1603447200

Peter Allen: REPLicant

A REPL, or online playground, is a great way to allow users to test-drive tools you have built without needing to install a thing, but they are also great for interactive tutorials, sharing snippets and reporting bugs!

The Svelte REPL has been a feature of the svelte ecosystem since time immemorial and while REPLs can be complex, the principles are simple when you know them.

We will take a dive into how the Svelte REPL works and build the simplest possible version. LIVE!

Open Collective: https://opencollective.com/svelte

#svelte #javascript #programming #web-development #developer

What is GEEK

Buddha Community

Peter Allen: REPLicant
Franz  Bosco

Franz Bosco

1603447200

Peter Allen: REPLicant

A REPL, or online playground, is a great way to allow users to test-drive tools you have built without needing to install a thing, but they are also great for interactive tutorials, sharing snippets and reporting bugs!

The Svelte REPL has been a feature of the svelte ecosystem since time immemorial and while REPLs can be complex, the principles are simple when you know them.

We will take a dive into how the Svelte REPL works and build the simplest possible version. LIVE!

Open Collective: https://opencollective.com/svelte

#svelte #javascript #programming #web-development #developer

Jerod  Mante

Jerod Mante

1600305360

SQL Replicate Function Example | Replicate() In SQL

SQL REPLICATE() is an inbuilt function that is used for repeating the string for the specified number of times. The replicate() function is one of the  SQL String Functions, which is used to repeat the existing string for a given number of times. This SQL replicate function accepts both character string or binary data and returns the same type as the specified expression.

SQL Replicate Function

The REPLICATE() function repeats the string a specified number of times. See the following syntax.

SELECT REPLICATE (input_string, count);

Parameters

  1. Input_string: The string which will be replicated the number of times. It can be a string, character, or any number.
  2. Count: This is the number that will specify the number of times string will be repeated.

#sql #sql replicate

Pereira Dias

Pereira Dias

1581748891

How To Configure Replication In MongoDB

In this article, we will discuss the replication process in MongoDB Database. If you want to read the previous articles of this series

Replication is one of the important features in MongoDB. Most probably replication is one of the most important assignments or works for the MongoDB administrator. With the help of replication, we can provide constant data availability from the server. In this article, we will discuss the replication process in MongoDB and also discuss how to implement replication in the MongoDB server.

What is Replication in MongoDB?

In MongoDB, Replication is the process through which we can synchronize the data of a server among multiple servers. In this way, we can provide data redundancy and increase data availability among multiple servers. The replication process always protects a MongoDB database from the loss of a server due to hardware failure or any other reason. In this way, we can provide uninterrupted availability of MongoDB data using replication servers. With the help of replication, we can ensure that the same data is always available in more than one MongoDB Server.

So, if due to any hardware failure or any other reason, the main MongoDB server goes down, then also we can access the data from the replicated server since data has been replicated into another server at regular intervals through the replication process. Also, replication can be done for the purpose of load balancing. If we have a large number of users access the MongoDB database, then instead of connecting a single MongoDB server, we can connect users into multiple servers so that the load can be equally distributed.
We can achieve the below advantages if we use replication in MongoDB for the production environment –

  1. Using Replication, we can keep the data safe.

  2. Replication process always ensure the high availability of data

  3. We can take care of disaster recovery

  4. No downtimes required for maintenance (like backups, index rebuilds, etc.)

  5. Replica Set is always transparent to the application.

What is a Replica Set in MongoDB?

In MongoDB, the replication process can be set up by creating a replica set. In MongoDB, a replica set contains multiple MongoDB servers. In this group of MongoDB servers, one server is known as a Primary Server and others are known as Secondary servers. Every secondary server always keeps copies of the primary’s data. So, if any time the primary server goes down, then the new primary server is selected from the existing secondary server and process goes on. The replication process works as below with the help of a replica set –

  • Replica Set is a group of one or more standalone MongoDB Servers (normally 3 MongoDB Servers are required).

  • In a Replica Set, one server is marked as Primary Server and rest are marked as a Secondary Server.

  • Data writes into the Primary Server from the application first.

  • Then all the data replicates to the secondary servers from the primary server.

  • When the primary server is unavailable due to hardware failure or maintenance work, the election process starts to identify the new primary server and select a primary server from the secondary server lists.

  • When the failed server recovered, it will again join the replica set as a secondary server.

This is image title

An above diagram of MongoDB replication is shown in the below image. In this image, a client application always communicates with the primary node and the primary node replicates the data to the multiple secondary nodes.

How to Configure Replication in MongoDB

In this section, we will discuss how to convert a standalone MongoDB Instance into a replica set. This process is not an ideal process for the production environment. Because in production, if we need to establish a replica set then we need to provide three different MongoDB instances for the replica set. But it is a good process for gaining knowledge about the idea of replication and knowing about the configuration of replication.

Step 1

Start up a mongo shell with the --nodb options from the command prompt. It will start a shell without any connection with the existing mongod instance.

This is image title

Step 2

Now, create a replica set with the below commands,

replicaSet = new ReplSetTest({name:'rsTest', nodes : 3})  

This command instructs the shell to create a replica set with three node servers:- one primary and two secondaries.

Step 3

Now run the below commands one by one to start the mongod server instances,

replicaSet.startSet()  -- this command start the three mongod processes.  
replicaSet.initiate() -- this command configures the replication  

Now, we have three mongod processes locally on ports 20000,20001 and 20002.

Step 4

Now open another command prompt and connect the mongod running on port 20000.

conn1 = new Mongo("localhost:20000")  
connection to localhost:20000  
rsTest: PRIMARY>

Note that, when we connect a replica set member, the prompt changes to rsTest: PRIMARY. Here PRIMARY is the state of the member and rsTest is the identifier of the replica set.

Now, if we want to check that the mongod instance is actually is a primary node or not, then we need to run the below command to check the status of the replica set –

primaryDB.isMaster() 

Change Replication Configuration

After defining a replica set, we can change the replica set at any time. We can add new members, removing any existing members. There is a mongo shell helper method is available to add new replica set members or remove existing replica set members. To add a new member into the replica set, we need to run the below command,

rs.add("server-4:27017")  

Similarly, we can remove any members from the existing replica set using the below command,

rs.remove("server-2:20002")  

If we need to check the existing configuration of the replication, then we need to run the below command in the shell,

rs.config()  

Syncing

The main objectives of the replication process are to keep the same or an identical set of data on multiple servers. For performing this task, MongoDB always maintains a log of operations or oplog which contains every writes information into the primary server. This log is a capped collection which exists in the local database on the primary server. The secondary servers are queries this collection for obtaining the operation details so that they can replicate that data.

Every secondary server maintains its own oplog where MongoDB captures each operation related to the replication process from the primary server. These log files allow any replica set members to use as a sync source for other members. The secondary server always first fetch the information related to the pending operations from the primary members, then apply that operation to their own data set and then writes down the logs about that operation into the oplog.

This is image title

If the secondary server goes down and after some time interval, the same secondary is going up and it starts the syncing process the last operation is done by itself according to its oplog file. As the operation first applied to the data and then it writes to the oplog, the secondary server may replay the operation that it has already applied to its data.

What is Heartbeat in Replication?

In Replication, Heartbeat is the process to identify the current status of the node servers within the replica set. Basically, replica set members send heartbeats (pings) to each other every two seconds. If a heartbeat does not return within 10 seconds, then the other members mark the delinquent member as an inaccessible member. This process is required to know the other member’s state, like who’s primary, from which member they need to sync from or which node is down. A heartbeat request is basically a short message that checks everyone’s current state.

One of the most important activities of heartbeats is to check that the primary server is available to reach for all secondary servers. If the majority of the secondary servers can’t reach to the primary server, then the process automatically demotes that primary server as a secondary server.

Elections

In the MongoDB replication process, if a member can’t reach the primary node then that member raises the election flag to the other members of the replica set. In this way, that member seeking election will send out a notice to all of the members to it can reach, so that other members of the replica set can’t raise the same election flag within the process. Assuming that there is no chance to raise an objection against the election request, the other members will vote for the member seeking election. If the member receives the majority votes from other members, then the election is successful and it will promote as the primary node. If it did not receive the majority of votes then it will remain as secondary and maybe try to become a primary node in the future.

If the network condition is healthy and most of the servers are up, then the entire elections process should be very fast. In this scenario, it will take two seconds to notify all the members that primary has gone down (since heartbeat response was not received yet) and the election process starts immediately.

Conclusion

In this article, we discussed the replication process in MongoDB like what is replication, what is replica set, how to configure it and the process mechanism of the replication process, etc.  In the next article, we will discuss sharding in MongoDB.

Hope, this article will help you. Any feedback or query related to this article is most welcome. Thanks!

#MongoDB #database #replication #developer #programming

Terry  Tremblay

Terry Tremblay

1603768620

REPLicant, a Super Simple Svelte REPL - Peter Allen at Svelte Summit 2020

Peter Allen recently gave a talk at Svelte Summit 2020 in which he explained the benefits of REPL (Read-Print-Eval-Loop) playgrounds. While the Svelte REPL is complex due to the handling of many edge cases, the principles underlying code playground implementations are simple. Allen progressively led the viewer into the implementation of the simplest possible version of the Svelte REPL.

Allen started by emphasizing the importance of online playgrounds to put a piece of technology in the hand of its target users. This may be especially valuable for innovative technologies to progress from the awareness stage to the evaluation stage of the marketing funnel. Allen credited the MDsveX playground for the sustained interest in MDsveX:

About 18 months ago, I published [MDsveX (MDX for Svelte)], a library [that allows using Svelte component in Markdown documents]. Honestly, nobody was interested […] Then one day, a friend was talking to me about how they wished they could write some combination of Markdown and Svelte.

It sounded like they needed MDsveX but they had no idea what it was. The README wasn’t helpful and neither was my explanation. […] I decided to spend a few evenings putting together an example site and an online playground so they could try it out for themselves. I posted a link on Svelte discord, it got shared around on Twitter and all of a sudden there was interest.

Allen mentioned having enjoyed using online, interactive playgrounds to try out a few programming languages. TryRuby a browser-based tutorial for the Ruby programming language, RustTypeScript, and Go have playgrounds too. The Svelte documentation site itself makes heavy use of the Svelte REPL.

#compilers #javascript #developer experience #svelte #web development #development #news

Jordan Sanders

Jordan Sanders

1622100876

Managing your MS SQL Replication

Replication is one of the oldest technologies on MS SQL Server, loved by every database administrator. It is a great and reliable technology for bringing data closer to users, especially for distributed reporting. Thanks to it, the database availability increases across multiple SQL Servers and regions.

Replication was introduced in SQL 2005. Can you believe it’s that old? Though newer managed SQL platforms on the cloud are introduced regularly, I believe that SQL replication will remain here. If it was a bug or insect, I would think of it as a cockroach. It’s hard to squash!
If you are one of those belonging to a small population of administrators who never managed a database, there is official Microsoft documentation on the topic. However, note that is it pretty long, comprehensive and will rob you of some time off from holiday or planned binge-watching TV series. Also, Codingsight offers the SQL Server Database Replication setup and configuration guide.

But before you get your hands dirty with the technical stuff, and I know you’re eager too, it’s important to plan for it.
The replication requirement may change with regards to location when you deploy to SQL Servers running on the cloud. But once the SQL replication is running like a well-oiled machine and replicating production data, you need to plan how you manage it.
In this post, I will share some tips and T-SQL scripts for you to use when you need to check many SQL Agent jobs are created after the replication configuration.

Monitor Replication Agents

When you set up and configure SQL replication, it also creates a set of standalone functions and SQL Agent jobs known as replication agents. Their goal is to carry out tasks associated with moving your tables, also called articles, in the replication configuration from publisher to subscriber/s. You can run replication agents from the command line and by applications that use Replication Management Objects (RMO).

SQL Server replication agents can be monitored and administered via Replication Monitor and SQL Server Management Studio.

This is image title

The primary concern of a database administrator/replication administrator is making sure that all SQL Agents replication jobs are running. If the replication agent job fails, the subscriber may not receive data. Therefore, the distribution database may grow huge because of accumulated rows that won’t move to the subscriber database.

To set an alert for any replication agent job failure, you can create another agent job. It will check the job failures and send an email to your dba team if it identifies problems.

Check for Failed Replication Agent Jobs

Use the below script:

set @time = dateadd(n,-30,getdate()) 
declare @date date 
set @date = convert(date,getdate()) 
declare @publisher varchar(100) 
set @publisher = @@SERVERNAME
SELECT LEFT(name,50) as [JobName], run_date AS [RunDate], run_time AS [RunTime], LEFT([message],50) AS [Message] 
FROM 
(select distinct b.name,a.run_date, run_time, message 
from msdb..sysjobhistory a inner join msdb..sysjobs b on a.job_id = b.job_id where b.name like 'servername here%' and run_status <> 1 and message like '%error%' 
and convert(date,convert(varchar,a.run_date ))= convert(date,getutcdate()) replace(convert(varchar(8),dateadd(n,-30,getutcdate())),':','') ) a 

Create an Email Alert to Notify about the Job Failure

Apply the following script:

@profile_name = 'DBA Alerts', 
@recipients = 'your dba team email here', 
@subject = '[Database name] Replication Jobs Failure', 
@query = 'SELECT LEFT(name,50) as [JobName], run_date AS [RunDate], run_time AS [RunTime], LEFT([message],50) AS [Message] 
FROM 
(select distinct b.name, a.run_date, a.run_time, message 
from msdb.dbo.sysjobhistory a inner join msdb.dbo.sysjobs b on a.job_id = b.job_id 
where b.name like ''servername here %'' and 
convert(date,convert(varchar,a.run_date)) = convert(date,getutcdate()) ) a 
', 
@attach_query_result_as_file = 0 ; 

Monitor the Table Containing Replicated Commands

To monitor the msrepl_commands table, you may use one more script provided below. Note that this table should grow too huge and too fast. If that is the case, the replication agent jobs might fail, or there could be a problem in the replication configuration.

The script is as follows:

SELECT Getdate() AS CaptureTime, LEFT(Object_name(t.object_id),20) AS TableName, st.row_count 
FROM sys.dm_db_partition_stats st WITH (nolock) 
INNER JOIN sys.tables t WITH (nolock) ON st.object_id = t.object_id INNER JOIN sys.schemas s WITH (nolock) ON t.schema_id = s.schema_id WHERE index_id < 2 AND Object_name(t.object_id) 
IN ('MSsubscriptions', 'MSdistribution_history', 'MSrepl_commands', 'MSrepl_transactions') 
ORDER BY st.row_count DESC

The msreplcommands table growth trend also gives you a hint of how healthy your replication latency is. There are many factors of impact. If your environment is in the cloud, the region selection may contribute a big deal to replication latency.

Create a Simple Reporting on Replication and Send it by Email

You may use the following script:

-- Set Publisher server and database name 
Set @Publisher = 'publication server name'; 
Set @PublisherDB = 'publishing database name'; 
-- Refresh replication monitor data 
USE [distribution] 
Exec sys.sp_replmonitorrefreshjob @iterations = 1; 
With MaxXact (ServerName, PublisherDBID, XactSeqNo) 
As (Select S.name, DA.publisher_database_id, max(H.xact_seqno) From dbo.MSdistribution_history H with(nolock) 
Inner Join dbo.MSdistribution_agents DA with(nolock) On DA.id = H.agent_id 
Inner Join master.sys.servers S with(nolock) On S.server_id = DA.subscriber_id 
Where DA.publisher_db = @PublisherDB 
Group By S.name, DA.publisher_database_id), OldestXact (ServerName, OldestEntryTime) 
As (Select MX.ServerName, Min(entry_time) 
From dbo.msrepl_transactions T with(nolock) 
Inner Join MaxXact MX On MX.XactSeqNo < T.xact_seqno And 
MX.PublisherDBID = T.publisher_database_id 
Group By MX.ServerName) 
Select [Replication Status] = Case MD.status 
When 1 Then 'Started' 
When 2 Then 'Succeeded' 
When 3 Then 'In progress' 
When 4 Then 'Idle' 
When 5 Then 'Retrying' 
When 6 Then 'Failed' 
End, 
Subscriber = SubString(MD.agent_name, Len(MD.publisher) + 
Len(MD.publisher_db) + Len(MD.publication) + 4, 
Charindex('-', MD.agent_name, 
Len(MD.publisher) + Len(MD.publisher_db) + 
Len(MD.publication) + 5) - 
(Len(MD.publisher) + 
Len(MD.publisher_db) + Len(MD.publication) + 4)), 
[Subscriber DB] = A.subscriber_db, 
[Publisher DB] = MD.publisher_db, 
Publisher = MD.publisher, 
[Current Latency (sec)] = MD.cur_latency,
[Current Latency (hh:mm:ss)] = Right('00' + Cast(MD.cur_latency/3600 As varchar), 2) + 
':' + Right('00' + 
Cast((MD.cur_latency%3600)/60 As varchar), 2) + 
':' + Right('00' + 
Cast(MD.cur_latency%60 As varchar), 2), 
[Latency Threshold (min)] = Cast(T.value As Int), 
[Agent Last Stopped (sec)] = DateDiff(hour, agentstoptime, getdate()) - 1, 
[Agent Last Sync] = MD.last_distsync, 
[Last Entry TimeStamp] = OX.OldestEntryTime 
From dbo.MSreplication_monitordata MD with(nolock) 
Inner Join dbo.MSdistribution_agents A with(nolock) On A.id = MD.agent_id Inner Join dbo.MSpublicationthresholds T with(nolock) On T.publication_id = MD.publication_id And T.metric_id = 2 -- Latency 
Inner Join OldestXact OX On OX.ServerName = SubString(MD.agent_name, Len(MD.publisher) + Len(MD.publisher_db) + 
Len(MD.publication) + 4, 
Charindex('-', MD.agent_name, 
Len(MD.publisher) + Len(MD.publisher_db) + 
Len(MD.publication) + 5) - 
(Len(MD.publisher) + 
Len(MD.publisher_db) + Len(MD.publication) + 4)) 
Where MD.publisher = @Publisher 
And MD.publisher_db = @PublisherDB 
And MD.publication_type = 0 -- 0 = Transactional publication And MD.agent_type = 3; -- 3 = distribution agent 
IF (@@ROWCOUNT > 500) 
BEGIN 
-- send alerts here.. 500 rows of undistributed transactions , should be higher. run this on remote distributor 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'DBA Alert', 
@recipients = 'your dba team email here', 
@body = 'This is replication latency alert. Check undistributed transactions query.', 
@subject = 'Replication Latency Alert' ; 
PRINT 'Alert here!' --since email is not yet working 
END

Query the Articles List and Check for the Subscribers’ Health

If you are working on a transaction replication, these operations are extremely important. Here is a script:

, LEFT(a.publisher_db, 50) AS publisher_db 
, LEFT(p.publication,25) AS publication_name
, LEFT(a.article, 50) AS [article] 
, LEFT(a.destination_object,50) AS destination_object 
, LEFT(ss.srvname,25) AS subscription_server 
, LEFT(s.subscriber_db,25) AS subscriber_db 
, LEFT(da.name,50) AS distribution_agent_job_name 
FROM distribution..MSArticles a 
JOIN distribution..MSpublications p ON a.publication_id = p.publication_id JOIN distribution..MSsubscriptions s ON p.publication_id = s.publication_id JOIN master..sysservers ss ON s.subscriber_id = ss.srvid 
JOIN master..sysservers srv ON srv.srvid = p.publisher_id 
JOIN distribution..MSdistribution_agents da ON da.publisher_id = p.publisher_id AND da.subscriber_id = s.subscriber_id 
ORDER BY 1,2,3 

Create a Reporting Summary for the DBA Team

To combine all replication statistics and delivered and undelivered commands, you can create a table in the distribution database to contain all the replication details.

From this table, you can create a reporting summary to distribute to the dba team. This table can be refreshed every day as part of the daily replication health check aside from the standard database administrator morning health check.

IF OBJECT_ID('Tempdb.dbo.#ReplStats') IS NOT NULL 
DROP TABLE #ReplStats 
CREATE TABLE [dbo].[#ReplStats] ( 
[DistributionAgentName] [nvarchar](100) NOT NULL 
,[DistributionAgentStartTime] [datetime] NOT NULL 
,[DistributionAgentRunningDurationInSeconds] [int] NOT NULL ,[IsAgentRunning] [bit] NULL 
,[ReplicationStatus] [varchar](14) NULL 
,[LastSynchronized] [datetime] NOT NULL 
,[Comments] [nvarchar](max) NOT NULL 
,[Publisher] [sysname] NOT NULL 
,[PublicationName] [sysname] NOT NULL 
,[PublisherDB] [sysname] NOT NULL 
,[Subscriber] [nvarchar](128) NULL 
,[SubscriberDB] [sysname] NULL 
,[SubscriptionType] [varchar](64) NULL 
,[DistributionDB] [sysname] NULL 
,[Article] [sysname] NOT NULL 
,[UndelivCmdsInDistDB] [int] NULL 
,[DelivCmdsInDistDB] [int] NULL 
,[CurrentSessionDeliveryRate] [float] NOT NULL 
,[CurrentSessionDeliveryLatency] [int] NOT NULL 
,[TotalTransactionsDeliveredInCurrentSession] [int] NOT NULL
,[TotalCommandsDeliveredInCurrentSession] [int] NOT NULL ,[AverageCommandsDeliveredInCurrentSession] [int] NOT NULL ,[DeliveryRate] [float] NOT NULL 
,[DeliveryLatency] [int] NOT NULL 
,[TotalCommandsDeliveredSinceSubscriptionSetup] [int] NOT NULL ,[SequenceNumber] [varbinary](16) NULL 
,[LastDistributerSync] [datetime] NULL 
,[Retention] [int] NULL 
,[WorstLatency] [int] NULL 
,[BestLatency] [int] NULL 
,[AverageLatency] [int] NULL 
,[CurrentLatency] [int] NULL 
) ON [PRIMARY] 
INSERT INTO #ReplStats 
SELECT da.[name] AS [DistributionAgentName] 
,dh.[start_time] AS [DistributionAgentStartTime] 
,dh.[duration] AS [DistributionAgentRunningDurationInSeconds] ,md.[isagentrunningnow] AS [IsAgentRunning] 
,CASE md.[status] 
WHEN 1 
THEN '1 - Started' 
WHEN 2 
THEN '2 - Succeeded' 
WHEN 3 
THEN '3 - InProgress' 
WHEN 4 
THEN '4 - Idle' 
WHEN 5 
THEN '5 - Retrying' 
WHEN 6 
THEN '6 - Failed' 
END AS [ReplicationStatus] 
,dh.[time] AS [LastSynchronized] 
,dh.[comments] AS [Comments] 
,md.[publisher] AS [Publisher] 
,da.[publication] AS [PublicationName] 
,da.[publisher_db] AS [PublisherDB] 
,CASE 
WHEN da.[anonymous_subid] IS NOT NULL 
THEN UPPER(da.[subscriber_name]) 
ELSE UPPER(s.[name]) 
END AS [Subscriber] 
,da.[subscriber_db] AS [SubscriberDB] 
,CASE da.[subscription_type] 
WHEN '0' 
THEN 'Push' 
WHEN '1' 
THEN 'Pull'
WHEN '2' 
THEN 'Anonymous' 
ELSE CAST(da.[subscription_type] AS [varchar](64)) 
END AS [SubscriptionType] 
,md.[distdb] AS [DistributionDB] 
,ma.[article] AS [Article] 
,ds.[UndelivCmdsInDistDB] 
,ds.[DelivCmdsInDistDB] 
,dh.[current_delivery_rate] AS [CurrentSessionDeliveryRate] ,dh.[current_delivery_latency] AS [CurrentSessionDeliveryLatency] ,dh.[delivered_transactions] AS 
[TotalTransactionsDeliveredInCurrentSession] 
,dh.[delivered_commands] AS [TotalCommandsDeliveredInCurrentSession] ,dh.[average_commands] AS [AverageCommandsDeliveredInCurrentSession] ,dh.[delivery_rate] AS [DeliveryRate] 
,dh.[delivery_latency] AS [DeliveryLatency] 
,dh.[total_delivered_commands] AS 
[TotalCommandsDeliveredSinceSubscriptionSetup] 
,dh.[xact_seqno] AS [SequenceNumber] 
,md.[last_distsync] AS [LastDistributerSync] 
,md.[retention] AS [Retention] 
,md.[worst_latency] AS [WorstLatency] 
,md.[best_latency] AS [BestLatency] 
,md.[avg_latency] AS [AverageLatency] 
,md.[cur_latency] AS [CurrentLatency] 
FROM [distribution]..[MSdistribution_status] ds 
INNER JOIN [distribution]..[MSdistribution_agents] da ON da.[id] = ds.[agent_id] 
INNER JOIN [distribution]..[MSArticles] ma ON ma.publisher_id = da.publisher_id 
AND ma.[article_id] = ds.[article_id] 
INNER JOIN [distribution]..[MSreplication_monitordata] md ON [md].[job_id] = da.[job_id] 
INNER JOIN [distribution]..[MSdistribution_history] dh ON [dh].[agent_id] = md.[agent_id] 
AND md.[agent_type] = 3 
INNER JOIN [master].[sys].[servers] s ON s.[server_id] = da.[subscriber_id] 
--Created WHEN your publication has the immediate_sync property set to true. This property dictates 
--whether snapshot is available all the time for new subscriptions to be initialized. 
--This affects the cleanup behavior of transactional replication. If this property is set to true, 
--the transactions will be retained for max retention period instead of it getting cleaned up 
--as soon as all the subscriptions got the change. 
WHERE da.[subscriber_db] <> 'virtual' 
AND da.[anonymous_subid] IS NULL
AND dh.[start_time] = ( 
SELECT TOP 1 start_time 
FROM [distribution]..[MSdistribution_history] a 
INNER JOIN [distribution]..[MSdistribution_agents] b ON a.[agent_id] = b.[id] 
AND b.[subscriber_db] <> 'virtual' 
WHERE [runstatus] <> 1 
ORDER BY [start_time] DESC 
) 
AND dh.[runstatus] <> 1 
SELECT 'Transactional Replication Summary' AS [Comments]; 
SELECT [DistributionAgentName] 
,[DistributionAgentStartTime] 
,[DistributionAgentRunningDurationInSeconds] 
,[IsAgentRunning] 
,[ReplicationStatus] 
,[LastSynchronized] 
,[Comments] 
,[Publisher] 
,[PublicationName] 
,[PublisherDB] 
,[Subscriber] 
,[SubscriberDB] 
,[SubscriptionType] 
,[DistributionDB] 
,SUM([UndelivCmdsInDistDB]) AS [UndelivCmdsInDistDB] 
,SUM([DelivCmdsInDistDB]) AS [DelivCmdsInDistDB] 
,[CurrentSessionDeliveryRate] 
,[CurrentSessionDeliveryLatency] 
,[TotalTransactionsDeliveredInCurrentSession] 
,[TotalCommandsDeliveredInCurrentSession] 
,[AverageCommandsDeliveredInCurrentSession] 
,[DeliveryRate] 
,[DeliveryLatency] 
,[TotalCommandsDeliveredSinceSubscriptionSetup] 
,[SequenceNumber] 
,[LastDistributerSync] 
,[Retention] 
,[WorstLatency] 
,[BestLatency] 
,[AverageLatency] 
,[CurrentLatency] 
FROM #ReplStats 
GROUP BY [DistributionAgentName] 
,[DistributionAgentStartTime] 
,[DistributionAgentRunningDurationInSeconds] 
,[IsAgentRunning]
,[ReplicationStatus] 
,[LastSynchronized] 
,[Comments] 
,[Publisher] 
,[PublicationName] 
,[PublisherDB] 
,[Subscriber] 
,[SubscriberDB] 
,[SubscriptionType] 
,[DistributionDB] 
,[CurrentSessionDeliveryRate] 
,[CurrentSessionDeliveryLatency] 
,[TotalTransactionsDeliveredInCurrentSession] 
,[TotalCommandsDeliveredInCurrentSession] 
,[AverageCommandsDeliveredInCurrentSession] 
,[DeliveryRate] 
,[DeliveryLatency] 
,[TotalCommandsDeliveredSinceSubscriptionSetup] 
,[SequenceNumber] 
,[LastDistributerSync] 
,[Retention] 
,[WorstLatency] 
,[BestLatency] 
,[AverageLatency] 
,[CurrentLatency] 
SELECT 'Transactional Replication Summary Details' AS [Comments]; 
SELECT [Publisher] 
,[PublicationName] 
,[PublisherDB] 
,[Article] 
,[Subscriber] 
,[SubscriberDB] 
,[SubscriptionType] 
,[DistributionDB] 
,SUM([UndelivCmdsInDistDB]) AS [UndelivCmdsInDistDB] ,SUM([DelivCmdsInDistDB]) AS [DelivCmdsInDistDB] 
FROM #ReplStats 
GROUP BY [Publisher] 
,[PublicationName] 
,[PublisherDB] 
,[Article] 
,[Subscriber] 
,[SubscriberDB] 
,[SubscriptionType] 
,[DistributionDB]

Summary

I hope that these few T-SQL scripts provided above will help you in your replication agents’ monitoring. I highly recommend you monitor them closely. Otherwise, users at the subscriber’s end may complain endlessly about not having (close to) real-time data.

In the coming articles, I will dig deeper into the SQL technology of replicating data to any part of the globe. Happy monitoring!

Author: Carla Abanes
Originally posted at https://codingsight.com/managing-your-ms-sql-replication/

#sql #sql-server #database #mysql #replication #tsql