November 1, 2010 at 8:28 am
Ok... this is rather unusual. Perhaps in part this is just not my understanding enough about internals [yet]? This might also be a non-issue?
ServerA: Windows 2008 Enterprise, 32-bit, 32-Gig, dual Xeon core
SQL Server 2005 Standard
Backing up (Server A) over local LAN (gigabit) to a Windows 2008 Standard server, 64-bit (Server B).
I see BACKUPIO waits on the backup process, but I suspect these may be false readings?
1) When looking at the backup file size, it was static at 257 GB.
2) Looking at the actual server (Server B) being backed up to (11.3 TB sub-system), it also had the same 257 GB size.
3) Select PROPERTIES on the file, the file size suddenly jumped to 534 GB (much more like I suspected). However, that number is now static.
Could there be anything in the new TCP stack that is putting more emphasis on the receive window auto-tuning or the larger buffer size that is causing SQL Server 2005 and Explorer to think there is something wrong or to not receive timely updates?
Ultimately, I would like to see about improving the backup speed, which for this particular database is taking 50 hours/week. At the moment, purchasing other software is out of the question, but we are considering moving the disk sub-system from the existing server to this server to see if that would improve the processing speed (initial testing on other databases show about a 10-20% improvement in processing time).
Some other info:
Data file size: 546 GB
Log file size: 709 GB
Database grows about 70-90 GB/week with updates running every night (5 hour process).
In case someone asks, the backup process is a scheduled (Agent) task running a stored procedure. I wrote this (originally as a cursor-based process) back in 2007 when I started as an "accidental" DBA (thus the name DB_Newbie2007) and have been using it ever since:
CREATE procedure [dbo].[usp_DBBackup] (@Debug int = 0) -- 0 run, 1 debug aka print
as
BEGIN
--DECLARE @Debug int;
--SET @Debug = 1;
SET NOCOUNT ON;
DECLARE @DBNameVar NVARCHAR(128),
@Statement NVARCHAR(2000),
@Cntr int,
@MaxRow int,
@drive nvarchar(100);
SET @drive = '\\ServerB\Backups\ServerA\';
CREATE TABLE #AllDatabases (RowNo int identity(1,1) not null, DBName varchar(100) NOT NULL);
INSERT INTO #AllDatabases
SELECT name FROM sys.databases WHERE database_id > 6
and name not like '%Temp'
and name not in ('Adventureworks', 'Northwind') ---- this is a generic script, these to not exist on Server A
SELECT @MaxRow = MAX(RowNo) from #AllDatabases;
SET @Cntr = '1';
WHILE (@Cntr < @MaxRow)
BEGIN
SELECT @DBNameVar = DBName from #AllDatabases WHERE RowNo = @Cntr;
SET @Statement = N'BACKUP DATABASE [' + @DBNameVar + N'] TO DISK = '''+@Drive
+ @DBNameVar + N'_backup_' + CONVERT(varchar(23),getdate(), 112) + '.bak'' WITH RETAINDAYS = 14, INIT, NAME = N''' + @DBNameVar + N'_backup_'
+ CONVERT(varchar(23),getdate(), 112) + '''';
IF (@Debug = 1) Print @Statement
IF (@Debug = 0) EXEC sp_executesql @Statement
IF (@Debug = 1) PRINT CHAR(13)
SET @Cntr = @Cntr + 1;
END
TRUNCATE TABLE #AllDatabases
DROP TABLE #AllDatabases
SET NOCOUNT OFF;
END;
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
November 1, 2010 at 8:41 am
The backup isn't designed to run across a network. The fact that you have some backup waits seems reasonable to me. The network should be slower than local storage.
As far as the file sizes, I haven't ever really tracked the sizes while things are in progress. I don't know if this is common or if there might be something that can be tuned. All guidance I've always seen is backup locally to avoid any issues.
November 8, 2010 at 6:18 am
Just as a follow-up, the backups on this server were taking approximately 89 hours/week to complete over the network. Last week we moved the storage array from one server (b) to this server (a). Backups last night took 6 hours!
Yes, as Steve indicated, backing up over the network is not a good idea!!!!
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
November 9, 2010 at 8:53 am
Thanks for the update, and glad it's working. The network can have big issues.
Note that you can mitigate this if you must do it. You can add a 2nd network card to each server, setup a private VLAN and have ONLY backup traffic going across this network. I've done that in the past and it has worked (though I did backup locally because I'm conservative).
I also heard that a well setup SAN can fly. Paul Randal noted that one of his clients does 2TB (backup size) in 36 minutes on one server. Wow.
November 11, 2010 at 12:00 pm
Steve Jones - SSC Editor (11/9/2010)
Thanks for the update, and glad it's working. The network can have big issues.Note that you can mitigate this if you must do it. You can add a 2nd network card to each server, setup a private VLAN and have ONLY backup traffic going across this network. I've done that in the past and it has worked (though I did backup locally because I'm conservative).
I also heard that a well setup SAN can fly. Paul Randal noted that one of his clients does 2TB (backup size) in 36 minutes on one server. Wow.
2TB in 36 minutes?? WOW.
Following question: is it using SAN backup? or 3rd party tools?
November 11, 2010 at 12:16 pm
SAN. He tells this as an anecdote that a well tuned SAN can really fly.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply