Immediate_sync on vs off

This is one of the settings that can cause issues with growth of your distribution database or when running Snapshot. It is FALSE by default and it can be set on Publication level. You can check what setting you have by checking syspublications on published database or mspublications on Distributor and distribution database. Let me try to explain what impact have that setting to your replication setup. We are assuming your distribution retention is not “fixed set” like this @min_distretention=48, @max_distretention=48 but more like this @min_distretention=8, @max_distretention=48. More about how this works in the following post.

TRUE

Normal behavior when your retention is set to min=8 and max=48 will be that transactions that been delivered to subscribers and are older than 8 hour will be removed by distribution cleanup. However that changes with Immediate_sync set to ON. The transactions will be kept (even if delivered) until get older than 48 hours. Also it matters when you adding new subscribers. If Snapshot was generated within last 48 hours and you add subscriber it will use existing snapshot and deliver all the transactions that were marked for replication after snapshot generation. Here is an example.

We have Publication1 with TableA as an article and it is published to Subscriber, Subscriberdb database.

Initial_setting

Snapshot was generated within that 48 hour period and two rows added to TableA on Publisher.

Snapshot

Inserts

We are planning to add new subscription stream to same subscriber but to Subscriberdb_2 database. After the creation you do not need to run Snapshot again it will use old Snapshot and all transactions that been delivered afterwards.

New subscription_ON

You can see that it used old Snapshot as TableA has 0 rows (we inserted 2 rows before creating new subscription) and once you select from TAbleA on Subscriberdb_2 you will find two rows. These been delivered as normal replication transactions.

From my perspective there is one downside of that setting, especially when you are planning to add new articles quite often. Every time you add new article and want to send a snapshot of these new articles down to subscribers the Snapshot Agent will generate a snapshot for ALL articles with that Publication. In other word last Snapshot has been invalidated. Lets add TableB to that Publication1 and lets run snapshot.

The Snapshot will generate two articles:

However existing Subscribers will send down only new articles TableB:

The generation of Snapshot for ALL tables could be problematic when you publishing hundreds of large tables already. Adding one article will generate new snapshot for all of these tables and from my experience it will generate some locks on Publisher especially if Publisher is very busy.

Lastly the snapshot will be generated no matter if you add new articles or not. If someone will kick off snapshot by mistake it will still do snapshot for entire publication.

Lets have a look how it looks if this setting will be set to FALSE.

FALSE

First thing will be that will be different is that you will need to generate full snapshot every time you adding new subscription.

Second thing that will change is when your retention is set to something like that @min=0 and @max=72. All transactions that have been delivered will be cleaned up on the next Distribution Cleanup job run.

Third difference is that when adding new articles to existing publication the next snapshot run will generate ONLY files for new articles. Lets have an example.

First we changing setting to FALSE by running sp_changepublication:

changingto_false

Now lets add TableC into Publication1:

addingTableC

and run snapshot:

snapshot_with_false

As you can see Snapshot has been generated only for one Table, TableC. That is quite convenient especially when you adding one table into publication that already have hundreds of other tables.

Which settings you should choose? As always it depends on your environment. How often you adding article into Publication? How much space you have on your distributor? How busy is your Publisher? Can you afford of running snapshot for entire Publication?

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.

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