July 20, 2012 at 12:09 pm
Hi there
I need to transfer data from a database(700 GB in size) on a server located in another city to here on a regular basis to have a reporting read only database here and also having high availability features. Then there's a need to transfer a backup(either Full or Diff) of this replica to some where else.
1) In case of using mirroring it's not possible to take a backup from the mirrored database although it's possible to take a snapshot of the mirror for reporting purposes
2)In case of using Log Shipping again it's possible to restore the replica in standby mode and use it for reporting but again it's not possible to take a backup of this replica database.
3)In case of using Replication there's no high availability feature plus it has it's own difficulties.
can anyone offer a practical solution plz.
Pooyan
July 20, 2012 at 12:17 pm
2) you have the backups, so you have HA. They can be restored on another instance.
3) replication can provide the HA if you have all the data replicated. There's no automatic switching, but that's the way it works.
If you want a better solution, move to SQL Server 2012 and AlwaysOn for this. It solves a bunch of your issues, but it's no cheap. I don't think there's any inexpensive solution here.
July 20, 2012 at 12:39 pm
The guys over here want to transfer backups from the replica to a cloud infrastructure which I know nothing about.
they don't want to restore the log backup to that cloud thing they just want to copy the backup files to there.in case of using log shipping the log backups files should be transferred to the cloud which in case of needing to restore the database from the cloud, it makes no sense (with lot's of log backups it takes forever to restore them) and because the Diff backups are so big (at list 13GB a day) it's impossible to transfer them from the source to the replica or the cloud to solve the restoring issue in case of using the cloud backups.We're using SQL 2008 R2 at the moment I'm just looking for a solution and prefer not use Replication
Pooyan
July 20, 2012 at 1:29 pm
Steve is right about backups from the source being your primary line of HA. Copying the fulls and any tran log backups somewhere as they are taken is critical no matter what you decide to do with the replica. An HA solution is not necessarily a replacement for good backups.
I know what you mean about transactional replication. I am not a huge fan of using it for HA, although it can come in handy in some scenarios.
An interesting (to me) but labor intensive option would be to look into a homegrown solution where you enable Change Data Capture (requires Enterprise Edition but I assume you have it based on snapshot comment) on the source and bring changes to the replica using custom SSIS packages run on-demand or on a schedule. In this configuration the replica would be a full-fledged DB and you could take backups, etc. A main problem with this approach is the level of effort required to implement what would essentially be analogous to merge replication.
Which brings us to merge replication. If it is an option your replica could be online, able to be backed up and could take changes from the source on-demand. As long as you ensure no one can write to the replica you will not have to deal with conflict resolution and it may be an easy road to implement. I have never used merge replication for HA, but from what you have shared so far it seems to fit nicely.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 20, 2012 at 2:04 pm
Thanks guys seems I have no other option but using replication:cool:
Pooyan
July 20, 2012 at 2:31 pm
Perhaps not. Replication can work, but it's a lot of administrative overhead. AlwaysOn is easier to administer, but $$. Depends on your resources.
If you want to go to the cloud, you need to save off fulls periodically somewhere. At some point the logs will be unweildy over time.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply