SQL Server 2012 Licensing

This blog is an attempt to provide key SQL Server 2012 licensing information to be aware of.

 

The pricing as a guide is Retail without Software Assurance in US$.

 

Ref Licence Virtual Env. Notes
1 Core-Based No All physical cores must be licensed with a minimum purchase of 4 cores.
2 Core-Based No Hyper-threading does not impact licences required e.g. a 4 core CPU with hyper-threading enabled only requires 4 core licences. This is not the case when virtualization is used.
3 Core-Based Yes and No Single and Dual Core Processors are classified as 4 core processors for licensing.
4 Core-Based No When some AMD processors have 6 or more cores, each core is classified as 0.75 of an Intel core e.g. 8 of these AMD cores would only require 6 core licences.
5 Core-Based Yes An unlimited number of VMs running any version or edition of Windows and SQL Server on a physical server can be achieved by licensing all available cores on the physical server with SQL Server 2012 Enterprise Edition with Software Assurance and Windows Server 2012 Datacenter Edition
6 Core-Based Yes All virtual cores in a VM must be licensed with a minimum of 4 core licences. The exception is if you are using the licensing mentioned in Ref 5.
7 Core-Based Yes Virtual cores require licences for each physical core they access e.g. if a VM has 1 virtual core assigned and that virtual core uses 8 physical cores then 8 core licences are required for the VM.
8 Core-Based Yes If all physical cores are licensed with SQL Server 2012 Enterprise Edition without Software Assurance then you are entitled to have the same number of VMs as physical cores on the server e.g. licensing all 8 physical cores with SQL Server 2012 Enterprise Edition allows use of 8 VMs with unlimited SQL Server instances in the 8 VMs. If more VMs are required then extra core licences must be purchased.
This is not available for non-SQL Server Enterprise editions.
9 Server+ CAL Yes 1 Server licence allows unlimited instances in a single VM.
10 Server+ CAL No 1 Server licence allows unlimited instances on the physical server.
11 Server+ CAL Yes and No 1 SQL Server 2012 CAL licence is required for each user or device which connects to the SQL Server instance. This includes multiplexing where connections are made indirectly via either hardware or software.
12 Server+ CAL Yes and No 1 SQL Server 2012 CAL licence allows the user or device to access any 2012, or earlier, licensed instances in your organisation e.g. a user only ever requires 1 SQL Server CAL to access all licensed SQL Servers.
13 Server+ CAL Yes and No The SQL Server instances can use the maximum cores/memory and is only limited by the SQL Server edition and OS edition.
14 Core-Based and Server+ CAL Yes and No Each licensed instance of SQL Server is entitled to have 1 passive instance on a separate physical server or VM within a server farm. This 1 passive instance can be used for DR and does not require a SQL Server licence. If you require a second passive server for the same instance, then an extra licence is required. Passive is defined as ‘not serving SQL Server data to clients or running active SQL Server workloads’. If core licensing is being used, the Passive instance must not have more cores. If Software Assurance is not in use for the licensed instance, the passive server can only be used actively once every 90 days and the passive instance cannot be in another server farm, a third party web hoster or non-private cloud. If Software Assurance is in use for the licensed instance the passive instance can be used as often as needed, and another server farm, third party web hoster or non-private cloud can be used once every 90 days.
15 Core-Based and Server+ CAL Yes and No If AlwaysOn Availability Groups use secondary instances for non-passive activities i.e. for backups, users queries etc, then these instances require SQL Server licences.

 

SQL Server 2012 Standard Edition:
Key points to be aware of for this edition include:

  • Uses Core-Based and Server+CAL licensing.
  • Cored-Based licences allow for an unlimited number of users or devices to connect to the SQL Server.
  • Server+CAL licensing requires a SQL Server CAL for each user or device which connects to the SQL Server.
  • Database Engine can only use 16 cores, cannot exceed 4 physical sockets and max memory is 64GB.
  • Analysis Services can only use 16 cores, cannot exceed 4 physical sockets and max memory is 64GB.
  • Reporting Services can only use 16 cores, cannot exceed 4 physical sockets and max memory is 64GB.

 

Licence Number Cost Comment
Core-Based 1 1,793 The minimum number of core licences for each physical processor is 4.
Core-Based 2 3,586 Core licences are sold in packs of 2 and therefore if you have 5 cores you have to purchase 6 licences.
Core-Based 4 7,172 This the minimum number of licences you can purchase for each physical processor.
Core-Based 6 10,758
Core-Based 8 14,344
Core-Based 10 17,930
Core-Based 12 21,516
Core-Based 14 25,102
Core-Based 16 28,688 Maximum number of cores.
Server 1 898 1 Server licence allows unlimited instances on a physical server or unlimited instances in a single VM.
CAL 1 209 1 SQL Server 2012 CAL licence allows the user or device to access any 2012, or earlier, licensed instances in your organisation e.g. a user only ever requires 1 SQL Server CAL to access all licenced SQL Servers.
Server+CAL 30 7,168 CAL threshold 4 core licences.
Server+CAL 47 10,721 CAL threshold  6 core licences.
Server+CAL 64 14,274 CAL threshold  8 core licences.
Server+CAL 81 17,827 CAL threshold  10 core licences.
Server+CAL 98 21,380 CAL threshold 12 core licences.
Server+CAL 115 24,933 CAL threshold 14 core licences.
Server+CAL 132 28,486 CAL threshold 16 core licences.

SQL Server 2012 Business Intelligence Edition:

Key points to be aware of for this edition include:

  • Uses Server+CAL licensing.
  • Server+CAL licensing requires a SQL Server CAL for each user or device which connects to the SQL Server.
  • Database Engine can only use 16 cores, cannot exceed 4 physical sockets and max memory is 64GB
  • Analysis Services is only limited by the OS for the number of cores, sockets and memory it can use.
  • Reporting Services is only limited by the OS for the number of cores, sockets and memory it can use.

 

Licence Number Cost Comment
Server 1 8,592 1 Server licence allows unlimited instances on a physical server or unlimited instances in a single VM.
CAL 1 209 1 SQL Server 2012 CAL licence allows the user or device to access any 2012, or earlier, licensed instances in your organisation e.g. a user only ever requires 1 SQL Server CAL to access all licensed SQL Servers.
Server+CAL 5 9,637
Server+CAL 10 10,682
Server+CAL 25 13,817
Server+CAL 50 19,042
Server+CAL 100 29,492

SQL Server 2012 Enterprise Edition:

Key points to be aware of for this edition include:

  • Uses Core-Based* licensing.
  • Cored-Based licences allow for an unlimited number of users or devices to connect to the SQL Server.
  • Database Engine is only limited by the OS for the number of cores, sockets and memory it can use.
  • Analysis Services is only limited by the OS for the number of cores, sockets and memory it can use.
  • Reporting Services is only limited by the OS for the number of cores, sockets and memory it can use.
  • A single SQL Server 2008 R2 Datacenter Edition processor licence with Software Assurance is usually upgraded to 8 SQL Server 2012 Enterprise Edition core licences.

*SQL Server 2008 R2 Enterprise Edition server licences with Software Assurance are usually upgraded to SQL Server 2012 Enterprise Edition server licences with a 20-core limit. SQL Server 2012 Enterprise Edition server licences allow unlimited instances in up to 4 VMs on the licensed server and these 4 VMs are restricted to 20 threads between them all e.g. if the physical server has 24 cores, only 20 will ever be used.

Licence Number Cost Comment
Core-Based 1 6,874 The minimum number of core licences for each physical processor is 4.
Core-Based 2 13,748 Core licences are sold in packs of 2 and therefore if you have 5 cores you have to purchase 6 licences.
Core-Based 4 27,496 This the minimum number of licences you can purchase for each physical processor.
Core-Based 6 41,244
Core-Based 8 54,992
Core-Based 10 68,740
Core-Based 12 82,488
Core-Based 14 96,236
Core-Based 16 109,984
Core-Based 20 137,480 *Core limit for SQL Server 2012 Enterprise Edition server licences.
Core-Based 24 164,976
Core-Based 32 219,968
Core-Based 40 274,960
Core-Based 48 329,952
Core-Based 64 439,936
Core-Based 128 879,872

A warning about the TRUSTWORTHY database option

At some stage in your database career you may be tempted to set the TRUSTWORTHY database option, to on or be requested to do so by a project.

 

As I’ll show, you will get some seriously unwanted side effects if:

 

  1. the database owner is a member of the sysadmin server role e.g. sa, which is usually the norm to prevent orphaned database owners, and
  2. there is a user in the database who is a member of the db_owner database role.

 

Here goes…

 

You as a sysadmin are requested to set a database called testdb to have the TRUSTWORTHY database option enabled and therefore execute the below:

 

ALTER DATABASE testdb SET TRUSTWORTHY ON;

 

Below shows the option is enabled for the database.

 

 

Let’s say you have a database user called Bob who is a member of the db_owner database role in testdb.

 

 

To be clear, Bob is not a sysadmin and he shouldn’t be one.

 

SELECT IS_SRVROLEMEMBER('sysadmin')

 

Bob is however able to create stored procedures in testdb and he creates this one:

 

USE testdb
GO

CREATE PROCEDURE testsproc
WITH EXECUTE AS OWNER
AS
ALTER SERVER ROLE [sysadmin] ADD MEMBER [Bob]
GO

 

 

Now Bob executes his stored procedure and guess what he has become?

 

testsproc
GO
SELECT IS_SRVROLEMEMBER('sysadmin')
GO

 

 

Now that should scare you and it would be good time to check which of your databases (besides msdb) have this option enabled.

 

SELECT name, is_trustworthy_on
FROM sys.databases
WHERE name <> 'msdb'
AND is_trustworthy_on = 1

 

Reporting Services Chart Grouping Example

If you would like to know how to show a separate chart based on the same dataset, these steps may help you.

 

The following steps will create a separate graph for each server.

 

The data used for this example was:

 

 

Using SQL Server Report Builder 3.0 create a Blank Report and follow these steps:

 

 

 

Below I am connecting to the default instance’s test database where the data is held

 


 

 

 

 

You should see the below

 

 

Drag ‘server’ to the first column

 

We only require the Header and therefore the table row can be deleted

 

 

 

Create a Line chart in the second column in the Header

 

 

Resize the Header so the chart is more visable

 

 

Remove the last column in the Header as it is not required

 

 

We don’t require the first Header column to be visable in the report so we can make it hidden

 

 

 

Click on the chart and you should see Chart Data.
Drag the Dataset fields to the Chart Data as shown below.

 

 

Now we specify that the chart rows should be grouped by server

 

 

 

 

Drag and hide the first 2 columns

 

 

 

Change the outline of the table to be white

 

 

 

After you run the report you should now see a chart for each server and that is the end of this simple example

 

 

SQL Server 2012 Standard Edition & Lock Pages in Memory

A quick note to advise that SQL Server 2012 Standard Edition does not require the start-up trace flag 845 in order to use locked pages in memory.

 

You do however need to ensure that the SQL Server service account has the ‘Lock pages in memory’ privilege.

 

The ‘Lock pages in memory’ privilege can be set via:

 

gpedit.msc -> Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment

 

As per best practice, ensure your SQL Server has Minimum and Maximum memory thresholds configured, with enough memory left for non SQL operations.

 

For further reading see How to enable the “locked pages” feature in SQL Server 2012

SQL Server 2012 AlwaysOn Creating a full backup error 35262

While creating a new availability group I received the below error

 


The database appeared under Availability Databases on the Primary, however the Secondary did not have the Availability Group or database. This was expected as several steps are shown as skipped in the error message.
 
In this case, I right clicked on the newly created Availability Group, deleted it, re-ran the New Availability Group Wizard and it successfully completed.

SQL Server 2012 AlwaysOn Joining error 35250

If you receive the below error while using the Availability Group Wizard you could try these steps to resolve it
 

First view the properties of the failed Replica
  

 

Make a note of the Endpoint port being used (In this case it was the default 5022)

 

 
 
Create a new Inbound Port Rule for the port on the Primary & Secondary servers
 

 

On the Secondary server (the one with the issue), right click on the database and select ‘Join to Availability Group’
 

 

Select ‘OK’
 

 

The database should now be joined
 

Why you should be cautious with the db_owner and db_ddladmin database roles

How many times have you heard the request by the business that an individual or individuals require either the db_owner or db_ddladmin privilege for their database.
 
As a DBA you need to understand the consequences of this type of request.
 
This may make the requestors job a lot easier, but did you realise you’ve potentially handed over the SQL server?
 
How is that possible?
 
Here goes.
 
1. Business approves for AUSER to be added to the db_owner role (or db_ddladmin  role) of ADATABASE
 
2. DBA grants the privilege
 

 

3. AUSER connects and creates a table in the database with an index
 

 

4. AUSER creates a trigger on the database
Update: Thanks to Pawel Wojtowicz for pointing out that the following code doesn’t work on SQL 2008 R2. It has been tested on SQL 2012/4. You would also tend to use the new ALTER SERVER ROLE instead of sp_addsrvrolemember.
 

 
The following works on SQL 2008 R2, but the maintenance job shown later in this post will fail when run.
image201408

 

5. Lets confirm that AUSER is not a sysadmin member on the server
 

 

6. AUSER now waits for the DBA to do their job i.e. INDEX maintenance. The DBA likely has a maintenance job of some type that runs under sa or elevated credentials as shown below
 

 

7. The maintenance plans runs.  AUSER’s is now a sysadmin and it’s their server.
 

 

If you didn’t know about this, you do now.
 
As always, ensure you have policies and monitoring in place to address this scenario.