LinkedInTwitterRSS

Blog

Application Vendors and SQL Server – A Cautionary Tale

Posted by on February 19, 2015 in annoyed, AppAssure, backups, Dell, SQL Server, vendor | 0 comments

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:

Truncating the log ... or not

Truncating the log … or not

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.

read more

CONTEST – Win a Seat In Allan’s Mission Critical SQL Server Class in Dallas

Posted by on January 5, 2015 in SQLHA University, SQLHAU, training | 0 comments

As we’ve done in the past in places like Boston and Chicago, we are giving away a seat for the January 26th Mission Critical SQL Server class in Dallas (some seats still available – 15% discount is applied at checkout). I really do believe in giving back to the community, and I think this is a perfect way of paying it forward.

Good luck!

The Rules
Send us an e-mail to sales at sqlha dot com with the subject Dallas Rocks as to why you think you deserve the seat and how it would impact you. I really would prefer the seat go to someone who truly needs this, such as learning new skills, switching jobs, etc. If you are part of a company that has the resources to send you, I’m not saying that you won’t be chosen, but I would prefer you have a need other than “we’re implementing HA next month and I wanted to brush up my skills.” In other words, you have to demonstrate that you truly would benefit from attending. This is a prize worth nearly $3,000, so treat your entry accordingly.

Don’t worry – we are not asking for a 1000 word essay, but don’t send a one line e-mail either. The grammar police won’t hold it against you if it’s not up to snuff; I would prefer heartfelt over perfect. Having said that, see The Fine Print. There is one exception.

Entries must be in by Wednesday, January 14, at 5PM Eastern. A winner will be chosen and notified on Friday, January 16.

Do not make or send a video; that will disqualify you.

The Fine Print

  1. One entry per person per class. Entry is only good for the class submitted. You must submit individual entries for different classes.
  2. Winners will not be eligible for a free seat in a future class and are ineligible for winning any other free SQLHA giveaway for 12 months after winning the seat in the class (excluding any giveaways in the class). If you cannot attend the class where you are chosen as a winner, you forfeit the prize.
  3. Do not enter if you cannot attend; it is not fair to those who can and a waste of everyone’s time.
  4. You (or your company) are responsible for all travel and expenses including, but not limited to: airfare, taxis, food, hotel, and so on. If you cannot meet this obligation for the class you are thinking of entering, please save it for one you can.
  5. Entries without the proper subject will be disqualified. Sorry.
  6. While we do not have delicate sensibilities, keep your entries clean.
  7. You are responsible for any taxes you may need to pay as a result of winning this contest.
  8. You must be eligible to win. For example, some who work in certain jobs or roles (such as some government agencies) would be ineligible. Know if you can before you enter. I apologize in advance if what you do rules you out, but we don’t want to waste anyone’s time or cause issues for you OR us.
  9. All entries must be in English.
  10. While we understand that writing is not everyone’s forte, anyone who uses text speak such as ur will be disqualified as well.
read more

Mission Critical SQL Server Class Sale Now Through December 5th

Posted by on November 23, 2014 in Mission Critical SQL Server, SQLHA University, SQLHAU, training | 0 comments

Happy Monday! Everyone likes a good deal, and boy do I have some for you.

From now until November 29th, both the Dallas (January 26 – 29, 2015) and Philadelphia (March 30 – April 2, 2015) Mission Critical SQL Server classes are going to be a whopping 40% off list price, bringing the class down to $1737.00 – over $1000 off!

If you miss that deal, starting November 30th until December 5, the classes will be 30% off.

After that, the Dallas discount goes back to $500 off until 12/31, and $500 off until 3/1/15 for the Philadelphia class.

All discounts are applied immediately – no codes needed.

If you were on the fence, now is the time to sign up – these prices will not be offered again. Consider this SQLHA’s Black Friday/Cyber Monday sales.

read more

SQL Server Coming to a Linux Distribution Near You?

Posted by on November 12, 2014 in .NET Framework, Linux, SQL Server | 1 comment

What’s next: open source SQL Server? Don’t laugh …

read more

SQLHA Has Given Birth

Posted by on October 30, 2014 in PASS Summit, SQLHA, SQLHA University, SQLHAU | 0 comments

SQLHA welcomes Fortified Data

read more

What’s New in Windows Server vNext for SQL Server: Cloud Witness

Posted by on October 27, 2014 in availability groups, failover clustering, FCI, PASS Summit, presenting, quorum, Uncategorized, Windows vNext | 0 comments

Allan introduces his new favorite feature so far in Windows Server vNext – cloud witness.

read more

CONTEST – Win a Seat In Next Week’s Mission Critical SQL Server Class in Boston

Posted by on October 14, 2014 in SQLHA University, SQLHAU, training | 0 comments

Would you like to attend Allan’s Mission Critical SQL Server class for free next week in Boston? Here’s how …

read more

Happy Anniversary (To Me), Training and Book Updates, and Other Thoughts

Posted by on August 12, 2014 in advice, PASS Summit, speaking, SQLbits, SQLHA | 1 comment

Can you believe it’s been seven years? I can’t.

read more