Replication Agent Profiles explained Part 1

As we know there are 3 Agents that are needed in order to Transactional Replication to work: LogReader, Snapshot and Distribution Agent. Each with different role. Each of these is running under given Agent Profile. What is Agent Profile? In short: these are the settings that our Replication Agents are running with, such as Batch sizes, History level settings etc. Let’s go into the details

Agent Profiles GUI (Replication Monitor)

You can change and see profile setting by going to Replication Monitor, open given agent (Snapshot, Log reader, Distribution):

Once you open it you can see stock standard SQL Server Agent Profile with Default profile as a current one if nothing been changed since Replication configuration:

You can create your new custom profile by clicking New and using one of the System profiles as a base of that user profile:

In order to see ALL settings that you can set you need to untick the checkbox. Otherwise you will see only settings used by the profile you choose at the beginning :

In order to change agent profile you just need to choose agent profile and restart given Replication Agent (from SQL 2014 SP3 the restart of the Agent is not necessary)

Agent Profile T-SQL

As always you can do exactly same thing using T-SQL.

Here are some useful procedures that can help you manage these. All these procedures need to be executed on distributor.

These need to be run on Distribution DB:

exec sp_help_agent_profile – list all profiles that are present on a given distributor. It is also showing which profile is a default for newly created agents

exec sp_help_agent_parameter – shows all parameters that are set for all profiles. @profile_id can be provided to show only settings for given profile.

Here are some system tables can be run on msdb database on distributor, these are pretty much base tables used by procedures I mentioned before:

select * from [dbo].[MSagent_profiles]
select * from [dbo].[MSagent_parameters]
select * from [dbo].[MSagentparameterlist]

If you want to change profiles settings or change assigned profile to given agent you need to use following procedures.

exec sp_add_agent_parameter – adding parameter to agent profile
exec sp_add_agent_profile – adding new profile
exec sp_change_agent_parameter – changing parameter of a given profile
exec sp_change_agent_profile– changing description of a given profile
exec sp_drop_agent_parameter – dropping parameter from the given profile
exec sp_drop_agent_profile – dropping agent profile

exec sp_update_agent_profile – assigning new profile for given Replication Agent

Here is small script that will give you a distribution agents mapped to agent profiles and what parameters these has set.

select a.name,p.profile_name,p.def_profile, par.parameter_name,par.value from MSdistribution_agents a
join msdb.[dbo].[MSagent_profiles] p
on a.profile_id = p.profile_id
join msdb.dbo.[MSagent_parameters] par
on p.profile_id=par.profile_id

As you can see you can add/modify agent profiles either via GUI or T-SQL. Same relates to assigning profiles to give agents (distribution/logreader etc.) however in this case you can “hard-code” the agent profile into given replication agent job. You can read about replication jobs more over here: Understanding Replication Agents Part 2. You can add -ProfileName in the replication agent parameters and then this setting will overwrite the one you can see in GUI or using T-SQL. You can also specify batch sizes and much more. You can check all agent parameters over here: https://docs.microsoft.com/en-us/sql/relational-databases/replication/agents/replication-distribution-agent?view=sql-server-2017

I would not recommend that as then you loosing visibility of what is really going on and under which profile the agents are running. Here is an example… I put in the job parameter -ProfileName [Default agent profie] and did restart the job:

I have change profile using GUI to Continue on data consistency errors:

Then I deleted one row on subscriber and tried to delete same row on Publisher. Running on Continue on data consistency errors profile agent should skip the error. But in our case we hard-coded the profile in the job…. and that is why we are getting error anyways:

I never put profile name (or any other related to profiles parameters) into the job directly and I strongly recommend the same to everyone.

In next blog post I will go more into details on some of the parameters used in Agent Profiles. In some cases you want to play around with these settings as it can improve performance of your replication.

October 6, 2017October 6, 2017

[TSQL Script] Browse replication commands

While you can use just simple stored proc to browse commands (How to “Browse” Distributor (sp_browsereplcmds explained)) you can notices few issues with that proc… for example commands are not ordered by command_id. Here is some script I created. I found it useful especially if I want see the order of commands applied to subscriber:)

You just need to provide, published database name, Seqno of transaction that you want to check (you can get it from replication monitor, query msrepl_transactions etc.) and command id that you want to check. Leave NULL if you want to see ALL commands in given transaction.

--BROWSE REPL COMMANDS

declare @publisher_db nvarchar(150)
declare @publisher_db_id int
declare @seqno nvarchar(500)
declare @seqno_bin varbinary(16)
declare @error_id int
declare @error_txt nvarchar(1000)
declare @command_id int


/*-----------------PARAMETERS---------------------*/
set @publisher_db='<Published db>'
set @seqno_bin=<LSN>
set @seqno='<LSN>'
set @command_id = <command id> -- Leave NULL if all you want to get ALL commands from that transaction.
/*-----------------------------------------------*/

create table #temp_commands (
xact_seqno    varbinary(16) NULL,                    
originator_srvname   varchar(100) NULL,                                                                                                            
originator_db        varchar(100) NULL,                                                                                                            
article_id   		 int NULL,
type        		  int NULL,
partial_command 		bit NULL,
hashkey 				int NULL,
originator_publication_id  int NULL,
originator_db_version      int NULL,
originator_lsn         varbinary	(16) NULL,
command                 nvarchar   (1024) NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
command_id				int NULL)

select @publisher_db_id=id from mspublisher_databases where publisher_db=@publisher_db
select @error_id=error_id from msdistribution_history where xact_seqno=@seqno_bin
select top 1 @error_txt=error_text from  dbo.MSrepl_errors where id = @error_id

insert into #temp_commands
exec sp_browsereplcmds 
@xact_seqno_start=@seqno,
@xact_seqno_end=@seqno,
@publisher_database_id=@publisher_db_id

if @command_id is NULL
BEGIN
select command,command_id, @error_txt as 'Error' from #temp_commands order by command_id
END
ELSE
BEGIN
select command,command_id, @error_txt as 'Error' from #temp_commands where command_id=@command_id order by command_id
END
drop table #temp_commands

September 7, 2017September 5, 2017

Execs and desktop heap

Agent Processing Account

In previous post I explained how Replication agents works. It is pretty mych exe running on Distributor (or in pull replication distrib.exe on Subscriber). As always each exe is running under some kind of Windows account. Like here: execs

As you can see distrib.exe and logread.exe are running on SQL Service account and also you notice each if these is using RAM. That account you choosing when you configure publication (configuring snapshot and logreader processing account) and subscriber (configuring distribution agent). Every time you configuring one of these things you need to provide Security settings. There are two things that need to be provided. First processing account and second authentication account. In some cases you can use same account for both, like here:

SecuritySettings

As you can see we will be using SQL Service account for processing logread.exe and also we will use same account in order to connect to Publisher (in order to read transactions). Side note: If you choosing to do so, you need to make sure that this account has all necessary permissions on Publisher server.

Side note 2: You can modify these settings always by going to Publication settings/properties (to modify snapshot and logreader security settings) and Subscription settings/properties (to modify Distribution Agent settings).

We focusing now only on first part. Processing account this is the one that exe will be running under.

You probably wonder why I’m writing about this? Well it is to do with “noninteractive desktop heap “.

Noninteractive desktop heap

Now I would like to put small disclaimer. You may not get to this point where you will get that issue. What is the exact issue?

Let’s assume you have one remote distributor that is used to run 50,60, 100 or more distribution agents. Each of this agent is configured to run under SQL Service account. Each agent is running exe under that account and each process taking some amount of memory on that instance. It is also taking so called “noninteractive desktop heap” that is allowing running processes concurrently. That value is limited for each processing account and it means once u reach it for SQL Service account you wont be able to run any more replication agents.

Once you get to this point the replication agents just wont run… the problem is it wont give you ANY error or just fail without giving any description… it will just stuck on “Starting agent” and finally fail after sometime.

You can test if you have that issue already and just run exe command manually under your account (if you have all necessary permissions on the server) and if agent is running fine then most probably you reached that limit. How to run replication agents manually I described here:

Understanding Replication Agents Part 2

Solution:

Change processing account to use different Domain account. Remember it need to have proper permissions on Distributor. You will need to restart agent after you change processing account.
Increase Desktop Heap Size. However this need server reboot:Increasing the noninteractive desktop heap size

I normally go with solution no. 1 and create few more domain account and run agent under these accounts.

September 5, 2017September 5, 2017

Understanding Replication Agents Part 2

What is really happening when snapshot, logreader and distribution job is running on SQL Server? I will try to explain in that blog post.

SQL Jobs

As I described before in Part 1 All replication agents are really SQL Jobs located either on Distributor server or in some cases (pull replication) on Subscribers. When you go inside the SQL job (exactly second step of the job) like distribution agent u will see few strange (or not so strange) things/parameters like this:

-Subscriber [MAINTEST\SUBSCRIBER] -SubscriberDB [subscriberDB] -Publisher [MAINTEST\PUBLISHER] -Distributor [MainTest\DISTRIBUTOR] -DistributorSecurityMode 1 -Publication [Pub1] -PublisherDB [AdventureWorks2014] -Continuous

It is pretty self-explanatory however there is more than only above settings. In this place you can modify subscriber or Distributor Authentication, you add multiple streams or hard code what agent profile it should use etc. Full list of settings you can find here… and it is quite a list:)

Distribution Agent Settings

Snapshot Agent Settings

LogReader Agent Settings

I need to make note that if you make change in SQL job it will override specific settings that you set via GUI. For Example if you use parameter [-ProfileName profile_name] this setting will be always used, even when you change agent profile using Replication Monitor.

Last thing that is crucial, you need to stop and start agent to make new settings “live”.

Execs

Now, how these setting are really executed? How it really works? Well it is pretty straight forward. It is using execs from COM folder where you installed SQL Server (mostly Program Files etc.).You can also go to Distributor and look at Task Manager on the server, you will notice these execs running:

execs

You can also see under what account that exec is running. There is one particular reason why you should not run ALL your agents under SQL Service account. I will describe it later in different post.

Each agent has different exec:

LogReader -> logread.exe

Snapshot -> snapshot.exe

Distribution -> Distrib.exe

The interesting thing is that you can execute this execs manually from cmd line, using your own account. You will ask why? I found few moments in my career that I need to check route cause of agent failure and replication monitor or SQL Job history did not give me any error. This is the way you can check if you have permission , authentication or user memory issue (more about memory in different post).

First stop the agent you want to test, otherwise you will get an error that agent already running. Then go to COM folder in your SQL server installation files:

Com_Path

Copy ALL settings from second step of SQL Job/Agent and paste it after either distrib.exe, snapshot.exe or logread.exe. I did distrib.exe:

exec_command

Execute:

exe_command_results

You can see all the information about subscription you are trying to run and under what settings it is running. Note that SQL job wont be started and when you go to Replication Monitor you will see that agent is running, however you WONT be able to stop it from here. In order to stop agent you need to close cmd window or just press Ctrl+C, it will stop the exec process and Replication Monitor will be refreshed as well.

I used this way few times at my work and it is good to know how replication is working and operates. Once you know this it is easier to troubleshoot some of the issues that occur.

July 3, 2017

Understanding Replication Agents Part 1

3 Musketeers …LogReader, Snapshot, Distribution

First two (Logreader and Snapshot) are directly impacting Publisher. First is to read the log, find replicated transactions and put it into distribution database. Snapshot is used to make snapshot of schema, indexes, constraints, data etc. It will generate schema, bcp files on distributor (folder that the snapshot will be generated is configurable).

Last one, the Distribution agent is responsible for delivering the data that Logreader or Snapshot generated. In my experience the most issues we have is with this agent.

These three agents are actually SQL Jobs and you can start/stop and manage it in few ways. Trough replication monitor, by right clicking on Publication (View Sanpshot/Logreader status) or Subscription (View synchronization Status) and Job Activity Monitor (on distribution server).
Job naming convention is exactly the same for Types of Agents. Snapshot will have publisher name,published db name, publication name. Log Reader will have only publisher name and published db name. Distribution Agent will have publisher name, published db name, publication name, subscriber name.

REplication_Agents_jobs

In all three cases if the names are too long some strings will be truncated. Also the number at the end of each job name is the “agent id” and it is matching the id in system tables in distribution database.

Because Im dealing with quite big volume of replication agents I often like to use job Activity monitor to manage my agents. You will notice that after configuring publisher/distributor and creating your first publication/subscription in Job Activity Monitor you can find new Categories of jobs. All replication related jobs has “REPL-” prefix.

Repl-Snapshot
Repl-Distribution
Repl-Logreader
Repl-Distribution Cleanup

new_job categories

You can easily filter these and if you ever need to start/stop multiple agents you can do it that way. Very useful when you want to change agent profiles on multiple agents and in order to new profile start working you need to restart the agent.

Last thing about this is the job history often gives you better information about errors occurred than Replication monitor. If you need more details about error always check job history of given agent.

As mentioned before you can also use Replication Monitor (right click on Replication tree) to manage your agents. If you using Replication Monitor for the first time you need to register your publishers first in order to see it on the tree list on left pane. You can either register the publisher one by one or add entire Distributor. This is useful thing when you using one distributor for many publishers is to choose Add Distributor (Specify a Distributor and add Its Publishers…)

registering new distributors

By doing it, you will add ALL Publishers that are using that distributor. The Replication Monitor connects only to Distributor and you don’t need the direct connection to Publisher. The agents in Replication Monitor are split between the tabs like Subscriptions Watch list and Agents. In Subscriptions you will find all your subscriptions for give publisher and you can drill down to given publication on left pane if needed. In Agents tab you will find all Snapshot, LogReader agents but by default you will see LogReader agents and if you want to Snapshot you need to use drop down list to see other ones. What is nice with Replication Monitor is that you can go directly to the SQL job settings/history on distributor. You just need to choose agent, choose Action and from there Job Settings. It will open normal job properties window as you getting by using Job Activity Monitor (note: This wont work if you connected to distributor and you are using pull replication).