Using third party tools to backup

Question

Is there any advantages to use Third Party Tools to make Backups ?

I'm not referring one in particular but I want to know if someone using them to DB greater than 200 GB. Servers are mostly MSSQL 2008R2, Standard and Enterprise Editions.

Answer 1

This is just my input, but I have supported most of the major players in the 3rd party backup vendors. I have supported clients that utilize Idera, RedGate, Dell LiteSpeed, NetBackup (which does not really have compression but is 3rd party), and a few off-the-wall vendors that I can't remember anymore.

The improved compression you may or may not get with 3rd party products have a trade-off....CPU workload. I have seen Dell's (LiteSpeed) product run in SQL Server 2014 environment against a database that is right at 500GB, and using high compression kills the CPU. The result was only a backup file of around 64GB. It ended up causing SQL Server to stop providing resources to the backups since Dell's product uses the VDI API. The compression level had to be dropped down from 7 to about 3, this brought the backup size down to about 82GB. We took a native backup with compression enabled of same database, and it was right at 84GB in size. So is the trade off and cost worth it? test, test, test

I seem to recall a client I had a few years ago was using Idera's product and I had constant issues with the backups for their medical databases that were 1TB+ in size. I think some of it had to do with the disk subsystem the backups were being written to, which again is why you need to test. Overall Idera's GUI interface is not inviting to me, and could use great improvement. I would chose RedGate or Dell over Idera any day of the week.

You also have to remember that most 3rd party backup products are going to use the VDI API to SQL Server. This in and of itself can cause problems as well, frequent to often "BackupVirtualDevice" or "BackupIORequest" errors showing up in the error log. I have some of these be false positives (backup actually did occur) and some where legit failures. Make sure to read through the vendor support article or forums. RedGate and Idera have very good support systems in place and are very good about getting back with help. Dell I have not had to make any support calls with their product so far.

The one last thing I will bring up is the format of the backup taken by the 3rd party product. If you get into a DR situation are you going to have a standby server licensed with the product as well? I believe Dell allows you to install LiteSpeed without a license to do a restore, but it has to be licensed to do any backups. I think most of the vendors also include a utility to convert a vendor specific database to a SQL Server native backup file, in case of emergency.

Answer 2

Is there any advantages to use Third Party Tools to make Backups ?

Definitely, there are depending upon the needs and requirement on the databases hosted on those SQL server instances. Some benefits that i have seen is backup encryption and the customization in terms of what levels of compression rate you want the backups to be compressed.

Also advantage of the third-party tools will be the ability to stripe the backups into a single-compressed file. Striping this way can reduce the time it takes to actually perform the backup.

I have been using Idera SQL safe for the couple of the databases hosted on env above 500 GB and we are very happy with results as not only it saves time for compressed backups but also, has an feature of instant restore.

So it depends upon you're need entirely , how to go from here.

Also, you can test the Native backup compression feature from SQL server itself in case backup compression is the only need.

There are various free trial version tools available, you can go ahead and test them accordingly and see what suits the requirement.

Answer 3

The only advantage that I see for using 3rd party backup (We use redgate's sql backup pro) is

• Very good compression - you can assign threads when performing backups.
• Password protection - you can encrypt your backup files with a $tr0ngP@ssw0rd!

From SQL Server 2014, there are good amount of enhancements for native backups.

Note: There is only a slight difference in compression between Redgate and SQL Server backup taken with COMPRESSION.

For larger database backups, you can play with MAXTRANSFERSIZE and BUFFERCOUNT and enable Instant file initialization.

Answer 4

The benefits of a third party backup tool will vary based on your individual needs. Generally speaking, the core benefits over built-in SQL backup features would include:

1. No requirement for server file system access; third party tools can be run on your local machine, a server, etc. and can store your backups wherever you choose. This is extremely relevant when using cloud-based SQL Server (like Amazon RDS and Azure) since they typically use proprietary backup or snapshot services.
2. Break VPN barriers; third party tools (like SQribe) are affordable and can also backup and restore on-demand in separate actions, so you can backup over VPN "A" and then later restore to a server on VPN "B", using Zip compression, and more. I am the lead developer for this product, FYI.
3. Extended feature sets; larger and more expensive toolsets (like Red-Gate) provide options that aren't available when using the built-in backup feature, like updating a destination in-place without dropping and recreating the objects.
4. Full control over time and location; as with any piece of software, third party tools give you the flexibility to schedule jobs to your liking, and store the backups wherever you like, including on cloud storage. Many of them can be used in batch/PowerShell scripts via CLI helpers.
5. Versioning; tools that generate T-SQL script files as their backup data allow you to include your database schema and/or data to source control alongside your application code (like on github). This makes rolling back and cherry-picking SQL schema or data changes possible.

Answer 5

Database corruption is one of the nightmares in the life of every SQL Server user. There is no perfect way to keep your data safe and sound, but the best thing you can do to protect your database is making scheduled backups.

Of course, if you administer database greater than 200GB to make backups by using T-SQL command is not very convenient. For such cases, it is better to use third party tools. I have some experience with using third party tools and I'm really happy that nowadays we have such software that make the life of DBA easier.

Today market is full of such third party tools, you can find different software with different price and features, for example, Idera, RedGate, and Quest LiteSpeed.

Here I want to add some benefits of using third party tools:

• You can schedule database backups
• You can send backups to FTP, local or network folder, Dropbox, Amazon S3, Google Drive,....
• You can backup different files
• You can receive email confirmation
• You can protect your backup by encryption

Anyway, third party tools for making a backup is really useful. They will help you to minimize the risk of losing data.