SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replication?


Replication?

Author
Message
MysteryJimbo
MysteryJimbo
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2231 Visits: 15344
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
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6682 Visits: 7394
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" ;-)
Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3208 Visits: 1657
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"! :-D)

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
schleep
schleep
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1018 Visits: 1326
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.



MysteryJimbo
MysteryJimbo
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2231 Visits: 15344
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
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6682 Visits: 7394
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" ;-)
MysteryJimbo
MysteryJimbo
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2231 Visits: 15344
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search