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

Replication? Expand / Collapse
Author
Message
Posted Tuesday, February 18, 2014 7:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 1,362, Visits: 15,266
schleep (2/18/2014)
When you say, "Replication ALWAYS delivers data transactionally" do you mean in the same order as the changes were made in the source?


Yes. The order in which they are written to the transaction log on publisher, they are written to the distribution db by the log reader, then committed on the subscriber by the distribution agent. It doesn't matter if its transactional or merge replication (the same applies for mirroring and log shipping), transactions are applied in order for consistancy
Post #1542571
Posted Tuesday, February 18, 2014 7:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
MysteryJimbo (2/18/2014)
schleep (2/18/2014)
And on their wildest-dreams wishlist, they could schedule changes going to the destination days in advance.



As mentioned, merge replication can use dynamic dates to publish so this is very possible.
Yes. Unless of course there is an error somewhere/somehow. Every now and again there's a need to re-snap the table.


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1542572
Posted Tuesday, February 18, 2014 7:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:13 AM
Points: 2,036, Visits: 1,380
Yes. for example, if a row is updated on the source and then later that same row is deleted on the source, that order of transactions will be preserved when replicated to the target. (Reminds me of the time a colleague recounted the answer that an interviewee gave to the question "what is transactional replication"? and the answer came back "it's when transactions are replicated transactionally"! )

Change Data Capture (CDC) has the ability to 'net' transactions so an INSERT followed by a DELETE would generate no change data. Not for the reason I just gave, but I wonder whether CDC is an option for you?
I think MisteryJimbo's suggestion to use Merge Replication is your best option if you don't want to create your own custom solution.

By the way, wouldn't it be better to validate the data as it is arrives in the source database rather than using a replication delay to QA it?

Regards
Lempster
Post #1542576
Posted Tuesday, February 18, 2014 7:51 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 5:21 AM
Points: 428, Visits: 928
OK, that's what I thought.

I guess we're going to roll-our-own. The information management application is up for a re-write anyway, it's 15 years old, and there are plenty of things we'll do differently, given new tech and experience.

Lempster: The QA involves using the retrieval application to visualize the data changes. Of course, it always looks good in dev at the time they write it to the source. txtPost_CommentEmoticon(''); It's the unintended consequences which occasionally bite us that, er... give us pause.




Post #1542584
Posted Tuesday, February 18, 2014 7:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 1,362, Visits: 15,266
MyDoggieJessie (2/18/2014)
MysteryJimbo (2/18/2014)
schleep (2/18/2014)
And on their wildest-dreams wishlist, they could schedule changes going to the destination days in advance.



As mentioned, merge replication can use dynamic dates to publish so this is very possible.
Yes. Unless of course there is an error somewhere/somehow. Every now and again there's a need to re-snap the table.


No, transactions are still delivered transactionally once fixed. Replication stops until that happens. Re-snapping the table is an exception as tables are delivered whole
Post #1542587
Posted Tuesday, February 18, 2014 8:05 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
Agreed, they are still delivered to the distributor (my bad for not clarifying that in my statement). What I meant to say was depending on the potential error at the subscriber, in a busy OLTP system, attempting to resolve the error can be a lot more work than simply re-snapping the table.

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1542596
Posted Wednesday, February 19, 2014 2:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 1,362, Visits: 15,266
MyDoggieJessie (2/18/2014)
Agreed, they are still delivered to the distributor (my bad for not clarifying that in my statement). What I meant to say was depending on the potential error at the subscriber, in a busy OLTP system, attempting to resolve the error can be a lot more work than simply re-snapping the table.


Depends on the size of the table and the impact of producing the snapshot. In truly large and busy OLTP systems that I'm used to, this is not an option.
Post #1542898
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse