Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replication?


Replication?

Author
Message
schleep
schleep
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 1289
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



MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
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'))
Lempster
Lempster
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2683 Visits: 1657
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
MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
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...
schleep
schleep
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 1289
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



Lempster
Lempster
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2683 Visits: 1657
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?
schleep
schleep
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

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



MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
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).
MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
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.
schleep
schleep
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

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



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