SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extremely Large Backup Solutions


Extremely Large Backup Solutions

Author
Message
BU69
BU69
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1929 Visits: 483
Can anyone point me towards any article on the ideal way to backup a server with multiple databases which equals about 8Tb. I've just calculated, based on the current set up, that a full backup would take over a day which is not a good solution for a system that is expected to be 24/7.

It's set up as a 3 node AlwaysOn cluster, two nodes automatic failover and the 3rd in a different DC for DR. The databases are Sharepoint databases. Ideally we would still want the ability to do point in time restore incase of data corruption etc but not a huge requirement. The servers are VMs.

What does anyone else use in this situation, would it be still SQL ackups or would we be looking at a different solution such as something on the VM disk level?

Thanks
BU69
BU69
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1929 Visits: 483
I've managed to half the time the backups take by using striped backups, 10 different files, I have 5 mountpoints for the backup server so this figure seemed logical to evenly spread the backups on the disks (also 10 was faster than 5!).

I read somewhere that adding extra nics to the server may reduce the time further, does anyone have any exerience of this or have any other suggestions to reduce backup time for large amounts of data?
goher2000
goher2000
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2649 Visits: 1376
That depends on how you connect extra NIC, I had extra NIC with direct cable connection to backup server and the results were amazing.

I been using striped backups with 64 files, using the stored procedure below

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_fast_backup] Script Date: 07/10/2013 12:56:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_fast_backup] ( @dbname varchar(8000)='Database_Name',@fno INT=64, @run INT=0 ,@Path varchar(8000)=null)
AS
SET NOCOUNT ON
declare
--@path varchar(300),
@finame varchar(8000),
@dsk varchar(8000),
@ts VARCHAR(8000),
@cmd VARCHAR(8000)

begin
print ' '
PRINT '-- USEAGE : sp_fast_backup , <databasename>, <no of file>, <run=1/0> , path=<path>'
PRINT ' '

IF @fno IS NULL
set @fno = 64
IF @fno > 64
SET @fno = 64
IF @dbname IS NULL
set @dbname ='Database_Name'
IF @path IS NULL
set @path ='\\10.12.12.25\sql_backups\' + CAST(REPLACE(@@servername,'\','_') AS VARCHAR) +'\'
SET @finame = REPLACE(@dbname,' ','_')
SELECT @ts='_' +convert(varchar, GETDATE(), 112)+ '_'+replace(convert(varchar, GETDATE(), 108), ':', '') + '_FILENO_'
print 'Backup Database [' + @dbname + '] to '
SET @cmd = 'Backup Database [' + @dbname + '] to '
--print @fno
while 1 < @fno
BEGIN
--SELECT 'this is test'
SET @dsk= 'disk=' +CHAR(39) + @path + @finame + @ts + cast(@fno as varchar) + '.full' + CHAR(39) + ','
PRINT @dsk
SET @cmd = @cmd + @dsk
set @fno = @fno - 1
END
--PRINT 'out of loop'
SET @dsk= 'disk=' +CHAR(39) + @path + @finame + @ts + cast(@fno as varchar) + '.full' + CHAR(39)
SET @cmd = @cmd + @dsk + ' with copy_only,compression'
PRINT @dsk + ' with copy_only,compression'
IF @run=1
EXEC(@cmd)

end



Jeffrey Williams 3188
Jeffrey Williams 3188
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61707 Visits: 10554
Its not the extra NICs that will help - it is the number of HBA's you have and how they are configured. Ideally you have enough paths to the storage so you can dedicate database access (both read and write) through on set of paths - and dedicate another set of paths to the backup storage.

Since these are virtual machines - the path to the storage is controlled at the VM host level - and you are not going to be able to do something at that level unless these servers have a dedicated VM host and probably not even then...it would depend on how your VM environment is configured.

Striping backups is the next best alternative - as well as compressed backups. Any of the 3rd party utilities (Litespeed, RedGates SQL Backup, etc...) will use striping in the background for you and also performs compression at the same time. I utilize Litespeed and I am able to backup a 10TB database in about 2.5 hours - but that is on physical hardware with lots of CPUs and memory, and to an Enterprise SAN with lots of SSD drives

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Ivan R.
Ivan R.
Old Hand
Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)

Group: General Forum Members
Points: 306 Visits: 366
agree that simply increasing the number of stripes does already improve backup duration.

one would think it has to be on separate disks, but it does not necessarily have to be. there seems to be a sweet spot for the number of backup stripes on the same disk before it saturates it somehow.

we have a 20TB DB and we do 8 stripes and that has been acceptable since 2012. adding backup stripes will definitely shorten your backup duration.
Alexander Zhang
Alexander Zhang
SSChasing Mays
SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)

Group: General Forum Members
Points: 631 Visits: 252
If your DB is so big, I think you need to think about re-designing File/File group (or split it to multiple relatively small DBs). Then you can just backup one file/filegroup per day. If some huge tables are readonly, just move them to a seperated filegroup, backup it once.

GASQL.com - Focus on Database and Cloud
BU69
BU69
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1929 Visits: 483

It's not one database so file groups wouldn't work, it's a Sharepoint farm with each content DB at the best practice limit of 200Gb, I'd rather have everything backedup all at once. We changed some of the VM settings and brought the backup time down further, the settings changed were the queue length:

http://longwhiteclouds.com/2016/05/24/performance-issues-due-to-virtual-scsi-device-queue-depths/
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pvscsi\Parameters\Device]
"DriverParameter"="RequestRingPages=32,MaxQueueDepth=254"

Then on to of that the stiping to 4 disks with 8 files, the disks are on multiple LUNs, I got a 11 minute backup I had at the beginning of the week for 87Gb down to 3 minutes.

Now all I need to do is get the time for CHECKDB down, might post something seperate for that.


John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)

Group: General Forum Members
Points: 106146 Visits: 18604
BU69 - Friday, February 9, 2018 12:43 AM

I'd rather have everything backedup all at once.


I think you're probably beyond that point, to be honest. So long as all your databases are in Full recovery mode, the time of individual backups isn't going to affect your ability to recover.

Now all I need to do is get the time for CHECKDB down

I would restore your backups one at a time on a test server and do the checks there. Not only are you taking some load off the production server, but you're also testing that your backups are good.

John

Beatrix Kiddo
Beatrix Kiddo
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21370 Visits: 6196
John Mitchell-245523 - Friday, February 9, 2018 2:44 AM
BU69 - Friday, February 9, 2018 12:43 AM

I'd rather have everything backedup all at once.


I think you're probably beyond that point, to be honest. So long as all your databases are in Full recovery mode, the time of individual backups isn't going to affect your ability to recover.

Now all I need to do is get the time for CHECKDB down

I would restore your backups one at a time on a test server and do the checks there. Not only are you taking some load off the production server, but you're also testing that your backups are good.

John

Would this not fall foul of licensing requirements though if they're Production databases?

John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)

Group: General Forum Members
Points: 106146 Visits: 18604
Beatrix Kiddo - Friday, February 9, 2018 3:18 AM
John Mitchell-245523 - Friday, February 9, 2018 2:44 AM
BU69 - Friday, February 9, 2018 12:43 AM

I'd rather have everything backedup all at once.


I think you're probably beyond that point, to be honest. So long as all your databases are in Full recovery mode, the time of individual backups isn't going to affect your ability to recover.

Now all I need to do is get the time for CHECKDB down

I would restore your backups one at a time on a test server and do the checks there. Not only are you taking some load off the production server, but you're also testing that your backups are good.

John

Would this not fall foul of licensing requirements though if they're Production databases?

No, I don't think so. It's what you use them for, not what data they contain. As soon as you restore them on a separate server for integrity testing (only), they're no longer production databases.

John

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search