Yay!

The fix for this bug is here. Microsoft published Cumulative Update 6 for SQL Server 2005 SP3. Please see previous post for the original bug description. We had problems when not using partition groups on our merge replication topology (see the TSQL Scripts below). When there were batch deletions caused by filtering at the Publisher, those rows were deleted at the publisher (published database) during the next replication (synchronization with subscribers)

The faulty stored procedure was sp_MSdelsubrowsbatch

The faulty line of code, believe it or not, was a single line
on the faulty SPROC and can be seen below in bold:

— change tombstone type for the non-original rows (the rows that got deleted via expansion).
update dbo.MSmerge_tombstone
set type = 1
from
(select distinct tablenick, rowguid from
#notbelong where original_row <> 1) nb,
dbo.MSmerge_tombstone ts
where ts.tablenick = nb.tablenick and
ts.rowguid = nb.rowguid
option (FORCE ORDER, LOOP JOIN)

On the sproc after CU6:
update dbo.MSmerge_tombstone
set type = @METADATA_TYPE_PartialDelete
from
(select distinct tablenick, rowguid from
#notbelong where original_row <> 1) nb,
dbo.MSmerge_tombstone ts
where ts.tablenick = nb.tablenick and
ts.rowguid = nb.rowguid
option (FORCE ORDER, LOOP JOIN)

Basically the deletions caused by filtering (Partial deletes) were marked as tombstone or user deletions.

Given the following variable declarations:

declare @METADATA_TYPE_Tombstone tinyint
declare @METADATA_TYPE_PartialDelete tinyint
declare @METADATA_TYPE_SystemDelete tinyint

and assignation:

set @METADATA_TYPE_Tombstone= 1
set @METADATA_TYPE_PartialDelete= 5
set @METADATA_TYPE_SystemDelete= 6

 

This caused deletions in the published database. Each subscriber had a subset of the published database. The merge replication was created with filters to allow having at one subscriber only the data pertaining to that subscriber and not the data pertaining to any other subscriber.

If you ever doubted the power of a single line of code… :-p

Happy coding guys!

 

Hi DBAs and Database Developers out there,
Finally the long awaited CU#6 was released on Feb 18th near mid night.

946608 Cumulative update package 6 for SQL Server 2005 Service Pack 2
http://support.microsoft.com/default.aspx?scid=kb;EN-US;946608

I’m currently testing our merge replication issues with this CU.

Even though the bug described in the DevX article is not in the KB article list, we got confirmation that the fix is on the CU#6.

So far the identity automatic management problem described on my previous post remains the same. If the user making the inserts is not a db_owner, automatic identity management ain’t going to happen on your publisher…

Back in Sept 2007 I blogged about the problem we were having with the identity range in merge replication:@@identity not working after SQL Server 2005 upgrade The problem continued until today, this post is to explain what we figured out.

The error message that describes this problem reads as follows:

[548] The insert failed. It conflicted with an identity range check constraint in database ‘DatabaseName’, replicated table ‘dbo.TableName’, column ‘ColumnNameId’. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

The identity range adjustment happens after every insert in the given article. The code responsible for the identity check adjustment is on the system trigger for the published article:
MSmerge_isn_GUID where GUID is the GUID for the given article.
…
if is_member(‘db_owner’) = 1
begin
— select the range values from the MSmerge_identity_range table
— this can be hardcoded if performance is a problem
declare @range_begin numeric(38,0)
declare @range_end numeric(38,0)
declare @next_range_begin numeric(38,0)
declare @next_range_end numeric(38,0)

select @range_begin = range_begin,
@range_end = range_end,
@next_range_begin = next_range_begin,
@next_range_end = next_range_end
from dbo.MSmerge_identity_range where artid=’BAEF9398-B1B1-4A68-90A4-602E3383F74A’ and subid=’0F9826DB-50FB-4F73-844D-AE3A111B4E1C’ and is_pub_range=0

if @range_begin is not null and @range_end is not NULL and @next_range_begin is not null and @next_range_end is not NULL
begin
if IDENT_CURRENT(‘[dbo].[TableName]’) = @range_end
begin
DBCC CHECKIDENT (‘[dbo].[TableName]’, RESEED, @next_range_begin) with no_infomsgs
end
else if IDENT_CURRENT(‘[dbo].[TableName]’) >= @next_range_end
begin
exec sys.sp_MSrefresh_publisher_idrange ‘[dbo].[TableName]’, ‘0F9826DB-50FB-4F73-844D-AE3A111B4E1C’, ‘BAEF9398-B1B1-4A68-90A4-602E3383F74A’, 2, 1
if @@error<>0 or @retcode<>0
goto FAILURE
end
end
end
…

As you might have noticed already, if the insertion is made by a user that is not member of the db_owner database role, the identity adjustment won’t happen. I believe this is a bug, not a feature. It forces the users that are allowed to do insertions to be owners of the database, or they will have the run out of identities error quite often and a manual adjustment will be required.

What the savvy DBA can do:

The template for all of the MSmerge_ins_GUID triggers are on the model database, these triggers are created by the system stored procedure: sp_MSaddmergetriggers_internal

If you change the code on this system stored procedure any new database creation will have the proper template for creating the merge insertion triggers. For the existing databases, you might like to modify the MSmerge_ins_GUID trigger manually, for now.

At the time I’m writing this post I’m not aware if this issue is addressed on any cumulative update. There is a similar case on the Cumulative Update released on January 15, 2008
http://support.microsoft.com/kb/941989

Happy coding guys!