When Microsoft released SQL Server 2016 there were a number of changes and improvements to the flagship database. However, when considering the two main editions, Standard and Enterprise, organizations must account for a long list of features before choosing between them. Let’s take a look at the main differences between the two editions regarding these available options.
First, it is important to understand the intentions behind the two different editions. The Enterprise edition provides high end data-center availability, incredible performance, a wide array of business intelligence, unlimited virtualization and user access to data reporting. The Enterprise edition is well worth the higher licensing cost for large enterprises because of how much it offers.
Meanwhile, the Standard edition of SQL Server 2016 offers many of the same features with limitations. This edition is intended to provide smaller organizations with all the necessary tools and features at a lower cost and less need for IT staff. The Standard edition is a highly effective version for business customers looking for budget relief.
However, there are those customers who may need to further examine SQL Server 2016 features in order to effectively make choices between the two editions. This is where a careful examination of all the features coupled with licensing can save some companies cost, while others may quickly find they need far more database computing power.
Since the SQL Server 2016 database engine is designed for far faster performance, the needs of an organization fall into the specifics designed into each edition. Digging further into the details can provide an assessment which enables Microsoft customers to make the best-informed decision for both immediate and long-term needs. Here are some highlights when to consider among the many options and features of SQL Server 2016 when choosing an edition:
When it comes to scale limits, SQL Server 2016 Enterprise edition provides either unlimited memory or up to operating system limits. However, the Standard edition has a variety of memory limitations which must be considered when designing database applications. It is most important to remember that Standard edition is limited to the lesser of four sockets for 24 cores when it comes to the database engine, analysis services or reporting services. Other components may also be limited in the Standard version so it’s important to check for those limitations. But, it is also an important to realize that both Standard and Enterprise editions have a maximum relational database size of 524 PB.
RDBMS High Availability
Comparing RDBMS high availability between the two editions, users will find that there is plenty of overlap available when it comes to features. There are a number of features that are not available in the Standard edition, some of the most important being the lack of always-on availability groups, online page and file restore, online indexing, online schema change, fast recovery, mirrored backups, and hot add memory and CPU.
RDBMS Performance and Scalability
Almost all of the RDBMS performance and scalability features are available in both Enterprise and Standard editions with the exceptions being: resource governor, partition table parallelism, NUMA aware and large page memory and buffer array allocation, and I/O resource governor.
SQL Server 2016 Standard edition strongly mirrors RDBMS security from that of the Enterprise edition except for transparent database encryption and extensible Key management.
The Standard edition also comes with many of the same replication features as that of the Enterprise edition, the exceptions being: Oracle publishing, peer-to-peer transactional replication, and transactional replication updateable subscription.
The management tools available in the Enterprise edition are exactly the same within the Standard edition.
Standard Edition possesses many of the same RDBMS manageability features, though there are some which are not included that are available in the Enterprise edition. These features are: parallel indexed operations, automatic use of indexed view by query optimizer, parallel consistency check and SQL Server Utility Control Point.
SQL Server 2016 Standard and Enterprise editions provide the exact same development tools which is a strong benefit either way.
Almost all of the available programmability features of the SQL Server 2016 Enterprise edition are available in the Standard edition with the exception of advanced R integration and R server (standalone). This means there are a wide array of programming options available even in the Standard edition.
Basic integration services are available in both additions, but advanced sources and destinations as well as advanced tasks and transformations are not available in the Standard edition
Master Data Services
Master data services are only available in the Enterprise edition, making the higher cost a consideration for those organizations in need of these features. Contact one of our specialists for more details.
There are several integration services features not available in the Standard edition which include: star join query optimizations, scalable read only analysis services configuration, parallel query processing on partitioned tables and indexes, and global batch aggregation.
The Standard edition does not provide support for scalable shared databases or synchronize databases, while AlwaysOn failover cluster instances only supports two nodes.
BI Semantic Model (Multidimensional)
Standard edition of SQL Server 2016 does support many of the same business intelligence semantic model (multidimensional) features as the Enterprise edition. Check with one of our specialists for more details.
BI Semantic Model (Tabular)
Business intelligence is an important inclusion within the Standard edition of SQL Server 2016. The Standard edition supports all of the same tabular models as the Enterprise edition except for Perspectives, Multiple partitions, and DirectQuery storage mode.
Power Pivot for SharePoint
The Standard edition of SQL Server 2016 does not provide any support for Power Pivot for SharePoint so if this is a major consideration for an organization the Enterprise edition may be the choice since SharePoint is a powerful Microsoft product in wide use.
While the Enterprise edition of SQL Server 2016 does support a wide array of data warehouse features, the Standard edition only supports standard algorithms and data mining tools (Wizards, Editors, Query Builders).
Spatial and Location Services & Additional Database Services
All features and options for spatial and location services as well as additional database services are the same between SQL Server 2016 Enterprise and Standard editions.
StreamInsight HA is not available in the Standard edition.
Choosing between Microsoft SQL Server 2016 Standard and Enterprise editions encompasses a wide array of factors. It is very important to remember that with the 2016 version, Microsoft included Business Intelligence features for the Standard edition to provide non-enterprise class customers with these useful options at a limited availability. Likewise, the Standard edition widely mirrors the Enterprise edition in terms of available features, only with limitations to scalability.
Purchasing an Enterprise edition license most frequently means unlimited scalability for many features not available for the Standard edition. Customers must consider between the two when needs might require the higher-end edition. However, it is also easier than ever to upgrade the Standard edition to the Enterprise edition so expected database growth can also be accounted for with the possibility of business growth. Customers who choose a Standard edition will find it easier to grow as necessary by moving into the Enterprise edition when necessity requires a change.