SQL Server Reporting Services Licensing

There have been quite a few questions about SQL Server Reporting Services (SSRS) Licensing recently, so I wanted to take a few minutes and talk through how the licensing for SQL Server Reporting Services works compared to the licensing for the normal database engine.

When you license SQL Server (of which the SQL Server Reporting Services engine is a part of) you license what is called the OSE or Operating System Environment. This is basically the OS that has SQL Server installed on it. Now, this can be the virtualization host (VMware or Hyper-V) or it can be the Windows Server (SSRS isn’t available on Linux, so we don’t have to deal with that, but if SSRS was available on Linux the rules would be the same as Windows). You can install SQL Server (or SSRS) as many times inside that OSE as you want to, but you can’t install SQL Server (or SSRS) on any other machines.

Standalone SSRS

Let’s look at an example, and we’re going to avoid Host-Based Licensing for right now as we’re going to assume core-based licensing. We have 8 cores of SQL Server License purchase. Our SQL Server machine requires 8 cores. We want to install SSRS, so we spin up another VM (or physical server) with 2 cores. We need to purchase 4 cores to handle that machine (don’t forget if you have less then 4 cores on the machine you still need to purchase 4 cores for the machine, per the licensing rules). What this means is that you have to license the machine running SQL Server Reporting Services, as if it had the full database engine on it.

Scaleout SSRS

If you have a scale out environment where you have a few SSRS servers behind a load balancer and using the same SSRS Database then you need SQL Server licenses for each machine that has SSRS installed on it. None of the SSRS servers are passive, which means that the free SA rights for DR don’t apply here, as those rights only apply to passive nodes.

Another thing to keep in mind about scale out, is that scale out is only available with a SQL Server Enterprise License. There is no scale out functionality for Reporting Services with a SQL Server Standard Edition license.

So lets look at an example again. If we’ve got 3 nodes of SSRS in a scale out configuration, and each node for 4 cores, then we need 12 cores of SQL Server Enterprise Edition to cover the SSRS servers. The server that’s hosting the SSRS database can be SQL Server Standard Edition. Having the database with Standard with the SSRS servers running Enterprise Edition is a perfectly valid and perfectly supported configuration.

But My Reseller Told me Something Else

I’ve head all sorts of incorrect licensing information from resellers. Frankly they are usually fine for Windows licensing information, but they tend to get the SQL Server licensing information pretty wrong. I don’t know if it’s because they simply don’t know it very well, or that they are giving you bad information to get you to buy the licenses for the SQL Server.

My guess is that it’s the latter of those options. If you are building a new platform and your SQL Server Licensing assumptions of 8 cores (our example earlier was an 8 core SQL Server, and 3 VMs with 4 cores each for SSRS) suddenly go to 20 cores, that’s going to more than double your price. So the licensing salesperson may give you some bad advice in order to get you to buy those 8 licenses.

I’m guessing that it you look at your contract with your licensing provider it holds them harmless if they give you bad advise on which software licenses you need to purchase. That means that if/when you get audited by Microsoft you’ll be the one of the hook for the extra licenses, not them. So be careful and make sure that you are correctly licensing all of your servers running the SQL Server product, as you could be in a lot of pain if you don’t license correctly.

Editions

Like SQL Server, SSRS comes in both Standard and Enterprise Editions. If you are running SSRS on the same server as your database engine, then you’ll want the editions to be the same. If you are putting SSRS on it’s own server (which is HIGHLY recommended) then I would highly recommend SQL Server Standard Edition. There’s a couple of advanced things that you get with SSRS Enterprise Edition, but unless you are using those specific features, or you want scale out, Standard Edition will do.

If your users already have SQL Server CALs, and you are using the Standard Edition of SSRS, then get the Server+CAL license of SQL Server for the SSRS server. There’s no need for a CAL based license unless you really need one. I say this, because the Server+CAL license of SQL Server is something like $800 US (don’t quote me on that). If your users don’t have CALs already, how many users will be using the system. Does is make sense to get them CALs, or does it make sense to get a Core based license.

Some math is going to be needed in order to figure out which license is going to be the best one for you to get.

Denny

Contact the Author | Contact DCAC

Hide and Group Columns in SSRS Using a Parameter

Ever had users come to you and request another version of a report just to add another field and group data differently? Today, was such the day for me. I really don’t like have multiple versions of the same report out there. So, I got a little fancy with the current version of the report and added a parameter then used expressions to group the data differently and hide columns. For those new to SSRS I’ve embedded some links to MSDN to help you along the way.

Current Report

The report gives summarized counts by invoice date.  It currently has a ROW group using date_invoiced and the detail row is hidden from user.

current-report

row-group-2

group-exp3

New Version

To complete the user request to have Item Codes and Descriptions added to the report I need to find a way to group the data by Item and show Item columns without disturbing the current report that is currently used by many consumers.

To Do:

  • Add Parameter
  • Set Available Values
  • Set Default Values
  • Add New Columns
  • Change Visibility
  • Change Grouping to group data using parameter

Step 1: Add Parameter

add-para-4

 Step 2: Set Available Values

add-values-5

Step 3: Set Default Values – I want to make sure my current users get their version of the report simply, so I set it to No (N).

add-default-6

Step 4: Next Add Columns.  I was lucky that the fields (Item Code, Item Desc) the user requested to be add was already part of the dataset used, so no additional coding was needed on the stored procedure.

add-fields-7

Step 5: Next change the Visibility attributes. You want to HIDE the column when the IncludeItemDetails parameter is NOT YES (Y). I did this for both item columns.

visibility-8

visibility-9

Step 6: Next I needed to change the grouping. The report is currently group by date_invoiced only. To make the data now total by Item I need to group it by Item only when the IncludeItemDetails parameter is Yes (Y). I did this using an IIF expression setting it to IF IncludeItemDetails=Y then group using field value else don’t (0). Again I did this for both fields.

grouping-10

expression-11

espression-12

You will see it’s relatively simple to do, and prevents a whole new report version from being created. For you beginners out there, it’s a very easy way to start to minimize the number of reports you have to maintain. Try it.

 

 

Contact the Author | Contact DCAC

Recommended reading from mrdenny for January 02, 2015

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: PASS_RM_Canada also known as PASS RM Canada

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny

Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers