Changing Article Properties without a new Snapshot

In our replication environment, the subscriber is initially set up with an snapshot of the publisher database. However, after that, the subscriber and publisher are different and we can never re-initialize from a snapshot again (we purge data on the publisher to reduce the database size but do not purge the same data on the subscriber; we do this by stubbing out the stored procedures on the subscriber that purge data on the publisher).

If an article is added or dropped from the publication, using snapshot and synchronize, just these changes are propagated to the publisher (without an entire new snapshot).

However, if an Article Property is changed (change SCALL to MCALL under Statement Delivery options for the UPDATE statement), the interface REQUIRES an entire new snapshot. Is there any way I can avoid the new Snapshot? It overwrites the subscriber database and this cannot happen!

Linda

Adding/dropping article(s) from an existing publication requires generates a new snapshot in general. This is to make sure the data convergence.

Do you mind sharing the business purpose - 'avoiding the new Snapshot' and 'can not overwrite the subscriber database'?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

Hi Linda,

Yes, changing CALL format will require a new snapshot.

But there is another way which might do what do you need (although requires more steps and a little intrusive). You can drop the subscription and publication. It will delete the replication, but not the data at subscriber. Then you can create the publication and subscription again. But when you create the subscription, you can choose to initialize subscription without snapshot. BOL has instruction (http://msdn2.microsoft.com/en-us/library/ms151705.aspx) on how to do it. Just follow the instructions under "Initializing a subscription with an alternative method".

Thanks,

Peng

|||

Thanks again, Peng. I have used this method in the past (breaking replication and restoring without a snapshot). I will test it for this scenario and let you know the results.

Linda

|||

Peng,

This works for changing article properties. Thanks!

Under what conditions will SQL2005 Replication generate an entire new snapshot?

Our configuration will not allow the subscription database to be re-initialized with a snapshot. The snapshot from the publisher will only be used for the initial subscription.

I need to identify all cases when a new snapshot will be created and use the alternative method of breaking replication, make the changes and then re-add the subscription without a snapshot.

Examples:

Adding or dropping a table: the snapshot agent just applies changes from the 1 item I changed without breaking replication.

Dropping a view: I dropped a view and the snapshot agent recreated the entire snaphot. Why?

How can I tell if my changes to replication are going to cause a new snapshot to be generated?

Linda

Changes to data not replicated

I have an application that uses web-based merge replication.My publisher is SQL 2005 and my subscriber is SQL 2005 Express.I control the replication with RMO code.If I make changes to the data in both databases using SQL Server Management Studio Express, my RMO code correctly syncs the two databases.However if I make changes to the data at the subscription through my application, these changes are not picked up by the replication process, even though the changes are present if you check the tables through Management Studio.What would cause these changes to not be recognized?Any ideas would be appreciated.

Hi there,

If you have changed the data in such a way as to stop the triggers from firing then these changes wouldn't be replicated.

This is normally associated with a bulk insert of data using something like a bulk insert but it doesn't sound like you are doing that.

To check if the triggers are firing you could check for the existence of the rowguids in MSMerge_Contents for the rows that you have changed.

|||

Merge replication uses triggers to track changes. First thing to check is if your updates are causing the triggers to fire and the transactions are commiting after updates. Is there any way for you to profile the updates made by your app? Do you know what DMLs your app uses to make the changes? If you can confirm that the triggers are firing then the next thing to check is if replication is picking up the changes. Are there any errors returned by replication? You can look at the replication log file for detailed errors. Replication log file is sqlmergx.log and its default location is %program files%\microsoft sql server\90\com.

thx

Sudarshan

|||

JR-J

Thanks for your resopnse.

I checked the MSmerge_Contents table after making changes to one of my application's tables. There was a row added when I made the changes from within Management Studio, but not when I made a similar change from within my application. So it looks as though my application doesn't cause the triggers to fire. The changes appear in the application's table in both cases.

I'm just using simple INSERT, UPDATE and DELETE statements in stored procedures in my application. No bulk inserts.

|||

Sudarshan,

Thanks for your resopnse.

I checked the MSmerge_Contents table after making changes to one of my application's data tables. There was a row added when I made the changes from within Management Studio, but not when I made a similar change from within my application. So it looks as though my application doesn't cause the triggers to fire. The changes appear in the application's table in both cases.

I'm just using simple INSERT, UPDATE and DELETE statements in stored procedures in my application. No bulk inserts.

I checked the log file you mentioned and it did not show any errors during merge. The merge picks up the data that is entered through Management Studio, but does not pick up the data I enter through my application.

Everything in the replication process except for this one problem, seems to be working as expected. We created the publication in Management Studio. I then create the subscription on the client using RMO, and also run the synchronization using RMO. This is my last hurdle in getting my application to work.

Are there any special security considerations you must consider for the triggers to work. I just seems to me that the INSERT statement from my stored procedures should cause the trigger to fire, just as entering data into the table in SQL Management Studio. I'm new to using triggers, so I may not be that good at understanding what they are doing behind the scenes.

|||

I am still having the same problem with not being able to sync the data entered through my application. I even created a new publication on a new SQL 2005 Server to ensure that the problem was not with the publication/server/permissions. I reproducibly get the same error mentioned in my original post. As this is the only step left in my development and deployment of this application, I am really stuck until I can solve this problem. Does anyone from Microsoft have and suggestions as to how to troubleshoot this issue. I'll bet that the soultion will be a simple comfiguration setting, or something like that, however, I don't have enough experience to know where to look. We also have several other applications that we hope to use replication on, if we can get this problem solved.

Thanks for any help.

|||

Ok so it's pretty clear that the triggers aren't firing for some reason.

Would it be possible for you to use profiler against your database whilst running the app and any of the logs event viewer/sql logs/ merge agent logs?

It might also be worth while ensuring that the triggers are infact in place. If you script out your tables you should see some triggers that look like this attached to the tables that are participating in replication msmerge_ins_<GUID>, msmerge_upd_<GUID>, msmerge_del_<GUID>.

Cheers, James

|||I tried installing profiler on the computer which runs SQL Express 2005, but the install would not allow me to because Express is installed. The triggers are on all of the tables, and they work, as the data I enter directly into the table through Management Studio Express is correctly merged. Its just that for some reason, the triggers either don't fire, or don't operate correctly when I add data to the same tables from my application. The application is a VB.NET application and uses stored procedures which INSERT, UPDATE and DELETE data in the SQL 2005 Express database. All of the stored procedures are working as expected. I'll see if I can attach profiler to my SQL Express database from another server.|||

yes sorry I forgot you had run the inserts etc manually.

if you have the normal client tools elsewhere hopefully you'll be able to connect across using profiler. I assume there are no error messages in the logs for your issues.

Cheers, James

|||

James,

I was unable to get profiler to connect to my subscriber database.

|||

Just a thought.

Do the row GUIDs need to be named RowGUID in each table. My row GUIDs are named according to the subject of the table, but then marked as a row GUID in the column's properties. This must not make a difference, as the triggers seem to work as expected in Management Studio.

Does anyone know if the Table Adapter's in Visual Studio require any special setup to allow the triggers to fire from within the application. I use DataSets/Table Adapters to design my Queries and Data Access Layer.

|||

No they don't need to be named rowguid in order for everything to work.

What problems are you experiencing connecting profiler to your subscriber?

Sorry I don't know anything about Table adaptors so I'll have to leave that to someone else.

|||

I found the problem.

In Visual Studio you must create a connection string to connect to the databases in your application. One of the advanced features of the connection is a parameter called Replication. It has values of True and False. It was set to True, which seemed logical, since I wanted to do replication of the database. However, True means that the application is recognized as a replication agent. Replication triggers do not fire for replication agents. I set the value to false, and now everything works as expected.

Thanks for your help.

sql

Changes not replicated to subscriber

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

|||Hi Chaps,

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

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

Changes not replicated to subscriber

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

|||Hi Chaps,

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

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

Change to new subscriber without snapshot?

We have a nightly replication of changes to a database going across the
WAN to a server that has been unstable and will be running out of disk
space soon.
I have a new subscriber set up, and can restore a backup from the old
subscriber, so the data will be the same.
Due to politics (!), I do not have access to the publisher. Can they
script out the replication, and point it to the new server without a
new snapshot coming from the publisher? The group that controls the
publisher estimates it would take 48 hours to push a snapshot across
the WAN.
Thanks in advance.
You can do a nosync initialization. Have a look at this article for more
info:
http://www.replicationanswers.com/No...alizations.asp
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||yes, its called a no-sync subscription. You will have to make sure that the
data is in sync, otherwise your distribution agent may fail frequently. The
group that controls the publisher will have to fix this each time.
You will also have to have them auto generated the replication stored
procedures for you by using sp_enumcustomresolvers 'PublicationName'
Run this in your publication database.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mr. DOS" <mike@.mrdos.com> wrote in message
news:1116228663.708155.103080@.f14g2000cwb.googlegr oups.com...
> We have a nightly replication of changes to a database going across the
> WAN to a server that has been unstable and will be running out of disk
> space soon.
> I have a new subscriber set up, and can restore a backup from the old
> subscriber, so the data will be the same.
> Due to politics (!), I do not have access to the publisher. Can they
> script out the replication, and point it to the new server without a
> new snapshot coming from the publisher? The group that controls the
> publisher estimates it would take 48 hours to push a snapshot across
> the WAN.
> Thanks in advance.
>

change publication sync_method from native to concurrent

I have a publication defined with the default sync_method of native. I have
1 subscriber subscribing to that publication . I am adding a new subscriber
and want to subscribe to it with the concurrent option. Can I change the
publication property without affecting the existing subscriber and if so how
?
right click on your publication, select the snapshot tab, and then change it
from native to concurrent. When you deploy the new subscriber it will use
the concurrent snapshot option.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:uREvlXdrFHA.2876@.TK2MSFTNGP12.phx.gbl...
> I have a publication defined with the default sync_method of native. I
have
> 1 subscriber subscribing to that publication . I am adding a new
subscriber
> and want to subscribe to it with the concurrent option. Can I change the
> publication property without affecting the existing subscriber and if so
how
> ?
>