August 26, 2011 at 12:55 pm
Kiara (8/26/2011)
Grant Fritchey (8/26/2011)
SQLRNNR (8/26/2011)
Grant Fritchey (8/26/2011)
Need the advice of the Thread.Putting together an article on the most common backup errors (apart from not having one) and how to avoid them. I have four so far:
Backing up files instead of creating backups (.mdf, ldf, you know)
Running out of space on disk
Log Filling up
Only having differential backups available.
Are there any glaring ones I'm missing here?
Backing up across network.
I guess Log Filling up takes care of Full Recovery mode w/out tran log backups
Oh, right, across the network. Gotta ask though, do we really think that's as big a deal as we used to? I only ask because I've been running backups for years to "local" drives that were actually SAN drives that required a network hop to get there. Further, it's never been an issue. Why would simply backing up to a network share be so much worse (and I'm asking that with the full knowledge that I've seen it behave badly, but it was a long time ago). Are we perpetuating old advice on that one? If not, it's a good one.
I don't know how common it still is, but I'm fighting with this issue routinely right now. Network backup of a 100GB db normally takes 2 hrs 5 min. Except when it takes 6, or 7, or 8 hours. Or I get wonderful "lost connection to the..." messages and the backups fail. And that with 2GB of connectivity (teamed 1GB NICs).
It shouldn't take anywhere near that long for something of that size.
We don't have the physical space in the SQL server to put more storage (yes, I want another controller and separate drives) in for me to use for the backups - my server admin just double-checked on that for me. So...
Yes, cross network backups are still an issue in some situations. (I think mine is actually that the server we're backing up to is low on memory, so we're going to test with a different destination, but I'd lose a lot less sleep if those backups were local or SAN based.)
That's weird. Before we switched to EMC for the backups, I was doing a full nightly backup for a 300gig database (as well as for 12 other smaller databases) and it only took an hour even with all of the backups running at once. Is it old hardware?
We had a problem where all of a sudden the backup started taking 5 or 6 hours, but that's because the server people started running the EMC backups at the same time as my backups without telling anyone. Is there something else running when your backups take extra-long?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 26, 2011 at 12:58 pm
We currently do backup over the network. When we started backing up to the local disk, the page file usage started going high. Also if you want to use less resources when copying the local back up to the network, the copy has to be initiated from the network share. Therefore we decided to continue with back up over network.
Our back up file is currently 95 GB. Our backup finishes in 35 minutes over the network.
-Roy
August 26, 2011 at 1:00 pm
Jack Corbett (8/26/2011)
Grant Fritchey (8/26/2011)
SQLRNNR (8/26/2011)
Grant Fritchey (8/26/2011)
Need the advice of the Thread.Putting together an article on the most common backup errors (apart from not having one) and how to avoid them. I have four so far:
Backing up files instead of creating backups (.mdf, ldf, you know)
Running out of space on disk
Log Filling up
Only having differential backups available.
Are there any glaring ones I'm missing here?
Backing up across network.
I guess Log Filling up takes care of Full Recovery mode w/out tran log backups
Oh, right, across the network. Gotta ask though, do we really think that's as big a deal as we used to? I only ask because I've been running backups for years to "local" drives that were actually SAN drives that required a network hop to get there. Further, it's never been an issue. Why would simply backing up to a network share be so much worse (and I'm asking that with the full knowledge that I've seen it behave badly, but it was a long time ago). Are we perpetuating old advice on that one? If not, it's a good one
I've done it, but with occasional issues on big backups +2GB, I know that isn't big for some of you but it was for me. It was also SQL 7 & 2000, I haven't seen issues with 2005+. I know Brent Ozar recommends doing backups across the network, http://www.brentozar.com/archive/2008/09/back-up-your-database-to-the-network-not-local-disk/. He's a pretty smart guy.
I have seen the issues in 2008. Not as bad (mostly due to hardware improvements) as the SQL 2000 days, but still present.
If you have teamed nics, redundant paths, and a fat pipe - It is less of an issue. Much the same as a SAN these days as Grant mentioned. I still prefer the multi-tier approach. Backup local, then backup that backup to network or grid.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 26, 2011 at 1:02 pm
Roy Ernest (8/26/2011)
We currently do backup over the network. When we started backing up to the local disk, the page file usage started going high. Also if you want to use less resources when copying the local back up to the network, the copy has to be initiated from the network share. Therefore we decided to continue with back up over network.Our back up file is currently 95 GB. Our backup finishes in 35 minutes over the network.
Easy enough to do. But, the resource usage I have noticed is not enough to blink at.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 26, 2011 at 1:06 pm
How would you let the Server that is going to initiate the back up know that the back up is complete?
Coping a 100 GB file does use quite a bit of resource. CPU, Page file, memory, IO and Network IO.
-Roy
August 26, 2011 at 1:12 pm
Stefan Krzywicki (8/26/2011)
Here's something everyone in IT should be aware ofYour decision making ability deteriorates as you make more decisions
Interesting article for the first half. Lost my decision making ability after that. In fact - I think I am done for the day. :crazy:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 26, 2011 at 1:20 pm
Roy Ernest (8/26/2011)
How would you let the Server that is going to initiate the back up know that the back up is complete?Coping a 100 GB file does use quite a bit of resource. CPU, Page file, memory, IO and Network IO.
If you are looking to copy from the destination server, then setup a service that pings the sql service to check if job is completed every min, 5 min, 10 min or whatever interval you want. It is low traffic and low resource.
I noticed much higher resource requirements (cpu, memory) when backing up across the network than to backup local and then dump it off across the network.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 26, 2011 at 1:21 pm
SQLRNNR (8/26/2011)
Stefan Krzywicki (8/26/2011)
Here's something everyone in IT should be aware ofYour decision making ability deteriorates as you make more decisions
Interesting article for the first half. Lost my decision making ability after that. In fact - I think I am done for the day. :crazy:
Quick! eat a sandwich!
Of course, a good way to get some calories that change into glucose would be with a beer or two, but for some reason I don't think that'll help the decision making process...
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 26, 2011 at 1:30 pm
Stefan Krzywicki (8/26/2011)
SQLRNNR (8/26/2011)
Stefan Krzywicki (8/26/2011)
Here's something everyone in IT should be aware ofYour decision making ability deteriorates as you make more decisions
Interesting article for the first half. Lost my decision making ability after that. In fact - I think I am done for the day. :crazy:
Quick! eat a sandwich!
Of course, a good way to get some calories that change into glucose would be with a beer or two, but for some reason I don't think that'll help the decision making process...
Yeah - I just ate a serving of pasta - no help.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 26, 2011 at 1:39 pm
SQLRNNR (8/26/2011)
If you are looking to copy from the destination server, then setup a service that pings the sql service to check if job is completed every min, 5 min, 10 min or whatever interval you want. It is low traffic and low resource.I noticed much higher resource requirements (cpu, memory) when backing up across the network than to backup local and then dump it off across the network.
Look at all the pain you have to go through. First you have to write an external service and monitor the service to make sure that is running constantly. 🙂
I did not notice much difference in CPU usage and memory when doing back ups over the network. If you have a 1 GB network card teamed up properly, you wont even see that much NetworkIO waits.
The biggest issue is this. If I am doing a network backup it takes me 45 min for the 100 GB DB. If I do it on the local disk it takes 35 min. Then to copy the file from DB server to network it will take another 30 min. Therefore the whole back up process will take more than hour. What would happen if the DB server goes down in before the back is completed? Isnt that more riskier?
-Roy
August 26, 2011 at 1:42 pm
Roy Ernest (8/26/2011)
SQLRNNR (8/26/2011)
If you are looking to copy from the destination server, then setup a service that pings the sql service to check if job is completed every min, 5 min, 10 min or whatever interval you want. It is low traffic and low resource.I noticed much higher resource requirements (cpu, memory) when backing up across the network than to backup local and then dump it off across the network.
Look at all the pain you have to go through. First you have to write an external service and monitor the service to make sure that is running constantly. 🙂
I did not notice much difference in CPU usage and memory when doing back ups over the network. If you have a 1 GB network card teamed up properly, you wont even see that much NetworkIO waits.
The biggest issue is this. If I am doing a network backup it takes me 45 min for the 100 GB DB. If I do it on the local disk it takes 35 min. Then to copy the file from DB server to network it will take another 30 min. Therefore the whole back up process will take more than hour. What would happen if the DB server goes down in before the back is completed? Isnt that more riskier?
IC. Yours behaves differently.
We had it configured to backup to network - took 4+hrs.
To reconfigure and backup local and then copy to network took <= 30 min total. Well worth it for us. Even if it took 3hrs after the reconfigure - we were much better off than the sql backup to network.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 26, 2011 at 1:44 pm
Roy Ernest (8/26/2011)
SQLRNNR (8/26/2011)
If you are looking to copy from the destination server, then setup a service that pings the sql service to check if job is completed every min, 5 min, 10 min or whatever interval you want. It is low traffic and low resource.I noticed much higher resource requirements (cpu, memory) when backing up across the network than to backup local and then dump it off across the network.
Look at all the pain you have to go through. First you have to write an external service and monitor the service to make sure that is running constantly. 🙂
I did not notice much difference in CPU usage and memory when doing back ups over the network. If you have a 1 GB network card teamed up properly, you wont even see that much NetworkIO waits.
The biggest issue is this. If I am doing a network backup it takes me 45 min for the 100 GB DB. If I do it on the local disk it takes 35 min. Then to copy the file from DB server to network it will take another 30 min. Therefore the whole back up process will take more than hour. What would happen if the DB server goes down in before the back is completed? Isnt that more riskier?
Well, if you're doing a local backup, you have a 33% smaller chance the DB server will go down before the backup is completed. Networked, it takes 45 min to complete the backup. Local it takes 30 min. The file transfer is a separate task taking 35 minutes, but the backup file is already completed. The only increase in risk there is if you can't recover the backup file as well as the database. If it goes down at the 40 minute mark, you've lost the backup and the database in a network backup. In a local backup with file transfer, you have a valid backup as long as you can recover the file.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 26, 2011 at 1:58 pm
I guess everyone has their own outlook of where the risk is when taking back ups. 🙂 I prefer going for network back up. I feel that it is bit more less risk.
I have seen lots of people arguing both way around. 🙂
-Roy
August 26, 2011 at 2:03 pm
Roy Ernest (8/26/2011)
I guess everyone has their own outlook of where the risk is when taking back ups. 🙂 I prefer going for network back up. I feel that it is bit more less risk.I have seen lots of people arguing both way around. 🙂
The info I have on this is that you can have a lost packet not reported (or something simlar). That backup will succeed but won't be restoreable.
So yes use network share all you want but just make sure you test the backup right away. That includes diffs and logs.
August 26, 2011 at 2:12 pm
Yep, we do just that. After the back up is completed, the same back up is restored in another DB Server, then it goes thru a scrubbing process to obfuscate all sensitive data. A back up is taken from that and given to developers for testing.
Our T-Log back up is restored continuously on our warm standby. We try to take all precaution.
-Roy
Viewing 15 posts - 29,386 through 29,400 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply