February 2, 2016 at 11:35 am
I'd like to get some opinions from my fellow SQL Server professionals on backup software. I'm getting a lot of flak from people on our system administration team because I use SQL Server to back up our databases. They insist that such backups are "outdated" or "antiquated", and they would rather I rely on software included with our storage solution for backups. I've explained that backups in SQL Server are not just for disaster recovery. We need them in order to truncate the transaction log and so forth.
However, I am an open-minded person and I am always willing to entertain new ways or doing things. So my questions to the community are these:
1) Are SQL Server backups outdated?
2) Are there better ways to backup database in the age of SANs and NAS?
3) What are the pros and cons of each approach?
February 2, 2016 at 12:03 pm
I'm not completely sure what you mean here. Are you doing backups with SQL Server as "backup database mydb to disk = 'xx.bak'?
I'm assuming you do this for transaction logs (and differentials) As well?
Those aren't antiquated, and are the most reliable, solid way of doing backups of databases. The full backup doesn't truncate the log. The log backup allows space to be reused.
Backup software from third parties may or may not work well. Most backup software isn't transactionally aware, and if it's getting the files if they're open, you don't get a consistent backup. If the backup software is making entries in the msdb backup tables, and you test restores in multiple situations (point in time, missing logs, multiple logs, etc) then you could switch, but I don't recommend it.
I recommend you back up with a SQL Server approved third party tool, like SQL Backup Pro[/url], or the native SQL Server backups (Backup database to disk) and do not use other backup software.
Disclosure: I work for Redgate Software.
February 2, 2016 at 1:12 pm
Steve Jones - SSC Editor (2/2/2016)
I'm not completely sure what you mean here. Are you doing backups with SQL Server as "backup database mydb to disk = 'xx.bak'?
Yes, that's what I meant.
What I've learned recently is that the SAN vendors like Nimble have implemented things like "snapshots" that are supposed to back up a database much faster than the traditional SQL Server backup. My primary concern is the same as yours: I didn't think the snapshots would be transactionally aware. Nimble's own documentation seems to indicate that they are not.
I'm asking the question because I work for a small software company and I wanted to see if perhaps I had missed some new developments in regard to backup and recovery. I had a conversation with our sys admins and they were insisting that the SAN software could take care of the database backups too. They honestly seemed surprised that I was backing up databases through SQL Server. So I was curious if others in the SQL Server community were using a different approach to backup and recovery.
February 2, 2016 at 1:28 pm
If the storage vendor can guarantee that the SAN snapshot is taken at the exact same time for all drives, *and* they can guarantee (together with the VM administrator) that on a restore the VM can continue from that exact moment of time as if the VM had been paused and is now resuming, then it *might* work. But I would still test it. Ensure that your SQL Server is running at peak capacity, make a SAN snapshot, then restore from it and see if it really works. Try this a few times in case this is a process that "sometimes" works.
If you do not get the guarantees, then don't even bother testing. Just a millisecond time difference between the snapshot of two files for the same database can already cause corruption when you try to bring the database back online after a restore.
Another thing to consider is that, based on my (limited) understanding of SAN snapshots, they are not actually a DR tool. My understanding is that they work similar to the database snapshot feature in SQL Server: after it is activated, every modification is recorded at one place with the original data being retained at the original place; reading the current version is done by checking both places to see if there is a new version and otherwise use the old version; reading the original data is done by only looking at the original version. But there is no extra copy made of all data, so this does not protect against disaster. It just creates an image of the state frozen in time.
Also, SAN snapshots cause overhead. My current customer uses Veeam to make backup of their VMs; this software works by first creating a SAN snapshot, then creating a backup of that snapshot (so that it can create a backup that is consistent in time, even if files are moved or changed while the backup runs), and finally removes the snapshot again. Some time ago a few backup failures had occurred, and the administrator had not noticed immediately that some of the snapshots were not removed; at one point we had a total of four snapshots - and that significantly impacted performance, since every read now had to be done in five locations.
(Since I did not get any guarantees that Veeam can restore the VMs such that the database is still intact, I still create traditional SQL Server backups. Apparently is it not possible to exclude the drives where data and log files are from the Veeam VM backup, so this means that the Veeam backup contains both my data and log files -probably worthless if we ever need to restore- and my SQL backup files -which I will use to restore the DB in such a case-. My storage admin is not happy with this, but that's not my problem. 😉 )
February 2, 2016 at 1:56 pm
This is very helpful. Thank you.
February 2, 2016 at 1:57 pm
We've experimented with DPM and Veeam. Both failed for various reasons.
SQL does SQL backups best. Let the 3rd party solution pick up the backup files from a replicated Lun or something, but don't let it near your databases.
February 4, 2016 at 6:29 am
Even robust third party tools like Symantec Backup Exec get it wrong. We used Symantec to do our SQL Backups at one point but found it would mark the database as backed up first, then continue to do the actual data backup in the background. If this then subsequently failed, the problem wouldn't be found until checked the next day by our backup admin. I have agent scripts which run every day to report if a backup has been missed, but as the database was marked as backed up these didn't fail. This caused a serious issue for us when a large database update was being performed. I checked msdb..backupset and could see a successful backup of the database had been taken the previous night, applied the update which subsequently failed, so went to get the backup from Symantec Backup exec, only to find then that the last 7 days worth of backups hadn't run successfully. Squeaky bum time!!! One weekend of work later and the database was manually corrected back to a good state!! It wasn't fun though!
I make sure all our backups use Redgate SQL Backup Pro now. Never had any issues with this and would highly recommend it. If I didn't have the company paying for these licenses, then I would use Ola Hallengren's scripts instead, or SQL Maintenance Plans. Ola's scripts are easier to set up quickly though on each instance.
February 4, 2016 at 6:35 am
Thanks to everyone who replied for your input. It helps to have a community like this as we battle the misunderstandings and misinformation around SQL Server.
February 4, 2016 at 7:15 am
Maddave (2/4/2016)
Even robust third party tools like Symantec Backup Exec get it wrong. We used Symantec to do our SQL Backups at one point but found it would mark the database as backed up first, then continue to do the actual data backup in the background. If this then subsequently failed, the problem wouldn't be found until checked the next day by our backup admin. I have agent scripts which run every day to report if a backup has been missed, but as the database was marked as backed up these didn't fail. This caused a serious issue for us when a large database update was being performed. I checked msdb..backupset and could see a successful backup of the database had been taken the previous night, applied the update which subsequently failed, so went to get the backup from Symantec Backup exec, only to find then that the last 7 days worth of backups hadn't run successfully.
Maddave I'm curious about this issue you saw. I would expect that an entry wouldn't be written to backupset until the backup actually completed? Or was SBE "forging" a date_completed entry, even when the backup hadn't finished yet?
Or, am I misunderstanding how this table gets used when backups are being run?
February 4, 2016 at 7:32 am
KJP (2/2/2016)
I'm getting a lot of flak from people on our system administration team because I use SQL Server to back up our databases. They insist that such backups are "outdated" or "antiquated", and they would rather I rely on software included with our storage solution for backups.
SQL Server backups are anything but antiquated, some of the backup solutions available merely pass the back end SQL required to perform a backup anyway. I wouldn't be relying on them to define a solution which you as the DBA will ultimately be responsible for.
If they set a backup solution and in a DR situation you cannot bring a database back it's you who'll get the flak not them!
KJP (2/2/2016)
So my questions to the community are these:1) Are SQL Server backups outdated?
No of course they're not
KJP (2/2/2016)
2) Are there better ways to backup database in the age of SANs and NAS?
You can certainly centralise this but it removes the DBA from the equation, the admins restoring a database from DPM or Netbackup need to understand the ramifications of choosing the restore point when restoring a database and generally in my experience they don't have a clue.
Manic Star (2/2/2016)
We've experimented with DPM
Probably the worst product I've ever had the misfortune to use
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 4, 2016 at 7:45 am
Used sql safe from Idera for years, proved to be fast and reliable
February 4, 2016 at 7:49 am
Maddave I'm curious about this issue you saw. I would expect that an entry wouldn't be written to backupset until the backup actually completed? Or was SBE "forging" a date_completed entry, even when the backup hadn't finished yet?
Or, am I misunderstanding how this table gets used when backups are being run?
I not sure what Backup Exec is doing or how the table is updated in SQL Server either. However, I tested the backup process using Symantec on a 300 GB database and when the job started, I could see the connection coming into SQL by the backup user. You would expect the backup job to take some time to complete on 300 GB database writing to a remote server, but when looking at the properties of the database a few seconds after the initial connection, the last backup date and time had been updated to the current time and this was also reflected in the msdb..backup_set table for all databases on the server too. So to SQL's eyes, the database had been backed up, near instantly. The SQL Server Log also reports the backup being taken successfully.
I've just tested this again whilst writing this on one of our Dev servers and can replicate this behaviour. The backup process starts and instantly, SQL Server is updated, but in the Backup Exec admin console I can see the job is still in a running state. I cancelled the job in Backup Exec, but SQL is still reporting in the logs that it is backed up fine. However, I couldn't restore the backup taken as it hadn't completed successfully.
I'm not sure if this is a configuration/operation issue on the Backup Exec server, but it looks fine to me. It's not good though and meant I couldn't rely on my own checks to make sure the databases had been backed up, hence going for Redgates tools.
February 4, 2016 at 9:49 am
Manic Star (2/2/2016)
We've experimented with DPMProbably the worst product I've ever had the misfortune to use
LOL, yep. Altho VEEAM was worse. Veeam made our failover cluster actually failover because its snapshot deletion process froze the VM just long enough that everything decided things had gone south on the primary node.
DPM: failed log backups, consistency checks that take FOREVER, restores that take 4 times longer than SQL. Failing backups because the drive needed to grow.. etc etc etc.
February 4, 2016 at 10:04 am
Manic Star (2/4/2016)
Manic Star (2/2/2016)
We've experimented with DPMProbably the worst product I've ever had the misfortune to use
LOL, yep. Altho VEEAM was worse. Veeam made our failover cluster actually failover because its snapshot deletion process froze the VM just long enough that everything decided things had gone south on the primary node.
DPM: failed log backups, consistency checks that take FOREVER, restores that take 4 times longer than SQL. Failing backups because the drive needed to grow.. etc etc etc.
For virtual clusters you may need to increase certain cluster health check timeout values so that when these processes occur they don't cause a failover
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 4, 2016 at 11:55 am
If backup exec writes the backup time before it's finished, that's an issue. Certainly you might not be able to rely on this for restores at that point.
I think Idera, Redgate, Quest, maybe a few others have been solid in using VSS backups that focus on SQL Server. Most of the others I've seen are unreliable.
In terms of SAN snapshots, as Hugo mentioned, these can work well and appear to be instantaneous, but from what I've seen, these work like Database Snapshots. A copy is made by moving blocks, and a read of the copy is either from a copied block, or the original block is it hasn't been copied. Blocks move as they change on the source, so it's a copy on write.
In most cases this works well. In a DR situation, it could be problematic, though I'd hope you keep the previous snapshot before taking another. However you are then limited to the time of the snapshot, unless you also have log backups from SQL Server.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply