Sql-server – SQL Server sync databases between enterprise – web edition

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):

• online reindexing
• row/page compression
• backup compression
• filtered indexes
• partitioning & partitioned indexes
• indexed views
• parallel index operations

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.

Considering that SO is one of the most highly visited sites in the world, it may not be the best site to compare against for a new venture.

That being said, SO, to my knowledge, has rolled their own solution in terms of scaling various aspects of the system, including caching just about everything that they can to remove as much load off of the database as possible. Most systems of the SO scale end up in this scenario because no off-the-shelf product or built-in scaleability feature fully meets the needs of these extremely-high-traffic sites.

So, while I wouldn't be very surprised if SQL Web couldn't handle high traffic loads, I would bet that the built-in scaleability pieces you get from other tiers of SQL Server will not, on their own, be enough to handle these sorts of extremely high loads. (hence why SO doesn't rely on them as their primary scale vector.)

Lastly, the business plan for any new venture that is concerned with a high potential to scale needs to also include a revenue stream that scales along with the business volume (whatever that may be).

So, if you scale in the volume of data your system has to hold and manage, does revenue scale with it? Likewise for number of users, or any other metric that will grow. If you find that you are not scaling your revenue as fast as the expense of scaling the combined metrics, you have issues to worry about.