Blog

Application Vendors and SQL Server – A Cautionary Tale

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.

1. I think Dell believes their customers are simple (pun intended) if they believe this. Any DBA or SQL Server professional worth a damn knows that the recovery model for all user databases is based on model, and that is Full. Simple is NOT the default recovery model for most databases, and it would be a conscious choice to make that change (unless model was altered).
2. The Simple = small businesses, Full = large companies is a crock of hooey. If you care about recoverability, use Full (or Bulk-logged, but few people do) which is always the recommended best practice for user and application databases. Many SQL Server features – like availability groups – require Full. Full allows you to recover to a granular point in time. Simple does not. You would only be as good as your latest full and/or differential backup. And you would think a backup vendor and, by extension, its product would know this! It’s SQL Server 101.
3. The Volume Shadow Copy service (VSS) in Windows that all hardware and software interfaces with to do backups does not require Simple; it is agnostic to SQL Server. VSS must talk to the SQL Server Virtual Device Interface (VDI) to ensure that a proper SQL Server backup for a database is generated. VDI is the only thing we as SQL Server folks should have to worry about here (and yes, AppAssure does use it; you can see the freeze/thaw of I/O in the SQL Server log) when using a third party backup utility that quiesces a drive. I have used other solutions (such as ones from EMC which were a combination of hardware and software) that had no issue with databases in Full.

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.

1. I am assuming based on my limited window into AppAssure at one client and this information presented that it is only capable of full and/or differential backups in SQL Server since a differential is the only granular recovery you will get with the Simple recovery model.
2. Simple recovery model makes sense if you don’t want to deal with the t-log because to do point in time recovery, you would a) need to be able to restore using WITH STANDBY or WITH NORECOVERY (I do not know if AppAssure can do that) b) be able to restore transaction logs c) quiesce in a way that you do the t-log and data at the same time to have a consistent snapshot of the database via VSS. You don’t need to be consistent if you’re ignoring the t-log.
3. There seems to be no way to just snapshot a single database, which would potentially impact how you lay databases out on disk. AppAssure looks like it just quiesces an entire drive at once. This is one reason why if you are going to use any third party utility (HW or SW) to back up SQL Server, you need to think about it before going into production and how it affects aspects of SQL Server like data and log file placement.
4. If you ‘re not going to deal with t-logs, AppAssure can make backups ’til the cows come home!
5. SQL Server Agent jobs can now go down to as frequent as 10 seconds – not that you would ever really do that in the real world for t-log backups. You get more granular if you want to in SQL Server by doing t-log backups if your recovery point objectives (RPOs) require it AND you can generate the backups fast enough.

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.

---