Re: replicated commands not executed on the subscriber side?

Thanks Adams for your continuous efforts and due dilligence, which is highly
appreicated.
My current environment is the most complex sql server environments in my 10+
DBA life, I believe I will post some other issues in future. I once even
debugged into the SQL Server's internal SP, which has a bug in that a
variable is declared as INT and later was populated by a BIGINT variable,
which no doubt leads to an error.

Anyway, thanks a lot!

Jeff
"Adams Qu [MSFT]" <v-adamqu@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:H3XUzcpWIHA.4200@xxxxxxxxxxxxxxxxxxxxxxxxx
Dear Jeff,

Appreciate your efforts and cooperation on this issue.

With the specific information given, we have conducted a lot of tests in
our test environment and have successfully reproduced the same behavior
you
have experienced.

After doing further research, we found this behavior occurs due to the
mechanism of "Transactional Replication with updatable subscriptions". As
we know, for immediate updating subscriptions the change is propagated
directly to the Publisher and applied using MS DTC. When a change made at
the Subscriber is captured by a trigger on the subscribing table, the
trigger calls through MSDTC to the appropriate stored procedure at the
Publisher. The stored procedure performs the insert, update, or delete
unless there is a conflict. After the change is made at the Publisher
using
two-phase commit, the transaction is replicated to OTHER Subscribers by
the
Distribution Agent.

Unlike the replication procedure when changes made at Subscriber, changes
replicated from a Publishers are completed via Log Reader Agent and
Distribution Agent (just as standard Transactional Replication).

In our case, when running following the distributed transaction, the
changes made on the subscription side is captured by a trigger and the
insert command "insert into T (a, s) values (2, 'from subscriber side
x1')"
will be executed on the Publisher side via MS DTC. However, as two insert
commands are done in one distributed transaction, system will mistakenly
recognize that the change has already been made on the Subscriber. So, it
is replicated to other Subscribers (not include the current Subscriber).
As
a result, the record (3, 'from publisher side x2') will not be replicated
to the Subscriber side.

*********************************
USE TestDB
go
set xact_abort on
begin distributed tran
insert into T (a, s) values (2, 'from subscriber side x1')
insert into DB1.TestDB.dbo.T (a, s) values (3, 'from publisher side x2')
commit tran
*********************************

As a workaround, we can use one of the following methods:

1. Please separate the distributed transaction into the different
transactions to ensure that the changes are respectively made on
Publishers
or Subscribers.

*********************************
begin tran
insert into T (a, b) values (2, 'from subscriber side x1')
commit tran
*********************************
*********************************
begin distributed tran
insert into [DB1].[TestDB].[dbo].[T] (a, b) values (3, 'from publisher
side
x2')
commit tran
*********************************

2. Alternatively, as the "Transactional Replication with updatable
subscriptions" is a bidirectional replication. It is not necessary for us
to involve distributed transaction to change the data on the remote
server.
We can just do all the changes on local server and the data will be
replicated to the remote servers automatically. E.X:

*********************************
USE TestDB
go
set xact_abort on
begin tran
insert into T (a, s) values (2, 'from subscriber side x1')
insert into T (a, s) values (3, 'from publisher side x2')
commit tran
*********************************

Thank you for your understanding and cooperation.

At the same time, I will also submit a internal feedback to reflect this
problem to the proper department for their consideration.

In addition, please feel free to submit your suggestion on our product to
the following link. Our Product Group reviews the suggestions submitted by
our customers. Your feedback is valuable for us to improve our products
and
increase the level of service provided.

https://support.microsoft.com/common/survey.aspx?scid=sw;en;1208&showpage=1&;
ws=search

Again, thank you for your understanding. I do appreciate all your efforts
and time on this issue.

Have a nice day!

Best regards,

Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support

Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.

--------------------
| From: "jeff_yao" <jeff_yao@xxxxxxxxxxxxxxxx>
| References: <OzMPEwIVIHA.4740@xxxxxxxxxxxxxxxxxxxx>
<j$iq8noVIHA.4200@xxxxxxxxxxxxxxxxxxxxxx>
| Subject: Re: replicated commands not executed on the subscriber side?
| Date: Wed, 16 Jan 2008 10:22:20 -0800
| Lines: 192
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
| X-RFC2646: Format=Flowed; Original
| Message-ID: <OioeAzGWIHA.3940@xxxxxxxxxxxxxxxxxxxx>
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: d154-5-0-112.bchsia.telus.net 154.5.0.112
| Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.replication:5879
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| Hi Adams,
|
| After long time investigation, I redeem this as an "bug" in sql server
2k5
| replication. I have posted the details about how to repeat this issue in
my
| blog, please see here for details:
|
|
http://blogs.sqlservercentral.com/jeffrey_yao/archive/2008/01/16/sql-server-
2005-replication-quot-bug-quot.aspx
|
| Thanks,
|
| Jeff
| "Adams Qu [MSFT]" <v-adamqu@xxxxxxxxxxxxxxxxxxxx> wrote in message
| news:j$iq8noVIHA.4200@xxxxxxxxxxxxxxxxxxxxxxxxx
| > Dear Jeff,
| >
| > Thank you for posting here.
| >
| > From our internal library, I found some old known issues regarding
| > transaction replication, however I did not find any known issues
regarding
| > transactional replication if the latest updates have been installed.
| >
| > The latest cumulative package that we have for SQL Server 2005 Service
| > Pack
| > 2 is Cumulative Update package 5 which is the build 9.00.3215. We can
| > first
| > install the cumulative update package 5 for SQL Server 2005 Service
Pack 2
| > on the PubSrv, Distributor and SubSrv(s) to fix any known bugs in SQL
| > Replication.
| >
| > You can refer to the following KB article to install these updates:
| >
| > Cumulative update package 5 for SQL Server 2005 Service Pack 2
| > http://support.microsoft.com/default.aspx/kb/943656/en-us
| >
| > If the issue persists, to isolate the issue, we suggest checking the
| > following points:
| >
| > 1. Does this issue can be reproduced consistently with a insert
command?
| > 2. Please create a new table and then create a new publication for the
| > table/new subscription on the SubSrv1, can they reproduce the same
| > behavior?
| >
| > If the answer of the 1st question is Yes, please collect the following
| > information for me to do the further research:
| >
| > 1. Verbose output for Distribution Agent
| > --------------------
| > Please collect the replication verbose output for me by the steps in
the
| > following KB article:
| >
| > How to enable replication agents for logging to output files in SQL
Server
| > http://support.microsoft.com/kb/312292
| >
| > 2. SQL Profiler Trace
| > --------------------
| > a. Please launch the SQL Profiler on the SubSrv1 and then add all the
| > Events under "Stored Procedure" and "Errors and Warnings".
| > b. Start the profiler trace before the issue is reproduced
| > c. End the profiler after the issue occurs and then send the profiler
| > trace
| > to me at v-adamqu@xxxxxxxxxxxxx
| >
| > If anything is unclear in my post, please don't hesitate to let me
know.
| > Thank you for your efforts and time.
| >
| > Have a nice day!
| >
| > Best regards,
| >
| > Adams Qu
| > MCSE, MCDBA, MCTS
| > Microsoft Online Support
| >
| > Microsoft Global Technical Support Center
| >
| > Get Secure! - www.microsoft.com/security
| > =====================================================
| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
| > =====================================================
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| > --------------------
| > | From: "jeff_yao" <jeff_yao@xxxxxxxxxxxxxxxx>
| > | Subject: replicated commands not executed on the subscriber side?
| > | Date: Fri, 11 Jan 2008 11:56:11 -0800
| > | Lines: 74
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
| > | X-RFC2646: Format=Flowed; Original
| > | Message-ID: <OzMPEwIVIHA.4740@xxxxxxxxxxxxxxxxxxxx>
| > | Newsgroups: microsoft.public.sqlserver.replication
| > | NNTP-Posting-Host: d154-5-0-112.bchsia.telus.net 154.5.0.112
| > | Path:
TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP02.phx.gbl
| > | Xref: TK2MSFTNGHUB02.phx.gbl
microsoft.public.sqlserver.replication:5835
| > | X-Tomcat-NG: microsoft.public.sqlserver.replication
| > |
| > | Hi all,
| > | I am looking forward to help regarding a very strange thing here.
| > |
| > | Environment: Windows 2K3 + SQL Server 2K5 + each server is two-node
| > | Active/Passive clustered box
| > |
| > |
| > | I have a replication scenario here:
| > | One publication server, PubSrv SS2K5 edition 3042
| > | Two Subscription Servers SubSrv1 SS2K5 EE edition 3042 and SubSrv2
SS2K5
| > EE
| > | edition 3161
| > | One Dedicated Distributor: DistSrv SS2K5 EE edition 3161
| > |
| > | On PubSrv, I need to replicate one table tbl_ABC to both SubSrv1 and
| > | SubSrv2, and due to the business requirements, I have set up two
| > | publications (both are transactional replication type) on the
PubSrv,
| > | Publication1 is a transactional replication with updateable
subscription
| > on
| > | SubSrv1 and Publication2 is a pure transactional replication with
| > | subscription (non-updatable) on SubSrv2.
| > |
| > | From time to time, I find some records are not replicated to
SubSrv1,
| > but
| > | are always replicated to SubSrv2. From the distribution server side
and
| > | using sp_browsereplcmds with proper parameters pointing to
Publication1,
| > I
| > | DO find there is an insert cmd that contains the record information
| > which
| > is
| > | expected to be executed on SubSrv1 but obviously not. Let me give a
| > detailed
| > | example here:
| > | Assume I do the following on PubSrv,
| > |
| > | Insert into tbl_ABC (pk_column, date_column) values (100,
getdate() )
--
| > | pk_column refers to the primary key column of tbl_ABC
| > |
| > | What I expect is this newly inserted record will be replicated to
both
| > | SubSrv1 and SubSrv2. However I can only see this record on SubSrv2
not
| > on
| > | SubSrv1
| > |
| > | To find out why, I go to the DistSrv, and do the following
| > | use distribution
| > | go
| > | exec sp_browsereplcmds @publisher_database_id=xx, @article_id=yy --
xx
| > is
| > | the publisher database on PubSrv, and yy is the article id refering
to
| > | tbl_ABC in the context of Publication1.
| > |
| > | Now I see a bunch of records returned and the last one has its
command
| > | column with the value of:
| > | {CALL [sp_MSins_tbl_ABC] (100, 2008-01-11 02:01:43.540,
| > | {999FA3D7-9B2B-4A96-AB97-3E5DAF228541})}
| > |
| > | So this clearly tells me at least the replicated command is
generated
| > but
| > | since I cannot find the record on the SubSrv1, the command is not
| > executed
| > | on the SubSrv1.
| > |
| > | I know I may be suggested that the record might be deleted by some
| > | application on SubSrv1, actually I have such doubts too. So I
created
a
| > | delete trigger on tbl_ABC on SubSrv1 to record any delete action to
a
| > | TriggerLog table, but I have never seen any records in this
TriggerLob
| > | table, which means the delete action never happens, just as promised
| > from
| > | the developer team.
| > |
| > | I have also started the profiler on SubSrv1 and never captureed
{CALL
| > | [sp_MSins_tbl_ABC] (100, 2008-01-11 02:01:43.540,
| > | {999FA3D7-9B2B-4A96-AB97-3E5DAF228541})}
| > |
| > | I do not see there is any error in distribution agent either and the
| > | replication seems working fine.
| > |
| > | So is this a possible bug in SQL Server 2K5? I have noticed that
some
| > people
| > | also complained missing records during the replication as seen
| > |
| >
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2564529&SiteID=17
| > |
| > | TIA,
| > | Jeff
| > |
| > |
| > |
| > |
| >
|
|
|



.

Relevant Pages

  • Re: Change distribution server
    ... This is more of a maintenance question regarding replication. ... With all the SQL Agent jobs and replication pieces, ... Remember my scenario has the remote distributor on SQL2000 if that matters. ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: SQLEXPRESS Replication?
    ... The way the Technet SQL BOL is arranged it is difficult to know just how to setup the required permissions to do replication which is the major stumbling block. ... You can replication from SQL Server 2005 Workgroup edition and above ... to a SQL Server Express subscriber using transactional replication. ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL 2000 and MSDE/Express/Compact edition..
    ... What is does is downloads .Net Framework and SQL Server Express if they are not installed and then configures SQL Server as a subscriber. ... string DotNetFramework2Location = ... LogMe("Creation of Replication Account failed."); ...
    (microsoft.public.sqlserver.replication)
  • Re: Advice Needed: Asynchronous Replication/Mirroring to central o
    ... Looking for a SQL Server replication book? ... geographically-dispersed remote locations. ...
    (microsoft.public.sqlserver.replication)
  • Re: Subscriber Transaction Logs Too Large
    ... Its not so critical to shrink the db files or transaction logs in SQL 2005. ... Director of Text Mining and Database Strategy ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
Loading