Ms Sql 2012 Edition Comparison Essay

Microsoft SQL Server Express: Version Comparison Matrix and Free Downloads

Microsoft SQL Server is an enterprise quality database management system that's usually hosted by a server and can scale across multiple servers and locations.

For local PC, small databases, and less critical situations, Microsoft offers a limited version of SQL Server called SQL Server Express. SQL Server Express can be downloaded for free and installed on your local PC. This is helpful for:

  • Deploying desktop Windows solutions that use SQL Server databases to users who don't own SQL Server
  • Deploying lightweight web applications that use SQL Server databases
  • Developers wanting to create and test applications with a SQL Server database on their own, rather than a database hosted on a server

SQL Server Express Limitations

There are many editions of the full version of SQL Server with Enterprise, Standard and Web the most common. Here are some of the major differences of the 2016 version compared to the Express edition:

The most critical issue is the maximum database size. The other differences are related to performance and maintenance.

For more details, visit Microsoft's SQL Server 2016 Comparison of Editions including Enterprise vs. Express

Note: SQL Server Express cannot be installed on a compressed drive. It only supports standard, non-compressed drives.

Microsoft SQL Server Express Versions

Many versions of Microsoft SQL Server Express and service packs were released over the years. Here's a summary by Major Version:

Major VersionService Packs
2016SP1
2014SP1 and SP2
2012SP1, SP2, and SP3
2008R2 RTM, R2 SP1, and R2 SP2
2005SP1, SP2, SP3 and SP4

Choosing the Right Version of SQL Server Express

Since organizations usually upgrade SQL Server less often than operating systems, an older version may be used for quite some time, especially if the application doesn't use the features added to the new versions.

  • If starting from scratch, choose the newest version that supports all the operating systems you're targeting.
  • If you are developing and need to eventually deploy to an actual version of SQL Server, you should use a SQL Server Express version that matches that version or an older version to avoid introducing features that don't exist on your SQL Server.

Version Comparison and Download Matrix

Here are the Microsoft SQL Express versions, when they were released, and the Operating Systems they explicitly support. The older versions generally work on newer operating systems but is not officially stated on Microsoft's web pages. The 2005 versions have a warning message when they are installed on Windows 8.x or later.

Links are also provided for downloading your own free copy of SQL Server Express and the related SQL Server Management Studio when it's not included in the main download.

* not explicitly supported, but functional

Backing Up SQL Express Databases

Once you start creating databases in SQL Server Express, don't forget that you still need to backup your databases. Sure, SQL Server databases as self monitoring and you don't need to run periodic compacts like you do to keep Microsoft Access databases healthy, but backups remain important for archival and disaster recovery reasons. SQL Server can't stop someone from entering bad data or accidentally deleting all your records. Transaction logs can help recover data, but if there are lots of transactions, that may be difficult to accomplish.

Here's our whitepaper to Automate the Backup of Your Microsoft SQL Server Express Databases

Additional Microsoft Resources

By: Simon Liew   |   Read Comments   |   Related Tips: More > Licensing



Problem

Selecting the right version of SQL Server to install is important to make sure you are getting the correct features that are necessary for your configuration.  As an example, in the Volume Licensing Service Center (VLSC) there are two downloadable editions of SQL Server 2012 Enterprise Edition (EE); SQL Server 2012 Enterprise Edition – Server/CAL and SQL Server 2012 Enterprise Edition – Per Core. What are the differences between these two downloads?

Solution

After you have login successfully into the Microsoft VLSC web site, you can see there are two SQL Server 2012 Enterprise Editions on the download page as per the below screen shot.

There are two key differences between SQL Server 2012 Enterprise Edition - Server/CAL vs. Per Core:

  • Licensing agreement
  • Processor compute capacity

Other than that, both have the same features and functions.

This tip uses SQL Server 2012 EE as an example in the licensing agreement to simplify explanation, but the same differences discussed would be applicable to the Enterprise Edition of SQL Server 2014 and SQL Server 2016.

SQL Server Enterprise Edition Licensing Agreement

An active Software Assurance (SA) benefit allows customers to upgrade their SQL Server deployments to the same corresponding newly released version of SQL Server at no additional cost during their agreement term. For example, if you have an existing SQL Server 2008 R2 EE – Server/CAL software license covered by an active SA agreement when SQL Server 2014 was released you were entitled to upgrade to SQL Server 2014 EE – Server/CAL at no additional cost.

SQL Server 2008 R2 EE – Server/CAL is used as an example here because EE Server licenses have ceased to be available for new agreements. For customers who have purchased SQL Server 2008 R2 EE Server licenses, then SQL Server 2012 EE (or a higher version) - Server/CAL download is applicable in this scenario.

In the Per Core licensing model, whether licensing the physical or virtual machine they should download and deploy SQL Server 2012 EE (or higher version) - Per Core.

The table below describes the version upgrade rights offered under SA for qualified licenses.

Version upgrade rights for SQL Server 2008 R2 EE
SQL Server 2008 R2 EE Server licenses
(ceased to be available)
SQL Server 2012 EE (or higher version) - Server/CAL
** subject to 20-core limit
SQL Server 2008 R2 EE Processor licenses
(ceased to be available)
SQL Server 2012 EE (or higher version) - Per Core

Processor Compute Capacity

SQL Server 2012 EE – Server/CAL only supports up to 20 physical cores on a physical machine and up to 20 virtual logical processors on a virtual machine. SQL Server 2012 EE – Per Core does not have this limitation and would be able to support up to the operating system maximum.

The table below provides a matrix which summarizes the processor compute capacity that can be used by a single SQL Server EE instance.

SQL Server 2012 EditionPhysicalPhysical hyper-thread enabledVirtual
SQL Server EE – Server 20 physical cores 40 logical processors 20 logical processors
SQL Server EE – Per Core Operating system maximum Operating system maximum Operating system maximum

SQL Server 2012 EE – Server/CAL Deployment

The screen shot below is an installation using SQL Server 2012 EE – Server/CAL download from VLSC on a virtual machine with 24 logical processors. The product name indicates something that we are familiar with seeing - Microsoft SQL Server Enterprise (64-bit).

The screen shot below is an informational message taken from the SQL Server Error Log on this virtual machine indicating that only 20 logical processors are in-use out of the 24 logical processors allocated to the virtual machine. This is not an error message, but rather an intended limitation because the EE Server licenses should only run on servers with a total of twenty cores or less.

SQL Server 2012 EE – Per Core Deployment

SQL Server 2012 EE - Server/CAL on the same virtual machine above goes through an Edition Upgrade using SQL Server 2012 EE – Per Core download media. This type of upgrade and vice-versa is officially documented and supported by Microsoft.

The product name now will indicate - Microsoft SQL Server Enterprise: Core-based Licensing (64-bit). So unless you see the core-based licensing in the Product name the processor compute capacity is limited to 20 cores.

The SQL Server Error Log will indicate an informational message that all 24 logical processors are in-use for the SQL Server 2012 EE – Per Core installation.

Summary

SA supports a variety of customer upgrade and downgrade scenarios in the licensing agreement. Licensing is a very complicated topic and this tip only covers a couple specific examples. You should check with your organization's Microsoft licensing representative and deploy the appropriate Enterprise Edition of SQL Server which you are licensed to use.

As noted, this tip uses SQL Server 2012 EE as an example in the licensing agreement to simplify explanation, but the same differences discussed would be applicable to the Enterprise Edition of SQL Server 2014 and SQL Server 2016.

Next Steps


Last Update: 2016-06-09






About the author




Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.

View all my tips

One thought on “Ms Sql 2012 Edition Comparison Essay

Leave a Reply

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