MSSQL Backups?

[Former Member]

What are people using for their MSSQL backups?

I'm curious.

Michael

[andreurban_blumberg]

Quest SQL LiteSpeed to disk, then disk to tape via Symantec (Veritas) NetBackup

[Former Member]

Interesting whitepaper from EMC on disk vs tape backups -

http://uk.emc.com/techlib/pdf/H2312_backup_to_disk_plan_opt_wp_ldv.pdf

Not EMC specific and applicable to any thinking on SAP/MS SQL backups.

Of course would still always recommend off-site tape backup in addition to any disk based solution.

[Rudi_Wiesmayr]

The link is dead.

Can anyone point to the whitepaper or send it?

Kind regards, Rudi

[Former Member]

We're currently running a 2.3TB SAP R/3 4.7 System on W2K3 and MS SQL 2000 32-bit on x86 hardware(soon to be migrated to AMD64 and SQL2005 but I'll post separately about that) and back it up as follows:

We use a product called Quest Litespeed to first dump the database to disk. To expand on NPC's point - the backup devices for the dump files are located on physically separate storage (EVA5000) than the database data and log files (EVA8000). The advantage of Litespeed (and products like it; there are several with the Quest version being no.1 with enterprise customers - M$ use it in-house on their own R/3 system - and accordingly the most expensive - but still cheap) is that they compress the data as it is written to the dump files. Whereas with native SQL backups we had to provide storage for the dump files equal to the size of the database, with Litespeed we're achieving about a 5:1 compression on our full and diff backups. This makes a big difference with a 2.3TB database - less disk, less tape. The backups are also much faster - from 12-13 hours native to 2-3 with Litespeed. The verify still takes as long (3hrs) which is a shame (not exactly sure why, any thoughts?) but overall a great improvement on our setup using native.

After the dumps to disk are complete they're taken off to tape via HP Data Protector.

Regarding the configuration we write 12 backup dump files in parallel and the target storage area is a single RAID5 LUN presented as a single drive.

We do a weekly full backup and a daily diff, with transaction log backups every 15 minutes. These are also log shipped to a standby database at our DR site. Incidentally, the performance of the transaction log restores onto the standby is very poor - it seems to restore lots of small files a lot slower than a few big ones - anyone have any experience with this?)

I'm not sure why M$ haven't built the compression functionality (it's just a fancy stored procedure) into the standard product. Maybe they will in future releases.

Final point regarding dump to disk - it can actually help speed up restore times as if you want to restore the most recent backup (you often do) as you already have it on disk and don't need to restore from tape. Obviously this wouldn't apply in a DR or complete loss of EVA/SAN scenario.

[Former Member]

Dear CJH,

You are correct - this tool is very effective. 2-3 hours to backup 2.3TB is very good.

You have isolated your backup storage from your database storage, which is the key point.

You may need to consider SNAP copy technology if you have a high batch requirement or want to run BW extracts at night and have a small backup window. SNAP technology lets you make a backup almost instantly and allows for almost instant recovery (depending on configuration).

I would check with your hardware vendor, but it would not surprise me if you find the license costs for litespeed and SNAP extensions were very similar.

We would be very interested to hear if there is any change in your backup duration after upgrading to SQL 2005

N.P.C

[Former Member]

Hi,

To clarify on current backup durations. I've checked and currently the full backup is taking 4 hours, not 2-3 as previously stated.

Diff backups vary between 7 mins and 30 mins.

I will update this post after SQL2005 upgrade.

Thanks,

CJH

[Former Member]

Excellent, that is exactly as we are doing

thanks,

Michael

[Former Member]

Dear Michael,

I would agree with a database dump (this means exporting the database to a file device) and then backing up this file only for small SAP systems or non-productive systems only.

A "Small" SQL database in August 2006 is less that 600-800GB. Medium would be around 1TB and large over 1.5TB

There are several reasons why "database dumps" are not desirable for large SQL Databases:

1. Frequently people will dump the database to a file to a network. Unless you have a dedicated backup LAN this will impact the performance of any application servers sharing the same LAN. This is true even for those using 1000Mb/s LAN. Networks are sometimes unreliable for long continuous write operations and a small interruption will cause the entire backup to fail.

2. Sometimes people will backup the database onto a local disk or SAN attached disk. This is very risky as this is "backing up a system onto itself". Some would argue that if you backup your DB onto a drive on the same SAN holding your SQL datafiles that this is not a backup. What happens if the SAN fails before the file has been transferred to tape?

3. In event of a recovery from tape, the backup must first be recovered from tape onto disk, then from disk into the database. This at least doubles the time to restore the database.

For large databases or for customers who require high availability “three nines” or “four nines” (99.9% or 99.99%) I would recommend at least using a LAN free backup agent. Most backup vendors offer these. Depending on the SAN you are using you will be able to use the high speed SAN Fabric to transfer the data directly to tape.

An even better option is using SNAP or FLASH copy options. This technology uses functionality of the SAN and the database to bring the DB into a consistent state for several seconds and then copy the database at a SAN level. With SQL this is called the VDI interface, though most DB vendors offer the same functionality. Using this technology a 1TB database can be “backed up” in 1-2 minutes.

Hope this helps – today a “database dump” is not the preferred method for backing up critical data. The technology you use has to be driven by your availability requirements, the RPO and RTO and budget (of course).

[Former Member]

Veritas, hands down.

[BenCurry]

The best thing to do is to run a DB dump using SQL Enterprise Manager and then use a backup tool like Veritas Backup Exec to schedule a job to backup the dump to tape.