February 17, 2014 at 1:00 pm
Hi all,
Here's my general scenario, I can elaborate as needed.
I need to be able to move (publish?) a subset of my database to a copy of my db on another server.
Both servers are on the same network, same domain (and are in fact linked).
It's a client/server setup, the source is OLTP, the destination is OLAP.
The subset is user-defined, and the schedule is user-defined - but I don't think we can actually schedule changes. Basically, my OLTP users want a short delay between making their changes and having those changes visible to the OLAP users so they do QA.
The database is not big (400 MB); any subset should not normally exceed 4 MBs (in my estimation).
The number of rows would be range between a couple from a few tables - a few hundred from a couple of tables.
The rows in the destination db can be locked to users for no more than roughly 3 seconds.
Any thoughts or ideas how to proceed? I'm thinking snapshot replication, but have never used any replication.
Thanks,
Paul
February 18, 2014 at 2:59 am
This depends, is the user defined data dynamic or fixed?
Fixed is easy, just define your filters and its good to go.
If you don't have something you can use to filter dynamically already you could add a column to each table (Publish BIT??) and update the rows you want to publish or remove using a filter like "Publish = 1". Rows which meet the condition will appear, rows that don't will be deleted.
Either way you need to intelligently use filters. With merge replication, you can use dynamic filters (date >= dateadd(dd,-1,getdate())). You cannot in transactional replication (date >= dateadd(dd,-1,'01 Jan 2014'))
February 18, 2014 at 4:16 am
If you only want a short delay between data being inserted into one database and appearing in the other, snapshot replication won't work for you. (Well, depending on exactly what you define as 'short' you could run the snapshot Agent very frequently, but it's overkill for your requirements). Transactional replication will work as mentioned in the first reply.
Regards
Lempster
February 18, 2014 at 4:28 am
Lempster (2/18/2014)
If you only want a short delay between data being inserted into one database and appearing in the other, snapshot replication won't work for you. (Well, depending on exactly what you define as 'short' you could run the snapshot Agent very frequently, but it's overkill for your requirements). Transactional replication will work as mentioned in the first reply.Regards
Lempster
And pay the penalty through table locks on the publisher and subscriber...
February 18, 2014 at 6:28 am
Thanks for the input, folks.
I had come to the idea of adding a publish/go live type column to my tables, although that would involve a *ton* of work.
The time between making a change in the OLTP db and "publishing" to the OLAP db could vary from minutes to days. So if Lempster is correct, that rules out snapshot repl.
And I'm fairly certain transactional replication can't be done, as one of our requirements is that the order in which the transactions are to be applied to the destination is not necessarily the same as the order on the source. Is this correct?
So I'm looking at merge or roll-your-own, both of which involve some sort of row-versioning, and a lot of work.
Anyone have any other ideas/guidance?
Thanks
February 18, 2014 at 6:40 am
schleep (2/18/2014)
And I'm fairly certain transactional replication can't be done, as one of our requirements is that the order in which the transactions are to be applied to the destination is not necessarily the same as the order on the source. Is this correct?
:ermm: Eh?? I don't understand that requirement, can you explain?
February 18, 2014 at 6:50 am
The users want some time between modifying data on the source and the mods being applied at the destination. Some changes (a typo, for example) don't need much in the way of QA. Large updates require a couple of hours to QA. And on their wildest-dreams wishlist, they could schedule changes going to the destination days in advance.
February 18, 2014 at 7:13 am
schleep (2/18/2014)
The users want some time between modifying data on the source and the mods being applied at the destination. Some changes (a typo, for example) don't need much in the way of QA. Large updates require a couple of hours to QA. And on their wildest-dreams wishlist, they could schedule changes going to the destination days in advance.
This will require some logic on your part. Replication ALWAYS delivers data transactionally. You will need to sort of authorisation process (maybe a job or application) in the form of a flag and filter to deliver data. Something like in my original suggestions would work (PUBLISH = 1).
February 18, 2014 at 7:14 am
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.
February 18, 2014 at 7:20 am
When you say, "Replication ALWAYS delivers data transactionally" do you mean in the same order as the changes were made in the source?
I understand that the the replication itself is a transaction, of course.
February 18, 2014 at 7:38 am
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
February 18, 2014 at 7:39 am
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; Theyll drag you down to their level and beat you with experience
February 18, 2014 at 7:42 am
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
February 18, 2014 at 7:51 am
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.
February 18, 2014 at 7:55 am
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply