Is low-end SQL Express 2005 the way to go for a heavily trafficked site? OK, I know. The question sounds daft, but hear me out.
Right now, Atomic Avenue (and ComicBase.com for that matter) use SQL Server 2000 as their back-end database. Like most databases, the bread and butter of the whole thing is garden variety SQL: SELECTs, UPDATEs, etc. using the requisite JOINs, stored procedures and whatnot, along with the occasional subquery, and—I think once or twice in the entire database—something really avant garde like a temporary table. The point is, we make use of basically none of the high-end features that Microsoft has been stuffing into SQL Server in the past few versions to try to sell to the “enterprise” market.
What we do make use of is connections—lots of them. And that’s where we’re starting to get into trouble. See, you don’t just pay Microsoft a couple of grand to use SQL Server, you pay a couple of grand to use SQL Server on one machine, with up to five simultaneous connections going. If you want more connections (and believe me, you do), you have to spring for something called a “CAL” — Client Access License, which permits your server to keep more than that number of connections open at once. These CALs come in “packs” of five, ten, twenty five, etc. and cost about $162 per connection license. Need 25 more simultaneous client connections on your “SQL Standard Edition” server? That’s a cool $4,050 on top of the initial purchase price of $1,899. And remember: that’s for one server.
Being the kind folks they are, Microsoft also offers the option of skipping all this CAL business and just licensing SQL Server for a fixed amount based on the number of processors on your server machine. An SQL Standard Edition license by processor runs $5,999—so that’s basically $12,000 if your machine has two processors. But heck, at least that’s better than the Enterprise Edition of SQL Server 2005: that baby runs $24,999 per processor. Imagine: $50,000 to run a single piece of software on one machine. I’ve just depressed myself utterly by realizing that a copy of ComicBase sells for something like 1/10th of 1 percent of that. We’re definitely in the wrong end of the software biz!
The only good news in this scenario is that connections go surprisingly far if your site is programmed halfway properly. Typically, you can open, service, and close connections in well under a second, allowing a relative handful of connection licenses to handle the connections generated by thousands of typical users without too much waiting around for a free connection to open up. Still, as Atomic Avenue’s traffic continues to grow, I’m getting concerned about lack of available client connections becoming a problem.
The obvious way to handle the problem is to just throw money at it and pay Microsoft for ever-more licenses. When I think of all the giant Alien statues and toy robots we could buy for the office with that same money, however, I’ll admit I have to think twice before reaching for my wallet.
One intriguing alternative is that we might be able to solve the problem by actually downgrading our production servers to the new “Express” version of SQL Server 2005. This version was introduced to compete with free databases like MySQL, and brings virtually all the features of the full versions of SQL Server to the table, but with no cost for the application, and unlimited client connections. The catch? Microsoft hobbled the server by limiting it to handling databases of 4 GB or less; the server can only make use of one processor (but as many cores as it wants); and it can only use 1 GB of RAM for the buffer memory.
Of these limitations, frankly, the memory one gives me the most pause, but it definitely seems worth checking out. I think there’s at least the chance that we may be better off going down to the free version, than by throwing untold thousands at the folks in Redmond. (And if this doesn’t work, we also have the option of rewriting the back-end database in something like MySQL anyway, although that would be a major project to say the least).
I just got our new quad-core database server racked up and ready to go using SQL 2005 Express. I’m not really getting my hopes up, but I’ll let you know what I find out… (And if any of you have experience in this area, by all means write and let me know what you discovered!)
[digg=http://digg.com/microsoft/Is_the_free_version_of_SQL_Server_better_than_the_paid_one]
