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...

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

AlwaysOnSynchronizer version 1.6.0 is now available for download

26/01/2017

This updated release includes the following major changes: 1. Add support for Operators 2. Add support for SQL Server 2016 3. Improve logging for the execution of script files on secondary replicas 4. Fix minor issues related to the initial deployment For additional details about AlwaysOnSynchronizer see this post. To download AlwaysOnSynchronizer click here.   Following the installation before running the program for the first time it is recommended to run the sql script file named drop_procedures.sql located under the AlwaysOnSynchronizer_1.6_Setup\Uninstall folder. Doing so you assure that you are using the latest version of the stored procedures. The next...

Shorten Replication failover time with AlwaysOn Availability Groups

05/01/2017

Recently, on a production system where we had implemented AlwaysOn Availability Groups not too long a go we experienced a situation where following a planned failover we noticed a latency in the delivery of commands to our Transactional Replication subscribers. Looking into this issue I found that the reason for the latency experienced is that the log reader was not harvesting transactions from the published database transaction log. When querying MSlogreader_history I noticed that the log reader was just scanning the VLFs looking for the LSN where it should start delivering transactions.  So executing sp_replcounters simply returned 0 for...

Query msdb backupset to get backup information

03/01/2017

Check out this useful query over msdb..backupset to get all backup information needed and also useful when need to restore transaction logs Note that the query returns the most recent backup per each database but if you comment out the line that comes just after this remark /* Get the most recent backup of type FULL */  you get all rows --SELECT DB_NAME(database_id) AS , name, physical_name, size /128 AS size_mb, state_desc FROM sys.master_files WHERE database_id > 4-- SELECT LEFT( physical_name, 3) drive ,SUM(size)/128 size FROM sys.master_files GROUP BY LEFT( physical_name, 3)USE msdb; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;...
no comments