One of the intriguing things about newborns is their propensity to attack themselves. After a few weeks their little hands begin to take on a life of their own: flexing, probing, grabbing. Since their stubby little arms are so short, there isn't much territory to explore for those little hands, and they invariably end up by the head, ears, face, etc.

There's nothing quite like seeing an infant's little hands grab an ear. It's like watching body parts from two, unconnected, people. The little hand finds the ear, and then GRABS on to it. The child, who has been minding its own business, realizes that something isn't right. The pain that their little hand inflicts upon them causes them to tense up. The sad part is that where their hand was, previously, acting as a seperate entity, it too now tenses up. Within a matter of a second, a happy, cuddly little newborn goes from a world of innocence to one where they are being attacked with unmerciful cruelty. (And trying to get them to let go can be a chore too.)

Sadly, newborns aren't the only people susceptible to such self-inflicted unmerciful cruelty. DBAs occasionally attack themselves in the same way.

Background: Once upon a time in the land of IT there lived a huge, hideous, beast of a website. Like Frankenstien, it had been cobbled together from various 'parts,' both living and dead. It had, however, been cursed with a fair amount of success -- to the point where a complete reinvention would have been out of the question from a business standpoint. So, this frankenweb was therefore given yearly sacrifices of developers. Entire teams would be built up around this monster, only to be gobbled up every few years. Only rarely would someone escape, by running, screaming out of the building.

As it turns out, a modification needed to be made to the site one day. Modifications were par for the course; they could be sewn directly on to the outer hide of this frankenweb, further increasing technical debt, but giving business an empty, and pointless, feeling of accomplishment.

There was, for the longest time, no suitable dev/testing/integration environment, and as it happened, a sproc needed to be changed out in production. A time window was selected, when the site was only making about $40k/hour, and the change was to go in to place.

Never one to just wing it, I had done my best to create a suitable test environment at the last minute, and had tested the functionality. It all worked fine in my test environment (that wasn't an exact match of the production environment.)

As the production DBA it was my responsibililty to push the change to the live site's database. Four or Five coworkers sat at the ready (at their consolses) as I made the change -- and they quickly confirmed my worst fear: "The site is broken, I can't add things to the cart." said one, and then another...

My fingers had just found the soft lobe of my right ear...

Damn. Well. No biggie. Stay Calm.
There was no way I was going to let this hinder me -- I was better than this... I had a rollback script. I fired it against the DB/Site. It executed perfectly.

"It's still down. I still can't add to the cart." reported a coworker.
"I've got problems too. No go." said another.

Sure enough. No dice. Functionality was broken.

I tensed up. The pain in my earlobe was excruciating. Someone was attacking me with unmerciful cruelty....

What followed were a few minutes of stark raving horror. About 12 minutes en toto. Eventually I was able to calm down and think (after I got my pacifier). The only thing that made sense was that when I had done my rollback script I had intentionally done a DROP and a CREATE on the sproc in question. I wanted to make sure that there was no way that older logic 'remained' and tainted the works.

Now the problem with doing a DROP/CREATE is that the old sproc goes away. All of it. Including permissions. I KNEW this. (When you do an ALTER, the sprocs objectID persists, and any perms on it remain.) I also 'knew' that it wasn't an issue -- because as frankenweb as the site was, I knew that the whole thing sadly ran as the sa.

Only, the only thing that made sense was that this was some sort of a permissions error. A few quick checks later, and I was able to confirm, to my continued horror -- although it quickly spread to relief, that the piece of functionality in question actually DID execute as a user other than sa.

Analysis of the problem afterward showed that: the initial change would have worked, the webservers should have been reset was all. Doing the rollback is what HOSED me.

Moral of the story:
1) Babies are cool.
2) Test in a REAL environment. Don't settle for unmerciful cruelty. (My second priority, after getting the site to run as non sysadmin, was to work on creating a perfect test/integration environment -- I never got there completely, but got close enough for comfort before I ran shrieking from the building.)
3) Rollback scripts are a MUST. Only make sure you script the permissions, or JUST use ALTERs unless you like crying like a baby when you attack yourself unmercifully.

--------------------------------------------------

NOTE: This post is (moderately) adapted from one of my posts out on REPEATABLEREAD.

The June issue of SQL Server Magazine is out – featuring a Buyer's Guide with "tips from SQL Server Backup and Recovery Experts" designed to help you figure out when to use a third party backup application, and what to look for if you decide to upgrade.

Is SQL Server's Native Backup Functionality Viable in MOST environments?
Of course, I'm sure I'll potentially catch some flak for saying that SQL Server's native backup capabilities are "a very viable solution in most environments", but I stand by that assessment. Of course, "most" is a very subjective word – and it's definitely not the same as "all". Mostly what I meant by it though, is that SQL Server comes with backup capabilities that work fine out of the box – and which should work in well, most, environments where there are smaller databases and less stringent disaster recovery requirements. And given that there are sooo many SQL Server deployments out there with only a handful of smaller SQL Server databases on a given server, my "most" statement makes sense. As such, for many businesses, the price of deploying SQL Server doesn't AUTOMATICALLY require the overhead of a third party backup solution.

And, as my series of Video Tutorials on SQL Server Backups showcases, it's pretty easy to get solid disaster recovery solutions in place, and while SQL Server Management Studio doesn't provide the best reporting on backups, there are some rudimentary details that should work fine in most environments where there are less than 20-30GBs of databases in production.

When to Turn to Third Party Backup Solutions
On the other hand, there are definite cases where you'll want to look into using Third Party Backup Solutions. As the Buyer's Guide points out, I recommend 3rd Party solutions to my clients when they're schlepping larger (or more heavily used) databases over the wire – where compression becomes a BIG factor, where databases are larger (say above 20-30GB), where recovery times need to be as fast as possible, and/or where data at rest needs to be encrypted.

SQL Server 2008 introduced native backup compression, but, frankly, I'm not that impressed by it. First of all, it costs $20k more per processor. That may sound a bit silly, but it's effectively true. Because either you're buying Enterprise Edition because you already need the features it offers, or you're buying Standard Edition (at $5k/socket instead of $25k/socket) because you don't need the extra BI, online, reporting, and other capabilities of Enterprise Edition. But no one in their right mind would/should swing another $20k/socket to get 'free' backup compression. Moreover, in my estimation, most environments using Enterprise Edition are already going to have compression/encryption solutions in place, so SQL Server 2008's native compression sounds great on paper, but it's practically useless in my book (and other DBAs feel the same way as well).

So, if you've got larger databases, need to encrypt your data at rest, or just need FASTER backups (and ensuing recovery), then you'll NEED a third-party backup solution. And don't worry about the extra cost – it will pay for itself the first time it saves your bacon.

Third Party Solutions
SQL Server provides a set of native APIs that vendors can use to address the specific issues of improved backup/recovery performance, storage, and security. So, if you're looking for SQL Server Backup solutions, make sure you're going with a solution that implements these NATIVE APIs. In other words, steer clear of so-called 'SQL Server Backup Solutions' that just use Shadow-Copy to grab a copy of your files out from underneath SQL Server. These solutions WON'T give you the kind of coverage you really need, and I've personally witnessed the havoc they can cause when they take 'ignorant' snapshots of SQL Server data and log files that don't account for how SQL Server works in terms of logging and recovery and therefore leave databases 'backups' in 100% non-usable form.

Then, as I mentioned in some of the quotes from the Buyer's Guide, third party backup solutions SHOULDN't require you to install a LOT of software on the machines/SQL Server's that you'll be managing. Yes, whereever you end up installing the management console or primary management node for your third party solution will typically require a full-blown software installation; and if you're only managing a single server, then that's fine. But what I'm talking about here (and in the Buyer's Guide) is that you should be able to VERY easily deploy new backup 'agents' or 3rd-Party capabilities to OTHER managed servers with just the addition of a couple of .DLLs and some new added special stored procedures. You shouldn't have to install lots of bloat on each managed server – that should only be necessary on your primary or central management/backup server.

My Personal Preferences
Otherwise, I've personally only really used Red Gate's SQL Backup and Quest's LiteSpeed with enough day-to-day exposure to be able to provide solid recommendations for or against. (I've obviously played around with a few other backup solutions – but not enough to really get a feel for how they perform day to day or pan out over time.)

Happily though, both SQL Backup and LiteSpeed are phenomenal products.

Red Gate's SQLBackup
SQLBackup is definitely (in my mind) more geared at smaller shops where there is only a single server or just a handful of servers to be managed – and it's pricing ($795/server or $295 for the Lite Edition (which doesn't provide encryption)) typically reflects that very well. It can, however, manage multiple servers though without much difficulty. As a consultant, I've recommended SQL Backup to a number of my clients who need faster, smaller, and/or encrypted backups – and it's been a very viable solution that works dependably and reliably day in and day out. It's also very easy to use and configure – which is a big bonus for smaller shops where there may not be as much dedicated talent on hand to master all the ins and outs of SQL Server (i.e. companies where the DBA has been 'upgraded' to their position from either an in-house developer or other IT professional who is constantly playing a game of catch-up between their normal responsibilities and their DBA-related tasks).

Quest's LiteSpeed for SQL Server
Likewise, I'm equally as impressed with LiteSpeed. It's a great product that offers excellent compression, reporting, and encryption capabilities. It also offers MUCH greater granularity in terms of what kinds of objects can be restored (i.e. you can restore individual tables and objects ONLY instead of entire databases – which is very cool). In my mind though, it's much more enterprise-caliber in the sense that while it's viable for single servers (or just a handful of servers), it REALLY starts to shine when we're talking about managing backups on LARGE numbers of servers. And, as you'd expect, the pricing for LiteSpeed really reflects this as well.

In fact, if I had any complaint at all about LiteSpeed it's that I _really_ hate how Quest does business: by making you call for a quote, rather than just posting prices online and offering volume discounts like other more 'modern' vendors. That said, given that Quest is doing a great job of hiring forward-thinking, no-nonsense, DBA evangelists and experts like Brent Ozar and Kevin Kline, as well as dumping substantial energy into facilitating great resources like SQLServerPedia, I'm hopeful that some-day the winds of change will prevail at Quest and we'll see a more open, and more hassle-free, pricing scheme. Otherwise, I have nothing but praise for the solution itself.

In Conclusion
The key thing to remember though, is that we can talk backup solutions until we're blue in the face. But unless you've got a documented and well-rehearsed disaster recovery scenario in place, it won't matter how much you're spending (or not spending) on backup solutions if your backups are no good. And as I intimated in the Buyer's Guide:

• Managers/Bosses/CEOs don't care about backup solutions or details. They only expect them to work. 
• Your backups aren't working unless you regularly VERIFY that they're working by testing them out.
• The WORST time to learn that your backups haven't been working is during a disaster where you need your backups.
• The WORST time to learn how to RECOVER a database is during a disaster.

So, get a solution in place, test it thoroughly, document it accordingly, and optimize/update your disaster recovery documentation whenever you make changes.

Otherwise, if you're looking for more background on backups and best practices, make sure to check out SSV's current series of free video tutorials on SQL Server Backups. There are currently over 2.5 hours of FREE content covering everything from backup basics to log file management, SQL Server backup best practices, and how-to tasks showing you exactly what you need to do to backup and restore your SQL Server databases. And once you've mastered those basics and principles, using 3rd Party Backup Solutions will only BUILD upon the skills you've already mastered.

Upcoming eSeminar
I'll have more details later, but on June 30th I'll be presenting a free online seminar (sponsored by SQL Server Magazine) where one of the sessions will be on increasing ROI through efficiently virtualizing SQL Server.

In that session I'll touch on some of the myths around SQL Server and virtualization, and discuss why virtualization makes sense with SQL Server – as long as it's done right. (And, of course, the main focus of the session will be on 'doing it right').

Virtualization in General
Ultimately, knowing about virtualization (its strengths and not-so-strengths) is eventually going to be part and parcel to knowing about SQL Server. So if you're in to SQL Server, you need to start paying attention to virtualization.

And if you're already familiar with virtualization, or if you've erroneously dismissed it as not being relevant to SQL Server… then you need to re-asses.

Virtualization: Here and Now along with what's on the Road Ahead
As much as I love SQL Server (it pays the bills around here), I also love virtualization (it also pays the bills around here). And to that end, one of the hands-down best resources out there to stay abreast of current developments, recent trends, and potential changes in the entire virtualization industry is Virtualization.info. Granted, I'm a bit biased since I know the site's owner (we met a few years ago doing a Longhorn Server Roadshow)… but I'm convinced that if you value IT in general, and are interested in staying abreast of virtualization, you'll value this site as well.

For example, take today's great review of the showdown going on between VMware and Google. It's a perfect example of the kinds of great reviews and insights you'll find on a regular basis at virtualization.info.

I'm not an actor and I don't even play one on TV…
And no.. I'm not being paid, or 'benefited' in any way by providing this endorsement. It's just that I so frequently find myself wanting to share the great insights I get from visiting.

In case anyone was wondering: I love SQL Server. And for those that don't know, I also run a SQL Server Consulting business over at SQLServerAudits.com.

So if you're in the market for some professional SQL Server consulting help, make sure to check out the site and feel free to contact me – as my initial consultations are free.

Referral Bonuses
If you know of someone who needs help, send them my way.

Just make sure to give me a heads-up when you send someone, as I'm happy to pay a $50 referral fee (either in cold-hard cash or as an Amazon Gift Card, etc.) for any client that books over 5 hours of consulting help.

And, if you send yourself, just let me know and you'll get a half-hour discount.

Along with the review of Red-Gate's SQL Data Generator that I recently did for SQL Magazine, I also had the chance to review a really cool BI Visualization Tool called Miner3D. My full review of this product was also in this month's edition of SQL Magazine, so if you're interested in BI, make sure to check out the review and check out Miner3D as well.

If you're serious about BI, I think you'll be very pleasantly surprised at how easy Miner3D is to set up, and how responsive it is when allowing analysis of your data. Even better (and as I pointed out in my review), I think you'll love how it effectively lets your analysts set up what amount to dynamically defined KPIs with virtually no effort or development time.

If you need to generate testing or performance data, make sure to check out Red-Gate's great SQL Data Generator utility.

I've been using it for quite some time now, and just love how versatile and useful it is.

Check out SQL Server Magazine for my full review.

There are lots of great things to be excited about when it comes to SQL Server 2008. (And shortly after completing my series on SQL Server Backups, I'll create a whole series detailing new features and benefits.)

But in terms of installation and configuration there are some really great, yet very minor, additions/changes that will largely go un-heralded in other blog posts and articles.

One of those changes is that the installer will remind you to punch appropriate holes in your firewall.

It's not like you won't remember to do that anyhow... but if you're like me (and do lots of SQL Server installations on virtual machines) you'll find that reminder up-front to be nice as you'll be able to go and toggle the firewall open while the installation is proceeding - instead of waiting until right-before you get ready to connect from an external machine.

The other, bigger, benefit? That horrible, horrible hack called the SQL Server Surface Area Configuration Manager is now GONE. (I always detested how managing security infrastructure and options for SQL Server required another, external (and not to mention insanely slow/sluggish) tool. Now, once you're done with a SQL Server 2008 installation, you're greeted with a much nicer conclusion that doesn't require you to jump through a bunch of hoops:

I'm with Stephen Wynkoop on this one - I'm not sure I'm too hip on Microsoft's emerging direction to divide SQL Server Tools into Developer and Admin Roles.

Stephen just posted an editorial based on an interview that he had with Matt Nunn, and points out that Matt confirmed that Microsoft is trying to divide tools along discipline lines - wherein developer related tools will be in Visual Studio (VS) and DBA related tools will be in Management Studio (MS).

Stephen does a good, non-inflamatory, review of the pros and cons - but effectively ends with the opinion that he doesn't think it works for mixed-hat DBA/Developers.

I think he's spot on. Most likely because I think that there are likely VERY few DBAs out there that don't see some part of their job related to writing, modifying, or tuning code. Even if it's just a question of creating spiffy queries based on dynamic management views, most DBAs are going to want full-powered development IDEs/tools at their disposal to quickly pound-out scripts that they'll likely end up saving and occasionally re-using.

I'd say give developers powerful tools as planned, give DBAs the additional 'DBA' related tooling, and then give DBAs the option to install additional tools and support when they install MS to their own boxes. Just make it an option. Otherwise I really see myself possibly switching to use a tool from a third party.