SQL Server Express: Dev or Prod?

  • [アーティクル]

Most people I’ve spoken to have at least heard of the Express edition. The next question I ask is where they’re running it: development or production? The answer is usually both. But when I drill into the production use it turns out that most of the time Express is running in production because of a 3rd party application not because of an in-house developed app. When I question why it hasn’t been used for in-house apps the response is one of three. The first reason is they have no idea why and realize that maybe they should consider it. The second reason is, they have ample capacity on existing servers/instances and to keep things simple they add the database to the existing environment. The last reason I hear has to do with the limits placed on Express. Let’s take a look at each of these in a little more detail:

Just Don’t Think of Using Express

This one baffles me. I can understand if you are needing features that aren’t available in Express (transparent data encryption, data/backup compression, etc.) but if Express is the right tool why not use it? It’s part of your toolbox – know your tools!

Existing Excess Capacity

This is a compelling reason to ignore Express. You’ve paid for an existing SQL Server license so why not get the highest utilization you can. Enough said.

Express Limits 

This one is interesting in that I find people with outdated information. The limits for SQL Server Express 2008 R2 are:

  • Database Size: 10 GB
  • Processors: 1
  • Memory: 1 GB

Now I agree there aren’t too many tier-1 (mission critical) applications that fall into this category of resource usage. However, the number of non-tier-1 application grossly out numbers the count of tier-1 apps and there are a lot of non-tier-1 applications that do.

Why is any of this interesting or important? SQL Server Express is the exact same codebase as the other editions. It’s fully tested and fully supported. All of the familiar tools (Management Studio, SQLCMD, and PowerShell) work against it. It’s good for development environments and even more importantly it’s completely capable for production environments. In certain situations you may not even consider Express and you may find yourself introducing yet another RDBMS into your environment. In the long run managing multiple RDBMS will be more expensive. Obviously if you have no other option, for example, your using a framework or app that doesn’t yet support SQL Server, you’ve got to do what you’ve got to do. Just don’t make a decision out of ignorance. Express is a solid edition of SQL Server for all environments: development, test, and yes, even production.

Comments

  • Anonymous
    January 07, 2011
    Excellent article by a person who know Express Edition For the limits : 10 GB yes for data and indexes but with the use of Filestream, you may limited only by the drive ( to read and write Filestream data, it is not simple by program, but with some spent time, it is not a real problem ). The unique feature which might give problem is maybe the user instance which is not clear for many people.A good idea which will disappear maybe because it has not been used as the SQL Server Express team has foreseen it Anyway, i will have a look on your blog which seems really interesting