You Should Push Back on Your Software Vendors

Published On: 2020-07-09By:

I’ve seen two twitter discussions in the last two days about terrible software vendor practices. The first was a vendor who wanted to install binaries on the C: drive (and only the C: drive) of the server hosting the SQL Server database for the application. The other was a vendor who didn’t support using replication to another database to report against their database. Both of these scenarios are terrible–database servers should really only run database services, and it’s none of your software vendor’s business as to what you do with your transaction log.

blue jeans
Photo by VisionPic .net on Pexels.com

Speaking of the software vendor’s business, let’s talk about the business model of software vendors. For the most part, smaller vendors don’t make their profits on your initial purchase of your licenses, instead they charge an annual maintenance fee (sometimes they have different names for it, like support, or in the case of Microsoft Software Assurance), As part of this agreement, you are typically entitled to patches, security fixes, new versions of software, and in some cases support tickets. In order to stay supported, you need to agree to a certain set of requirements from the vendor.

This is a lucrative business–software has very high profit margins, making it a target for investors, private equity, and venture capital. The latter two of those can do bad things to otherwise good companies in order to try to extract every penny of profit out of them. This can include laying off core engineering staff, and replacing them with much cheaper offshore resources, who while good engineers, aren’t familiar with the core architecture and more importantly use cases of the product. They may also cut testing resources, so the program is only “certified” on older versions of database and operating system software. Private equity has done terrible things to a lot of businesses and software isn’t exempt from that. Read this article about a company that has acquired a bunch of zombie independent software vendors (ISVs) and just milks support fees for profit. Seriously, read that article.

While there are some good ISVs out there, they are few and far between, but at all times you need to remember that you are their customer, and they work for you. That doesn’t mean you can yell at a support engineer on the phone, but when they tell you that you can only run their software on SQL Server 2005 running on Windows 2003, and oh yeah, they need the SA account for their application to run, you should push back.

A lot of DBAs I encounter are too timid to do this–the business needs some app for manufacturing widgets, and the widget team just wants that app even though the vendor insists that the backups need to be shipped to a server in Russia. I will say this–pick you battles–it’s not worth to argue about something like a MaxDOP requirement (unless there’s an obvious performance problem there), but when the vendor says something like you can’t use Availability Groups with their app, or wants to trap you onto a legacy version of the RDBMS, you should push back. The other #sqlhelp thread I saw was where someone wanted to build a log reader to apply transactions to a secondary database for reporting, because the vendor didn’t support replication. That’s stupid–you’d be building a very fragile system, when SQL Server has a perfectly good feature (transactional replication) to do the same thing. Or even the vendor who wanted to install software on the SQL Server. No. Just say no.

In summary–you own your software, and you manage your database environment. You shouldn’t do anything that puts your business’s data at risk, but at the same time, you want to manage your environment in as consistent a fashion as possible, while adhering to best practices. https://www.forbes.com/sites/nathanvardi/2018/11/19/how-a-mysterious-tech-billionaire-created-two-fortunesand-a-global-software-sweatshop/#38fe205e6cff

Contact the Author | Contact DCAC

Refreshing a Power BI Dataset in Azure Data Factory

Published On: By:

I recently needed to ensure that a Power BI imported dataset would be refreshed after populating data in my data mart. I was already using Azure Data Factory to populate the data mart, so the most efficient thing to do was to call a pipeline at the end of my data load process to refresh the Power BI dataset.

Power BI offers REST APIs to programmatically refresh your data. For Data Factory to use them, you need to register an app (service principal) in AAD and give it the appropriate permissions in Power BI and to an Azure key vault.

I’m not the first to tackle this subject. Dave Ruijter has a great blog post with code and a step-by-step explanation of how to use Data Factory to refresh a Power BI dataset. I started with his code and added onto it. Before I jump into explaining my additions, let’s walk through the initial activities in the pipeline.

ADF pipeline that uses web activities to gets secrets from AKV, get an AAD auth token, and call the Power BI API to refresh a dataset. Then and Until activity and an If activity are executed.
Refresh Power BI Dataset Pipeline in Data Factory

Before you can use this pipeline, you must have:

  • an app registration in Azure AD with a secret
  • a key vault that contains the Tenant ID, Client ID of your app registration, and the secret from your app registration as separate secrets.
  • granted the data factory managed identity access to the keys in the key vault
  • allowed service principals to use the Power BI REST APIs in in the Power BI tenant settings
  • granted the service principal admin access to the workspace containing your dataset

For more information on these setup steps, read Dave’s post.

The pipeline contains several parameters that need to be populated for execution.

ADF pipeline parameters

The first seven parameters are related to the key vault. The last two are related to Power BI. You need to provide the name and version of each of the three secrets in the key vault. The KeyVaultDNSName should be https://mykeyvaultname.vault.azure.net/ (replace mykeyvaultname with the actual name of your key vault). You can get your Power BI workspace ID and dataset ID from the url when you navigate to your dataset settings.

The “Get TenantId from AKV” activity retrieves the tenant ID from the key vault. The “Get ClientId from AKV” retrieves the Client ID from the key vault. The “Get Secret from AKV” activity retrieves the app registration secret from the key vault. Once all three of these activities have completed, Data Factory executes the “Get AAD Token” activity, which retrieves an auth token so we can make a call to the Power BI API.

One thing to note is that this pipeline relies on a specified version of each key vault secret. If you always want to use the current version, you can delete the SecretVersion_TenantID, SecretVersion_SPClientID, and SecretVersion_SPSecret parameters. Then change the expression used in the URL property in each of the three web activities .

For example, the URL to get the tenant ID is currently:

@concat(pipeline().parameters.KeyVaultDNSName,'secrets/',pipeline().parameters.SecretName_TenantId,'/',pipeline().parameters.SecretVersion_TenantId,'?api-version=7.0')

To always refer to the current version, remove the slash and the reference to the SecretVersion_TenantID parameter so it looks like this:

@concat(pipeline().parameters.KeyVaultDNSName,'secrets/',pipeline().parameters.SecretName_TenantId,'?api-version=7.0')

The “Call Dataset Refresh” activity is where we make the call to the Power BI API. It is doing a POST to https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/refreshes and passes the previously obtained auth token in the header.

This is where the original pipeline ends and my additions begin.

Getting the Refresh Status

When you call the Power BI API to execute the data refresh, it is an asynchronous call. This means that the ADF activity will show success if the call is made successfully rather than waiting for the refresh to complete successfully.

We have to add a polling pattern to periodically check on the status of the refresh until it is complete.

We start with an until activity. In the settings of the until loop, we set the expression so that the loop executes until the RefreshStatus variable is not equal to “Unknown”. (I added the RefreshStatus variable in my version of the pipeline with a default value of “Unknown”.) When a dataset is refreshing, “Unknown” is the status returned until it completes or fails.

ADF Until activity settings

Inside of the “Until Refresh Complete” activity are three inner activities.

ADF Until activity contents

The “Wait1” activity gives the dataset refresh a chance to execute before we check the status. I have it configured to 30 seconds, but you can change that to suit your needs. Next we get the status of the refresh.

This web activity does a GET to the same url we used to start the dataset refresh, but it adds a parameter on the end.

https://docs.microsoft.com/en-us/resGET https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/refreshes?$top={$top}

The API doesn’t accept a request ID for the newly initiated refresh, so we get the last initiated refresh by setting top equal to 1 and assume that is the refresh for which we want the status.

The API provides a JSON response containing an array called value with a property called status.

In the “Set RefreshStatus” activity, we retrieve the status value from the previous activity and set the value of the RefreshStatus variable to that value.

Setting the value of the RefreshStatus variable in the ADF pipeline

We want the status value in the first object in the value array.

The until activity then checks the value of the RefreshStatus variable. If your dataset refresh is complete, it will have a status of “Completed”. If it failed, the status returned will be “Failed”.

The If activity checks the refresh status.

If activity expression in the ADF pipeline

If the refresh status is “Completed”, the pipeline execution is finished. If the pipeline activity isn’t “Completed”, then we can assume the refresh has failed. If the dataset refresh fails, we want the pipeline to fail.

There isn’t a built-in way to cause the pipeline to fail so we use a web activity to throw a bad request.

We do a POST to an invalid URL. This causes the activity to fail, which then causes the pipeline to fail.

Since this pipeline has no dependencies on datasets or linked services, you can just grab my code from GitHub and use it in your data factory.

Contact the Author | Contact DCAC

T-SQL Tuesday #128: Learn From Others

Published On: 2020-07-06By:

Pilots do something that a lot of non-pilots will find fairly weird if not outright horrifying: We read accident (“crash”) reports. Some of us spend a lot of time reading accident reports, actually. Officially “Accident Reports”, these are put out by the US National Transportation Safety Board (NTSB) after investigation into a crash or an “incident.” In addition to aviation-related reports, there are highway and railroad reports, and even hazardous materials incidents.

Reports come in two flavors, a “preliminary” report, and ultimately, a “final” report after the investigation has completed. The final reports includes such items as conclusions and the probable cause of the accident or incident. To make life easier, they also include a Recommendations section, which, well, includes recommendations for how to keep this type of accident from happening in the future. These tend to be regulatory in nature, as they are geared towards the FAA.

The search form for aviation reports is here–https://www.ntsb.gov/_layouts/ntsb.aviation/index.aspx–if you’re, uh, thinking you want to get into this sort of thing.

Why do pilots do this? The rationale is pretty simple: To learn from the mistakes of others. Or, to learn how a bad day was kept from becoming a worse day after something broke.

What Does This Have to Do With SQL Server?

Great question. Besides the fact that I think piloting airplanes and DBA-ing are the same job, just with different scenery,  I wish we had this kind of transparency in the IT world when things went wrong. When a corporation has a big security incident, we’re likely not to hear a lot of details publicly about what went wrong and what was done to mitigate similar attacks in the future. This kind of information could help everyone. This is one of the things that cloud providers do quite a bit better: When something breaks, we get good information on what happened, why, and what’s going to be done about it. Of course, this is done because public cloud providers basically have to–if things went down a lot and we never heard why, that provider probably wouldn’t have a lot of customers for very long.

This brings me to T-SQL Tuesday.

Tell me (us all, obviously) about something recently that broke or went wrong, and what it took to fix it. Of course, the intent here would be for this to be SQL Server-related, but it doesn’t have to be. We can all learn from something going wrong in infrastructure-land, or how there was a loophole in some business process that turned around and bit somebody’s arm. It doesn’t even have to be all that recent–maybe you’ve got a really good story about modem banks catching on fire and that’s how you found out the fire suppression system hadn’t been inspected in years. Just spitballin’ here. If you’ve got an incident whose resolution can help someone else avoid the same problem in the future or improve a policy as a preventative measure, let us hear about it.

The Rules

Here are the rules as set out for the T-SQL Tuesday blog party.

  1. Your post should be published on Tuesday, 14 July, 2020 between midnight and 11:59:59 UTC/GMT/ZULU
  2. Include the T-SQL Tuesday logo in your post
  3. Link back to this invitation (usually done through the logo)
    (this will get syndicated, so link back to the original on airbornegeek.com, please)
  4. Include a comment on the invitation post or a trackback link
  5. Enjoy the chance to be creative and share some knowledge.

Contact the Author | Contact DCAC

SQL Server Reporting Services Licensing

Published On: By:

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
1 2 3 466

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
Share via
Copy link