The fix for batch deletions at Publisher when use_partition_groups is set to false In Merge Replication is indeed there!!!

Yay!

The fix is in there. Please see previous post for the original bug description. We had problems when not using partition groups on our merge replication topology, whenever there were batch deletions caused by filtering those rows were deleted at the publisher during the next replication.

The faulty stored procedure was sp_MSdelsubrowsbatch


The faulty line of code, believe it or not, a single line
on the faulty sproc:

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

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

Happy coding guys!

Cumulative Update Number 6 for SQL Server 2005 SP2 is out!!!

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

Identity Range not working for Merge Replication in SQL Server 2005

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!

Merge Replication parameterized row filtering, beware of the optimizations

I haven't blogged in a while, mostly due to my workload that has sky rocketed.
My most recent challenge is with SQL Server 2005 Merge Replication.

We have had quite a few undesirable effects when we upgraded our subscribers from ole good MSDE to SQL Server Express.
The side effects led to data loss and emergency fixes, in a really chaotic way. This data loss didn't happen with MSDE.

The article I recently published on DevX described a data loss scenario with SQL Server 2005 Ent as publisher/distributor and MSDE or SQLE as subscriber.

The scenario we got recently happens only with SQLE in the subscribers and is related to the parameterized filter optimizations in SQL Server 2005.

Tweaking the parameters @use_partition_groups and @keep_partition_changes produce different side effects that go from data loss to inability to merge the data, the parameterized filters stop working.

If you're interested on the subject hang on, I will publish repros in the next post.

If anyone knows how to set up the Windows Synchronization Manager for Verbose History Logging to output to a file, please comment here, all my attempts have failed.

Cheers and happy holidays!

SQL Server Merge Replication issues

After a month of talking over the phone with Microsoft tech support and more than a month and a half of struggling with then problem and trying to isolate the cause; I put together a short article on how to lose your data with merge replication if you use parameterized row filtering and upgrade your publisher from SQL Server 2000 to SQL Server 2005.
The details of the problem, steps to reproduce the behavior and scripts can be found on this article published on DevX:
SQL Server 2005 Bug Alert: Merge Replication Could Mean Data Loss

My main purpose is to speed up the resolution, so any comments, workarounds or similar cases you might find, please give me a shout.
Have a great weekend!

PG