Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Replication - Can this be accomplished through Replication? Expand / Collapse
Author
Message
Posted Tuesday, September 11, 2012 7:49 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 13, 2012 9:33 PM
Points: 6, Visits: 14
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
Post #1357759
Posted Tuesday, September 11, 2012 10:04 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 06, 2014 6:06 PM
Points: 286, Visits: 570
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


Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1357775
Posted Tuesday, September 11, 2012 10:31 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 13, 2012 9:33 PM
Points: 6, Visits: 14
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.
Post #1357777
Posted Tuesday, September 11, 2012 10:43 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 06, 2014 6:06 PM
Points: 286, Visits: 570
deon 25081 (9/11/2012)
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.


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 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!



Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1357782
Posted Tuesday, September 11, 2012 10:54 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 13, 2012 9:33 PM
Points: 6, Visits: 14
Thank you very much, I really appreciate your help.
Post #1357784
Posted Wednesday, September 12, 2012 12:20 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 06, 2014 6:06 PM
Points: 286, Visits: 570
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

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.


Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1357798
Posted Wednesday, September 12, 2012 2:51 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 06, 2014 6:06 PM
Points: 286, Visits: 570
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!

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1357841
Posted Wednesday, September 12, 2012 4:04 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 13, 2012 9:33 PM
Points: 6, Visits: 14
Hi Clare

Thank you for the info. I will be trying this today and provide feedback. Thank you very much for the info.
Post #1357869
Posted Thursday, September 13, 2012 5:50 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 13, 2012 9:33 PM
Points: 6, Visits: 14
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
Post #1358487
Posted Thursday, September 13, 2012 7:04 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 06, 2014 6:06 PM
Points: 286, Visits: 570
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.


Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1359003
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse