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.

Just saw some tweets come across the wire and figured I'd check into it: sure enough, my upcoming SQL Server event looks like it's scheduled for June 30th and it recently made the front page of SQLMag.com (SQL Server Magazine is sponsoring this event).

From SQL Server Magazine:

SQL Server Unleashed, free virtual event

Unleash SQL Server's Full Potential to Maximize ROI. Join us on June 30, 2009. Sessions will be led by SQL Server expert, consultant, and author Michael K. Campbell.

Here's a more complete overview from the registration site:

SQL Server Unleashed!

Unleash SQL Server's Full Potential to Maximize ROI

Business reliance upon SQL Server databases continues to grow. Yet fewer and fewer businesses are spending the time, energy, and resources needed to sufficiently train DBAs, developers and other IT professionals about SQL Server options and capabilities. Not only does this drastically decrease ROI on SQL Server licensing costs, but it frequently comes at the expense of making operations more difficult for IT personnel, DBAs and developers left to solve complex problems and issues on their own.
Don't wait any longer! Stop merely using SQL Server and start unleashing it to maximize its full potential! Make plans to attend "SQL Server Unleashed," an interactive live virtual event produced by SQL Server Magazine.
Join SQL Server expert, consultant, and author Michael K. Campbell in a clear, insightful, and independent look at ways in which your business can better take advantage of SQL Server capabilities to decrease management costs and improve reliability, uptime and performance.
In just three hours, directly from your own computer you will:

• See how virtualization and consolidation benefits apply to SQL Server and understand why there's never been a better time to virtualize SQL Server deployments.
• Learn how to best virtualize SQL Server in order to optimize provisioning, uptime and consolidation without allowing virtualization to impair workload throughput.
• Understand the benefits and limitations of each of SQL Server's native high-availability options, and learn how to evaluate each option based upon business needs, network latency and other real-world considerations.
• Learn the difference between High-Availability solutions and disaster recovery solutions and ensure that both critical concerns are handled in the best way possible.
• Gain insights into what causes SQL Server performance problems and learn about the best ways to profile, load-test and tune SQL Server workloads rather than just throwing more and more expensive hardware at each problem as it is encountered.

Gain valuable insights as these topics are covered in detail. Each session also includes sufficient Q&A time to make sure that your questions are answered. Boost ROI and improve your business' bottom-line. You'll also be able to improve stability, increase up-time and maximize performance. Time to unleash the power of SQL Server within your organization!

Otherwise, you can find details about all three sessions: SQL Server Virtualization, SQL Server High Availability, and SQL Server Performance here.

The event is free, and registered participants get a free one-year (DIGITAL) subscription to SQL Server Magazine. So either way you slice it, it's a great deal.

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.

Toad for SQL Server just took Best of TechEd 09 for Database Development.

And I'm not surprised.

For the May issue of SQL Server Magazine, I took Toad for SQL Server for a test-drive, and I was floored at how responsive, capable, and CUSTOMIZABLE it was. In fact, the Technical Editor for my article was a bit worried that my article came across as tooo glowing.

Short story though? If you're not impressed with some of the limitations that SSMS currently 'enjoys' make sure to give Toad for SQL a test-drive. It's quite compelling – especially for developers.

About a week ago I made some minor changes to the site – in terms of video playback. Specifically, I changed playback to include a new set of controls that should make it much easier to pause/resume videos, as well as fast-forward and rewind on-demand.

I'll be adding a few other minor changes over the next week or so, and then I'm planning on going silent for a week or two as I create a handful of new videos.

So stay tuned…

If you're interested in learning more about what Hyper-V brings to the table, and whether or not it's ready for prime-time, make sure to check out Hyper-V Unleashed – a free Web Seminar from WindowsITPro.

The event will be on July 21st, and I'll be presenting the 'Hyper-V In Depth' session. Registration is free, and there are some great goodies/freebies available for registrants – in addition, of course, to the content.

Here's a recap of the Agenda:

11:00 a.m.

Hyper-V In-Depth

This session would provide detailed information about Hyper-V. We will begin with the Hyper-V architecture and a discussion of the differences in the Hyper-V role and Hyper-V Server 2008 standalone edition. We also would cover storage and networking options; installing and managing Hyper-V on Server 2008 and Server Core; and advanced topics such as offline VHD access, backup and recovery, high availability options, and management.
12:30 p.m.

Partner Technical Presentations

1:45 p.m.

VMware ESX vs. Microsoft Hyper-V

This session would compare ESX server and Hyper-V, showing the differences between their architectures. We would look at the different features and management interfaces and compare the performance of the two products. In addition, we would look at the more advanced enterprise management capabilities offered by VI3 and SCVMM.
3:00 p.m.

Highly Available Hyper-V

This session would present the different high-availability features offered by Hyper-V. We would cover setting Failover Clustering at both the Hyper-V server and guest level and provide an overview of Live Migration, discussing how Live Migration works and then showing how to set up and use Live Migration with Hyper-V and Server 2008 R2

I knew that one of my articles was going to be in the April issue of SQL Magazine, so imagine my surprise when the April issue actually arrived with two feature articles from yours truly.

Maximize Storage Performance – Sadly only what amounts to a fairly introductory approach to maximizing disk performance, but something that I've been keenly aware of in terms of how few SQL Server Deployments really take advantage of correctly.

Using SQL Server 2008 FILESTREAM Storage – I always wondered if the conventional wisdom which dictated that storing images and other files in a database truly did result in a performance hit, and in this article I tackle some of those conventional theories while addressing how to use SQL Server 2008's new FILESTREAM storage options. (Which I think I'll have to cover in a video here in a while too… )

If you're using SQL Server Management Studio 2008 to manage database objects, you may run into an issue that causes you some grief when you right click on a sproc and either specify the Modify or Script > Create options. Specifically, you may see something that wraps your entire sproc in a call to sp_executesql, like what follows:

Not only does this behavior needlessly complicate things, but it also makes ad-hoc modifications to your sproc a bit more difficult as any ticks that you had in your actual sproc body, now have to be escaped (i.e. ' becomes '') and then you also need to make sure that you keep on top of that with any modifications or edits you may make.

Personally, I'd rather see the scripts generated by SSMS look more like the following – as that's also what they look like under the covers:

As near as I can tell, there are at least one (possibly two) factors involved. First, the exact issue that causes this problem is the scripting option "Include IF NOT EXISTS clause", as shown below.

When that option is set to True is when I've encountered the problem.

Or, more specifically, it appears that when I set that option to True, connect to a SQL Server 2000 server, and generate a script – then I run into the problem with sprocs from SQL Server 2000. More interestingly though, is that this seems to break something internally, because then my scripts for SQL Server 2005 and 2008 servers/objects also get broken.

Seems like a bug to me, but maybe it's just a 'by design side-effect'. Either way, switching the option to False seems to solve the problem for me.

In a recent video on improvements added by SQL Server Management Studio 2008, I covered a number of bigger, and even smaller, changes to SSMS introduced in SQL Server 2008.

One very cool (and very subtle) feature I didn't explicitly call out though deserves some mention: the new addition to generate DROP and CREATE scripts in a single action.

Granted, all this option really does is combine the ability to independently generate DROP and CREATE scripts so it's not that big of a deal.

But it can help out in a number of scenarios, and I've found it actually quite useful in a number of recent DBA-related activities where I was cleaning up objects and wanted to ensure that I also had rollback scripts and so on…