caution sign in mountains

The Perils of Running SQL Server Express in a Production Environment

So there you are, falling off the side of a mountain. As the ground races toward you, and your life flashes before your eyes, you think, “I wish I had listened to my climbing instructor. He tried to warn me—if only I had taken the other path…”

At this point, you’re probably wondering what a mountain climbing disaster has to do with the choice of a database solution to power your business. Well, think of it this way—there are often a lot of paths you could take in climbing a mountain, and some are safer than others, to be sure. Likewise, there are a lot of database solutions to choose from, and, like the path the climber chose, some of them may be fraught with peril. So should you follow the tried-and-true path recommended by your trusted technology advisor?  Or should you let cost alone drive your decision?  If you choose the latter route, then like our ill-fated climber, you may well come to regret your decision, and your business could end up figuratively falling off the side of a mountain. Unconvinced?  Read on.
 

Free Always Costs Something

SQL Server Express, Microsoft’s free database platform, is free to download, distribute, and use, even in a production environment. That makes it a tempting choice for the cost-conscious entrepreneur. But remember that free doesn’t equate to no cost. What am I talking about?  Well, the costs I’m referring to are hidden, down-the-road costs—the costs you’ll pay when your website goes down for a week and you lose thousands of dollars in sales while pulling your hair out and spending more money on disaster recovery.

You see, unlike a full-blown edition of SQL Server, Express has some built-in limitations that you may have underestimated, such as the fact that a single database is limited to a maximum size of 10 GB. If you exceed that limit, then it’s simply game over. There’s no advance warning—there’s just your website dead in the water with an error message on the screen and the inevitable 911 emergency that follows.

Maybe you think your database will never get that big, right?  Well, let me ask you this—have you planned and implemented a data archival strategy?  That is, are you actively removing old data from the database in order to ensure that space will be available for new data down the road?  From my experience, most people don’t give much thought at all to data archival. So chances are that you’re eventually going to hit the limit. It’s just matter of time, and that database is like a ticking time bomb. In case you’re thinking that this is an extreme case, I can tell you that I’ve actually seen it happen. Just imagine frantically trying to decide which data to delete from the database in order to make more space available because you hit the limit. That’s not my idea of a good day at work.
 

Wait! There’s More!

But there’s more to worry about than the 10 GB limit. You see, there’s no SQL Server Agent service in Express, either, so you’d better have an alternative strategy for taking database backups. And without usable backups, you could lose all of your data in the case of database corruption.

What about the problem of transaction log growth and how to manage it without an Agent job to truncate the log?  No, log truncation doesn’t just take care of itself, and it can become a fatal problem if left unchecked. More than once I’ve seen a database become corrupted due to uncontrolled transaction log growth. How?  Well, when a database log gets large, SQL Server can bog down due to high disk IO associated with log related operations. High disk IO is generally detrimental to the performance of SQL Server. But when SQL Server is suffering from high disk IO from trying to cope with a bloated database log file, it can cause timeouts or worse problems to occur.
 

You Might Be Snowballing if…

snowball effect
So what’s an IT person’s first thought to try to address the problem?  Isn’t it always, “Reboot the server”?  But that’s just like trying to put out a fire with gasoline. Why?  Because the server reboot often comes while SQL Server was in the middle of doing something really important, like writing to the transaction log. If a log write fails, then bad things could happen, right?  So when the server comes back up from the reboot, you may find the database in a “suspect” state, which means it’s about as useful as a paperweight. At that point, your best option is to restore the database from backup. In that case, I hope you have a recent backup handy, because the alternative is to try to repair the database with the possibility of data loss. And who knows how that might turn out?
 

Looking Past the Obvious

So far we’ve considered only a few of the most obvious limitations, but the point is that what seemed like a great idea driven by the thought of considerable cost savings could end up being an absolute disaster for your business. Worse, any initial cost savings you gained from using a free product could be easily negated down the road by the cost of disaster recovery and the damage to your business’ reputation while an important database is down. So needless to say, despite the appeal of its zero dollar price tag, SQL Server Express is definitely not the right database solution to power your business.
 

What is the Cost of Downtime?

business wasteland
In other words, you’d need a pretty big parachute to save you from hitting the ground after falling off that mountain. Over the years, I’ve helped a number of customers recover from disasters resulting from using SQL Express in production. Some packed a parachute, but others did not. I have seen actual cases where production websites were sadly limping along with frequent outages for an entire week because of these types of issues. I’m sure there are others who’ve fared even worse. So ask yourself, could your business survive days or weeks of downtime?  Still, even assuming you packed a parachute by planning for these types of issues, using SQL Express in production would be like going base-jumping with your business data. Why take that kind of unnecessary risk with the lifeblood of your business?

So, please, save your business from falling off of a mountain. Instead of taking a cheap shortcut, take the path that’s tried and true. Here at Bit-Wizards, we’re experts in working with both cloud-based and on-premises editions of SQL Server. So if you’re trying to decide on a database solution that offers affordable cost, performance, and reliability, come talk to Bit-Wizards and we’ll be glad to help you choose a database solution that’s right for you.
Comments
 
comments powered by Disqus

Author

Matthew J. Parry-Hill, Principal Senior Software Engineer
Matthew J. Parry-Hill

Principal Senior Software Engineer

Read more