SQL Server Reporting Services Licensing

Published On: 2020-07-06By:

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

New Cartoon Friends that Will Be Traveling With the DCAC Team

Published On: 2020-06-29By:

As the DCAC team has continued to evolve we’ve decided that we need some friends to help us talk about the fact that indexes will make your databases faster.

So I’d like to take a minute to introduce you to Sheamus our crazy wizard, Casandra our playful unicorn (she also goes by Cassie), Molly our intense unicorn, and Peggy our adorable Pegasus. All of our new friends are having stickers made of them which the entire DCAC team will have with them when in-person events start back up; and you may see them pop up in slide decks before then.

Our New Friends

Sheamus is going to perform some white wizard (Lord of the Rings) style tuning on your databases to get them performing perfectly.

Casandra (or Cassie to her friends) is having tons of fun making workloads go faster and showing off her skills.

Molly has had one too many developers explain that indexes are horrible for write performance, but she’s here to fix the performance of your database anyway because Indexes Are Magic.

Peggy is going to fly in, fix your databases, and fly away; all while looking adorable and awesome in the process.

Be sure to find someone from DCAC at your next in-person event and grab some stickers from us of all our new friends.

But Wait There’s More

If you can’t wait for an in-person event, we’ve got you covered. We’re holding a raffle that’s open to everyone where raffles are valid (if you have a skills test to win like Canada we’ll handle that), where we will send 25 people a full set of stickers totally free, anywhere in the world, just for entering our raffle.

(Government employees, the prize has no cash value, but please check with your compliance office before accepting the prize.)

Denny

Contact the Author | Contact DCAC

Encrypting Stored Procedures Doesn’t Make Me Avoid Looking at Your Code

Published On: 2020-06-22By:

Dear Vendors that encrypt stored procedures in SQL Server,

Stop It!

We were having a discussion on Twitter about vendors encrypting stored procedures recently, and this justification came up that had been told about why vendors encrypt stored procedures some times.

To this I point out, that if you’ve encrypted your code so that I won’t look at it by accident, you are actually getting the exact opposite result. Because you are encrypting code that means that I can’t see if. That means that I want to make sure that you aren’t hiding any stupid practices from me. That means that as soon as I see your encrypted procedures I’m decrypting them to see what is going on with this code.

Along with this, because you’ve bothered to encrypt the stored procedures that means that I can’t get an execution plan, and query store can’t be used for the queries within the stored procedure. And since I’m guessing that I can performance tune your database better then your developers can, I’m going to be decrypting the procedures so that I can tune the system.

But It’s Our IP

Another reason that companies have for encrypting stored procedures is that the code is their IP. Does the vendor have a patent or trademark on their code? The answer to this is no, as you can’t patent or trademark the actual code, and anyone that tells you that they can trademark their code, is wrong.

If you have given me your code, then I can look at it. If we have an NDA in place then I can’t use the code. If there’s no NDA in place, then I can use your code all I want. You at that point have no legal way of stopping me.

If you want to stop supporting the software that we have purchased from you, is there something in the EULA about us not decrypting the stored procedures? No, then support it or get risk getting sued for violating the EULA and support contract. And assume that you’ll be loosing us as a customer and that your competitor will be gaining us as a customer (yes I’ve done this at companies, so it’s not an empty threat).

Running Code

When you sell your customer a license to run your software you are giving the customer your code, and you have to trust the customer to not use your code in order systems. The customer has to trust that you are providing them a system that will perform well. You usually don’t and someone has to to some performance tuning on the application that you provided in order to keep their business up and running. If you hamper or slow that process down, expect to have your application replaced in short order. No application is so critical to a business that it can’t be replaced, so make it so that your application doesn’t make the live of the people that have to actually maintain it suck less.

Denny

Contact the Author | Contact DCAC

Moving Your WordPress Cron to Azure Automation

Published On: 2020-06-15By:

If you have an extremely popular WordPress site you want to optimize it to get each page load on it as fast as possible. One way to do this, is to change the way that Cron works in WordPress. We can do this by making it to so Cron is checked every few minutes instead of every time a visitor views your page.

How Cron Works

Cron in WordPress is a little different than Cron in Linux. In Linux there’s a file called Cron.tab that stores how often jobs are run. WordPress has its own version of Cron that is stored within the WordPress database.

By default, with an “out of the box” install of WordPress Cron runs every time a page is viewed on your website. So when there is a task to run, the users page request may be slightly delayed while cron is running.

The thing to remember about Cron in WordPress is that is isn’t exact in its timings. Because Cron only runs when a page is viewed, if there’s no viewers on your website, then Cron won’t run until the next viewer of your website.

Moving Cron to Azure Automation

For sites where you don’t want to tie the Cron process to page views, either because you want to reduce the load on your site, or you want to increase the frequency of when Cron runs, you can do this pretty easily.

The first step is to tell WordPress to stop running Cron on every page load. You do this in the wp-config.php. You do this by simply adding a new line into this file with the text “define(‘DISABLE_WP_CRON’, true);” as the text of the line, as shown in this image from our website’s wp-config.php.

At this point there’s basically no Cron on your website, so we want to fix this. We do this via Azure Automation as we don’t want to stand up a VM to simply fire off Cron on our website. First we will need to log into the Azure portal at portal.azure.com. From there select “Automation Account” as the service that you want to use. If you don’t have an Automation Account you’ll want to set one up. If you already have one you can either use that one, or a new one.

The runbooks option in the menu

Select the Automation Account that you want to use to trigger Cron, and then select RunBooks from the menu on the left. It should be the first thing listed under Process Automation (unless things have changed since July, 2020, it may have moved).

Selecting your runbook name and the type of PowerShell

Click “Create a runbook” at the top to create a new runbook. Give the runbook a name (don’t forget that you can’t use spaces) and set the type to “PowerShell” and then click the “Create” button down at the bottom.

Give the page a minute and the editor will open. The code that we want to run is pretty straight forward. We simply call the wp-cron.php page on your website, and that will run any pending cron tasks.

$WebResponse = Invoke-WebRequest "https://www.yoursite.com/wp-cron.php" -UseBasicParsing
$WebResponse

Once you have the RunBook created you’ll want to test it to make sure that the code works as expected. If you click the “Test Pain” button you can then run the code in the Azure Automation environment. It should return something like the screenshot that’s shown below.

An example of the job running successfully

Once that is done you’ll want to bind some schedules to your Cron. Now something to keep in mind is that you only get 500 minutes of Azure Automation each money, and there are ~730 hours in each money. So if you schedule this to run every hour, it’ll cost you a few cents (US) per month to have this job.

When I set this up, I configured the job to run 6 times an hour (every 10 minutes) so I had 6 schedules created as the most often a schedule will run is every hour. This solution caused the monthly cost to be about $6.

Frequency

How often you might need to run the Cron is really up to you. Cron controls pretty much anything scheduled. That includes publishing new posts and pages, running any automated backups that you have configured in WordPress, or any scheduled jobs from plugins. So depending on your needs to may want to run this more than every 10 minutes or less than every 10 minutes. It’s really up to you and the needs of your website.

Based on the frequency you have this setup, you should get results of the overview blade for the Automation Account pretty quickly showing that the job is running successfully. If it’s not, a little troubleshooting will be needed, but this was a pretty quick change over for me.

Denny

Contact the Author | Contact DCAC
1 2 3 360

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
American Business Awards Gold Award    American Business Awards Gold 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