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.

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

Firs 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?

 

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.

subsriber_select.JPG

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.

adding_newarticles_on.JPG

The Snapshot will generate two articles:

new_snapshot_newarticle_on.JPG

However existing Subscribers will send down only new articles TableB:

Subscription_new_article_on.JPG

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?

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:

  1. 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.
  2. As procedure on Subscriber has 2 instead of 3 parameters we need to modify that procedure manually and include 3rd parameter. modifying proc
  3. 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 Publisher1error_publisher1
  4. 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.

  1. 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.Replication_procs_changed
  2. 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.

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

Security1Security2

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.

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:

  1. 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.
  2. 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:)

[TSQL Script] Browse replication commands

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

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

--BROWSE REPL COMMANDS

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


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

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

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

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

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

 

How to setup Transactional Replication? Part 2 (Publications and articles)

In Part 1 we covered configuring Publisher and Distributor server. Now you are ready to create first publications and add articles. We will focus on doing this by using SSMS.

Publication

First step is to create Publication on a database that you want to publish. Since you already setup Publisher and Distributor you should be able to extend Replication tree and Right Click on Publication, choose New Publication:

NewPublication

This will open new Publication Wizard:

NewPubwizard

On the Next window you will need to choose which database you want to publish. This will enable given database for Publishing:

Choosingdatabase

In next step you need to choose type of replication. We focusing only on Transactional Replication and we choosing that one:

ReplicationType

Now you need to choose articles. You can choose Tables, Procedures, Functions etc. In my DB I have only Tables:

ChoosingArticles

You can extend each Table to get column list and you can “filter” it vertically by removing some columns from replication. You don’t need to replicate entire table schema.

On the right side you have Article Properties button. You either set Properties for highlighted article or for ALL articles in this Publications. The list of the settings is quite impressive.

Articles Properties

You can decide if you want to copy triggers, all indexes, if table will be dropped on subscriber, choose if you want to send deletes or not etc. There are plenty of settings that you can modify. Choose wisely as some of these settings can invalidate snapshot and you will need to re-snapshot publication. As there are so many of these properties I will go describe it more in separate post.

In next step you can “filter” articles horizontally by adding WHERE condition. For Example you want to replicate only new data then you can set condition on date column etc.

FilteringRows

As you can see amount of properties and configurations that you can use are making replication very flexible:)

In next step you need to decide when Initial Snapshot will be run. If you don’t want to choose anything, you can always run it manually from replication Monitor. You can either schedule Snapshot or choose to generate snapshot straight after publication will be created:

Snapshot

Next step is to configure Security settings for LogReader and Snapshot agent:

SecuritySettings

I already explained (Understanding Replication Agents Part 1 , Understanding Replication Agents Part 2 ) How Replication Agents work. In that place you configuring Processing Account (under what account jobs and exec will be running) and what account will be used in order to connect to Publisher. You can setup different account for LogReader and Snapshot. You also need to keep in mind that account that will be used to connect to Publisher need to have relevant permissions (sysadmin) on Publisher. That account will be generating Snapshot and reading transaction log on Published database.

SecuritySettings

For this example I choose for both SQL Service account. NOTE: I needed to grant permission for Distributor (if remote one) SQL service account on Publisher. If you choosing different processing account for Snapshot than SQL service account, you need to make sure that this account have permissions do file system on Distributor. Snapshot agent will be creating folders and files on disk. SQL Service will have that permissions but if you use different account you may have issues when generating snapshot.

In next step you can choose that either Publication will be created or just generate the script for the Publication you just configuring (or both):

Createorscript

It could be useful to save T-Sql script as it may me useful in future. It is also nice to see what exactly happen when you configuring Publication (as there are many procs executed and configuration changed during that process that you do not see when using SSMS).

Finally we can put name of our Publication and Finish configuration:

Naming Publication

If all went well you will see new Publication in your SSMS:

Completed

DonePublication

This is pretty much entire process of creating basic publication for Transactional Replication. Easy?? Yeah it is easy we will go bit “deeper” in next post.

In next posts will describe some of the articles settings that you can modify, creating subscriptions and configuring Publications/Subscriptions by  using T-sql.

Understanding Replication Agents Part 2

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

SQL Jobs

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

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

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

Distribution Agent Settings

Snapshot Agent Settings

LogReader Agent Settings

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

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

Execs

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

execs

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

Each agent has different exec:

LogReader -> logread.exe

Snapshot -> snapshot.exe

Distribution -> Distrib.exe

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

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

Com_Path

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

exec_command

Execute:

exe_command_results

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

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

Configuring distributor and publisher with T-SQL

I already described how to configure distributor and publisher by using GUI.

How to setup Transactional Replication? Part 1

However if you have to setup more than one publisher  to setup and also multiple distributors (or distribution dbs) it is getting quite hard to do it quickly. I often you one remote distributor server and multiple distribution dbs on that one server. That below script helped me a lot.  By default I set retention to 48 hours and 24 hours for history. You can change it in the script or later by using GUI. Script will also generate code that you need to run on Publisher server in order to enable Replication and configure distributor.

Note: It will try to create a folder, if parameter @create_folder set to 1, otherwise it will create db in default or provided path. Still if you want to create folder for new distribution database xp_cmdshell need to be enabled on the server.

If you have any improvements or comments to below script let me know.

/* NOTE: 
1.You need to run the script for EACH distribution db u want to create
2.Model db cant be in use during the installation (It need exclusive lock on model in order to create distribution db)
*/
/****** Installing the server as a Distributor.  ******/
use master

declare @distribution_Db nvarchar(300),
@path nvarchar(300),
@SQL nvarchar(max),
@publisher nvarchar(300),
@password nvarchar(300),
@distributor nvarchar(200),
@create_folder bit

-------------- setting variables ------
set @distributor=@@servername
set @distribution_db=''
set @password=''
set @publisher=''
set @path =NULL -- leave null if u want to create distribution db in this forlder: 
--select substring(physical_name,1,patindex('%tempdb%',physical_name)-1) from sys.master_files where database_id in (select database_id from sys.databases where name='tempdb') and file_id =1
set @create_folder =1 --xp_cmdshell need to be enabled otherwise distribution db will be created in default folder. Set to 0 if you created folder manually.
-------------- end of variables --------

--Configuring Distributor
if not exists (select 1 from sys.servers where is_distributor=1 and name ='repl_distributor' and data_source=@distributor)
BEGIN
exec sp_adddistributor @distributor = @distributor, @password = @password
END

if @create_folder =1 and @path is NULL

BEGIN
		select @path=substring(physical_name,1,patindex('%tempdb%',physical_name)-1) from sys.master_files where database_id in (select database_id from sys.databases where name='tempdb') and file_id =1
END

if @create_folder =1 and @path is not NULL
	BEGIN
		set @path = @path + @distribution_Db
set @sql = 'exec xp_cmdshell ''mkdir '  +@path +''''
print @sql
if @create_folder =1
	BEGIN
	 if exists (select 1 from sys.configurations where name ='xp_cmdshell' and value_in_use =1)
	 BEGIN
	 
	print 'Creating Folder  ' + @path
	exec sp_executesql @sql
	END
	ELSE
	BEGIN
	print 'xp_cmdshell not enabled. Db will be created in default folder'
	set @path =NULL
	END
	END
END




-- Adding the distribution databases
if @path is not NULL
set @path = @path + '\'

if @path is NULL
print 'Distribution db will be created in default folder'


if not exists (select 1 from sys.databases where name =@distribution_db)
BEGIN
exec sp_adddistributiondb @database = @distribution_db, 
@data_folder = @path, 
@log_folder = @path,  
@min_distretention = 48, 
@max_distretention = 48, 
@history_retention = 24, 
@security_mode = 1
END


-- Adding the distribution publishers
exec sp_adddistpublisher @publisher =@publisher,
 @distribution_db = @distribution_Db, 
 @security_mode = 1, 
  @publisher_type = N'MSSQLSERVER'

/****** End: Script to be run at Distributor ******/



select 'Execute on publisher ' + @publisher
select 'use master 
exec sp_adddistributor @distributor = ''' + @distributor + ''' , @password = '''+ @password +''''