By: Allan Hirt on February 19, 2015 in Annoyed, AppAssure, Backups, Dell, SQL Server, Vendor
Earlier today I tweeted this: “Dear Third Party Vendors: Do us all a favor – if you don’t understand SQL Server, don’t make products for it.” (Link if you want to see the original tweet.) Some of the biggest complaints I hear from customers across the board all have to do with the third party applications and the vendors that make them. I previously touched on applications in an older blog post from 2009 entitled “Consolidation, Application Compatibility (or lack thereof), and SQL Server“. Support for a version of SQL Server is one problem, but blatantly having false information and trying to use outdated features is another.
Today I’m going to call out Dell‘s product named AppAssure, which is backup software. Let me say up front I like Dell overall – they make good server products and bought Quest who makes Lightspeed and Toad. Many of our customers use Dell for hardware, along with some of their storage products. They are one of the few certified Storage Spaces vendors. However, in my opinion, this AppAssure is all kinds of wrong when it comes to SQL Server. Looking at this video (script is below if you can’t watch the video) specifically talking about how AppAssure and SQL Server are a marriage made in backup heaven.
Dell says this:
There are actually three modes for back up. The two most important are simple and full. So let’s say, small organization, they don’t have a dedicated DBA. They just want to back up and be able to recover their SQL Server. Simple mode is the default mode. And it enables Microsoft VSS to snapshot and quiesce.
Full mode is what you would find in most larger shops that have dedicated DBAs. And what they’ll do is they will omit or not protect the database volumes. In this case, it would be F and G, F for the databases, G for the logs. They would not be protected because, in full mode, you don’t have VSS.
There is quite a bit to address from these short sentences.
Next up, Dell says:
The next thing they would also do is configure another volume. And they would use it for database dumps. They would be dumping the database and the transaction logs periodically throughout the day to that volume. And then AppAssure can come in and back that volume up.
The difference in your capabilities, in simple mode, AppAssure can back up as frequent as every five minutes. And that would be your RPO. In full mode, you get the ability to choose a literal point in time of an hour, a minute, and a second when you want to recover by either replaying the logs or rolling back the logs, the SQL logs.
These actually ties into the previous one and speak to a few things, not the least of which is your on-disk architecture for SQL Server and how backups should be done.
Let’s talk terminology. In the golden years of SQL Server when I was just a baby in this industry, backing up SQL Server could be done via the DUMP Transact-SQL command and backups were called dumps. They are still using that phrasing which is now outdated.
However, the most egregious offender in that video was the following:
The option highlighted is “Truncate log after successful attachability check (simple recovery model only)”. Is this 1998? I want Sphinx (aka SQL Server 7.0) back if so. Prior to SQL Server 2000, there was the ability to truncate the t-log on checkpoint. With the introduction of recovery models in SQL Server 2000, this is no longer possible. Simple != truncate the t-log. So I have no idea what Dell is doing here because there is no corresponding feature I’m aware of in SQL Server today that even corresponds to what they are claiming. Simple (in essence) will reuse and basically wrap around, but there’s no truncation going on in the way truncate log on checkpoint worked in SQL Server 4.21a, 6.0, 6.5, and 7.0. This is a meaningless checkbox to nowhere.
All I can say is caveat emptor if you want to use AppAssure for your SQL Server backups! But this is a cautionary tale of what you do not know – or understand – can hurt you. Applications vendors should have a passion for doing things right for SQL Server. Dell is partially there – they got the VDI bit right – but everything else is just off to me. I wouldn’t have a problem with it coming and backing up a drive as they talk about, but not as my main backup for SQL Server databases.
Tom LaRock (Blog | Twitter) posted this question after my initial tweet: “Why stop at vendors and products? I’d throw in blogs/videos, too …”. I agree with that statement, but it is hard to police. I remember years ago talking with folks at PASS Summit about how some sessions were giving advice based on their experience – which is technically valid – but in some cases, worst practices were being told as if they were best practices! I will admit to sometimes being wrong, but I always strive to correct if I am. Some people or companies are ignorant and don’t give a damn. If it’s on the internet it must be true, right? Wrong. Ignorance is not bliss and can lead people who through no fault of their own down a path which will result in trouble.
PS – I would be more than happy to correct and update any part of this blog post if I am wrong, Dell.
From my experience since 2001, Dell sucks.
Which backup software or hardware would you recommend for backing up SQL databases?