Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server Backups Expand / Collapse
Author
Message
Posted Wednesday, November 14, 2012 8:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 6:45 AM
Points: 104, Visits: 389
This is more of a poll but I'd like to get others opinions. What is the recommend and preferred methods of SQL Server backups considering all technologies:
- Native SQL Server backup to tape
- Native SQL Server backup to disk
- SAN backups (specifically for my purposes - NetApp Snapshots / FlexClones).

Go!
Post #1384648
Posted Wednesday, November 14, 2012 8:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 5,132, Visits: 4,922
We do backups to local disk, then the backups are backed up via the SAN backup, they are also shipped to the virtual tape array via NetBackup and they are also replicated to the DR server, which is also backed up by the DR SAN backup and is also backed up to the DR virtual tape array.

So we have a multiple point of failure system where we can get the backup back.

Data recoverability is a big issue for us, as every hour we have an outage costs the company millions of pounds in productivity and lost revenue, so the quicker we can get things back should an issue or a failure at any point of the infrastructure should happen




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1384666
Posted Wednesday, November 14, 2012 8:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
It depends.

On DB size, backup windows, technology available, restore time window, etc.

I wouldn't backup to tape though. Not directly at least.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1384671
Posted Wednesday, November 14, 2012 9:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 6:45 AM
Points: 104, Visits: 389
@Gila: I can provide you with my options but was looking for some general feedback first. Obviously I'd like to rule out options and not focus on them if they simply do not apply. But to elaborate here are additional details:

  • Just under 300 databases across 15 servers (with new servers on the horizon)

  • Each server contains a few to 20+ databases where the total size of all databases range from small to medium (few hundred MB up to just under 1 TB)

  • The largest databases are approximately 300ish GB. We're just now approaching the VLDB thresholds. My projections show by the end of 2015 we'll be over double our current size with our largest and most active databases.

  • Backup window, 3 hours for all - tops... I'd like to beat that by aiming for 1 hour

  • Options available native backups, tape backups, NetApp snapshots

  • Restore time, not defined but the expectation is ASAP... working on that one next



Let me also add our databases are currently versions 2005-2008. We also have SharePoint 2010 databases that will be folded into the mix so their backup is slightly different. In addition I want to try and get the backup strategies as consistent as possible across all servers / platforms.
Post #1384717
Posted Wednesday, November 14, 2012 9:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:30 PM
Points: 33,055, Visits: 15,167
Disk is my vote, perhaps striped to multiple physical locations for speed. Then copy over to tape/DR.

The SAN stuff can work, but you better check this carefully. Not all SAN systems are transactional aware, and you might end up with a backup/copy that can't start.

Either way, you still probably need some log backups, unless you plan on losing data between the backups/snaps.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1384736
Posted Monday, November 19, 2012 11:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 6:45 AM
Points: 104, Visits: 389
Thanks for your input everyone. You've helped me confirm my initial direction is valid - backup to disk and then allow our infrastructure group backup the files to tape.

Currently our databases are backuped up to tape directly and there are all sorts of issues. It currently takes 9+ hours to do a full backup of all our databases (just under 1 TB). And that's only production. My new proposal is going to go straight to disk plus include a few additional optimizations (to name a few):
- Use compression where necessary
- Use multiple backup files (up to 1 per CPU)
- Separate backup traffic from the network traffic

I see no reason why we cannot backup 1 TB of data to disk in 30 minutes tbh. I based most of this on a case study for fast and reliable backup and restore of VLDB over the network.
Post #1386528
Posted Monday, November 19, 2012 1:02 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
tafountain (11/19/2012)
Use multiple backup files (up to 1 per CPU)


I wouldn't necessarily recommend this.
If you're going to stripe your backups, stripe based on how many IO paths you have (if you have 2 backup drives that are separate physical drives or separate IO channels, then stripe to 2 files).
Backup is an IO-bound operation, not a CPU-bound, if you stripe to multiple files on the same drive you probably won't see much of a gain. Only add multiple backup destinations if you have multiple separate IO paths (so 32 cores, 32 destinations on 1 LUN isn't a good idea. 32 cores, 32 destinations across 16-32 LUNs or 32 cores, 16 destinations across 16 LUNs may work)

Separate backup traffic from the network traffic


Backing up to disk or backing up to a network location?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1386548
Posted Tuesday, November 20, 2012 1:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:49 PM
Points: 191, Visits: 891
Just to add my scenario, I have multiple SQL Servers (approx. 8), with a few databases on each, not big, largest db is 25 GB. (One Oracle DB is 115 GB.)

For SQL Server, I backup straight to disk which is on-server (preferrably the non-data disk, but can't always be helped). I wrote a .net program to take a list of directories, and compare the first directory with the throw-over directory. Any files not already in the throw-over area, copy them. This runs every hour. The throw-over goes to a Data Domain dedupe machine 1/2 mile away. You could use a NAS if needed, though it won't dedupe, but is a LOT cheaper.

I try to keep 2 days' worth of full and tranlog backups on server, with (by policy) 2 weeks' worth on the Data Domain. (I also wrote a program to go in each directory and delete files over X days old, using either Windows create date or SQL Server file timestamp date, can set different length of time for each directory.) This keeps any needed restores on disk for 2 days. Any needed from longer than that can just be copied back over to the server from the Data Domain.


Post #1387120
Posted Tuesday, November 20, 2012 1:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 6,166, Visits: 13,309
GilaMonster (11/19/2012)
Only add multiple backup destinations if you have multiple separate IO paths (so 32 cores, 32 destinations on 1 LUN isn't a good idea. 32 cores, 32 destinations across 16-32 LUNs or 32 cores, 16 destinations across 16 LUNs may work)

also, the multiple drives should have the same I\O capability\performance


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1387129
Posted Tuesday, November 20, 2012 2:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
Perry Whittle (11/20/2012)
GilaMonster (11/19/2012)
Only add multiple backup destinations if you have multiple separate IO paths (so 32 cores, 32 destinations on 1 LUN isn't a good idea. 32 cores, 32 destinations across 16-32 LUNs or 32 cores, 16 destinations across 16 LUNs may work)

also, the multiple drives should have the same I\O capability\performance


That should fall into the category of 'common sense'.... not that common sense is all that common.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1387155
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse