I hate badmouthing a product on the internet because it’s permanent. It goes into search engines, people looking for “SQLsafe sucks” run across it, and it decreases sales. Here’s the thing, though: Idera SQLsafe v4.5 really does have some crippling products that limit its use and make it a bad choice for database server backups, and I’ll explain why.

First, a little about me and my qualifications to talk about SQL backups. I’ve been using SQLsafe v3.0 and v3.1 since I started at Southern Wine & Spirits in 2005. Before I first purchased it (SQLsafe, not Southern Wine, ha ha ho ho), I got demos of all of the competing SQL backup compression products out there and put them in our labs for a couple of weeks. I did speed benchmarks, CPU load tests, and compression tests. SQLsafe won out as the best balance of features versus price, and while I ran across a couple of really small bugs, they weren’t enough to discourage me from putting it into production. I relied on SQLsafe to back up a handful of servers from a ~200gb OLTP server to a 2-terabyte SAP BW data warehouse, and I was a happy camper.

At the Professional Association of SQL Server Summit 2007, I got a demo of their latest version (4.5) and walked away pretty impressed. They showed how I could quickly and easily restore single tables out of a backup, and how the product was flexible enough to restore to different databases and different tablenames. Sold. Plus, the central management service finally runs on 64-bit servers, and since I’m converting to an all-64-bit environment, I decided to give it a shot. I have a maintenance agreement on SQLsafe, so I downloaded the new version and installed one server for starters.

First SQLsafe Bug: Can’t Create Backup Policies

SQLsafe backup policies can cover all databases, only user databases, only system databases, or specific databases. I wanted to set up two policies — one for user databases, and one for system databases, because you can’t have one plan cover both full-recovery databases and simple-mode recovery databases. The system database policy worked fine, but the user database policy did not. It warned me:

“A log backup cannot be performed on database “master” while the recovery mode is SIMPLE.”

Uhhh, news flash to Idera, master is not a user database. I opened a support ticket with Idera (#00023903) and they passed the bug on to developers.

Idera said that until the bug gets fixed, I have to create backup policies for specific databases – meaning, choose the databases manually, and check off each of them.

Side Effect #1: New Databases Aren’t Backed Up

Since I have to pick databases manually, any new databases aren’t automatically added to the SQLsafe policies.

For me, this was a showstopper, and the end of my production evaluation. I have a hard and fast rule: nothing goes into production that requires more of my time instead of saving me time. New things have to make my life easier, not harder. With SQLsafe 4.5, I would have to do a daily check of all databases and manually add them to the SQLsafe policies. Sure, I could roll my own integration code, but I’m also dead-set against that as well.

Side Effect #2: Manual Policies Can’t Cover All User Databases

SQLsafe policies are implemented via SQL Agent jobs, and a single job step can’t exceed 3200 characters. Because the SQLsafe job code is wordy and lengthy, they quickly violate the 3200 character limit even with less than a dozen databases.

As a result, I had to make multiple policies, grouping the databases together. Thankfully, I did the eval on a server with under a dozen databases, so I was able to make do with just two policies, but on my real production servers, some of them have dozens and dozens of databases. I’d have to manually manage handfuls of policies, and I’d have to stay on top of which databases were in which policies.

And it gets worse: each policy has its own independent schedule, so I completely lost control of my backup window. I didn’t have a centralized way of knowing that my full backups took X minutes and my t-logs took X minutes, because I was ending up with multiple policies.

Second Bug: Object-Level Restore Doesn’t Work with Identity Numeric Fields

SQLsafe v4.5 has a new object-level restore option. During backups, it scripts out objects in order to enable SQLsafe to restore individual objects later.

If the database has identity numeric fields, SQLsafe does not back up the database. (There may be other database design issues that also cause SQLsafe OLR to fail, but that’s the only one that caused me problems.)

An enterprise-ready backup package would say, “Okay, I can’t back this up with object-level restores, but I’m going to back it up the old-fashioned way and just log an error.” Instead, SQLsafe v4.5 just skips the database altogether and logs an error.

Idera’s support said the workaround was not to enable OLR on those databases. Unfortunately, OLR has to be turned on or off at backup time, not at restore time, so database admins have to have separate backup policies for databases that do support OLR, versus backup policies for those that don’t.

This is especially problematic because it means even if they fixed the first bug (allowing a single policy for all user databases), we still couldn’t have one backup policy, because some of our databases have identity numeric fields. I still want the ability to do OLR in the databases that can support it, so that means…

Side Effect #3: Backup Policies Become a Mess

My backup policies on this first server now look like this:

• System databases
• User databases that don’t support OLR
• User databases that support OLR, part 1
• User databases that support OLR, part 2 (because part 1 was more than 3200 characters)

This is not an improvement.

Third Bug: SQLsafe Agent Fails to Install, But Says It Succeeded

Pretend for a second that this product is actually deployable. I said to myself, let’s keep giving this another shot, and let’s deploy it to a disaster recovery server and test it there too. In the management console, I right-clicked on the target server and installed the agent. The console reported a successful install.

Or did it? The console’s server list reported that the server still had no agent installed. Eh? I refreshed the view, restarted the SQLsafe services on the target server, and finally rebooted the target server. I tried installing it again, and got another success message, but still no new agent. Only after reviewing the application logs on the target server did I discover that the install was failing with a message that I had to uninstall the previous version of the agent.

I reported this to Idera support, who responded that yes, the install wasn’t actually succeeding despite what the management console said. I had to uninstall the SQLsafe agent manually on the target server, and only then could I push out the new agent.

I would have understood if the management console had reported an installation failure, but quite the opposite, it reported a successful install. It didn’t report an error, didn’t say there was a problem, it reported success.

That bug is flat-out unforgivable unless I’m the only guy who ever bought Idera SQLsafe prior to v4.5. (I’m beginning to wonder if that’s maybe the case.)

Bottom Line: Nowhere Near Production Quality

I could excuse a lot of these bugs in early alpha or beta code, but these types of bugs are simply inconceivable in production code that’s responsible for backing up mission-critical databases.

Idera SQLsafe v4.5 is not ready for production, and shouldn’t be purchased by any database administrator.

I sincerely hope the code improves quickly. I wish I had the object-level-restore ability of v4.5, but I can’t put my production database servers at risk like this. I’m already rolling back my servers to v3.1.

Update on November 7, 2007 – followup blog post, “Idera SQLsafe Followup: Problems with Restoring Databases”

Update on February 10, 2007 – I get an email at least once a week whether a new version of SQLsafe fixed the bugs.  We stopped using SQLsafe, so I can’t say, but you can get an evaluation version from Idera to find out by testing in your own environment.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:

Tom Casey: Keynote

Tom covered basic SQL Server marketing slides about the BI stack. Yawn. Good presenter, but couldn’t overcome the dry material. Then he handed it off to a Dell woman to announce that they’ll be selling packaged BI solutions with hardware, software, storage, etc all in one bundle. They’re targeting customers in the 1-4 terabyte range with pricing around $100k per terabyte.

I don’t understand the target customer for Dell’s packaged BI systems. I think of the decision tree like this: if a company is large enough to spend $100k per terabyte on a BI solution, aren’t they large enough to already have a preferred x86 hardware vendor, and probably a SAN? Why not just buy hardware from your existing preferred vendors? Surely you wouldn’t choose Dell because of their BI services expertise….

Russell Christopher suggests that companies will be attracted because Dell’s answered the question of what hardware configuration is required for a 1/2/4tb BI project. I’m not so sure – I haven’t seen two 1tb BI projects go the same way.

Anyway, regardless of my dim-witted analysis, this package would have been a hard sell coming out of anybody’s mouth, but it was an especially tough sell from this particular presenter. She was a nervous wreck: she kept constantly rehashing the same “I came from the dark side, not SQL Server” joke, and her slides didn’t match her speech. She would advance a slide only to say, “Oh, yeah, I already mentioned that.” Argh. Evidently Dell saves money on their marketing staff and passes that savings on to us.

Keynotes should be reserved for the very best speakers. Tom was fine, but it all went south when she picked up the mike. I bailed just before her presentation finished, and I heard bad things from other attendees as well.

Jerry Foster: Plan Cache Analysis in SQL Server 2005

In contrast to the Dell presenter, Jerry lit up the room as soon as he got started. This guy was born to teach. This was the first SQL seminar I’ve seen where people burst into spontaneous applause during one of his demos, even before he got to a conclusion.

Jerry and the crew at Plexus Online built a slick system to interpret the dynamic management views for the query plan cache. In a nutshell, his queries make it easy to see where load is coming from down to the batch and statement level, all without running a resource-expensive trace on the production servers.

About five minutes into the session, I knew I wouldn’t bother taking notes because I’d print out the slides and pore over them anyway. I downloaded his code the minute he put the URL up on the projector, and I’m going to spend a week going through it. He didn’t give out the source code for his front end, and I’m torn between building my own in classic ASP (miserable language, but I know it well enough to be dangerous) versus SSRS 2008 (which I don’t know at all, but might do well to learn.)

I’m not even going to try to touch base on everything Jerry discussed. Hard-core database engine DBAs owe it to themselves to go get his samples and pore over them.

I got chuckles out of some of the audience members’ questions, though. One of them started picking out differences between memory allocations on 32-bit versus 64-bit servers, trying to find out how much memory over 4gb his 32-bit servers could use for the plan cache. Hey, buddy, if you have to ask that question, then you need to upgrade to 64-bit. And if your database server isn’t capable of upgrading to 64-bit SQL, but you’re sitting in a seminar about caching, then you’ve got your priorities all wrong.

SQL Customer Advisory Team: Building Highly Available SQL Server Implementations

Going in, I thought this would be more technical, but it turned out to be a fairly high-level comparison of the newer HA technologies: database mirroring, peer-to-peer replication, log shipping and just a tiny bit on clustering. I didn’t learn much because I’d already researched the bejeezus out of these options, but their lessons-learned stuff bears some repeating here for people who haven’t done the homework.

Peer to peer replication and database mirroring have one good advantage over log shipping: the backup server (replication partner or mirror) can have a read-only copy of the database for query purposes. The CAT guys didn’t mention that the act of doing this means you have to pay licensing on the backup server; if you use it for purely disaster recovery reasons, you don’t have to license it.

Mirroring & log shipping should be done with Active Directory group security instead of SQL logins. Companies that frequently create SQL logins and modify their passwords will run into problems during disaster recovery, because the SQL logins aren’t synced between servers using mirroring or log shipping. If you strictly use AD groups for access permissions, then no user info is stored in the system databases, and you won’t have to worry about syncing the individual users.

Syncing SQL agent jobs, alerts, SSIS packages and maintenance plans is also a headache when doing disaster recovery planning, because those aren’t synced automatically either.

When doing database mirroring, remember that databases fail over individually, not the whole server at once. If your application uses multiple databases, you don’t want to have the failovers occur automatically, because a single database might fail over without the others, and timing would be important.

Monitor resources when mirroring more than 10 databases on an instance. That 10 number is flexible, just a rough guesstimate. (That scared me because I mirror more than 10 already.) Due to the way the mirror server handles writes, it may incur significantly higher I/O than the principal server.

In the event of a disaster, break mirroring quickly if there’s a chance the log files may fill up before the principal server comes back online.

When planning database mirroring, carefully analyze the log backup volume over time. The maintenance process of rebuilding indexes will add a lot of log volume, and you want that to happen during a very low activity window so that the mirroring logs don’t get too far behind. They’ve seen index rebuilds cause asynchronous mirroring to get over 2gb behind in less than 10 minutes.

They talked through a rather esoteric DR setup: two servers in the primary database doing synchronous database mirroring between each other, and then a third server in the disaster recovery datacenter with log shipping. That struck me as ridiculous because I’d have three possible database server names, which would be a configuration nightmare on the client side. Anyway, to get that scenario working requires manual setup and scripting, because log shipping has to be set up on both mirrored servers, and it can’t be done with the GUI.

Jason Carlson: Rich Report Design with SSRS 2008

I don’t know Jack about SSRS, but I figured I’d better sit in on this seminar after a midday conference call suggested that we might be doing it in-house.

The SSRS report design process is pretty much all new from the ground up with SSRS 2008, which makes me glad I didn’t put time into learning SSRS 2005. (Yay, procrastination!) The new design tool will be completely integrated into Visual Studio after CTP5, with a second non-VS designer with a Vista/Office 12 feel inspired by PowerPoint. The non-VS designer will support server mode (instead of just working locally), whereas the VS designer will only work when paired with an SSRS server.

Microsoft acquired the rights to a lot of Dundas chart code a few months ago. Dundas circular and linear gauges are coming in CTP6, but maps may not make it to RTM.

The chart setup is much more drag & drop than it’s been in previous releases (they say, and the crowd oohed in approval). Coders can click right in the chart to change the legend, title, locations, etc., much like Excel. As you’re doing chart setup in pop-up dialogs, the charts update in the background instantly. As a user of Excel for over ten years, I wasn’t quite as impressed as the developer members in the audience, but that’s okay – it just means I picked the right time to start poking around in SSRS.

I left about halfway through this presentation because I got some bummer news via email about a project, and wanted to do some damage control.

Tomorrow’s going to be tough – I’m stymied as to which sessions to attend. There’s some pretty good stuff out there with scheduling conflicts. I’m staying overnight on Friday, so I’ll be able to stay through the last session: troubleshooting connectivity errors. Sounds boring, but we’re having those issues at work, so I’ll be tuned in.

On to my Day Three notes.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:

I splurged and forked over $180 for a first class upgrade for my flight from Miami to Denver. It’s been years since I’ve flown, and it was scheduled to be a four hour flight, so it seemed like a good – albeit decadent – option. Turned out to be a lucky gamble: the plane was stuck on the runway for an hour while the crew worked on a communications problem, and I relaxed in my big leather seat. The only drawback: the worn-out recliner insisted on gradually, slowly, imperceptibly leaning itself back. Every ten minutes, I had to reset it back up to the upright position. Maybe it’s trying to tell me something, but I’m not an easy sleeper on flights, even in the expensive seats.By the time we were at cruising altitude and the flight attendant came around with the champagne and warm nuts, I’d forgotten that the five hours of comfort worked out to about the same cost as a new iPod Nano.

Speaking of Apple products, this was also the first time that I’d turned off my iPhone since I bought it. Nice. That thing is just rock-solid stable, especially compared to my old Cingular 8125 (HTC Wizard) that required constant reboots.

Once in Denver in my rental Nissan Xterra (free upgrade, woohoo!), I remembered two things about the West: height and distance. Even in the dark, it’s obvious how much vast, undeveloped open space looms around Denver. Being in Miami for a couple of years made me forget what that looks like.

For updates and eye candy, keep an eye on my Flickr photos.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:

I’ll be attending the SQLpass Summit 2007 in Denver in a couple of weeks.  It’s an annual conference for Microsoft SQL Server database administrators with seminars like “Managing Large Data Warehouses” and “Plan Cache Analysis in SQL Server 2005.”  I’m excited because this is the first national SQL conference I’ve been able to attend.

I’m also excited just to get away from the daily grind at work for a few days.  The last two and a half months were brutal, and things have finally slowed down to a normal pace again.  Not everything is back to normal – we’ve still got a couple of SANs that I’m afraid to touch – but for the most part, things are up and running.

I’ve got a new junior DBA starting the same week I’m heading out of town for SQLpass, too.  I needed the help, although I gotta say that the new guy probably isn’t going to get much SQL for the next few weeks.  After doing only SAN and VMware management for months, I’m eager to get back to SQL again.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me: