Tip

SQL sprawl: Why is SQL Server Express installed everywhere?

I recently visited a client with a fairly large number of computers – an estimated 30,000 client and server machines in total.

They asked me to come in and evaluate how much unpatched software they had floating around. They were using Microsoft System Center Configuration Manager

    Requires Free Membership to View

to inventory and manage almost all of their machines, so that seemed like a good place to start.

The number one installed software package? It was Microsoft Office, of course, which they were pretty diligent about keeping patched. The number two, believe it or not, was SQL Server -- the Express edition, that is.

The guys I was working with were actually shocked. They hadn't really explored the inventory reports the way we were doing, and hadn't realized that SQL Server was so widely deployed in their environment. Worse, they said they'd never made any effort whatsoever to patch SQL Server beyond the eight server machines where they ran full editions of the product.

"Is this thing part of the operating system, now?" one of them asked me.

If only.

Mommy, where do SQL Servers come from?

Back in 1998, Microsoft was planning a release of SQL Server that could run multiple instances of itself side-by-side. The idea was that certain SQL Server configuration options were server-wide, and by allowing multiple instances on a single machine, you could have several different "server-wide" configurations running in parallel. Each instance would also be completely standalone in terms of performance and stability, so they could be managed more independently.

More SQL Server DBA resources

Common oversights with SQL Server security audits

Top 5 SQL Server DBA tasks that are a waste of time

DBA career paths could lead to business intelligence

At the same time, Microsoft was looking at the huge number of applications – even commercial ones – that depended on Microsoft Access for data storage. Now the benefit of Access is that its databases were easy to deploy and work with. Your application's installer had to install a couple of database drivers, copy the database file to disk, and you were done.

The problem is that this wasn't exactly what Microsoft wanted people using. Access had issues with larger numbers of concurrent users, and it had really been intended for smaller applications by knowledge workers – not as part of commercial applications. What Microsoft really wanted commercial applications to do was use SQL Server, which cost a bit more.

The problem was, commercial application developers selling $300 software weren't going to publish a prerequisite for a $5,000 piece of software that required a server-class machine to run on. So Microsoft created the Microsoft SQL Server Desktop Engine, or MSDE, a stripped-down version of SQL Server 7.0 that (a) cost nothing, (b) could be packaged into an application's installer for a seamless install experience, and (c) provided a super-easy path for "scaling up" to the full version of SQL Server. Over time, MSDE became known as SQL Server Express.

That's one reason why SQL Server Express is so prevalent in some environments. Tons of applications include it and rely on it. In fact, for the client I was visiting, we found several machines that had no less than eight copies of Express installed. Each was associated with a separate application, and each application followed the poor practice of installing their own named instance of SQL Server Express rather than looking for an existing instance and just using that. (I think half of Microsoft's problems in this world come from application developers who follow poor practices.).

Today, Microsoft has actually backed off of making it easy to bundle SQL Server Express into your application's installer; they'd prefer that you simply launch Express' own installer in "unattended" mode, and then proceed to install your application.

So why did this client have so many copies of SQL Server Express? One of their corporate sales applications used it for local storage on laptop computers and would replicate data with a central SQL Server whenever the laptop was connected to the network. Essentially, every salesperson was toting around a copy of SQL Server on their laptop. All of their in-house developers had SQL Server Express installed, because the easiest install of Visual Studio includes Express. There was lots of stuff like that. Without realizing it, they'd been installing SQL Server on thousands of machines all along.

Care and feeding of SQL Server Express

Modern editions of SQL Server Express are actually quite easy to keep patched. You can either use Windows Server Update Services (WSUS) or upgrade your computers' Windows Update to the full Microsoft Update. Microsoft distributes SQL Server patches through Update just as it does for Windows operating systems, Office applications, and other software. Provided you have a good Update infrastructure in place, Express will be fine.

There's no Group Policy setting to prohibit Express from installing or running, mainly because such a setting would probably break a quarter of all applications in the universe.
,

SQL Server Express presents other challenges, though, which haven't been well-addressed by Microsoft. For example, there's no Group Policy setting to prohibit Express from installing or running, a la Internet Information Services (IIS) 6.0. This is mainly because such a setting would probably break a quarter of all applications in the universe.

Another problem involves companies whose applications store sensitive data in all of those Express instances – data covered by HIPPA, GLB, SOX, PCI DSS, or some other compliance effort. SQL Server Express doesn't have any central way for you to specify auditing controls, encryption, or other settings. Microsoft would tell you that Express shouldn't be used for that kind of data, and I'd tend to agree with them. Data that needs to be managed should live in data centers.

SQL Server Express also doesn't have any kind of centralized backup and restore technology that can be centrally configured. Again, Microsoft would probably suggest that data which needs to be backed up should live in the data center – and again, I'd tend to agree. Express is intended as a simple, local data store – not unlike an Access database. In fact, it has many of the same limitations. The good news? In most cases, Express can automatically close database files that aren't in use, meaning those files can be grabbed by a simple file-level backup – just like an Access database.

Do you need to be worried about a large number of SQL Server Express installations? Potentially, yes. If Express is storing sensitive data, important data, or something along those lines, you might think about relocating that data into the data center.

The beauty of Express is that any application using it can automatically use a full edition of SQL Server just by changing a few settings. If you have an application that doesn't allow the necessary settings to be changed, it's time to get on the phone with the vendor and raise some heck.

Miss a tip? Check out the archive page for Don's series, SQL Server for the Reluctant DBA.

ABOUT THE AUTHOR:
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 IT books and a speaker at technical conferences worldwide. Contact him through his website at www.ConcentratedTech.com.

This was first published in January 2010

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.