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


Backup questions for 612GB database!


Backup questions for 612GB database!

Author
Message
Hyabusact
Hyabusact
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 121
We have a database that is over 612Gb and growing. Our backup is being done on tape using Backup Exec 2012. This process starts at 10PM and runs until 6AM every day. We do an incremental backup every 15 minutes so that we can restore up to any 15 minute windows if necessary. The nightly backup is a full backup with transaction logs so that we release the space back to the log file.
My question is this: What is the best way to perform this backup so that we can perform in in the least amount of time possible and yet make sure to keep the transaction logs clean as well. We were thinking of setting up a load balancing type of configuration but will this help with our backup jobs? One option is to convert the server into a virtual server (VMware) where more spindles are working with the database.
Any suggestions on how I can get the backups done more efficiently? I should mention that during the backup process its almost impossible to use the application that writes to the database because we get time out errors.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87733 Visits: 45272
Try backups to disk, later copied to tape if/as necessary. 620GB isn't huge, backups of that size DB shouldn't take hours.

If you're having timeouts, examine the latencies and throughput of the data drive. Could be you're swamping it with the reads that the backup does. May need more of an IO subsystem than you have.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


M-Hol
M-Hol
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 60
convert your server to Virtual Machine... is the best idea.
M-Hol
M-Hol
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 60
i'm using Snapshots with VMWare and working ok ...
Hyabusact
Hyabusact
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 121
We started out on VM but the performance was horrible. I think we have a better environment now but I am still a bit scared to move it back. As a backup point of view I can see VM being a better solution but what about database access and latency? The consulting firm is telling us that we can run with minimal memory and increased I/O but I have been burned by this before and a bit afraid to go back.
M-Hol
M-Hol
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 60
For Database (in Virtual Server) requires good infrastructure, for example NetApp, etc..
Andrew G
Andrew G
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2487 Visits: 2254
I agree with Gail, you should be backing up to a separate disk or raid group/set of spindles, disable SQL backup compression, then use Backup Exec 2012 to do data deduplication and store it on tape. I'm assuming the tape backup is the performance bottleneck.

You should first test the theoretical max backup performance by writing to the NUL device.
Adjust the BUFFERCOUNT and MAXTRANSFERSIZE (1MB) and BLOCKSIZE.


-- Try different MAXTRANSFERSIZE
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 25, MAXTRANSFERSIZE = 524288
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 25, MAXTRANSFERSIZE = 1048576
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 25, MAXTRANSFERSIZE = 2097152
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 25, MAXTRANSFERSIZE = 4194304

--Try different BUFFERCOUNT with best MAXTRANSFERSIZE
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 10, MAXTRANSFERSIZE = 2097152
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 40, MAXTRANSFERSIZE = 2097152
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 100, MAXTRANSFERSIZE = 2097152

--Try different BLOCKSIZE
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 100, MAXTRANSFERSIZE = 2097152, BLOCKSIZE = 1024
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 100, MAXTRANSFERSIZE = 2097152, BLOCKSIZE = 16384
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 100, MAXTRANSFERSIZE = 2097152, BLOCKSIZE = 65536




Once you find the max possible speed, backup to mulitple files on a raid or SSD.
Good example here:
http://henkvandervalk.com/how-to-increase-sql-database-full-backup-speed-using-compression-and-solid-state-disks

Im backing up a 250GB database in around 12 minutes. Max backup speed was 340MB/sec with Raid5 disks using NUL device and 280MB/sec with 8 backup files.
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20048 Visits: 17244
support-555807 (4/30/2014)
We have a database that is over 612Gb and growing. Our backup is being done on tape using Backup Exec 2012. This process starts at 10PM and runs until 6AM every day.

The backup direct to tape will be the bottleneck since you're constantly waiting for the tape device which will be inherently slower than a hard disk drive. As Gail has advised, backup to disk first then let BackupExec pickup the SQL backup files to tape.
Backup compression, whether native or 3rd party, will help. There'll be a CPU hit but you will be writing less data to disk since the data is compressed.
How big is the final backup file for the database?


support-555807 (4/30/2014)
One option is to convert the server into a virtual server (VMware) where more spindles are working with the database.

This is not a VMware specific option. You can get more spindles purely by sweet talking your storage admin to provide the appropriate disk devices.
Virtualising a server can have anywhere between a 10-15% performance overhead. Virtualisation is a consolidation exercise, you can get good performance if it's done correctly but even then there is the Hypervisor overhead.


support-555807 (4/30/2014)
We started out on VM but the performance was horrible.

My point exactly!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87733 Visits: 45272
M-Hol (4/30/2014)
convert your server to Virtual Machine... is the best idea.


I disagree. There's nothing magical about VMs that makes performance problems disappear, all too often it's the opposite.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


jasona.work
jasona.work
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4083 Visits: 11749
support-555807 (4/30/2014)
We do an incremental backup every 15 minutes so that we can restore up to any 15 minute windows if necessary. The nightly backup is a full backup with transaction logs so that we release the space back to the log file.
My question is this: What is the best way to perform this backup so that we can perform in in the least amount of time possible and yet make sure to keep the transaction logs clean as well.


One thing in your question jumped out at me, the bold bit above...

If you're looking to keep the transaction log size down, I'd start with Gails suggestion, and switch the incrementals to Log backups. This will keep your log file size down, allow point-in-time recovery, and TLog backups tend to be quick.
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