For our soon-to-be-hosted application I need to choose between SQL Server 2008 Web edition and SQL Server 2008 Enterprise edition. There isn't any "must-have" functionality: the application currently runs on SQL Express, but it will exceed the 10GB maximum size that SQL Express imposes.
I'm looking at this comparison chart, and the most significant table in this page "Scalability & Performance". As tables get larger, functionality like "Table and Index Partitioning" would be vital to guarantee performance speeds. But at what table sizes would this be required?
What strategies should I use to determine if the size and usage of my database requires the Enterprise Edition Scalability and Performance functionality?
Best Answer
This is a very tough question to answer and the only correct thing to say is "it depends". Yes, it's trite but that doesn't make it untrue. That being said I'll try my best to give you an answer (use Standard) and explain the thought process behind it.
I'm not familiar with the "Web" Edition of SQL but I can certainly rattle off the differences between Standard and Enterprise. At the risk of a massive generalization I'd say that if you're looking at between 10 and 100GB for a single DB and you're not sure you need Enterprise features, the upfront cost difference combined with the ease of upgrading the SKU (Edition) if/when you need to mean that choosing Standard over Enterprise probably makes the most sense. I'm guessing that "Web" edition is some semi-stripped down Standard version and personally I'd avoid it based on the "Web Workloads Only" disclaimer. What does that even mean?!
So how does one truly determine if they'll need Enterprise features? Well, the best way I can think of is to download the Standard and Enterprise trials (or get an MSDN license) and do performance testing with both editions. If you find you can't hit/exceed your performance targets with the lower edition identify the bottlenecks and then see if the Enterprise features would assist. If so, upgrade & make the needed changes to use the features then re-test. In my experience the main features one makes use of in Enterprise are (in order of most common to least):
And finally... When moving up from something that's running well on SQL Express I don't think there would be many cases that justify the jump to Enterprise. Alone, the additional system resources that the full version of SQL can use should deliver big gains.