Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

The dark side of Microsoft SQL Server Express

Don Jones explains why Microsoft SQL Server Express can be more dangerous than you'd think and why it might be worth it to invest in the paid edition.

Microsoft's SQL Server Express is both a blessing and a curse. It's a free, "lightweight" edition of SQL Server...

that's just shy of fully-functional, designed to serve as a local database for desktop applications. It's helped wean developers from Microsoft Access databases, and it provides a no-hassle path to the bigger, paid editions of SQL Server for applications that need to grow. Also, anybody can download and install it and many applications even install it as a prerequisite without you or your users even realizing it.

As a result, you could easily have hundreds of these databases littered around your organization. Each one is probably storing something important. None of them are probably backed up very often. They're also likely not maintained, meaning they're a performance problem waiting to happen -- one that'll be difficult to track down if your IT team isn't aware that Express is in play.

But it's free!

Let me tell you something: It isn't worth the price in a business environment. It'd be far better to stand up a single paid instance of SQL Server and to migrate all of your Express instances' databases into it. Standard Edition will do fine, since it already offers more features than Express but without any of Express' limitations. Pop it into a virtual machine and you can probably migrate in dozens, if not hundreds, of databases from existing Express instances. After all, if they're running on Express, those databases aren't likely creating any serious performance load.

With everything migrated to a central SQL Server instance, you can start taking control. Implement automated maintenance routines -- something Express doesn't support, by the way. Run regular backups. Add some security. Add high availability if you feel the need.

Last I looked, a two-core license for SQL Server 2012 Standard Edition was under $4,000. That's a tiny price to pay to replace a couple dozen Express databases -- very possibly more, depending on their workload -- and to pick up the centralized administration, monitoring, maintenance and security that comes with that price. Running that in a virtual machine gives you all the advantages of your virtual infrastructure, too. Live-migrate the VM to another host when needed, and you start to get awesome high availability for almost no cost.

I know you're probably thinking, "Why would you need high availability when you were just using Express to begin with?" Ask any help desk technician who's had to console some user whose desktop application died when nobody in IT even realized Express was in the picture. The point is that everything is mission-critical to somebody.

There are plenty of organizations that have outright banned SQL Server Express in their environments and have even taken steps to prevent it from running. I have a client who uses AppLocker to "blacklist" SQL Server Express so it won't run at all. It's not that Express is bad software. It just places data out of IT's control, and those organizations can't allow that to happen. But the corollary is, if you're going to ban Express, you need to make it just as easy for users to stand up their little databases when they need to. In other words, Express wasn't a hobby for those users. It was how they were accomplishing part of their job. You can't take Express away without providing an alternative, and a six-month investigative process to stand up a new SQL Server database isn't an alternative.

Think private cloud. Stand up something like Windows Azure Pack, so that authorized users have a Web portal to self-service-provision a new SQL Server database on the "main" SQL Server infrastructure. They can pay for that instance, if you like; Azure Pack is designed to understand that things have a cost, so you're not just opening the floodgates. Although, I'll point out, with Express being installed free of cost and restriction, those gates have been wide open for a while.

Next Steps

Microsoft's latest SQL Server release

A boost of power comes with SQL Server 2014

Dig Deeper on SQL Server Security

Join the conversation


Send me notifications when other members comment.

Please create a username to comment.

Is your company using SQL Server Express?
We are, and for a lot of the same reasons you mention. And for a lot of the reasons you mention (storage limitations, security, backup), we're moving away from SQLServer Express to more sustainable, enterprise-level solutions. So the question to me is "why sqlserver?" rather than MySQL or another open-source database with fewer limitations?
I'm the same as atteboyskie - lots of SQL Server Express instances dotted about different servers and in some cases different client computers. A lot of these don't seem to need the specific features offered by Microsoft's SQL Server, most could run happily on PostgreSQL, MySQL/MariaDB and others operate local-only, something like SQLite would have served fine in these cases.
We use it on a few PC for training purposes. New users developing on the production server is too great a risk to take. One wrong  command and you could have a disaster on your hands. 
This article is not really saying anything new. For years short-sighted managers and security departments have rushed to disable or delete anything that they don't understand without ever stopping to think why the users installed it in the first-place.
I think this may have intentionally been for personal use. I use it at home to play around and develop code. I can then debug the whole process without affecting the company server.  A company putting all their eggs in this basket is taking a chance where I would not.
Some softwares come with express edition so it does not worth to upgrade to higher edition (despite SQL Server Express limitation). Sometimes DBA manage what architect buy.