olly - Fotolia


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