September 11, 2012 at 7:49 pm
Hi
I would like to know what replication model I should use to achieve the following if even achievable. This is a simplified example.
Database 1 (DB1)Contains a table with client A, B, C, D.
Database 2 (DB2) Contains a table with client B, E, F,G
As you can see both DB's share a common client B.
Objectives
I only want to replicate the data for Client B between the two DB's
I do not want to replicate any of the other clients. (A, C, D, E, F, G - Not to be replicated)
Transactions must take place at either DB (Publisher and Subscriber ex Merge Replication)
Existing data at DB2 must be preserved. In other words, if using replication filtering, the data must not be removed from DB2. It seems that a replication filter actually removes any other data out of the subscriber ie, A, C, D, E, F, G gets deleted. (Surely a replication filter should be about what gets replicated and what does not, and not about deleting existing data at the subscriber because it does not match the filter criteria?)
What I have done.
1. I have setup a filter on DB1 (Publisher - Merge Replication)to filter client B. The problem is:- I can now only see Client B at DB2 (Subscriber) and if I insert a new record for any other Client other than B it is automatically deleted for DB 2 when I sync. Why? DB2 must be able to preserve exising data and add any other data. Only filter gets replicated.
2. So I tried republishing. The problem is:- When I modify Client B at Subscriber it causes a conflict at the Republisher. All new transactions at subscriber are posted to the conflict manager. Why?
I would appreciate any assistance or.
Kind Regards
Deon
September 11, 2012 at 10:04 pm
I have run transactional replication in the past where I have customised the replication stored procedures in order to preserve data at the subscriber. I've never done much with merge replication though so I don't know if this would be a viable option. Do you need to use merge replication in this scenario or would it be possible to use transactional replication with an updateable subscription?
Sorry I don't have any exact resolution but if no one else has a quick answer I'll be more than happy to tinker with some scenarios and see if I can come up with anything:-)
September 11, 2012 at 10:31 pm
Hi Clare
Thank you for your reply.
I have not had many replies in various forums, so would appreciate if you could you explain a bit more about transactional replication with an "updateable subscription"? Would the Updates for client B be replicated back to the Publisher?
I have also browsed BOL. I thought my answer would be a Trigger NOT FOR REPLICATION however that applies to the actual schema. 🙁
I am sure this is not an "exceptional" scenario as many people have disparate databases that need to sync only a portion of the data between the databases.
Thanks again for your assistance.
September 11, 2012 at 10:43 pm
deon 25081 (9/11/2012)
Hi ClareThank you for your reply.
I have not had many replies in various forums, so would appreciate if you could you explain a bit more about transactional replication with an "updateable subscription"? Would the Updates for client B be replicated back to the Publisher?
I have also browsed BOL. I thought my answer would be a Trigger NOT FOR REPLICATION however that applies to the actual schema. 🙁
I am sure this is not an "exceptional" scenario as many people have disparate databases that need to sync only a portion of the data between the databases.
Thanks again for your assistance.
Yes that's right, a publication with updatable subscriptions means that changes at the subscriber are allowed and are pushed back to the publisher (its uses triggers on the subscription tables to do this).
In terms of your requirement to horizontally filter the data without it being removed at the subscriber, I am absolutely certain it can be done because I've implemented a very similar system. However...it was a fair while ago :ermm: so I'd need to go back and refresh my memory. It involves a little 'tweakery' of the replication stored procedures, as I mentioned, so I will need to do a little set up before I can give you full details.
Of course if anyone else responds in the meantime with a more straightforward method then go with that!
September 11, 2012 at 10:54 pm
Thank you very much, I really appreciate your help.
September 12, 2012 at 12:20 am
Ok, so we're nearly there but with some caveats, which I'll list first.
1. In order to deploy an updatable subscription the tables need to have a uniqueidentifier column. If you should happen to have one of these, then that's all good but, if you don't, then you will need to manually create one at the subscriber and populate it with data....
ALTER TABLE myTable
ADD [msrepl_tran_version] [uniqueidentifier] NULL;
UPDATE myTable
SET msrepl_tran_version = NEWID();
2. At the time you create the subscription any EXISTING data at your subscriber won't be pushed back to the publisher, so you'd need to be able to manually sync it first.
Ok, with those out of the way and, assuming they're not showstoppers the rest is really easy 😀
You need to create your Publication as 'Transactional..with updatable subscriptions'
You will choose your tables and be prompted at this point if your publication doesn't have a uniqueidentifier column (if you don't it will create [msrepl_tran_version] for you)
Next you can create your filter so...WHERE Client = 'B'
After you've gone through the next steps of security settings etc it is important that you only select 'Generate a script file with steps to create the publication'. ENSURE 'Create the publication' is UNCHECKED:exclamationmark:
Run through the rest of the steps and then open the sql script it has created. You only need to change one thing to get your desired behaviour. The exec sp_addarticle proc has a parameter @pre_creation_cmd; change this to 'none'.
You can now execute the script and the publication will be created.
Create the subscription in the usual way.
You will see that three triggers have been created on your subscription table and you will need to edit each these because they contain code to constrain the subscription to the filter as follows:
if exists (select * from inserted where not ( [ClientGroup] = 'B' ))
begin
exec sys.sp_MSreplraiserror 21034
goto FAILURE
end
By removing the 'error' call here - you can simply replace it with a RETURN call, you will bypass this issue.
Tweakery, as I said, but it is achievable.
September 12, 2012 at 2:51 am
And I should have added the biggest caveat of all...make sure you test the living daylights out of this because there will be a myriad of factors that will have an impact!
September 12, 2012 at 4:04 am
Hi Clare
Thank you for the info. I will be trying this today and provide feedback. Thank you very much for the info.
September 13, 2012 at 5:50 am
Hi Clare
I have have been running into error after error from the start. I have managed to get around them and am hitting my head against the wall of "linked servers". It seems to be related to security and not SQL security from what I can see? Unfortunately I am running out of time for this project and will have to revert to merge without filtering, not that that is ideal for the client, however we have to move on.
I will get stuck into it again to get to the bottom of it when I have some more time (if that ever happens :))
What I did want ask, which is something I read after following your suggestions, is that if the subscriber had to create a new client (ex. Client J) other that the filtered Client B, client J would be replicated to the Publisher. So in essence the filter applies one way from the publisher to the subscriber but not the other way? Is that statement correct?
Thank you again for taking the time to assist. I do really appreciate that.
Regards
Deon
September 13, 2012 at 7:04 pm
Sorry you're having troubles with this...I did say it would be fun I think 😛 The difference with this scenario and the way I've deployed it before in production is that I didn't have the updatable subscription, I just had the replication customised so that data deleted at the publisher wouldn't be deleted at the subscriber and also, as with your scenario, that any data at the subscriber would not be deleted during the snapshot process. The two-way element is a more interesting one!
To answer your question about filtering:
'out of the box' if you filter the publication, with an updatable subscription, any attempt to insert data at the subscriber that doesn't match the filter clause would generate an error, hence the need to modify the triggers. With the modification to remove the error check, only the data that matches the filter criteria would be pushed back to the publisher and anything else is ignored.
September 13, 2012 at 9:38 pm
No worries, you have been very helpful. I will get the linked servers waxed, its just a time project time factor which is forcing me to move on. I will still test your suggestion as I am sure this is actually a common requirement for a lot of clients that only need to replicate a subset of data???
Many thanks again!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply