Importance of choosing the right architecture and SQL Server Edition

Last week's survey was another two-fold one – when you buy new servers, what architecture to you predominantly buy, and why?; when you buy new servers, which Edition of SQL Server do you predominantly buy, and why? Here are the results as of 5/17/2009.

For the first survey, the 'other' values were basically that 64-bit is purchased only if required. For the second survey, nine of the 'other' values were basically that Enterprise Edition is purchased only if required, one person uses Web Edition, and one person uses Developer Edition as they're just a developer.

I don't have a huge amount to say about these two surveys, I really just wanted to confirm my gut feel (and give you all a view of what's happening out there in the field).

For the architecture survey, I was entirely unsurprised to see that almost 80% of respondents are using 64-bit. It's widely known that 64-bit can give you better performance through the availability of more memory for SQL Server to use. Although you can use more than 4GB using AWE on 32-bit, that extra memory is only available for the buffer pool to use – not for general query processing – and AWE access does incur a little overhead. Much has been written about this and I'm not going to duplicate it here. A selection of articles can easily be found at http://www.google.com/search?hl=en&q=sql+server+64+bit+vs+32+bit&aq=1&oq=sql+server+64  More interesting is the small number of respondents who are not able to use 64-bit, either because their corporate policy is 32-bit (maybe because of cost?), they have software that doesn't run on 64-bit systems, or specifically because 64-bit is too expensive.

A few people only use 64-bit if required, and (I imagine) prefer to save money if it not by sticking with 32-bit. I wonder how long it will be until 32-bit servers are not available at all? Certainly future versions of Microsoft server software are starting to become 64-bit only – for instance Exchange 2010 (see here) and SharePoint Server 2010 (see here), will the next version of SQL Server be 64-bit only?

For the Edition survey, again, unsurprising results. 55% use Enterprise Edition for one or a combination of the various "-abilities" (if you make performancability a new word), with almost 10% more having the option to use it if required, and 16% having to stay with Standard because of budgetary constraints. What you may be surprised to see (but I wasn't from my time working side-by-side with the SQL marketing team) is that 20% of respondents don't need Enterprise Edition. There were a lot of improvements to the database engine in SQL Server 2000 and SQL Server 2005 that meant that for intermediate workloads (hmm – just made that up, but you know what I'm trying to say), Enterprise Edition isn't needed. And with synchronous database mirroring available in Standard Edition, you can implement a great, low-cost high-availability plan without paying for Enterprise. As you can see, I don't get any kickbacks from the SQL team for selling Enterprise Edition  Saying that, however, there are a lot of very cool features in Enterprise Edition in the various versions – but if you don't *need* them, why pay for the higher Edition just for the sake of it?

One thing I will say to summarize, it's important to look at your requirements in terms of performance, availability, etc to make the right choice of server architecture and SQL Server Edition. If you make the wrong choice, do you think your company will pay to rectify your mistake before the next round of capital-expenditure? Probably not – but you'll definitely pay for the wrong choice with several years worth of hassle trying to make the system be more performant and easily recoverable than your choices allow.

As always, thanks for participating in the surveys – I've had a bunch of mail from people who like to see what other people are doing.

Next up – this week's survey!