August 9, 2009 at 3:38 pm
We have been having issues building the new SQL2008 with imbedded TSQL to split the backup files into three separate parts < 15GB each. It seems anything over 15Gb will give us issues moving across over to the backup server.
We are going to have to start just pushing 10Gb chunks across the network at a time at the most. We need to start thinking of a way to efficiently do that for all systems WITH automatic retention in mind AND proper notifications. Our biggest database backup we now will have to now break up into 4 parts.
Anyone have any other ideas of what else could be our issues? (compressed area on 5.250, backup verification switch yes/no, timings, etc). We probably also need to be cognitive of the timing off all applications that backup to the DR area and also backbone backup timings.
(Current script which breaks backup into 3 three parts)
-- Backup
DECLARE @BakDate as datetime
DECLARE @Bak1 as nvarchar(250)
DECLARE @Bak2 as nvarchar(250)
DECLARE @Bak3 as nvarchar(250)
SET @BakDate=getdate()
SET @bak1 = '\\SERVER1\-- BAK files\KRONOS_1_'+LTRIM(STR(YEAR(@BakDate)))+RIGHT('0'+LTRIM(STR(MONTH(@BakDate))),2)+RIGHT('0'+LTRIM(STR(Day(@BakDate))),2)+'1230.BAK'
SET @bak2 = '\\SERVER1\-- BAK files\KRONOS_2_'+LTRIM(STR(YEAR(@BakDate)))+RIGHT('0'+LTRIM(STR(MONTH(@BakDate))),2)+RIGHT('0'+LTRIM(STR(Day(@BakDate))),2)+'1230.BAK'
SET @bak3 = '\\SERVER1\-- BAK files\KRONOS_3_'+LTRIM(STR(YEAR(@BakDate)))+RIGHT('0'+LTRIM(STR(MONTH(@BakDate))),2)+RIGHT('0'+LTRIM(STR(Day(@BakDate))),2)+'1230.BAK'
BACKUP DATABASE [KRONOS] to
DISK = @bak1,
DISK = @bak2,
DISK = @bak3
WITH INIT, NOUNLOAD, Name='KRONOS Backup', Noskip, noformat
go
Thanks for pointing us in the right direction!
Tony
Things will work out. Get back up, change some parameters and recode.
August 9, 2009 at 4:16 pm
I am not real sure I understand your problem - or in fact, what your question really is. I can, however, tell you that I currently backup a 450GB database across the network to our backup share using Litespeed with no problems.
We also backup the 800GB data warehouse across the network using Litespeed with no problems. As well as the other 5TB of backups we have.
It mostly comes down to how good your network is. With a good fabric, 10GB switches - Netapp NAS iSCSI storage there is quite a bit you can do.
The 450GB database using Litespeed compression - compresses down to less than 70GB and backs up in under 1 hour. The 800GB data warehouse compresses down to a couple hundred GB's and takes 4.5 hours mostly due to the limitations of the machine running our DW.
For your problems, I would definitely be looking at how the network is setup and configured. I would also be looking at using a third-party backup solution like Hyperbac, Litespeed, Redgate's SQL Backup, etc... Any one of these tools will reduce the impact on your network and can only help.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 9, 2009 at 5:33 pm
I thought I had detailed the situation very clearly. However, since the problem is going around in my head and not in others, what I am thinking may not have been conveyed well in my post. 😀
The problem:
Backups files are not moving across the network as they are getting bigger. We currently are moving the backup in three parts, but will need to break the backup into four chunks 10 Gb. If the current chunks get bigger than 15 Gb, they are not moving across the network.
The Question:
Is there a better way to backup huge backups across the network than the current method? I've included the backup script currently being used.
Some thoughts:
1) May compress the drive?
2) backup verification switch yes/no
3) I am also looking at the network traffic at the time we are trying to move the backups.
Now having said that, any thought or ideas from any of those that have experience backup hugh backups across the network? I am going to look into the tool suggested also.
Tony
Things will work out. Get back up, change some parameters and recode.
August 9, 2009 at 7:19 pm
You are going to have to tell us what problem you are actually having with backing up across the network. How have you determined that backing up 10-15GB is okay, but larger is not okay?
I would be curious to find out why backing up 60GB across the network in 4 separate threads is okay and works, but backing up 60GB using a single thread is not. I would think the network latency on 4 threads would cause more problems than a single thread.
The ideal solution would be to backup locally and then use robocopy, xcopy or some other copy program to copy the file up to the network share. That would avoid any issues you would have with the backup failing because of an issue on the network.
What I tried to show you was that striping your backups because of some limitation on the network is not going to solve any problems. If you are striping to get your backups completed in your maintenance window, that is another thing altogether. But, you are striping because you can't move more that 15GB across the network - which does not make any sense at all.
What happens if you backup locally and copy the 60GB backup file across?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 10, 2009 at 7:53 am
Jeffrey,
One of our policies, is that we are supposed to keep the servers "vanilla". So we can't add RoboCopy or some other copy program.
Xcopy is a cmd shell and we are in a constant battle with the sys admins over the "security risk". They sometimes disable it.
I appreciate the thoughts. Anything else you can think of? Is using the backup script that I posted the only other solution that comes to mind?
Here is the error message from last night's backup failure:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 18204
Date: 8/10/2009
Time: 6:00:03 AM
User: SQLService
Computer: MYSERVER
Description:
BackupDiskFile::OpenMedia: Backup device 'MYSERVER (KRONOS Backups)\-- BAK files\KRONOS_2_200908100100.BAK' failed to open. Operating system error 2(The system cannot find the file specified.).
(NOTE: Parts 1, 2, and 3 all failed.)
Thanks again for any ideas.
Tony
Things will work out. Get back up, change some parameters and recode.
August 10, 2009 at 9:11 am
I really think that your network has issues ...
We toss around Terrabytes of data fairly regularly.
Our network throughput in copy these 200-500 Gb backup files ranges anywhere from 375 Mb/Minute to 675 Mb/Minute.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 10, 2009 at 9:14 am
Also ...
Our database backup throughput to local or SAN disk is about 1 Gb/Minute.
Our network database backups average about 500 Mb/Minute.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 10, 2009 at 9:55 am
Thanks for that idea.
I am going to test our network throughput and find out the speed of network/backup.
Thanks.
Tony
Things will work out. Get back up, change some parameters and recode.
August 10, 2009 at 9:59 am
Well, there's the real problem with striping backups across the network. If one fails, the whole thing is no good anyways.
If this was my system I would be taking this up with management. Either they need to come up with the money to add local storage for backups and provide a way to copy those backups up to the shared storage, or they need to come up with the money to improve the network.
If they do neither - then you do not have a valid backup methodology. You cannot guarantee your backups are reliable and in the event of a disaster you are not going to be able to recover the system.
If you have the space to backup locally - and then copy up to the share, and all that is preventing you from doing so is policy - I would take that straight to management and let them know the risk that is causing to the organization.
I think you get the gist of where I am going - that is, you need to get management involved to solve the problems instead of trying to solve the problem programmatically. These types of issues cannot be resolved or worked around. Not without increasing the risk to the organization.
You need to make sure management understands that the current situation will cause data loss. It is not a matter of if - but really when it is going to happen. And if losing that data is going to cost the organization a lot of money, it will be well worth it to spend the money up front to prevent the data loss in the first place.
As others have noted - backing up across the network is feasible if you have a good network in place. Like I said before, we backup on a nightly basis more than 100 SQL Server across the network. That is a total of more than 4TB of data each night, with an additional 1TB several times a week. It is possible without resorting to striping the backups.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 10, 2009 at 10:22 am
Another thing to consider is that the file share server you are backing up to might not be able to handle the IO load.
I have seen cases where the backup server just could not handle the load of multiple backups running at the same time, especially if tape backups are running against the same share at the same time.
August 10, 2009 at 1:55 pm
Hello everyone,
Thanks for the great feedback.
I had a talk with my manager and he let me know that I have to implement some clever scripting. The politics with the sys admin is something he didn't even want to get into.
To test the throughput and checking the i/o of the backup server is in the realm of the sys admins and not the DBAs.
So that is that. However, if I am stuck with the scripting of backup "chunks" that presents another problem which I will start another topic on.
Again, thanks for the feedback.
Things will work out. Get back up, change some parameters and recode.
August 10, 2009 at 2:14 pm
Political problems usually do not have technical solutions, so it sounds like your manager is just postponing a fight that is certain to happen eventually.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply