September 30, 2011 at 12:03 pm
Hi Everybody,
We are currently using log shipping to create a standby database that we use for reporting purposes. This takes the load off of our production database and in general it works really well. The only problem we are having is that when something happens to break the log shipping chain (network glitch..corrupted log backup) because our database is 500GB, it takes a long time to restore the standby from a full backup and roll forward on the logs. My boss is asking me to see if there are any 3rd party tools or software that might be a better solution for us in order to create and maintain a read-only database for reporting purposes. Due to the way our database is setup, replication and mirroring are not options. What are some of you doing other than log shipping, replication and mirroring to create an updated daily read-only reporting database?
Thanks in advance!!
Isabelle
Thanks!
Bea Isabelle
September 30, 2011 at 12:22 pm
Well, if replication and mirror/snapshots are out, really the only other option left is SSIS delta data moves, and that's going to rely on you having an efficient delta detection mechanism. Small tables can just be dropped/rebuilt (figure under 250,000 rows), anything larger I'd look to delta.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 30, 2011 at 4:08 pm
Yeah, pretty much you're down to moving the data in some fashion. There's no magic method that I'm aware of. Although...close to magic, if you're running virtuals, there is a way within the virtual machines, to replicate databases, supposedly transactional aware, although I haven't tested this. Also, there is a way to move data to second locations using SAN systems. Other than those, nothing else comes to mind.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 30, 2011 at 4:15 pm
Hi,
Thank you for your replies. Unfortunately I am dealing with some tables that are about 30GB with over 40 million rows, so i don't think some sort of SSIS delta job would work. Hmmm...i will keep looking.
Thanks again! 🙂
Thanks!
Bea Isabelle
October 5, 2011 at 12:20 pm
You could look into Red-Gate's SQL Backup. I use it and think its great. I don't have any log shipping going on, but it makes quick work of backups and restores.
October 5, 2011 at 12:38 pm
Hi,
We are actually evaluating some different Backup software for that exact reason. Idera's SQL Safe Lite cut my backups down from 500GB to 60GB and only 2 hours vs over 18 hours the way we are currently doing it.
So I'm thinking that might be the way to go. Thank you for your input! 😀
Isabelle
Thanks!
Bea Isabelle
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply