0

SQL Server 2012 Editions & Differences

Hi Friends, Today we are going to discuss SQL server 2012 editions and various features which these editions support or not. However, I tried to compile all major features here but there is a huge list so trying to cover only major points.

So let’s start

SQL Server 2012 Editions

Below is the list of SQL server 2012 editions.

Principal Editions of SQL Server:

  1. Enterprise
  2. Business Intelligence
  3. Standard

Specialized Editions of SQL Server 2012:

  1. Web

Breadth Editions of SQL Server 2012:

Breadth editions of SQL Server are engineered for specific customer scenarios and are offered FREE or at a very nominal cost. 

  1. Developer 
  2. Express
  3. Evaluation

Features Support

Let’s understand each feature and see what all SQL server 2012 editions support the feature.

Maximum Compute Capacity Used by a Single Instance

This is the computing power which Sql server instance is going to consume. As we know that these days servers are having multiple sockets. Each socket is filled with multi-core units. Each unit have hyper threading enabled. So there is too much processing power a single server can have. It is important to know that what is the maximum processing power, a single Sql server instance can use.

Database Engine:

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Operating system maximum

Limited to lesser of 4 Sockets or 16 cores

Limited to lesser of 4 Sockets or 16 cores

Limited to lesser of 4 Sockets or 16 cores

Operating system maximum

Limited to lesser of 1 Socket or 4 cores

Operating system maximum

 Analysis Services, Reporting Services:

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Operating system maximum

Operating system maximum

Limited to lesser of 4 Sockets or 16 cores

Limited to lesser of 4 Sockets or 16 cores

Operating system maximum

Limited to lesser of 1 Socket or 4 cores

Operating system maximum

Maximum Memory Utilized (per instance of SQL Server)

These day servers can too much memory by installing high capacity or multiple RAM. But when it comes to use this memory by Sql server then it have few limitations. Let’s see what these limitations are.

Database Engine:

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Operating system maximum

64 GB

64 GB

64 GB

Operating system maximum

1 GB

Operating system maximum

 Analysis Services:

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Operating system maximum

Operating system maximum

64 GB

NA

Operating system maximum

NA

Operating system maximum

 Reporting Services:

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Operating system maximum

Operating system maximum

64 GB

64 GB

Operating system maximum

4 GB

Operating system maximum

Maximum Relational Database Size

As name implies, this is the maximum database size. This take disk space.

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

524 PB

524 PB

524 PB

524 PB

524 PB

10 GB

524 PB

Log Shipping

SQL Server log shipping is a technique which involves two or more SQL Server instances and copying of a transaction log file from one SQL Server instance to another. The process of transferring the transaction log files and restoring is automated across the SQL Servers. As the process result there are two copies of the data on two separate locations.

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes

NA

NA

NA

Database Mirroring

SQL Server database mirroring is a disaster recovery and high availability technique that involves two SQL Server instances on the same or different machines. One SQL Server instance acts as a primary instance called the principal, while the other is a mirrored instance called the mirror. In special cases, there can be a third SQL Server instance that acts as a witness. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes (Safety Full Only)

Yes (Safety Full Only)

Witness only

Yes

Witness only

Yes

 Safety Full Only:

Data is written and committed on the principal and mirror databases synchronously. Only after committing on both databases, the database application can continue with activity.

 Witness Only:

The witness is the one that brings the third vote into the mix in order to create a quorum. Principal and mirror each has one vote, so they can tie 1:1 and no decision can be made. With a witness there can be a quorum established 2:1 or 1:2 (or o:2 if the principal is unreachable) and therefore a decision can reached who should be the new principal.

AlwaysOn Failover Cluster Instances

As part of the SQL Server AlwaysOn offering, AlwaysOn Failover Cluster Instances leverages Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the server-instance level—a failover cluster instance (FCI). An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one WSFC node to another if the current node becomes unavailable. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes (Node support: Operating system maximum)

Yes (Node support: 2)

Yes (Node support: 2)

NA

Yes (Node support: Operating system maximum)

NA

Yes (Node support: Operating system maximum)

Online Index

In ONLINE mode the new index is built while the old index is accessible to reads and writes. Any update on the old index will also get applied to the new index. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

NA

NA

NA

Yes

NA

Yes

Online Schema Change

Another feature that can increase uptime is the ability to perform online schema changes where you can change a table’s schema and that table can be continuously available. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

NA

NA

NA

Yes

NA

Yes

Table and Index Partitioning

The data in partitioned tables and indexes is horizontally divided into units that can be spread across more than one filegroup in a database. Partitioning can make large tables and indexes more manageable and scalable. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

NA

NA

NA

Yes

NA

Yes

Auditing

Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server audit lets you create server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Audited events can be written to the event logs or to audit files. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes

Yes

Yes

Yes

User Defined Roles

SQL Server 2012, you can create user defined server roles and add server level permissions to the user defined server roles. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Replication

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes (Subscriber only

Yes

Yes (Subscriber only

Yes

Various Tools

These are various tools available with Sql Server.

SQL Management Objects (SMO), SQL Configuration Manager, SQL CMD (Command Prompt tool): 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes

Yes

Yes

Yes

SQL Profiler: 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

No

Yes

No

Yes

SQL Server Agent: 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes

Yes

No

Yes

These are few major features which I listed. I’ll keep on adding other features in this article.

Deepak Gera

Hi, I am Deepak Gera, Consultant, Founder and Chief Editor for www.lazyheap.com. Working as Technical Architect and having diversification in Industry types, Tools, Techs.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.