While I was piecing together a few queries for a report intended to deliver subscription information about subscribers that are nearing the expiration threshold for replication I found myself needing to calculate warning threshold levels. In one case I found not only do I need to calculate the threshold warning criteria but I also needed to round the outcome to the nearest whole integer.
As you probably know the default retention period for replication defaults to 14 days; however, in replication monitor you can set up warnings to indicate if a subscriber is nearing the threshold. Typically this is 80% so in short I am going to take the retention value and multiple it by .8 and round to the nearest whole integer. For this example I really only need to query data from one table [MSreplication_monitordata] which resides within the distribution database. If you have replication setup then you will have a distribution database, but if you don’t then the distribution database will not be available. Also note this example is based on a single publication so if you have multiple publications then you will see more than one entry per subscriber per publication.
Step 1: Simple Query
I just want the retention period…
SELECT retention FROM [Distribution]..[MSreplication_monitordata]
Now we know what the retention period is set to and that is 14 days. Now I need to multiply the retention value by .8 and the result will be 11.2. So 80% of 14 = 11.2.
Step 2: Simple Calculation
SELECT retention ,(retention * .8) FROM [Distribution]..[MSreplication_monitordata]
Keep in mind if you want to get the threshold value of the metric being monitored from the database JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id and pull the t.[value] which will return the value of 80 which is the default. If it was changed then of course the value will differ. Just note that this is a sql_variant data type so you will need to CAST or CONVERT it to perform the calculation.
SELECT m.retention ,t.[value] -- These steps are just for illustration ,(CAST(t.[value] AS DECIMAL(10,2))/100) '1' ,(m.[retention] * (CAST(t.[value] AS DECIMAL(10,2))/100)) '2' ,CAST((m.[retention] * (CAST(t.[value] AS DECIMAL(10,2))/100)) AS INT) '3' -- The following is faster and easier ,(m.retention * CAST(t.[value] AS INT)/100) '4' FROM [Distribution]..[MSreplication_monitordata] m JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id
What this means in terms of Replication Monitor is that any subscriber that has not synchronized for 11 days with the retention set to 14 days will have a subscription status of “Expiring Soon/Expired“. If the retention was set to 30 days which is also common and the threshold set to the default of 80% then the subscribers would show the same the status when they reach 24 days without synchronizing.
Step 3: Round the Results
11.2 is a good start, but I need a whole number so I will wrap line 2 from (Step 2) inside a ROUND function and supply 0 for the length or precision as it needs one argument.
SELECT retention ,ROUND((retention * .8) ,0) FROM [Distribution]..[MSreplication_monitordata]
Step 4: Remove the Decimal
The above statement returns a value of 11.0 which is rounded, but now I need to get rid of the decimal place so that 11 is the only thing returned. Again I will take line 2 this time from (Step 3) and wrap it in a CAST function and CAST it as INT.
SELECT retention ,CAST(ROUND((retention * .8) ,0) AS INT) FROM [Distribution]..[MSreplication_monitordata]
Or you could have saved yourself a few steps and done this…
SELECT retention ,CAST((retention * .8) AS INT) FROM [Distribution]..[MSreplication_monitordata]
When I apply the rounded value to my query as part of the WHERE clause only the records that fall within the expiration threshold (retention * .8) will be returned. Meaning subscriptions that are close to expiration and subscriptions that are expired.
The results are exactly what I wanted. Now I just need to take it a bit further… think email delivery!
I have wrapped this into a stored procedure and added some logic that will check for expiring records and if there are records found it will then tie the results into a nice html formatted message delivered by database mail. Then I setup a sql job which runs the stored procedure daily including weekends and fires off an email only when records are found. Technically I could fire off a report subscription which can be formatted very nicely and just fire off the job using exec sp_start_job @JobName = ‘Report Subscription ID goes here’ when ever records are detected.
For more information about using ROUND please visit: http://msdn.microsoft.com/en-us/library/ms175003.aspx
Download the code
/***************************************************************** ** Author : Samson J. Loo (justsamson.com | @just_samson) ** Created : 9/26/2010 ** Intent : Notification of subscriptions that are nearing the ** the expiration threshold ** Notes : Requires db mail ** Version : 0.01 *****************************************************************/ IF OBJECT_ID('sp_RP_GetExpiringSubscribers') IS NOT NULL DROP PROCEDURE sp_RP_GetExpiringSubscribers GO CREATE PROCEDURE [dbo].[sp_RP_GetExpiringSubscribers] AS DECLARE @cnt INT SET @cnt = 0 SELECT @cnt = COUNT(a.[subscriber_name]) FROM [Distribution]..[MSmerge_sessions] s JOIN [Distribution]..[MSmerge_agents] a ON s.agent_id = a.id JOIN [Distribution]..[MSreplication_monitordata] m ON s.agent_id = m.agent_id JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id WHERE s.end_time IN ( SELECT TOP 1 s1.end_time FROM [Distribution]..[MSmerge_sessions] s1 WHERE s.agent_id = s1.agent_id ORDER BY s1.end_time DESC ) AND DATEDIFF(d,s.[start_time],getdate()) >= CAST((CAST(t.[value] AS DECIMAL)/100)* m.[retention] AS INT) AND t.[isenabled] = 1 GROUP BY s.[start_time] IF @cnt > 0 BEGIN DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'<H1><font color="#FF0000">Expiring Subscription Report</font></H1>' + N'<table border="0" cellspacing="2" cellpadding="2">' + N'<tr><th bgcolor="#BDBDBD">Subscriber</th>' + N'<th bgcolor="#BDBDBD">Status</th> <th bgcolor="#BDBDBD">Delivery Rate</th> <th bgcolor="#BDBDBD">Last Sync</th>' + N'<th bgcolor="#BDBDBD">Duration</th> <th bgcolor="#BDBDBD">Conn Type</th> <th bgcolor="#BDBDBD">Result</th> <th bgcolor="#BDBDBD">Days Behind</th> <th bgcolor="#BDBDBD">Subscriber Status</th></tr>' + CAST ( ( SELECT td = CASE WHEN CHARINDEX('\',a.[subscriber_name]) > 0 THEN LEFT(a.[subscriber_name],CHARINDEX('\',a.[subscriber_name])-1) ELSE a.[subscriber_name] END ,'' ,td = CASE WHEN s.[runstatus] = 3 THEN 'Synchornizing' WHEN s.[runstatus] = 5 THEN 'Retrying failed command' ELSE 'Not Synchronizing' END ,'' ,td = CAST(s.[delivery_rate] AS VARCHAR) + ' rows/sec' ,'' ,td = s.[start_time] ,'' ,td = CAST((s.[duration]/86400) AS VARCHAR) + '.' + CAST(REPLACE(STR(((s.[duration]/3600) - ((s.[duration]/86400) * 24)),2),SPACE(1),0) AS VARCHAR) + ':' + CAST(REPLACE(STR((s.[duration] % 3600/60),2),SPACE(1),0) AS VARCHAR) + ':' + CAST(REPLACE(STR((s.[duration] % 60),2),SPACE(1),0) AS VARCHAR) ,'' ,td = CASE WHEN s.[connection_type] = 1 THEN 'LAN' WHEN s.[connection_type] = 2 THEN 'Dialup' WHEN s.[connection_type] = 3 THEN 'Web Sync' END ,'' ,td = CASE WHEN s.[runstatus] = 1 THEN 'Start' WHEN s.[runstatus] = 2 THEN 'Succeed' WHEN s.[runstatus] = 3 THEN 'In Progress' WHEN s.[runstatus] = 4 THEN 'Idle' WHEN s.[runstatus] = 5 THEN 'Retry' WHEN s.[runstatus] = 6 THEN 'Error' END ,'' ,td = DATEDIFF(d,s.[start_time],getdate()) ,'' ,td = CASE WHEN (DATEDIFF(d,s.[start_time],getdate()) < CAST((CAST(t.[value] AS DECIMAL)/100)* m.[retention] AS INT)) THEN 'Good' WHEN (DATEDIFF(d,s.[start_time],getdate()) <= m.[retention]) THEN 'Expiring Soon' WHEN (DATEDIFF(d,s.[start_time],getdate()) > m.[retention]) THEN 'Expired' END --,m.[retention] FROM [Distribution]..[MSmerge_sessions] s JOIN [Distribution]..[MSmerge_agents] a ON s.agent_id = a.id JOIN [Distribution]..[MSreplication_monitordata] m ON a.id = m.agent_id JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id WHERE s.end_time IN ( SELECT TOP 1 s1.end_time FROM [Distribution]..[MSmerge_sessions] s1 WHERE s.agent_id = s1.agent_id ORDER BY s1.end_time DESC ) AND DATEDIFF(d,s.[start_time],getdate()) >= CAST((CAST(t.[value] AS DECIMAL)/100)* m.[retention] AS INT) AND t.[isenabled] = 1 ORDER BY s.[start_time] FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'WorkingNotifier', @email@example.com', @firstname.lastname@example.org', @subject = 'Expiring Subscription Report', @body = @tableHTML, @body_format = 'HTML' ; END ELSE BEGIN PRINT 'No Records Found!' END
Recently during a deployment I ran through a replication setup rehearsal in order to practice my processes. It is always good practice to run through an upgrade rehearsal and document your steps, experiences and results. To give you a brief background in all of our deployments we use merge replication since we send and receive data between our server and mobile subscribers.
As I ran through the article selection process I selected the appropriate tables, indicated their sync direction (download only or bi-directional), followed by the stored procedures, views and ended with the functions as usual. We didn’t have an indexed views otherwise they would have went before the functions. Little did I realize that a view would so happen to reference two other views, meaning that in order for the initialization to complete successfully the two referenced views would need to be created first. The rule “you cannot put the carriage before the horse” definitely applies here.
So to clarify here is a simple scenario: you have a table article and a view which directly references the specific table… simple enough. Well you cannot have the view process before the table because it has a dependency which requires the table to be in place before the view can be processed. Otherwise the initial snapshot at the subscriber will fail. Hopefully that makes sense.
Well in the case with the two views, they needed to be processed before the initial referencing view can be applied. The quick and dirty work-a-round was to remove the article and reapply it, then generate a new snapshot. Honestly this is really not an acceptable practice even if you have a small subscriber base of three and especially not acceptable when dealing with subscriber numbers that exceed 200+. The problem with this is that if we introduce new subscribers they will fail at the initial snapshot delivery at this very article because of its dependencies. However the process works fine when you do a re-initialization with an existing subscriber.
So a method to rectify this issue is to identify the article processing order by running the sp_HelpMergeArticle as followed:
USE [AdventureWorks] exec sp_HelpMergeArticle
It returned a result set of all the articles including the name (article name), upload_option (sync direction), processing_order and such. The information proved to be very useful as it identified the values for the processing_order were all set to zero. However in the event that you want information returned about a specific article then the following statement will do just that.
USE [AdventureWorks] exec sp_HelpMergeArticle @publication = 'AdventureWorks_publication' ,@article = 'AddressType'
Well the default processing_order values were all set to zero based on the first query. Since all were set to zero the article would always revert back to its original position. Luckily there is an option to change the article by using the sp_ChangeMergeArticle store procedure:
USE [AdventureWorks] exec sp_ChangeMergeArticle @publication = 'AdventureWorks_publication' ,@article = 'AddressType' ,@property = 'processing_order' ,@value = 10 ,@force_invalidate_snapshot = 1 ,@force_reinit_subscription = 0
Just to make sure it worked I re-ran the first query and sure enough the article in question dropped down to the very last position in the results set now that it’s processing_order value was set to 10. The real test revealed the true results when I staged a new subscriber by dropping the local database and clearing out the accounts. The delivery of the initial snapshot processed successfully without any further errors. Now I can rest better knowing that their support staff can add subscribers without running into this issue.
Today I came across a rather trivial scenario where the publication database was set to Simple recovery. Typically I am use to having the recovery model set to Full, because that was a business norm. After a few trials within my development environment (thanks to VMWare) I began to seriously change my position. To be honest it never really settled well with me. Especially knowing that fact that even though the model was set to Full I still could not reap the benefits of a “Point-in-Time” (PIT) restore. Why? Well if you really must know, it is due to the fact that you won’t get exclusive access to the database since it is being replicated. Matter of fact, you’ll get a nifty little error indicating so if you attempt a restore of any sort. Here I’ll prove it to you.
See why did you doubt me? Now with that out of the way I figured I should shift my practice and change the recovery model to Simple for these specific instances and quite frankly for other similar projects going forward. So truthfully the recovery model does not really play a significant factor unless you have plenty of hard drive space that you are willing to sacrifice for log file consumption. Seriously the log file will grow and grow since it logs all transactions while in Full recovery. From a performance standpoint having your replicating DB in simple recovery mode might be a better route. Exactly the same for Bulk-Logged when you are (coincidentally) bulk loading data. It just makes things faster.
I also read an online article published by Microsoft which was sent to me via twitter by a SQL Server (Bad Ass) named Kendal Van Dyke (website | twitter). The article stated that the recovery model can be either of the three. So that there just confirmed my original theory. I must tell you that Mr. Kendal has been extremely responsive to several of my questions even with the topics varying significantly by area. He is a top notch guy! That’s my story and I am sticking to it.
The only drawback that I can think of at this particular moment is that some backup software like Symantec’s Backup Exec requires databases to be set to Full in terms of the recovery model. I would suspect you would be bombarded with notifications screaming at you until you change it. If you know of any drawbacks with having a merge replicated database set to simple recovery, please let me know. I would be more than interested in hearing your experience.