Google+

Selecting the right edition of SQL 2012 for the task

Leave a comment

November 15, 2013 by Ruaan Harmzen

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.

Feature

Description

Express

Standard

Business Intelligence

Enterprise

Database Size Maximum size of any relational database.

10GB

524PB

524PB

524PB

Max Processors or Processor Cores Maximum processing power available to an instance of SQL Server 2012 when used as a database.

Lesser of four cores or one processor

Lesser of 16 cores or four processors

Lesser of 16 cores or four processors

Maximum supported by OS, when licensed per core
Max Memory Maximum RAM available to an instance of SQL Server when used as a database.

1GB

64GB

64GB

Maximum supported by OS
LocalDB and User Instances On-demand and user-session based instances of SQL Server for use by applications.

Yes

No

No

No

AlwaysOn Failover Clustering Failover technology providing high availability through shared storage (formerly named Failover Clustering).

No

Yes—Two Node

Yes—Two Node

Yes—Maximum nodes supported by OS
AlwaysOn Availability Groups Failover technology providing high availability and disaster recovery through replication (new for SQL Server 2012).

No

No

No

Yes—Up to four Replicas in SQL Server 2012, eight in SQL Server 2014
Replication Replicate from one SQL Server to another using merge, transactional, or snapshot replication.

No— Replication subscriber only

Yes

Yes

Yes

Database Compression Compression of stored data while at rest in a SQL database.

No

No

No

Yes

Database Encryption Encryption of stored data while at rest in a SQL Server database.

No

No

No

Yes

Auditing Analysis of actual and attempted database access.

No

Yes—Basic

Yes—Basic

Yes—Fine-grained object-level auditing

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.

Feature

Description

Express

Standard

Business Intelligence

Enterprise

Max Processors or Processor Cores Maximum processing power available to an instance of SQL Server 2012 Reporting Services.

Lesser of four cores or one processor

Lesser of 16 cores or four processors

Maximum supported by OS

Maximum supported by OS, when licensed per core

Max Memory Maximum RAM available to SQL Reporting Services.

4GB

64GB

Maximum supported by OS

Maximum supported by OS

Report Builder End-user-created reports.

No

Yes

Yes

Yes

Report Archival and caching Storage of reports for later retrieval or analysis.

No

Yes

Yes

Yes

External Report Database Server Reporting Services instance can connect to non-local, or non-SQL Server database.

No

Yes

Yes

Yes

Custom Role Creation Creation of custom security roles to control access to reports.

No

Yes

Yes

Yes

E-mail and File Share Subscriptions and Schedules Automatically send reports by email, or store them on a server.

No—On-demand only

Yes

Yes

Yes

Infinite Click-through Reports Support for infinite interactive drill-down into reports.

No

Yes

Yes

Yes

Reporting Services Server in SharePoint Mode for SharePoint 2013 Integrate SQL Server into SharePoint to deliver centralized reporting.

No

Yes

Yes

Yes

Power View Support Interactive reports for analysis, based upon Power Pivot data.

No

No

Yes

Yes

Data-Driven Report Subscriptions and Alerts Automatic delivery of reports to specific recipients based on current data, and notification when reports match predefined rules.

No

No

Yes

Yes

Web Farms Fault-tolerant SQL Server Reporting Services deployments.

No

No

Yes

Yes

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.

Feature

Description

Express

Standard

Business Intelligence

Enterprise

Max Processors or Processor Cores Maximum processing power available to an instance of SQL Server 2012 Analysis Services.

N/A

Lesser of 16 cores or four processors

Maximum supported OS

Maximum supported by OS, when licensed per core

Max Memory Maximum RAM available to SQL Analysis Services.

N/A

64GB

Maximum supported by OS

Maximum supported by OS

Multidimensional BI Semantic Model Cube-based OLAP data modeling technology included in previous versions of SQL Server.

N/A

Yes—Limited to three partitions, no proactive caching, additional limitations

Yes

Yes

Data Mining Support Tools to analyze volumes of historical data to understand trends to help predict future trends.

N/A

Yes—Limited to standard algorithms and standard data mining tools

Yes

Yes

Tabular BI Semantic Model Table-based data modeling technology (new for SQL Server 2012).

N/A

No

Yes

Yes

Power Pivot Support Supports the use of Power Pivot in Excel 2010 or 2013 and SharePoint 2010 or 2013.

N/A

No

Yes

Yes

Analysis Services Server in SharePoint Mode for SharePoint 2013 Integrate SQL Server into SharePoint to provide centralized business intelligence and Power Pivot for SharePoint.

N/A

No

Yes

Yes

Scalable Shared Databases Connect multiple instances of SQL Server to a shared read-only database on external storage.

N/A

No

Yes

Yes

xVelocity Columnstore Index In-memory compression technology that can improve query performance on complex combinations of some types of data.

N/A

No

No

Yes

Advanced Data Warehousing Support Features to deliver scalable and high-performance data warehousing, and build complex data warehouse queries.

N/A

No

No

Yes

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: