The edition of SQL Server “made” for you is the one that fulfills your needs. And the best to place for finding the needs that can be fulfilled by Enterprise, Standard, Express and Business Intelligence editions is msdn itself. The link could be find below.
But still just to give a brief of all these:
The best thing about express edition is that its free. Yes FREE. J . (sorry have to add a but) But the features are limited by limited I mean way too limited in comparison to Enterprise. That doesn’t mean that it its not useful.
Thers no need to buy pricey editions if you just want a server which can hold your subscriber database for replication.
You can run DML and DDL queries in it.
If you need a server to monitor your database mirroring than Express edition can do that for you.
You can create reports, yes by Reporting services, but only for the local databases and that too can be stored locally only. But still creating reports of your database is a benfit considering its FREE. (Like many I love free things)
But obviously there are limitations in SQL Express, if there weren’t who will buy Enterprise. J
There are many but stating some important ones: (For the rest refer the link at the end)
You can have a database of only 10GB size.
Express edition supports 1 core and 1 GB RAM only.
There are no High availability features.
No SQL Server Agent. That means no jobs, no Database mail, no alerts and everything else which requires SQL Server Agent.
But if you come across a need to upgrade your express edition to some other one that could be done.
It is a bit serious business SQL Server edition. And by that I mean it will cost you money.
It provides the features of Express edition and many more above that. You should choose this edition when you need:
The maximum size of the database can be 524PB.
A Server which you know that will not need more than 16 cores processor and 64 GB memory.
You will have High availability features like Log shipping; Database mirroring, Replication and Clustering.
SQL Server Agent service is available in Standard edition.
SSIS, SSRS, SSAS obviously with some limitations. But it still can satisfy the needs of many.
The limitations of Standard edition are:
Only 16 Core and 64 GB support, now a days that could not be sufficient for your production server.
You can set DM in safety only mode.
No Peer-to-Peer replication.
No Transparent database encryption.
That means if you don’t need advanced features of SQL Server (such as Data compression, Bakcup compression ,indexed views, Database snapshot, Table and index partitioning, Resource governor and much more) than Standard edition is enough for you.
Remember that Standard edition is much much cheaper than Enterprise. That’s why double and triple check your requirements and if you don’t need the advanced features of SQL Server go with Standard edition.
Standard edition licensing is done both on core and Server + CAL basis.
Business Intelligence Edition:
As said by Microsoft, “A comprehensive platform empowering organizations to build and deploy secure, scalable and manageable BI solutions.”
That means it is made for BI. It has all the features of Standard edition plus some additional features for Business Intelligence like BI Semantic Model (Tabular).
It has all the advanced features of Analysis services.
The 16 Core and 64 GB limitation is limited only to Database Engine. That mean SSAS and reporting services can use the OS maximum.
Master Data Services.
As the name states it is for Business Intelligence. It has all the features of Standard edition and thus all the limitations of Standard edition (as far as HA, scalability and features, and DE is concerned)
BI edition is available under the licensing model of Server + CAL.
This is the most robust and expensive edition of SQL Server.
It brings all the features that SQL Server has to offer on your plate.
No limitation in any High Availability features.
No Core or RAM limitation.
No hidden features in DE, SSIS, SSAS or SSRS.
The only limitation or we can setback of it is its cost. It is EXPENSIVE. 5 times more expensive than standard edition per core. And my friend SQL Server per core license is not cheap, for SE its 1800$ and for Enterprise its 6900$.
Enterprise edition is available in per core licensing model only.
Comparison of features of different Editions
Reporting services on Express