Selecting the right edition of SQL 2012 for the task

A bit of a long post today, but worth it, because probably the only thing more complicated than SharePoint…Microsoft Licensing:

For most organizations, there are four editions of SQL Server 2012 to consider when using the product in its primary roles of database server, reporting server, or analysis or data-mining server. SQL Server editions are packaged such that each higher edition offers a unique set of features built upon the features of the lower edition. To make an effective decision, organizations must first understand the role SQL Server is to be used for and how the features and capabilities included in an edition align with that role as there are significant pricing and licensing implications for each edition.

SQL Server Editions and Roles

There are four editions of SQL Server 2012 for most organizations to consider:

• SQL Server Express (free)
• SQL Server Standard
• SQL Server Business Intelligence (BI)
• SQL Server Enterprise

While the licensing details of each edition differ, teams building applications need to consider an edition’s features and the role for the server prior to considering whether the licensing details are compatible with their business requirements.

The three most common SQL Server roles are as follows:

• Database server, from which users or applications can query or store data in columns and rows
• Reporting server, which aggregates data into manually or automatically generated reports that can be stored or sent to users
• Analysis or data-mining server, which can process and return results from large, complex datasets for users to help guide business decisions.

In addition to these three roles, each edition of SQL Server 2012 provides varying degrees of support for migrating data between SQL Server and other data sources and destinations. Simple manual import and export functionality is a feature of all editions, while automatic and more complex transforms require Standard edition. Still more complex data transformations, including those from some heterogeneous data sources, require Enterprise edition. (For more information on the data management features and limitations of each edition by role, see the chart “Data Management Feature Comparison“.)

SQL Server: Database Server Role

SQL Server is best known for its traditional role—an online transactional processing (OLTP) database engine where data is stored and queried from related tables of data, each table is comprised of rows and columns, and data in records are created, updated, and deleted in a transactional (all-or-nothing) manner.

Examples of SQL Server used in the database server role include the following:

Web application server, including SharePoint Server, where SQL Server is deployed as a data store for a Web site

Client-server applications, such as an order entry system with an application deployed to each client, that connects to a back-end data storage server

Local applications installed on workstations or shared servers, which are only used directly on the same system.

Comparing Database Role Features

All four editions of SQL Server merit consideration for use in the database server role. Selecting the best edition entails evaluating the range of hardware constraints, security requirements, and features available in each edition. (For more information on features and limitations of each edition for the database server role, see the chart “SQL Server Database Feature Comparison“.)

Another important consideration is virtualization of SQL Server applications, as the virtualization licensing rights vary by edition, and virtualization rights may prompt an organization to purchase a higher-tier edition than features alone might mandate.

Express edition, which is free to download and use, makes sense primarily for small-scale or local applications due to limitations on database size, hardware support, and the lack of fault tolerance. Express edition is available as three distinct downloads: Express edition, Express edition with Tools, and Express edition with Advanced Services. The database engine is identical across all three downloads, the difference being the inclusion of development tools and SQL Server Reporting Services (SSRS) components with tools, respectively. Express also includes two special modes that can optionally run SQL Server either in a user’s context when logged on or on demand. These modes were designed for use in software development and with local applications and are not available in any other edition of SQL Server 2012.

Standard edition is well suited for many business applications, as it allows for databases up to 524 petabytes (PB), the maximum supported by any edition of SQL Server 2012 edition, includes support for increased processor and memory compared with Express edition, and enables some limited fault tolerance capabilities.

Business Intelligence (BI) edition is functionally identical to Standard edition when used for the database server role. However, due to its licensing model and the fact that it is markedly more expensive than Standard edition, BI edition should be deployed only in the unusual circumstance when a server is performing the role of both a BI server and a database server.

Enterprise edition is the best choice when a database server must provide the highest level of fault tolerance, scale, security, and manageability. Enterprise edition delivers the largest scale, offering support for all of the RAM and processor capacity that the underlying version of Windows is able to access, and it offers database compression and database encryption.

SQL Server: Reporting Role

All four editions of SQL Server include SSRS, though the reporting features vary by edition. SSRS enables the creation of data-driven reports for manual retrieval or automatic distribution.

Examples of SQL Server use in the reporting server role include the following:

Redistribution with third-party commercial or in-house line of business applications that require preconfigured ad hoc reports created by the software developers

Ad hoc and automatically distributed reporting to help employees extract the information they need to make business decisions

Fully automated, data-driven reports, which are triggered by certain events and distributed automatically to the appropriate recipients.

Comparing Reporting Role Features

SSRS capabilities, similar to database services, vary by edition. For example, only BI and Enterprise editions feature support for the new Power View interactive reports for end-user data analysis. (For more information on features and limitations of each edition by role, see the chart “SQL Server Reporting Services Feature Comparison“.)

Express edition with Advanced Services includes SSRS, and it is ideal for use with commercial applications that include a collection of preconfigured reports but do not need fault tolerance, automatic report generation, and when additional reports do not need to be created by end users after SQL Server is deployed. Express edition with Advanced Services can retrieve only data from a Microsoft SQL Server database on the same local system, meaning that it is ideal primarily for redistribution with local applications. Express edition SSRS capabilities are also limited in terms of RAM and processor capacity.

Standard edition removes some of the hardware constraints limiting Express edition with Advanced Services. Standard edition includes customization of reports and roles, e-mail distribution of reports, report archival, and caching. Standard edition also enables Report Services to connect to database servers other than a local Microsoft SQL Server. Reporting Services Server in SharePoint Mode, which integrates SSRS functionality into SharePoint Server to provide centrally managed and distributed reports, requires Standard edition at a minimum.

BI edition is ideally suited when a reporting server must provide scalability, fault tolerance, or Power View end user interactive analysis reports. When SQL Server is being used in conjunction with SharePoint Server 2013, this could mean selecting BI edition over Standard edition if Power View functionality is desired. When used for SSRS, BI edition can take advantage of all the RAM and processor capacity the version of Windows being used is able to access. BI edition also provides alerts, which inform users when reports match certain rules that they have predefined, and data-driven report subscriptions, which distribute reports based upon constraints defined by the current data set. However because BI Edition employs a Server/CAL license model only it’s use must be very carefully considered vs. SQL Enterprise edition.

Enterprise edition is functionally identical to BI edition when used as a reporting server. However, it features a different licensing model (per CORE licensing), which means it is generally the only way to build a reporting server that connects to the Internet or that allows connections from external users or devices that are not licensed for SQL Server within the organization.

SQL Server: Data Analysis Role

SQL Server has included an online analytical processing (OLAP) component since SQL Server 7, traditionally based around the concept of multidimensional data, known as cubes. SQL Server 2012 introduced an additional data analysis model, called the tabular data model, provided as a component of SQL Server Analysis Services (SSAS), the SQL Server feature that enables businesses to find information that can help guide business decisions. The tabular data model is intended to make data analysis and processing more approachable than the multidimensional model. SSAS also provides high-capacity data warehousing, special-purpose databases that store large volumes of data for reporting, data mining, and decision making. Though BI edition can be used to build a data warehouse, many features necessary for a high-performance data warehouse are limited to Enterprise edition.

Examples of using SQL Server for the Analysis Services role include the following:

Power Pivot repository in SharePoint Server, which enables users to access centrally managed Power Pivot workbooks and optionally create Power View reports from them

Workgroup BI server, which provides reports to a small business or segment of a large business in order to guide business decisions, based upon data trends

Enterprise-wide data warehouse, which combines data from numerous sources into a single, large data repository to enable business reporting and insights across the organization.

Comparing Data Analysis Role Features

SQL Server Express edition does not include any SSAS functionality, while the other three editions each include different levels of SSAS features. (For more information on SSAS features and limitations of each edition by role, see the chart “SQL Server Analysis Services Feature Comparison“.)

Standard edition is limited to the SSAS multidimensional model carried forward from previous versions of SQL Server, and it has limitations to the scale, caching, and data-mining features available when analyzing data. In addition, as with the SSRS and database roles, Standard edition is limited in terms of processor and RAM available to SSAS when compared to the BI and Enterprise editions.

BI edition includes all features of Standard edition, and it adds support for the new tabular data model, Power Pivot for SharePoint, and support for Analysis Services in SharePoint Integrated Mode, which underpins Power Pivot for SharePoint. When used for SSAS, BI offers support for all the RAM and processor capacity the version of Windows being used is able to access.

Enterprise edition is the ideal choice for high-volume and mission-critical data warehouses and BI solutions, as it adds parallel query processing and support for high-availability SSAS deployments. It also includes export capabilities for third-party sources (SAP) and destinations (Oracle, Teradata, SAP) as well as including the new xVelocity Columnstore index, which can improve data warehouse query performance on complex combinations of some types of data. SQL Server 2014 Enterprise edition is expected to include a higher-performance xVelocity Columnstore index feature, currently available in the SQL Server 2012 Parallel Data Warehouse (PDW).

Data Management Feature Comparison

SQL Server provides a variety of data migration and management features as well as event-processing capabilities. The key related SQL Server 2012 features are shown here by edition.

Feature	Description	Express	Standard	Business Intelligence	Enterprise
Import/Export Wizard	Migrate data between SQL Server and a variety of predefined data sources in standard formats.	Yes	Yes	Yes	Yes
Integration Services	Migrate data between SQL Server and a variety of data sources, and perform data transformation.	No	Yes	Yes	Yes
Integration Services— Advanced Adapters and Transforms	Migrate data between SQL Server and many heterogeneous data sources, and perform complex data transformation.	No	No	No	Yes
Master Data Services	Provides a single unified data source for reporting and analysis across an organization in order to guarantee data consistency.	No	No	Yes	Yes
Data Quality Services	Profiles, cleanses, and matches data to ensure quality.	No	No	Yes	Yes
StreamInsight	Complex Event Processing (CEP) platform for processing and acting on high-volume streams of data.	No	Yes—StreamInsight Standard Edition	Yes—StreamInsight Standard Edition	Yes—StreamInsight Premium Edition

SQL Server Database Feature Comparison

SQL Server’s database engine allows users or systems to query or store data in columns and rows. The key features for SQL Server 2012 being used as a database server are shown here by edition.

SQL Server Reporting Services Feature Comparison

SQL Server’s Reporting Services rolls up data into reports that are stored or sent for analysis. The key features for SQL Server 2012 being used as a reporting server are shown here by edition.

SQL Server Analysis Services Feature Comparison

SQL Server’s BI and data-mining components can process and return results from large, complex datasets. The key related features for SQL Server 2012 are shown here by edition.