September 10, 2008 at 4:14 am
I've got a serious problem with 2 merge replications which doesn't make any sense. Both of these replications have been in use for well over a month with no issues, however yesterday we changed the way the data is created. Let me explain.
We have 2 production databases ("config" and "data") that are merge replicated from a remote location.
Previously the databases were populated by an application (windows service) with no issues. This application has been superceded and now the data is inserted into a 3rd database ("source") - we are unable to do much to this database as it is from another vendor (still SQL2005) and may be changed by them with updates etc. Unfortunately the schema has all changed too, so rather than re-developing lots of applications we decided to replicate and transform the data (transactional replication) from database "source" to database "data". We also have another database ("config-creator") which is needed to create data in the "config" database (performance reasons).
Issue 1: "data" database
To do this we have overridden the replication stored procedures and created our own (sp_MSins_... sp_MSupd... sp_MSdel...). There are 3 tables involved in the "source" database, however they do not actually exist in the "data" database, the replication stored procedures manipulate the data and insert the remapped data into the existing tables used by the legacy applications. Note that data is only ever inserted and updated, never deleted. The bulk of the data is created at the remote site - occasionally data is created at the local site (however this has not occurred for a while).
The tables used by the legacy system are part of a merge replication from the remote site to the local site.
So the (theoretical) order of things is as follows:
Data is captured into the "source" database
Transactional replication is used to the "data" database.
On the "data" database the replication sp's are overridden and transform and insert the data into other legacy tables.
This legacy tables are merge replicated from the remote site back to local (and occasionally data goes in the other direction).
The issue is that the merge replication is not replicating all of the data, some is going missing. There are no errors reported, the replication history matches the data replicated (i.e. has missed data). However, this only affects the tables that are populated by the transactional replication, another set of tables that are populated by an application are merge replicating correctly.
Issue 2: "config" database.
The config database is a similar scenario, with a slight change. The data is replicated from the "source" database to a "config-creator" database using transactional replication. This time only the update of a particular table is of interest, and when this occurs the sp_MSupd... stored procedure calls a batch of stored procedures in the "data" database. These stored procedures insert data into the "config" database. The reason for this complexity is historical, and was to ensure that the new application worked with the legacy framework.
So the order of events is:
1. "source" database updates a table
2. Transactional replication used to the "config-creator" database
3. "config-creator" database calls stored procedures in the "data" database.
4. sp's in "data" database create rows in several tables in the "config" database (takes some time).
5. merge replication copies data from remote "config" database to local "config" database.
The issue here is that no data is replicated from remote to local at all. I have emulated the creation of the "config" data by calling the stored procedures from the "config-creator" database manually - and it worked ! So clearly the transaction replication is getting in the way here.
Note that the merge replications all worked happily before the transaction replication was added. The remote database is populated as expected.
So my questions are -
1. Has anyone else tried to mix and match transaction replication into a merge replicated database and got it to work ?
2. Has anyone any idea why data may not be replicated randomly ?
3. Are there any ways of finding out what is happening to the data ?
4. Any tips etc would be great.
Apologies for the rather complicated explanation, I am limited a little by legacy system requirements and performance considerations (this is a live system reading data points created by machinery and has to be reasonably quick).
Thanks in advance for any assistance !
September 11, 2008 at 9:24 am
I have found a possible reason for this. Merge replication uses triggers on the tables that are in the publication at both the publisher and the subscribers. These triggers are used to signify a data change to the replication agent. There is a line in this that checks whether it is valid to submit the change (to stop circular references) and this checks to see if the session user is the replication agent. As the data is being inserted by a transactional replication, I guess that the user is indeed the replication agent and therefore it is bypassing the code.
I thought about trying to impersonate another user to work roudn this. However, we decided that the business doesn't actually need merge replication for these tables, transactional is fine. Adding transactional was no problem as there are no table triggers - it's all done from the log reader. So not really got a solution for this problem as such, change of plans has provided a business solution instead.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply