January 3, 2012 at 9:21 am
Hi guys,
I want to copy changes in an database from one server to another.
It has not to be realtime but it should refresh every 5-10 minutes.
The Destination Database should be readable
I tried out a few things, Tranaction-Log-Shipping, Mirroring, Replication but non of thoses seem to fullfill all my requirements.
For example with transaction log shipping I get the 5-10 Minutes but the receiving database is not accessable
Same with database mirroring. Copying "Realtime" but not accessable
With replication I have a lot of constrains and it seems to work best if you just have a static table structure because new tables/views/SPs are not replicated.
How did you fix that? I cannot imagine that you adjust your replication everytime you have a new table etc.!?
Cheers for your thoughts
Mitch
January 3, 2012 at 4:15 pm
Why is your structure changing so frequently?
January 3, 2012 at 10:30 pm
Some suggestions:
1) Log shipping. Database can be accessed under "stand by" mode
2) Mirroring: Snapshot database is accessible.
January 4, 2012 at 1:53 am
@benjamin: This database contains the Data for my reports and I want to transfer it from the main DWH to a server which is doing Reporting Service and Analysis Service.
So as we a continuously building new reports and therefore new fakt/dim-tables I do not want every developer to adjust the replication
1) Log shipping. Database can be accessed under "stand by" mode
Isn't that just when the source system fails?
2) Mirroring: Snapshot database is accessible.
You suggest that I Mirror the Database and then take snapshots from it?
I guess queries on the snapshot will drop when I am switching to an new snapshot?
I wish there would be a more elegant way to do this 😉
January 4, 2012 at 2:43 am
mitch.fh (1/4/2012)
@Suresh:1) Log shipping. Database can be accessed under "stand by" mode
Isn't that just when the source system fails?
No. Secondary database can be accessed (read-only) during log-shipping.
mitch.fh (1/4/2012)
I wish there would be a more elegant way to do this 😉
You are right. Log-Shipping and Mirroring are not elegant ways.
Hope others will pitch in and give better ideas.
January 4, 2012 at 4:14 am
Suresh B. (1/4/2012)
mitch.fh (1/4/2012)
@Suresh:1) Log shipping. Database can be accessed under "stand by" mode
Isn't that just when the source system fails?
No. Secondary database can be accessed (read-only) during log-shipping.
mitch.fh (1/4/2012)
I wish there would be a more elegant way to do this 😉You are right. Log-Shipping and Mirroring are not elegant ways.
Hope others will pitch in and give better ideas.
Both of these suggestions will work with one caveat, the database will become unavailable when restoring fresh logs or dropping and regenerating the database snapshot. You basically have to kick everyone off. The mirroring option is much quicker but last time I checked required enterprise edition (? will double check). Do you have this?
I would have thought merge or transaction replication would be better suited. Neither will require any outages. Both can be scheduled but transactional is designed to be closer to real time.
January 4, 2012 at 4:18 am
mitch.fh (1/3/2012)
Hi guys,With replication I have a lot of constrains and it seems to work best if you just have a static table structure because new tables/views/SPs are not replicated.
Its an administrative overhead. 2 (3) extra statements for 100% accessible data. Of course you may end up with additional subscribers.
CREATE TABLE|VIEW|PROC
sp_addarticle
sp_addsubscription
Manually run the snapshot agent. You could do sp_startjob if you wanted to keep it all TSQL.
Contrary to many peoples understanding, this does not require you to reinitialise the subscribers.
January 4, 2012 at 9:32 am
If you are changing schema often, then you don't have stable reports. Replication works well, and handles schema changes, though a high volume might be a problem. I would vote for replication to move data over in a simple way.
If you can track changes, or want something more robust, a series of SSIS/ETL jobs might make more sense, but it's more work.
January 4, 2012 at 2:57 pm
MysteryJimbo (1/4/2012)
Both of these suggestions will work with one caveat, the database will become unavailable when restoring fresh logs or dropping and regenerating the database snapshot. You basically have to kick everyone off. The mirroring option is much quicker but last time I checked required enterprise edition (? will double check). Do you have this?
I would have thought merge or transaction replication would be better suited. Neither will require any outages. Both can be scheduled but transactional is designed to be closer to real time.
Steve Jones - SSC Editor (1/4/2012)
If you are changing schema often, then you don't have stable reports. Replication works well, and handles schema changes, though a high volume might be a problem. I would vote for replication to move data over in a simple way.If you can track changes, or want something more robust, a series of SSIS/ETL jobs might make more sense, but it's more work.
I have Enterprise Edition, so that would not be a problem. But no access while refreshing is not an option. The Reporting Service should work all the time and not if the user is lucky.
By
>>> So as we a continuously building new reports and therefore new fakt/dim-tables I do not want every developer to adjust the replication <<<
I meant that we are adding new tables/views/sp. After a report is build completly that structure of those tables stays untouched.
It would be just too sweet if the replication could automatically add new objects itself to the replication. As the destination database just has to readable not writable.
I will try the transactional replication and see if we can minimize the overhead when creating a new object.
IF we have to create/modify every object by hand on every subscriber (which is just one at this time)
Then I could see me confronted with the question why not add an additional part to the SSIS Packages that would transfer the new data to the destination database(s)
IF you have further suggesstions let me know
Cheers
Mitch
January 5, 2012 at 5:59 am
You do not have to create / modify every object by hand on your subscribers when using Transactional replication. When creating a new table, just add one more step to it. ie adding the new object to replication.
If you wait till SQL 2012 is out you can use HADRON for this quite easily. 🙂
-Roy
January 5, 2012 at 6:11 am
Roy Ernest (1/5/2012)
You do not have to create / modify every object by hand on your subscribers when using Transactional replication. When creating a new table, just add one more step to it. ie adding the new object to replication.If you wait till SQL 2012 is out you can use HADRON for this quite easily. 🙂
Thats what I said. A few extra tsql steps
January 5, 2012 at 6:19 am
MysteryJimbo (1/5/2012)
Roy Ernest (1/5/2012)
You do not have to create / modify every object by hand on your subscribers when using Transactional replication. When creating a new table, just add one more step to it. ie adding the new object to replication.If you wait till SQL 2012 is out you can use HADRON for this quite easily. 🙂
Thats what I said. A few extra tsql steps
Apologies... Did not read all the replies... 🙁 And you even had the T-SQl statements. I think I should go get another cup of coffee
-Roy
January 5, 2012 at 6:46 am
Roy Ernest (1/5/2012)
MysteryJimbo (1/5/2012)
Roy Ernest (1/5/2012)
You do not have to create / modify every object by hand on your subscribers when using Transactional replication. When creating a new table, just add one more step to it. ie adding the new object to replication.If you wait till SQL 2012 is out you can use HADRON for this quite easily. 🙂
Thats what I said. A few extra tsql steps
Apologies... Did not read all the replies... 🙁 And you even had the T-SQl statements. I think I should go get another cup of coffee
@rob-2: Okay, I didn't get that 😉
I will try it next week and tell you, how it went
Cheers
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply