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 Monday, February 17, 2014 1:00 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 5:21 AM
Points: 428, Visits: 928
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



Post #1542255
Posted Tuesday, February 18, 2014 2:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:33 AM
Points: 1,361, Visits: 15,264
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'))
Post #1542432
Posted Tuesday, February 18, 2014 4:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:05 AM
Points: 2,036, Visits: 1,378
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
Post #1542463
Posted Tuesday, February 18, 2014 4:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:33 AM
Points: 1,361, Visits: 15,264
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...
Post #1542470
Posted Tuesday, February 18, 2014 6:28 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 5:21 AM
Points: 428, Visits: 928
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






Post #1542533
Posted Tuesday, February 18, 2014 6:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:05 AM
Points: 2,036, Visits: 1,378
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?


Eh?? I don't understand that requirement, can you explain?
Post #1542537
Posted Tuesday, February 18, 2014 6:50 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 5:21 AM
Points: 428, Visits: 928
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.




Post #1542546
Posted Tuesday, February 18, 2014 7:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:33 AM
Points: 1,361, Visits: 15,264
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).
Post #1542553
Posted Tuesday, February 18, 2014 7:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:33 AM
Points: 1,361, Visits: 15,264
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.
Post #1542554
Posted Tuesday, February 18, 2014 7:20 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 5:21 AM
Points: 428, Visits: 928
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.




Post #1542560
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse