Hi,
I hope you can help me with this.
I am setting up a tranasctional publication with updateable subscriptions using:
use [LIMS_PUBLISHER_TEST]
exec sp_replicationdboption @.dbname = N'LIMS_PUBLISHER_TEST', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [LIMS_PUBLISHER_TEST]
exec sp_addpublication @.publication = N'LIMS_PUBLISHER_TEST', @.description = N'Transactional publication with updatable subscriptions of database ''LIMS_PUBLISHER_TEST'' from Publisher ''RIVIERA''.', @.sync_method = N'concurrent_c', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'false', @.alt_snapshot_folder = N'\\RIVIERA\SNAPSHOT', @.compress_snapshot = N'true', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'true', @.autogen_sync_procs = N'true', @.allow_queued_tran = N'true', @.allow_dts = N'false', @.conflict_policy = N'pub wins', @.centralized_conflicts = N'true', @.conflict_retention = 14, @.queue_type = N'sql', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @.publication = N'LIMS_PUBLISHER_TEST', @.frequency_type = 1, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1
use [LIMS_PUBLISHER_TEST]
exec sp_addarticle @.publication = N'LIMS_PUBLISHER_TEST', @.article = N'AUD_AUTHORISED_TEST', @.source_owner = N'dbo', @.source_object = N'AUD_AUTHORISED_TEST', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x00000000080314DF, @.identityrangemanagementoption = N'manual', @.destination_table = N'AUD_AUTHORISED_TEST', @.destination_owner = N'dbo', @.status = 16, @.vertical_partition = N'false'
GO
I am adding a pull subscription with immediate with queued failover updating using:
/*at the publisher*/
use [LIMS_PUBLISHER_TEST]
exec sp_addsubscription @.publication = N'LIMS_PUBLISHER_TEST', @.subscriber = N'RIVIERA', @.destination_db = N'LIMS_SUBSCRIBER_2', @.sync_type = N'Replication Support Only', @.subscription_type = N'pull', @.update_mode = N'failover'
GO
/*at the subscriber*/
use [LIMS_SUBSCRIBER_2]
exec sp_addpullsubscription @.publisher = N'RIVIERA', @.publication = N'LIMS_PUBLISHER_TEST', @.publisher_db = N'LIMS_PUBLISHER_TEST', @.independent_agent = N'True', @.subscription_type = N'pull', @.description = N'', @.update_mode = N'failover', @.immediate_sync = 1
exec sp_link_publication @.publisher = N'RIVIERA', @.publication = N'LIMS_PUBLISHER_TEST', @.publisher_db = N'LIMS_PUBLISHER_TEST', @.security_mode = 1, @.login = null, @.password = null
exec sp_addpullsubscription_agent @.publisher = N'RIVIERA', @.publisher_db = N'LIMS_PUBLISHER_TEST', @.publication = N'LIMS_PUBLISHER_TEST', @.distributor = N'RIVIERA', @.distributor_security_mode = 1, @.distributor_login = N'', @.distributor_password = '', @.enabled_for_syncmgr = N'False', @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20051117, @.active_end_date = 99991231, @.alt_snapshot_folder = N'\\RIVIERA\TEMP', @.working_directory = N'', @.use_ftp = N'False', @.job_login = null, @.job_password = null, @.publication_type = 0
GO
The publisher, subscriber and distributor all sit on the same server at present.
I can update a table at the publisher and the changes are replicated to the subscriber, I can update at the subscriber and the changes are replicated to the publisher. After a small number of updates the subscriber falls out of sync despite replication monitor saying the changes has been propagated from the publisher to the distributor and on to the subscriber.
If I run the subscription in a queued failover mode- all works well but unfortunately I need immediate updating as the primary method with queued failover.
What am I missing?
Thanks,
Barney
Hi Barney,
>> After a small number of updates the subscriber falls out of sync despite replication monitor saying the changes has been propagated from the publisher to the distributor and on to the subscriber.
Could you be more specific about this part? What kinds of transactions was missing at subscriber? And if you take a look at the history log for the logreader and distribution agent, do you notice any error messages?
Thanks,
-Peng
|||Hi Peng,Neither the log reader or the distribution agent record an error- using verbose history logging.
I am performing a simple table update of an nVarChar field on a table which does not appear to have user triggers, just a primary key, two foreign keys an unique key.
This is similar to my previous query PostID=152388 which I resolved by using a 'queued failover' update_mode for the subscription.
I do not think transactions are being delayed by latency issues as subsequent replicated transactions are process successfully.
If you have any ideas your input would be much appreciated :o)
Thanks,
Barney|||What happens at the subscriber when you re initialize replication?
You need to be specific about what is happening. i.e.
The change you make (update a field x on table y at the subscriber/publisher), what happens next, and what problem you see at what stage.|||
Hi Barney,
It is strange to me that replication agent doesn't have any errors but subscriber is out-of-sync. So as Mulhall said, probably you need to be very specific. Saying:
1) what do you mean "out of sync"? Is the table update transaction missing at subscriber or the wrong value is updated?
2) Are there any other transactions at publisher or subscriber at the same time?
3) you mentions the table has primary/foreign/unique constraints. Is the nvarchar column involved in these constraints?
It will be great if you illustrate your scenario step by step and as detail as possible.
Thanks,
-Peng
Thanks for your help.
I will try to be more detailed.
At the moment I am initialising my subscriber from a backup following the procedure described in Books Online (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/repref9/html/d0637fc4-27cc-4046-98ea-dc86b7a3bd75.htm)
Once my replication topology is up and running (viewing details of the subscription through Replication Monitor shows no replicated transaction pending).
I then begin updating an nVarChar column within a single replicated table. The column in question is not a key or a constraint and does not have a dependent trigger. I am the only user of the databases- other transactions are not occurring at the publisher or subscriber.
The table I am using is populated with 13 records.
I work through the table at the publisher updating the contents of the particular column.
I see these changes propagate through Replication Monitor from the publisher to the distributor and on to the subscriber.
Once the changes have been propagated and are visible at the subscriber I update the same column at the subscriber.
Again I see the changes propagate and the publisher table is updated.
I can perform this cycle four or five times before the subscriber table does not appear to have been updated by the distributor. Replication Monitor confirms the changes have been propagated and the MSReplication_queue table at the subscriber is empty.
If I now try to update the subscriber I receive the following error:
No row was updated
The data in row 6 was not committed
Error Source: .Net SQLClient Data Provider
Error Message: Updateable Subscriptions: Rows do not match between Publisher and Subscriber. Run the Distribution Agent to refresh rows at the subscriber.
Updateable Subscriptions: Rolling back transaction.
The transaction ended in the trigger. The batch has been aborted.
I cannot see an error with the distribution agent.
Thanks for you help,
Barney|||That's much clearer - though I don't see a solution yet;
How are you doing the updates? I'd suggest adding a TRY/CATCH routine to capture the problem.|||Hi Mulhall,
I have perfoming updates by opening the same table from the publisher and subscriber in an instance of SQL Server Management Studio.
I can knock up a quick .NET Try Catch process if you think it will help.
Thanks,
Barney|||
Hi Barney,
Looks like we need to dig more to find out at which point the transaction is lost on the route to subscriber.
1) Run update command at the publisher.
2) Run sp_browsereplcmds on distribution database. Look at the last command to see if the update command has been transferred to the distribution database.
3) Most likely the command in sp_browsereplcmds is calling a stored procedure (created by replication) at subscriber (with name like sp_MSupd_dboXXX). Check the definition of this stored procedure and check if it can correctly update the table.
4) You may also want to run Profiler at subscriber to see if this stored procedure is called or not.
5) Check the data at subscriber to see if the transaction arrives.
Let me know how it goes.
Thanks,
-Peng
Again, thanks for spending your time investigating this with me, it is much appreciated.
I have worked through your above steps and this is what transpired.
Update command run at the publisher
sp_browsereplcmds shows 5 commands being passed to the subscriber:
sp_MSdel_dbo...
sp_MSins_dbo...
sp_MSupd_dbo...
followed by an update of my audit tabke
sp_MSins_dbo...
sp_MSins_dbo...
The target table is sucessfully amended.
When run at the subscriber
sp_MSins_dbo... (audit table)
sp_MSdel_dbo...
sp_MSins_dbo...
sp_MSupd_dbo...
sp_MSins_dbo... (audit table)
sp_MSins_dbo... (audit table)
sp_MSins_dbo... (audit table)
The target table is sucessfully amended.
I am a little intrigued as to the difference between audit table updates.
I can repeat the above sequence a number of times.
At the point of failure (update at the publisher not seen at the subscriber) sp_browsereplcmds has the same set of entries as listed in the top section:
sp_MSdel_dbo...
sp_MSins_dbo...
sp_MSupd_dbo...
followed by an update of my audit tabke
sp_MSins_dbo...
sp_MSins_dbo...
If if copy the replication commands being actioned according to SQL Profiler and try to run them manually, they say they have completed but do not result in a field update. Looking at the first command in particular sp_MSdel_dbo... I see the command complete successfully but do not see a row be deleted.
Any ideas?
Thanks,
Barney|||Hi Barney,
From your description, looks like sp_MSdel_dbo... doesn't delete the row, in spite of no error message. You can go to the subscriber and take a look at the definition of this stored procedure to see why it didn't delete the row.
If possible, could you post the definition of sp_MSdel_dbo... in this thread (or send it directly to me), together with the the schema of your published table and the DML at publisher?Then I can take a look and see why the SPs don't update the row properly at subscriber.
Thanks,
-Peng|||Hi Peng,
My apologies for not posting earlier.
Here are the stored procedure definition and the table schema.
sp_MSdel_dbo...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_MSdel_dboMACHINE]
@.pkc1 int
,@.msrepl_tran_version uniqueidentifier
as
begin
delete "dbo"."MACHINE"
where "MACHINE_ID" = @.pkc1
and msrepl_tran_version = @.msrepl_tran_version
end
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
Table Schema dbo.MACHINE
USE [LIMS_SUBSCRIBER_2]
GO
/****** Object: Table [dbo].[MACHINE] Script Date: 12/13/2005 11:50:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MACHINE](
[MACHINE_ID] [dbo].[D_ID] NOT NULL,
[TEST_FACILITY_ID] [dbo].[D_ID] NOT NULL,
[IDENTIFIER] [dbo].[D_MACHINE_IDENTIFIER] NOT NULL,
[NAME] [dbo].[D_MACHINE_NAME] NOT NULL,
[MACHINE_TYPE_ID] [dbo].[D_ID] NOT NULL,
[IS_DELETED] [dbo].[D_BOOLEAN] NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL CONSTRAINT [DF__MACHINE__msrepl0F4D3C5F] DEFAULT (newid()),
[AUD_UPDATED_BY_ID] [dbo].[D_ID] NULL,
[AUD_UPDATE_TYPE] [dbo].[D_AUD_UPDATE_TYPE] NULL,
[AUD_UPDATED_WHEN] [dbo].[D_TIMESTAMP] NULL,
CONSTRAINT [MACHINE$PK] PRIMARY KEY NONCLUSTERED
([MACHINE_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [DB_Data],
CONSTRAINT [MACHINE$UQ$TEST_FACILITY$IDENTIFIER] UNIQUE NONCLUSTERED
([TEST_FACILITY_ID] ASC,
[IDENTIFIER] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [DB_Data],
CONSTRAINT [MACHINE$UQ$TEST_FACILITY$NAME] UNIQUE NONCLUSTERED ([TEST_FACILITY_ID] ASC,
[NAME] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [DB_Data]
) ON [DB_Data]
GO
SET ANSI_PADDING ON
GO
EXEC sys.sp_bindrule @.rulename=N'[dbo].[R_BOOLEAN]', @.objname=N'[dbo].[MACHINE].[IS_DELETED]' , @.futureonly='futureonly'
GO
EXEC sys.sp_bindrule @.rulename=N'[dbo].[R_AUD_UPDATE_TYPE]', @.objname=N'[dbo].[MACHINE].[AUD_UPDATE_TYPE]' , @.futureonly='futureonly'
Please let me know if I have missed anything or if you would like any other details.I look forward to hearing your thoughts.
Thanks,
Barney|||
Hi Barney,
I setup replication with your schema above. But still can not repro your issue.
As you reported, sp_MSdel_dboMACHINE is executed but doesn't delete any row. Do you know it is due to not be able to find the appropriate row with the specifed MACHINE_ID or the specifed msrepl_tran_version?
If you can post the actual DML you are using, that might be helpful. Do you repro it with only UPDATE? Or there are also INSERT and DELETE?
Thanks,
