Bartek

December 4, 2018

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.

August 22, 2018

Cannot insert explicit value for identity column in table ” when IDENTITY_INSERT is set to OFF

Another error in transactional replication is the one related to Identity columns. That error is easy to fix and it is being raised if the setting “not for replication” is set to incorrect value on SUBSCRIBER database.

Lets start from beginning with creating table that includes identity column with following script (we do not specify “Not for Replication”, by default it will be set to 0):

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_IdentityA](
	[RowID] [bigint] IDENTITY(1,1) NOT NULL,
	[Field1_int] [int] NOT NULL,
	[Field2_int] [int] NOT NULL
 CONSTRAINT [PK_IdentityA] PRIMARY KEY CLUSTERED 
(
	[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO

TAble_setup_default

After adding that table into Publication with following setting:

@identityrangemanagementoption = N’none’

Here is the script:

use [PublishedDB]
exec sp_addarticle @publication = N'IDENTITY_Test',
@article = N'Table_IdentityA', @source_owner = N'dbo',
@source_object = N'Table_IdentityA', @type = N'logbased', 
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', 
@destination_table = N'Table_IdentityA', @destination_owner = N'dbo',
@status = 24, @vertical_partition = N'false', 
@ins_cmd = N'CALL [sp_MSins_dboTable_IdentityA]', 
@del_cmd = N'CALL [sp_MSdel_dboTable_IdentityA]', 
@upd_cmd = N'SCALL [sp_MSupd_dboTable_IdentityA]'
GO

As there is identity column in that table SQL won’t allow you specifying ‘none’. You will get following error.

“Msg 21830, Level 16, State 1, Procedure sp_MSrepl_addarticle, Line 2124
You cannot specify schema_option 0x4 (script identity as identity rather than the base data type) for article ‘Table_IdentityA’. The value specified for the parameter @identityrangemanagementoption is NONE. To replicate identity as identity, the value must be MANUAL or AUTO for publications that do not support queued updating subscriptions.”

AUTO setting is only allowed when using updating subscriptions:

Automatic identity range support is useful only for publications that allow updating subscribers.

Microsoft is suggesting to avoid that setting:

Updatable Subscription

The only setting that we left is MANUAL. Lets add article with that setting set to Manual.

use [PublishedDB]
exec sp_addarticle @publication = N'IDENTITY_Test',
@article = N'Table_IdentityA', @source_owner = N'dbo',
@source_object = N'Table_IdentityA', @type = N'logbased', 
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', 
@destination_table = N'Table_IdentityA', @destination_owner = N'dbo',
@status = 24, @vertical_partition = N'false', 
@ins_cmd = N'CALL [sp_MSins_dboTable_IdentityA]', 
@del_cmd = N'CALL [sp_MSdel_dboTable_IdentityA]', 
@upd_cmd = N'SCALL [sp_MSupd_dboTable_IdentityA]'
GO

This time no error. After Initializing and sending snapshot down to Subscriber,lets check what we have in Not For Replication.

Table_setup_subscriber

A bit surprise it is different than on Publisher. now lets try to insert rows into that table on Publisher. No issues there all 10 rows inserted properly:

no_Error_after_inserting

Lets see what has changed on Publisher:

TAble_setup_after_Adding_article

As you can see setting “Not for Replication” has changed to 1. I did test it and it seems while adding a table with identity column into Publication, either with GUI or T-SQL that setting is being changed to 1. Replication taking care of it from it side and I would not touch that setting on Subscriber. Pretty much which ever setting it is set on Publisher it will be always changed when adding table to Publication (changed to 1).

You can also see that replication procedure for that table is passing the value of Identity column (RowId):

CREATE procedure [dbo].[sp_MSins_dboTable_IdentityA]
    @c1 bigint,
    @c2 int,
    @c3 int
as
begin  
	insert into [dbo].[Table_IdentityA](
		[RowID],
		[Field1_int],
		[Field2_int]
	) values (
    @c1,
    @c2,
    @c3	) 
end

The error I mentioned at the begging can be caused only in two scenarios. First someone manually changed that setting on Subscriber database. Second is that tables on subscriber were created manually instead of snapshot. In the second scenario for example we used a backup of Published database (before Publication has been created) and restore it on Subscriber to initialize from backup.

Solution

In order to fix that error you can run following script on Subscriber database and provide table name that have an issue:

DECLARE @ApplicableObjectID INT
SET @ApplicableObjectID = OBJECT_ID('

‘

)
EXEC sp_identitycolumnforreplication @ApplicableObjectID,1

That script will change ‘Not For Replication’ to 1 and fix the error that you have with your distribution agent.

Alternatively to prevent that happen for other table you can change that setting to all tables on Subscriber (that are populated by replication) using script

select 'EXEC sp_identitycolumnforreplication ' + CAST(c.id AS VARCHAR(10)) + ', 1' ,t.name
FROM syscolumns c
join sys.tables t
on c.id=t.object_id
WHERE columnproperty(c.id, c.name, 'IsIdentity') = 1 
AND columnproperty(c.id, c.name, 'IsIdNotForRepl') = 0
AND  is_ms_shipped <>1

That script will do all user tables that has Identity column and set “Not For Replication” = 0 and set it to 1.

Lastly I would not recommend modifying replication procedures on Subscriber by adding SET IDENTITY_INSERT setting. As any changes to table schema will update that proc to old version and it will break distribution agent again.

Even if that error is not so common I hope it helped you better understand how identity is managed by transactional replication.

June 28, 2018

The row was not found at the Subscriber when applying

In next few posts will try to cover most of the common Transactional Replication errors.

First one that I want to focus on is “Row not found at the Subscriber”. That error will come up anytime that data is missing at the subscriber and that given row been modified (update or delete) on Publisher. Only UPDATE and DELETE can cause this error. Since SQL 2014 SP2 Microsoft added better error handling and you wont need to browse distribution db in order to get more information. More about this here in a update section: sp_Msreplraiseerror (2014 SP2)

Here is my error (as you can see I am using SQL 2014 with SP2).

Troubleshooting

Lets assume we still on older version and the only thing we are getting is the seq no and command_id of the failing command/transaction. First what you need to check is distribution db and browse it just to get full transaction. Here are my results:

As you can see it is update to TableC where PK value is =1. After checking subscriber we can confirm that the row is missing there.

Now there are two ways of fixing it:

1.Skip

By default all agent running on “Default Agent profile” that do not skip ANY Errors. However if you do not care about data on your subscriber to be consistent then you can skip that error by changing Agent Profile. You can see that this error have a code 20598, you can change agent profile to “Continue on data consistency errors.” or create your own Agent Profile with that -SkipErrors value 20598 and restart the agent (since 2017 CU3 you won’t need to restart agents). After that change you will see that Distribution Agent skipped that command and continue with next transactions. Note: That means ALL errors with that error code will be skipped for ALL published tables for that subscription!

If you want to be more selective and skip row not found only for specific table what you can do is modify the update replication proc for given table on the subscriber. You just commenting out last part of proc and put RETURN(1) like that:

After error been skipped you can revert changes.

2. Insert missing data

I would assume we do not want to miss data on the subscriber. The easiest way for fixing smaller amount of data we can just use tool sp_generate_inserts I am using it a lot and it is very useful with plenty of settings (include columns, Identity etc.). However it we have bigger set of data you could export table (or just set of data) to subscriber under different name and use left/right join to find missing data. It is quite a bit of work but in this case you will avoid running snapshot and overwriting entire table on Subscriber. Alternative for this is creating separate Snapshot replication on the side for that one table (with different destination name) and deliver the snapshot to Subscriber and then do the join to find missing data. Just note on that if you decide to overwrite table on Subscriber with new Snapshot you will get other errors on that Distribution agent. Here is an example.

We are assuming TableC is still being populated with inserts/updates/deletes… but our distribution agent is failing with an error (row not found) therefore our pending commands start to climbing:

Lets create a Snapshot replication for that one table:

Once Publication created you can go and create Subscription (remember to leave Initialize ticked).

Now we can generate the Snapshot for or TableC_fix replication:

Now after Snapshot been delivered we stopped getting Row not found error, we start getting PK violation… just because the command in pending queue in our transactional replication have INSERTS for data that been already delivered by our Snapshot replication.

That pretty much means our data is already there in Subscriber. To fix it just Create an Agent Profile with SkipError value 2627 and restart the agent. Once Transactional Replication get to the point when we generate Snapshot the error will disappear and normal replication will start delivering data.

3. Snapshot

In my world I am not able to do it and it can be a bit of pain as well. However if table is big and you want to be sure 100% that you are not missing ANY data, you may not have other option. In order to do that you need to drop article from subscription first by running sp_dropsubscription with @article parameter specified and then sp_Droparticle command for that given article. Then re-add that table into publication and run EXEC sp_refreshsubscriptions @publication ='<publication name’ . (Just remember you need to have Immediate_sync option set to false otherwise you will generate entire snapshot).

DELETE

How you should proceed if it turns out that it is DELETE statement? In my experience you can just skip it, by using agent profile or more selective way… changing DELETE replication procedure on subscriber (point 1). The data is already gone from Publisher and there is no way that we can fix it…

Lastly the most important thing is to find out WHY we are getting these errors (either UPDATE or DELETE) and get the root cause of the issue. Ideally that error should NEVER happen, especially in 1 to 1 structure (one publisher to one subscriber).

Most of you experience that error, for sure. Do you have any other ideas to fix it? Other approach when handling that error? If yes, lets discuss in the comment section.

March 14, 2018May 23, 2019

The way how the changes are replicated in transactional replication (CALL vs SCALL)

As I already mentioned before Replication is using replication procedures in order to replicate data changes Today I will focus on that how the changes can be send down to subscriber, especially for UPDATE.

By default these start with sp_MSins_%, sp_MSupd_%, sp_MSdel_%. You may already seen CALL or SCALL associated with these replication procedures. You can see these in GUI under articles properties:

call_articles_grab

or by just selecting from sysarticles on Publisher db or msarticles on distribution db:

grab_sysarticles

You can change these either by drop down list in SSMS in article properties or by using sp_changearticle stored procedures (executed on Publisher)

The most interesting in that is the UPDATE as that DML you can send down in few different ways. I will focus on two most common ones CALL and SCALL.

Lets go into more detail…

SCALL (default)

SCALL is default setting when you setting up the publication and adding articles into it. The update will pass only columns that have been changed, including PK values and lastly bitmask with information about what columns has been changed. Here is an example how this looks like in distribution database (Browse replication commands).

Updated one column in TableD:

UpdateTAbleD_SCALL

After browsing distribution db we can find this:

TAbleD_SCALL_Browse_with comments

As you can see the first parameter is NULL, this is the column that has not been changed. Here is how the procedure looks like on Subscriber:

USE [RC] 

go 

/****** Object:  StoredProcedure [dbo].[sp_MSupd_dboTableD]    Script Date: 2/27/2018 6:00:49 AM ******/ 
SET ansi_nulls ON 

go 

SET quoted_identifier ON 

go 

ALTER PROCEDURE [dbo].[Sp_msupd_dbotabled] @c1     INT = NULL, 
                                           @c2     NVARCHAR(100) = NULL, 
                                           @c3     NVARCHAR(200) = NULL, 
                                           @pkc1   INT = NULL, 
                                           @bitmap BINARY(1) 
AS 
  BEGIN 
      DECLARE @primarykey_text NVARCHAR(100) = '' 

      IF ( Substring(@bitmap, 1, 1) & 1 = 1 ) 
        BEGIN 
            UPDATE [dbo].[tabled] 
            SET    [tabled_id] = CASE Substring(@bitmap, 1, 1) & 1 
                                   WHEN 1 THEN @c1 
                                   ELSE [tabled_id] 
                                 END, 
                   [tabled_text] = CASE Substring(@bitmap, 1, 1) & 2 
                                     WHEN 2 THEN @c2 
                                     ELSE [tabled_text] 
                                   END, 
                   [text2] = CASE Substring(@bitmap, 1, 1) & 4 
                               WHEN 4 THEN @c3 
                               ELSE [text2] 
                             END 
            WHERE  [tabled_id] = @pkc1 

            IF @@rowcount = 0 
              IF @@microsoftversion > 0x07320000 
                BEGIN 
                    IF EXISTS (SELECT * 
                               FROM   sys.all_parameters 
                               WHERE  object_id = Object_id('sp_MSreplraiserror' 
                                                  ) 
                                      AND [name] = '@param3') 
                      BEGIN 
                          SET @primarykey_text = 
                          @primarykey_text + '[TableD_ID] = ' 
                          + CONVERT(NVARCHAR(100), @pkc1, 1) 

                          EXEC Sp_msreplraiserror 
                            @errorid=20598, 
                            @param1=N'[dbo].[TableD]', 
                            @param2=@primarykey_text, 
                            @param3=13233 
                      END 
                    ELSE 
                      EXEC Sp_msreplraiserror 
                        @errorid=20598 
                END 
        END 
      ELSE 
        BEGIN 
            UPDATE [dbo].[tabled] 
            SET    [tabled_text] = CASE Substring(@bitmap, 1, 1) & 2 
                                     WHEN 2 THEN @c2 
                                     ELSE [tabled_text] 
                                   END, 
                   [text2] = CASE Substring(@bitmap, 1, 1) & 4 
                               WHEN 4 THEN @c3 
                               ELSE [text2] 
                             END 
            WHERE  [tabled_id] = @pkc1 

            IF @@rowcount = 0 
              IF @@microsoftversion > 0x07320000 
                BEGIN 
                    IF EXISTS (SELECT * 
                               FROM   sys.all_parameters 
                               WHERE  object_id = Object_id('sp_MSreplraiserror' 
                                                  ) 
                                      AND [name] = '@param3') 
                      BEGIN 
                          SET @primarykey_text = 
                          @primarykey_text + '[TableD_ID] = ' 
                          + CONVERT(NVARCHAR(100), @pkc1, 1) 

                          EXEC Sp_msreplraiserror 
                            @errorid=20598, 
                            @param1=N'[dbo].[TableD]', 
                            @param2=@primarykey_text, 
                            @param3=13233 
                      END 
                    ELSE 
                      EXEC Sp_msreplraiserror 
                        @errorid=20598 
                END 
        END 
  END --

You can see how bitmask is used to determine if the column actual changed or no.

CALL

First thing lets change SCALL to CALL of TableD. You can do it in SSMS by going to Publication Properties and then to Article properties or just run T-SQL like that one:

exec sp_changearticle 
@publication ='Publication1',
@article = 'TableD',
@property ='upd_cmd',
@value='CALL [sp_MSupd_dboTableD]',
@force_invalidate_snapshot=0

After you run this you do not need to run snapshot. New update procedure will be delivered as normal replicated commands and now the code for update procedure looks like this:

create procedure [sp_MSupd_dboTableD]     @c1 int,     @c2 nvarchar(100),     @c3 nvarchar(200),     @pkc1 int
as
begin   	declare @primarykey_text nvarchar(100) = '' if not (@c1 = @pkc1)
begin  
update [dbo].[TableD] set     [TableD_ID] = @c1,     [TableD_text] = @c2,     [text2] = @c3
	where [TableD_ID] = @pkc1 if @@rowcount = 0
    if @@microsoftversion>0x07320000
		Begin
			if exists (Select * from sys.all_parameters where object_id = OBJECT_ID('sp_MSreplraiserror') and [name] = '@param3')
			Begin
				
				set @primarykey_text = @primarykey_text + '[TableD_ID] = ' + convert(nvarchar(100),@pkc1,1)
				exec sp_MSreplraiserror @errorid=20598, @param1=N'[dbo].[TableD]', @param2=@primarykey_text, @param3=13233 
			End
			Else
				exec sp_MSreplraiserror @errorid=20598
		End end   else
begin  
update [dbo].[TableD] set     [TableD_text] = @c2,     [text2] = @c3
	where [TableD_ID] = @pkc1 if @@rowcount = 0
    if @@microsoftversion>0x07320000
		Begin
			if exists (Select * from sys.all_parameters where object_id = OBJECT_ID('sp_MSreplraiserror') and [name] = '@param3')
			Begin
				
				set @primarykey_text = @primarykey_text + '[TableD_ID] = ' + convert(nvarchar(100),@pkc1,1)
				exec sp_MSreplraiserror @errorid=20598, @param1=N'[dbo].[TableD]', @param2=@primarykey_text, @param3=13233 
			End
			Else
				exec sp_MSreplraiserror @errorid=20598
		End end  end   --

As you can see no bitmap mask just simple update to ALL columns in tables and proc does not check if values has changed.

Now lets see how the update looks like in the distribution db. As before we just updating one column in that table:

update TableD
set text2='test_2222'
where TableD_ID=2

If you check distribution database you will see this:

CALL_browse

As you can see it does provide all columns and values even if these has not changed. It will always send everything.

CALL vs SCALL

Now you wonder why this is important? Imagine you have large table with 30 columns some big ones like varchar(max) etc. Your application updating only one column in that table…. How do you think this will affect your distribution db? Lets have a look.

I cleaned up distribution db and will populate 10 column table with 1 million rows. Then update that 1 million rows first with SCALL and then with CALL settings.

Table Structure looks like that:

CREATE TABLE [dbo].[TableA](
	[TableA_ID] [int] NOT NULL,
	[Col1] [nvarchar](100) NULL,
	[Col2] [nvarchar](100) NULL,
	[Col3] bigint NULL,
	[Col4] int NULL,
	[Col5] int NULL,
	[Col6] int NULL,
	[Col7] datetime NULL,
	[Col8] int NULL,
	[Col9] bigint NULL,
	[Col10] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
	[TableA_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

After creating I will populate table with random data and then publish it. Once Snapshot of TableA wth 1 million rows delivered to Subscriber I am checking space of msrepl_commands on distribution database for that Publication (nothing else is getting into that distribution db at this point).

Snapshot delivered

Right now msrepl_commands is 35 rows and 40KB reserved. It contains only initial snapshot. Article is set to use SCALL for updates. Lets send now 1 million updates just for one column, Col4 which is just INT. I will increase that column by number 100 that is it.

1 million SCALL updates sent down

After sending 1 million updates with SCALL my msrepl_commands table went to: 1000035 rows and 183368 KB reserved for that table.

Lets change article to normal CALL and lets send 1 million updates for Col5.

1 million CALL updates sent down

Now my msrepl_commands table went to 2000088 rows and reserved 759624 KB on drive. Just to explain why row count is not 2000035… it is because I changed article using sp_changearticle which added few rows still very small ones (should not take more than 40 KB)

Summary

As you can see using normal CALL will take way more space inside your distribution db

CALL = 576 256 KB

SCALL = 183 328 KB

Same transactions but with CALL we are using almost 3 times more of storage space. Before you change default SCALL setting to CALL think twice and check storage on your distributor.

If you have any questions just use comments section.

Did you use CALL in some cases and if yes to what were these? What setting do you normally use for UPDATES?

January 11, 2018

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?

December 7, 2017

sp_msUpd% has too many arguments, sp_msUpd% expects parameter. Replication Procedures part 2

In previous post I explained basics of replication procedures. Now lets have a look into some common issues with these. Especially when you Publishing from two (or more) sources, same tables into the same destination (Subscriber).

Here is an example:

Twopublishers_articles

As you can see both articles from two different Publishers are using exactly same procedure. If you check Subscriber you will see only 3 (not 6) procedures for that table:

Subscriber_procs

In that specific scenario you need to be careful with any changes to schema on Publisher. Why? …..

Lets assume you have Subscriber that is getting data from two (or more) different sources (Publishers). TableA on Subscriber getting data from Publisher1 and Publisher2. By default replication procedure names will have the same name “sp_Msupd_dboTableA” , that means both publishers will be using exact same procedure for insert, updates and deletes. You are receiving request from DEV to modify that table. To be exact drop a column. If you deploy change to Publisher1 only the replication procedure will be changed and new version (without dropped column) of the procedure will be applied on subscriber. The new procedure wont have that old column… but what about Publisher2? Publisher2 will be trying to execute procedure still with old column and you will receive “Procedure or function sp_MSins_dboTableA has too many arguments specified.”.

Here is an example. We dropped column id_temp on Publisher1 and then inserted one row on Publisher2:

Column dropped

Once we did that Distribution agent for Publisher2 will start failing:

Dropped column error

How to solve it and/or how to prevent it to happen?

Solution

Once you get to that point when receiving such error you should follow these steps:

Apply change (in our case drop column) on the second Publisher. This wont solve the issue straight away but it will modify replication procedure code to the same as we have on Publisher1. The DDL change will wait in the queue for delivery.
As procedure on Subscriber has 2 instead of 3 parameters we need to modify that procedure manually and include 3rd parameter.
Completing Step 2 will cause any inserts coming from Publisher1 failing as modified procedure expect @c3, meanwhile Publisher2 will deliver command that was failing before. You can also set default NULL value in the proc @c3 bigint=NULL and this wont cause error on Publisher1
Wait until change from Step 1 will be replicated down to Subscriber. It will change back procedure to version without old procedure. This will automatically fix distribution agent that will be failing (Step 2). Both Publisher will have same schema and same replication procedure code for that table.

Similar step you need to follow if u add new column to replicated table. Just instead of adding parameter you need apply old procedure (without new column) from second Publisher (Scripting out Replication Procedures).

Preventing it

You can prevent it to happen in two ways.

While you are configuring Publication. When you are adding articles to Publication you can specify INS/UPD/DEL procedure names. Just use different names for Publisher1 and Publisher2. Below you can see that both are using different procedures.
You can always modify procedures names by using sp_changearticle procedure on a Publisher. However if are receiving error on distribution agent already, this change wont help as we are using Transactional Replication the “change article” will be in the queue behind the failing command. You need to be proactive and do that change before error occur. Here is an example of modifying Insert procedure: exec sp_changearticle
@publication=’Publication2′,
@article=’tableA’,
@property=’ins_cmd’,
@value=’CALL sp_MSins_TAbleA_Publisher2′

Now important thing is that you can’t prevent this issue to occur for dropped columns as the old procedure (from second Publisher) will try to insert data into old column anyways and column will be missing. If so happens follow the steps I provided in that post.

The Best way to avoid issues with replication procedures is working closely with DEV when doing schema changes and configure you publications as mentioned above.

November 28, 2017

Replication Procedures Explained Part 1

I already wrote in few other posts about so called “replication procedures”. What are these you wonder? These procedures are used by transactional replication to do INSERT/UPDATES/DELETES on Subscriber DB.

If you did not rename it (or modify it on Publisher) the default “prefix” will be sp_MSins%, sp_MSupd%, sp_MSdel% etc. The exact proc names you can find in properties of any article published while using SSMS (Publication Properties->Articles):

procs_guI

Or just run select on published database and sysarticles table:

procs_tsql

Here is an example of Insert procedure:

procedure_text
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
if object_id(N'[sp_MSins_dboTableA]', 'P') <> 0 
                                        drop proc [sp_MSins_dboTableA]
go
if object_id(N'dbo.MSreplication_objects') is not null 
                                    delete from dbo.MSreplication_objects where object_name = N'sp_MSins_dboTableA'
go
create procedure [sp_MSins_dboTableA] 
  @c1 int,@c2 nvarchar(400),@c3 bigint
as 
begin 
insert into [dbo].[TableA]( 
 [id]
,[TableA_text]
,[id_temp]
 )
values ( 
 @c1
,@c2
,@c3
 ) 
end
go
if columnproperty(object_id(N'dbo.MSreplication_objects'), N'article', 'AllowsNull') is not null 
exec ('insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type) values (
                                + N''sp_MSins_dboTableA'' , N''MAINTEST\PUBLISHER'' , N''AdventureWorks2014'' , N''Publication1'' , N''TableA'' ,''P'')')

These procs are executed on Subscriber when Insert/Update/Delete is performed. You most probably noticed that there is CALL or SCALL before each procedure. This determine how these commands are created and how data is replicated down. That topic is quite big and I will cover it in separate blog post.

These procedures DON’T exists as an objects on Publisher, however you can find these on Subscriber.

proc_subscriber

Replication procedures are delivered in two ways. If you initialize subscription with Snapshot the code for replication procedures is included in SCH (schema) files of the snapshot for given table. If you choose to not initialize the procedures will be delivered as normal command (you can actually browse it in distribution database). Important thing is that any changes to schema on published table will force replication to UPDATE all the procedures code for changed tables and new version of procedures will be send down to Subscriber. Just a note on that, if you going over the WAN these procedures are delivered slowly, you just need to be aware of that especially if u changing hundreds on articles.

I already mentioned that these procedures do not exists as an object on Publisher. The question is how I can get code for these procedures on the Publishers? That is quite important question as there are some cases you will need the most “recent” procedure definition from Publisher. Remember that the procedure definition on Publisher is the actual one that is used by your replication at that point. You may have cases that code of replication procs been changed by someone or something on Subscriber and it caused replication to fail. In these cases you need the actual code from Publisher…. There are two ways of doing it.

Script out all replication procedures for given Publication. Run following code: exec sp_scriptpublicationcustomprocs @publication = ‘#publication name#’
You can script out specific article and specific procedures (delete, insert, update etc.) First you need an article ID from sysarticles. Then once you have that you need to run following procedures in order to get procedure code: exec sp_scriptinsproc #article_id# –for insert procedure exec sp_scriptupdproc #article_id# –for update procedure exec sp_scriptdelproc #article_id# –for delete procedure

Now there is problem with 2nd option. I already mention there are other ways of sending Updates or Deletes like SCALL, MCALL etc. If you using SCALL for example for updates you need to execute following: exec sp_scriptsupdproc. For MCALL exec sp_scriptmappedupdproc. For XCALL: sp_scriptxupdproc etc. If you not sure what “call” you using just check sysarticles table on your Publisher.

Last thing you need to be aware is that when you generating code for these procedures into Text Results… you need to extend maximum characters for Text Results from default 256 to max which is 8192 (new setting will be only “active” when you open new query window). Otherwise some large procedures will be truncated and incomplete. I know small thing but still you need to be aware of this.

These are basics of replication procedures in next post I will focus on what problems and issues you may encounter with these.

November 15, 2017

Push vs. Pull Subscription

As I mentioned already on How to create new Subscription? you can choose between push or pull subscription. There are some benefits but also some drawbacks for both solutions.

The main difference between push and pull is the location of a distribution agent (actual SQL Job). In Push distribution agent is located on the Distributor and there you will be able to find it as a local SQL job (Category: REPL-Distribution). With Pull the job will be located on a Subscriber. Here are pros and cons of both.

PULL (Pros)

Faster when going over WAN. In this case agent is “downloading” the data instead of sending it. You may need to consider this if data is going over to the other side of the world.
The load is moved from Distributor to Subscriber. The agent will be using subscriber resources. Could be important for you if you have multiple agents coming from one Distributor.

PULL (Cons)

Assuming you have multiple distribution agents to maintain it will get harder to maintain these as SQL jobs will be located on Subscriber. The big disadvantage for me is that you can’t stop/start agent while being on Distributor. Again if you have multiple distributions agents to maintain it is much better if you can do it from central location which is Distributor. If you logged on Distributor and have Replication Monitor open you wont be able to stop/start such distribution agent (screenshot below). Also job_id in msdistribution_Agents in Distribution db is different than job id on subscriber when using pull, which is bit harder to map SQL job to actual distribution agent.

PullSub

If Subscriber is really busy and under pressure already it won’t be good to get more load by adding Distribution agent. However one distribution agent wont make a big difference. That could be different story if we have multiple agents running on that given subscriber.
In some cases it could be a security issue. Especially when we have subscriber located on some 3rd party/remote location etc. In that case you will need to open port 1433 for subscriber to your distributor. Distribution agent need access to Distributor.

Push (Pros)

Everything can be managed from one location. It is much easier to apply changes to distribution agents, monitor and stop/start agents in bulk.

Push (Cons)

Slower than Pull when going over WAN.
If there are multiple agents going trough one Distributor you may need to consider separate box just for remote Distributor (that means additional costs).

What should you use? As always… IT DEPENDS:). Depends:

how many distribution agents you have
how fast is connection to your Subscribers
what are security policies in your company
how you prefer to manage your replication environment

The choice is yours:) You just need to be aware of some limitations of both solutions

November 6, 2017

How to create new Subscription?

We already covered Configuring Distributor and Publisher as well as Creating Publication . Now there is time to tell more about creating Subscriptions. As always you can create it by using GUI or T-SQL. In this post I will cover GUI setup of new subscriptions.

We already created Publication and we are ready to create subscription. Go to Replication tree on Publisher and extend it. Right click on Publication and choose New Subscriptions:

New subscriptions

Wizard will Open and you will need to choose under what publication you want to create new subscription:

choose publication

Now you will need to choose if the subscription will be Push or Pull. The main difference is where the Distribution Agent will be located. Pull -> Distribution Agent will be located on Subscriber, Push -> Agent will be located on Distributor. More about PUSH vs PULL I will cover in separate post.

PushorPULL

In next window you will need to choose the new subscriber. Note: you can create in that window Multiple subscriptions. You can add more than one server (!) which is pretty great if you need to send same data to multiple subscribers:

Choose subscriber

At this point you will make first connection to the subscriber there are two things you need to keep in mind. 1st You need to have access to that server from the place you creating the subscription. 2nd you need to use same or higher version of SSMS than your Subscriber is… otherwise u will get an error (more about this here: The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication. ) Both conditions are not needed when using T-SQL.

Once you connect to subscriber you need to choose subscriber DB:

Subscriberdb

Next window is Security Settings for Distribution Agent. As always you need to choose processing account. That is the account that distrib.exe will be running under (more about this here: Understanding Replication Agents). Second, how this agent will connect to Distributor and third is the account that will be used to connect to Subscriber. If Processing account don’t have permissions to subscriber or Subscriber is in different domain just use SQL Authentication (or grant permissions if possible).

Security1 Security2

In next step you need to decide if the Distribution Agent should run continuously or not. In 99% cases you want to run continuously (for some reason you using Transactional replication). It pretty much means that Agent will be running all the time and will be started every time SQL Agent will start.

continously

Last step is to choose if you want to initialize Subscription and when. Initialize means new snapshot will be generated (or not if using Immediate Sync) and schema/data will be synced to subscriber. If you choose not to, the agent wont send ANY schema and start replicate data from the point when subscription will be created. The only thing that Distribution Agent will “sync” will be replication procedures. Those will be delivered before any data. Replication Procedures will be always send down in either option (if initialize then replication procs will be included in snapshot).

You can also choose if initialization will be done later (when snapshot agent will be started) or Immediately (means snapshot will be kicked off as soon as Subscription will be created).

Initilize

Last window you need to decide that either Subscription will be created or just script generated (or both).

Create

It is good practice to save the script as it is bit easier later if you want to re-create or create more same subscriptions.

Once you finished wizard you will see the Subscription under Publication that you choose at the beginning. In next post I will describe more how to create subscirption by using T-sql. Using T-SQL u have a bit more control over the settings and you avoiding few issues that can occure when setting up by using SSMS.

October 16, 2017October 15, 2017

The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication.

As you may know SQL Replication can replicate data between different versions of SQL. It can replicated data from SQL 2012 to SQL 2016 etc. It can also replicate data from higher version to a lower version of SQL. However if you are trying to setup replication to a subscriber that is on higher version than your publisher you may get following error:

“The selected Subscriber does not satisfy the minimum version compatibility level”

Now the problem occurs only when you are adding a new subscriber using GUI and it is related only to version of SSMS you are using during setup.

Lets assume you are configuring subscriber that is on 2016 but your publisher and distributor is on 2012. You most likely connected to a Publisher and trying to add new subscription stream.

You have two solution for this:

Use T-sql. When setting up by using T-SQL, the version of subscriber is not being checked as you are not even trying to connect to a subscriber. Only Distribution agent will connect to it later after the setup.
As T-SQL setup could be bit difficult you have a second solution, which is to use tools that are on the same version as subscriber (in our example it should be SSMS 2016). You can do it either from Subscriber or other server. NOTE: The tools/SSMS should be located on workstation/server that have access to Publisher/Distributor and Subscriber. you need to have access to all 3 components of replication.

If you have other solution for this issue let me know in comments:)