Subscriber – Bartosz Lewandowski

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.

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

August 4, 2017September 5, 2017

How to “Browse” Distribution Db (LSN/seqno explained)

You ever wonder if you can get more information what is actually replicated in your system or how to get exact commands/transactions that been replicated?

I believe you did:). As I mentioned before Transactional Replication operates on LSN (sequence numbers of transactions). Understanding how sequence number works is crucial thing. Lets try to explain.

Once Log Reader read the transaction log of published database it is putting information about transaction and commands into system tables into relevant distribution database. The system tables I mentioned are msrepl_transactions and msrepl_commands. First one contains one row for each transaction and second all commands for given transaction. The most important column is xact_seqno which is the transaction ID and you can join these two tables on that column (and publisher_database_id) to get what commands are included in what transactions.

select top 10 * from MSrepl_transactions (NOLOCK) t
join MSrepl_commands (NOLOCK) c
on t.publisher_database_id=c.publisher_database_id
and t.xact_seqno= c.xact_seqno

Now, How you get current LSN that is being delivered. There are two ways. First one is to check msdistribution_history system table for given distribution agent. Second is to check subscriber and system table msreplication_subscriptions and get current_timestamp. That values is the information about last delivered transaction and as the replication is sequential next seqno in the queue will be the one that is currently delivered. With both there are issues that you need to be aware of. I noticed that misdistribution_history is not live data and it is refreshed with some delay. The problem with second solution is that the next seqno that you get from distribution database could belong to different publication (there is no pubid column in these tables). To over come this you can filter by article id, however then you need to join msrepl_commands table and this can be quite heavy query. Here are the examples:

On Subscriber:

select publisher,publisher_Db, publication,transaction_timestamp from MSreplication_subscriptions

msreplication_subscriptions

On Distribution database:

select top 10 * from MSrepl_transactions (NOLOCK) t
join MSrepl_commands (NOLOCK) c
on t.publisher_database_id=c.publisher_database_id
and t.xact_seqno= c.xact_seqno
join MSarticles a
on a.article_id=c.article_id
join MSpublications pub
on a.publication_id = pub.publication_id
where t.xact_seqno >= <seqno from subscriber>
and pub.publication =< publication name>
order by t.xact_seqno asc

LSN/sequence number is often used in order to troubleshoot replication issues. While u checking replication monitor for failing distribution agent you will get and error that includes seqno and a command id. With these you can find what command exactly failing. In order to do that you need system procedure located on distributor called sp_browsereplcmds. This procedure allows you to browse actual commands. More on that next post.

How to “Browse” Distributor (sp_browsereplcmds explained)

June 23, 2017February 27, 2018

Procedure or function sp_MSreplraiserror has too many arguments specified.

UPDATE (27/02/2018)

Some great news! It seems that that error been fixed in SQL 2014 SP2 CU3. More information here . They added following logic at the end of the replication procedures:

            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

It seems it is checking a version of SQL and if version is low it is executing proc without additional parameters.

Still if patching server is not an option for you, follow this blog post:)

END OF UPDATE

This is quite common error when you upgrade your 2014 publishers with SP2. First of all the best way of upgrading your replication infrastructure is to start with subscribers. In real world this is not always possible.

This error occurs when you apply SQL 2014 SP2 to your publishers but not to subscriber. It seems Microsoft modifying error handling (thank GOD!) for replication. Currently as you may know when u getting PK, row not found errors you only getting seqno and command id. Since 2014 SP2 the replication will give you table name and values of Primary Key (to identify the duplicate/missing row). That is great improvement as you don’t need to go to distributor to browse commands (more about browsing commands over here …. )

They added 3rd parameter to sp_MSreplraiserror procedures. Since this is systems proc you not able to update it on subscriber. The only permanent solution for this is applying SP2 to your subscriber.

However there is a workaround for this but only temporary one. First step is to script out ALL replication procs for given publication on publisher. Using following script:

sp_scriptpublicationcustomprocs @publication = ”

Once you copy output, paste it on subscriber db and replace ‘,@param3=’ with ‘- -@param3=’

As I said this is just temporary solution as these procedures may be overwritten if you make changes to some or all articles, you create new subscription or drop/recreate it etc. When you add new articles you will need to repeat process for all articles or if you like for given articles you just added.

I know it is not the best solution for this but I don’t see any other way of doing it. If you do have other ideas, let me know in comments.