AlwaysOnSynchronizer 1.7.2 is now available for download

13/01/2018

For some background information see the very first post of AlwaysOnSynchronizer here. For additional details about setup and configuration of AlwaysOnSynchronizer see this post. To download the latest release of AlwaysOnSynchronizer click here.   This release adds support for Regular Expressions to be used with the configuration option “ScriptJobsToExclude”. It can be helpful in environments where there are many jobs with the same pattern in the job names. The bellow example of my Servers.config file has the job named “Regex-Test” in three variations. Here is the outcome when using Regex. When “^” is used (“^Regex-Test”) this job only...
2 comments

Take advantage of the “Blocked Process Report” event

27/12/2017

If you are new to SQL Trace “server side traces” you may want to read this post first.   Recently I have been working on a project where the customer has hired me for performance tuning. Analyzing the system it turned out the degraded performance experienced is mostly due to high contention that the system suffers from,  and based on experience I was, in my mind, already making the assumption that when I get rid of these blocking activity the system's performance will be in a different place. One of the great challenges was to address a severe contention...
no comments

Trace Flag 1448 – Replication with Always On Availability Groups

14/07/2017

  Working with Replication in an environment where Always On Availability Groups provides high availability brings a few challenges unique to this environment. To begin with when setting replication with Always On you should follow this kb providing a detailed description as per the steps required and configuration.   One important point to be aware of is that there are some situations where your log reader can stop reading transactions from the published database t-log resulting in your subscribers being way behind real time. This is where Trace Flag 1448 comes in to help. See Trace Flag 1448...

AlwaysOnSynchronizer 1.7.0 is now available for download

24/06/2017

  For additional details about AlwaysOnSynchronizer see this post. To download AlwaysOnSynchronizer click here.   The new release includes some minor bug fixes: 1. The “ScriptLogins” option did not script a login deleted on the primary replica 2. The “ScriptJobs” option did not script a job that had a new schedule added In addition there is a major change in the configuration file (Servers.config) that now requires us to define all replicas that should be synchronized where as in previous versions we only had to define the Primary replica and the tool issued a query to retrieve...
4 comments

Transactional Replication Customized Profiles–LogReader Agent

16/06/2017

In this post I introduce a stored procedure that is used to enforce a customized profile for a log reader agent in Transactional Replication, Peer-2-Peer and CDC (Change Data Capture). Before you go please see this post which has more details about the topic of Replication Agents Profiles and how I implement them.   The MonitorLogReaderProfile stored procedure does the following: 1. Creates a customized log reader agent profile named “CustomizedLogReaderProfile” 2. Set the “CustomizedLogReaderProfile” to be the default profile for log reader agents 3. Verify that the current parameters of any existing profile named “CustomizedLogReaderProfile” match the values...

Transactional Replication Customized Profiles–Distribution Agent

When working with Transactional Replication you may want to use a different profile for any of the agents involved (i.e. distribution agent or the log reader agent) than the default profile you get at first just following your setup. An agent profile is a way to define a set of parameters that you pass to the replication agent (i.e. logread.exe, distrib.exe). The values defined in the agent profile are the values passed as parameters to the executable which in the case of the Distribution Agent it is distrib.exe. So if for any reason we would like to work with...

Distribution Agent (sp_MSupdatelastsyncinfo) blocking when using subscriptionstreams

11/06/2017

Today I received a few notification emails from a client who's production environment I monitor and maintain. I received a “Query timeout expired” message coming from MSrepl_errors and since I have a few tens of agents in this system there were many messages… in addition I also received notification about contention indicating a that a process is waiting on a resource held by another process for a long term and so it is blocked and cannot proceed until the resource it is waiting for is released. I used my LeadBlockers query (see the very bottom of this post) to...

MonitorReplicationDistributionAgentsSynschronizationStatus

25/05/2017

  In this post I share a stored procedure I have recently added to my Transactional Replication monitoring toolbox. The procedure which I have called “MonitorReplicationDistributionAgentsSynschronizationStatus” is based on top of a few procedures used by the Replication Monitoring built in UI tool by Microsoft. Credits go to a colleague DBA Saby Levy who coded the query while I have only twisted it a little for my needs. I think it is real cool as it gives you a true indication to the last time a synchronization took place by the distribution agent. So if anything along the delivery...

Transactional Replication – Know your Distribution Queue

01/05/2017

Here is a useful query that lets you see the undelivered pending commands at the distribution database per each distribution agent at the table level.   SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SELECT @@SERVERNAME AS --,a.article_id ,a.article ,p.publication ,S.UndelivCmdsInDistDB ,s.DelivCmdsInDistDB ,agents.nameFROM distribution.dbo.MSdistribution_status AS sINNER JOIN distribution.dbo.MSarticles AS a ON a.article_id = s.article_id INNER JOIN distribution.dbo.MSpublications AS p ON p.publication_id = a.publication_idINNER JOIN distribution.dbo.MSdistribution_agents agents on agents.id = s.agent_idWHERE 1=1 AND subscriber_db NOT LIKE...
no comments