January 9, 2012 at 2:45 pm
I am looking to replicate production data from site A to site B. I am using SQL 2008 R2 Enterprise. The database at site B will be refreshed nightly and needs to be read/writable. The connection between the two sites is slow and cannot be increased.
I am looking for the most efficient way to bring the data over to site B. Doing a compressed native backup and restore is too large for the connection to do on a nightly basis. Mirroring and Log Shipping will not work since the destination database is not writable.
I could use a third party tool to compress the backups (more than native) and restore at site B. I could do differential backups and restores during the week and fulls on the weekend. The issue is that the data rate of change is high due to a nightly data mart refresh. The differential by the end of the week may be very large.
Does anyone have other creative suggestions? Thanks
January 9, 2012 at 3:01 pm
You had mentioned that Log Shipping is not an option because the data is not readable? There is a option where you can make the database readable.
When you used the term refreshed nightly, I had the impression that the data on "Server B" can be updated once a day. Is there something that I am not understanding?
January 9, 2012 at 3:10 pm
How far apart are the two data centres? Are there staff at both data centres?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2012 at 3:10 pm
Thanks Chris - The Server B (destination) database actually needs to have both read and write capibility.
Server B will be refreshed with prod data once a night but needs to be read/writeable all day for QA script running/testing purposes.
Hope this helps. Sorry for the confusion.
January 9, 2012 at 3:15 pm
Gila - 900 miles across an ocean. One is staffed part of the day and the destination is not staffed.
January 9, 2012 at 3:21 pm
Ok, so courier is not an option.
Transactional replication?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2012 at 3:43 pm
why does the database have to be refreshed nightly and data should be writable to it at the destination?
Coming to possibilities, I was thinking of FTP for one, but that could be slow as well.
Third party tool might be a best bet - SQL Safe, Litespeed, etc. You might get good compression rates.
How big is the backup? One more option, which I have seen in action, and is by no means my recommendation - a compression utility to compress the backup and then have a task to copy it, unzip it and restore. This would be if the backup is not already a compressed backup. You wouldn't want a compression tool on a compressed database backup.
Once again, probably third party tool is the best solution, as you can manage the backups/restores from a central place too. You can request a trial of these software.
January 9, 2012 at 3:59 pm
full backup once a week at the weekend and diffs during the week may work for you
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 9, 2012 at 4:52 pm
A couple questions:
When you say refreshed, do you mean that all changes (ins/upd/del) at the source are transferred to the secondary site? Is it one shot, just apply the updates, or are you trying to make the secondary look like the primary when the process is done.
Why is the secondary writeable? I mean purpose-wise here.
The reasons are this. If you are moving changes, you have lots of options, though they have some maintenance. Replication can work, as can SSIS possibly integrated with CDC or CT. Those are administratively a little headache of you get much in the way of schema changes.
In terms of backup/restore, it seems that writeable at the secondary means the changes don't need to be persisted. Is that true? If I change the price product A to 12 from 10 at the source, and someone changes the price the same day to 14 and the secondary, when I move 12 to the secondary, do I update things to 12, or leave it at 14? If you're just making the changes, then I might lean towards some replication, perhaps on a schedule, but with the source always winning for conflicts.
January 9, 2012 at 6:13 pm
If the time it takes to backup and restore is unacceptable perhaps you could increased the speed of backup by splitting it into mutiple files and if you have multiple controlers then writing each file to different drives. Then use SSIS to FTP the files to the destination (I have been able to get this working with multiple threads, moving tons of data in just a few minutes). Run a little script on the destination side to restore the database and Robert's your mother's brother. 😉
January 10, 2012 at 8:19 am
Gila
With transactional replication, are all FKs created at the subscriber? If I am correct, they aren’t. If the FKs are not on the subscriber, I am concerned about keeping referential integrity during the update scripts that are tested on the subscriber. Also, wouldn’t I have to reapply a daily snapshot each night to mimic a daily refresh? I need to make it mirror prod on a nightly basis and wipe out any update on the subscriber that were done during the day.
Steve
Yes, the secondary needs to look like the primary when the process is done. The secondary needs to be writeable to test scripts and processes that will be run on production(primary). And yes, the changes on the secondary do not need to be persisted and would be wiped out on a nightly basis. Maybe replication on a schedule would work with the source winning conflicts but I have a question. What if product B was changed at the secondary but never changed at the source. Is there a way to have it revert back on the secondary to the value that it is at the source?
One solution I am thinking of is using a third party backup tool to get the backup to be smaller due to connection constraint between the two sites. I would then restore the database or use Red Gate Virtual Restore to mount a writeable copy to save disk space on the secondary server.
Thanks for the help.
January 10, 2012 at 9:03 am
If you want your secondary reset, then replication is not the choice, nor is SSIS. My misunderstanding of the refresh was the way it was worded with other choices being considered. My apologies.
A refresh means that you need to just do a backup/restore. The third parties can reduce the transfer time and backup/restore time with compression. Virtual Restore will definitely save you space, and even more time on the secondary.
You could do mirroring and then create a snapshot of the mirror each morning. You would delete the old snapshot, and recreate a new one. It's a short disconnect for clients, but in this case, it might work. It's writeable, so you can actually run tests. That might be the shortest/easiest way to do things. Plus you can reset the snapshot at any time of the day and be current.
January 10, 2012 at 9:46 am
Steve - I like where you are going with creating a snapshot of a mirror but isn't a snapshot read-only? How can I make it writeable? This is a perfect solution if it can be writeable. Thanks again for your help.
January 10, 2012 at 9:56 am
I hate to jump back in on this,
I have built reporting enviorments like this in the past. Where, we would copy the Full over each week, and the Diff each night. This way we could go to a full database reset each night. So it sounds like the problem may be that the initial Full is to big? Could it then be split into smaller files and/or compressed?
How big of a database are we talking, and bandwidth between the two locations?
January 10, 2012 at 10:01 am
sjs-36273 (1/10/2012)
One solution I am thinking of is using a third party backup tool to get the backup to be smaller due to connection constraint between the two sites. I would then restore the database or use Red Gate Virtual Restore to mount a writeable copy to save disk space on the secondary server.Thanks for the help.
So, like I said then full weekly and diffs every night 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply