Press "Enter" to skip to content

Month: January 2018

Blue-Green Deployments

Published 2018-01-09 by Kevin Feasel

Michael J Swart has started a new series on online deployments and covers the blue-green deployment architecture:

When using the Blue-Green method, basically nothing gets changed. Instead everything gets replaced. We start by setting up a new environment – the green environment – and then cut over to it when we’re ready. Once we cut over to the new environment successfully, we’re free to remove the original blue environment. The technique is all about replacing components rather than altering components.

Check it out for a great explanation, not only of how true blue-green doesn’t jibe well with databases, but how to get it to work well enough.

Comments closed

Goal Tracking With Power BI

Published 2018-01-09 by Kevin Feasel

Stacia Varga uses New Year’s resolutions to motivate a Power BI tutorial:

As I was thinking about this relationship between goals and feedback, I thought Microsoft Power BI would be a great tracking tool. It’s free, so use it! In years past, I used spreadsheets or checklists in journals or OneNote, any of which is a fine way to accumulate a comprehensive list of all that a person wants to do. However, I never measured progress, thereby denying myself feedback. Consequently, I’d let myself get sidetracked during the year.

This year I promised myself I’d try a different approach and thought I’d share the process with you through a series of blog posts. Although I’m going to discuss goal-tracking from a personal point of view, you can also use the same techniques for your business-oriented goals. Either way, I hope you learn something about Power BI along the way and are inspired to do some goal-setting of your own.

It’s a good use of Power BI.

Comments closed

Columnstore Functionality Per Edition

Published 2018-01-09 by Kevin Feasel

Niko Neugebauer looks at how columnstore indexes differ between SQL Server Standard Edition, Express Edition, and Enterprise Edition:

One rather small (relatively other features, as I imagine), but an incredibly useful improvement was described in Columnstore Indexes – part 109 (“Trivial Plans in SQL Server 2017”) – is the ability to automatically produce Fully Optimised execution plans for the Database, which compatibility level is set to 140.

Running on both instances (Standard & Express), the following script, while altering the compatibility level between 140 (SQL Server 2017) & 130 (SQL Server 2016), will produce different execution plan for the SELECT COUNT_BIG(*) operation – the fast one (with FULL optimisation in 140 compatibility level) and slow one (with TRIVIAL optimisation in 130 compatibility level):

I am happy that this feature has got no Edition dependence, this is a needed improvement that simply increases the value of the offer and can actually be achieved in a lot of different ways, event without parallelism kicking in.

Niko has also helpfully provided a table at the end of the post to summarize his findings.

Comments closed

2018 Data Professional Survey Results

Published 2018-01-09 by Kevin Feasel

Brent Ozar has posted data for the 2018 Data Professionals Survey:

A few things to know about it:

  • The data is public domain. The license tab makes it clear that you can use this data for any purpose, and you don’t have to credit or mention anyone.

  • The spreadsheet includes both 2017 & 2018 results. For the new questions this year, the 2017 answers are populated with Not Asked.

  • The postal code field was totally optional, and may be wildly unreliable. Folks asked to be able to put in small portions of their zip code, like the leading numbers.

Looks like I’m going to add one more thing to the to-do list for this week…

Comments closed

Know Your Audience

Published 2018-01-08 by Kevin Feasel

I continue my series on dashboard visualization:

Before you build a dashboard, you have to know your audience.  If you don’t know who your viewers will be and where their interests lie, you run the risk of building a dashboard which fails to serve their needs.  When that happens, people stop looking at your dashboard.  In order to increase the likelihood that your dashboard will be useful, I have a few critical questions:

  1. Who is your intended audience?
  2. How will your intended audience use your dashboard?
  3. What actions do you want them to take as a result of what they see?
  4. Are you showing the right measures in the right way?
  5. What cultural differences might matter?

The rest of this post will drill into each of these concepts.

These are the types of questions which can make the difference between a dashboard people love and a dashboard people never use.

Comments closed

Voice-Driven Workflow With Apache MiNiFi

Published 2018-01-08 by Kevin Feasel

Tim Spann shows how to take off the shelf hardware and build a voice-activated workflow:

Using a Raspberry Pi 3 with Google AIY Voice Kit is an easy way to control data flows in an organization.

The AIY Voice Kit is a good way to prototype voice ingestion. It is not perfect, but for low-end, inexpensive hardware, it is a good solution for those who speak clearly and are willing to repeat a phrase a few times for activation.

I was able to easily add a word for it to react to. I also have it waiting for someone to press the button to activate it.

It seems like a fun DIY project.

Comments closed

More SSMS Tips

Published 2018-01-08 by Kevin Feasel

Wayne Sheffield continues his series on SSMS tips and has four new posts for us.

Check out his posts on pinned tabs, easily opening or copying query file paths, template variables, and map mode:

Have you ever had a long script that you are trying to scroll through? Do you wish that you could see a preview of the scroll area to easily see what section you are in? Well, starting in SSMS 2016, you can. Just right-click the vertical scroll bar, and select “Scroll Bar Options…”

Of the four, map mode is my favorite.  It’s extremely helpful when going through large files.

Comments closed

Inventive Uses Of Python In SQL Server 2017

Published 2018-01-08 by Kevin Feasel

Gerald Britton has a couple non-ML uses for Python in SQL Server 2017:

One of the new features announced with SQL Server 2017 is support for the Python language. This is big! In SQL Server 2016, Microsoft announced support for the R language – an open source language ideally suited for statistical analysis and machine learning (ML). Recognizing that many data scientists use Python with ML libraries, the easy-to-learn-hard-to-forget language has now been added to the SQL Server ML suite.

There’s a big difference between R and Python though: R is a domain-specific language while Python is general purpose. That means that the full power of Python is available within SQL Server. This article leaves ML aside for the moment and explores a few of the other possibilities.

Gerald has two good cases for using Python with SQL Server.  Funny enough, they’re both also easily supported in R, so you could do this in 2016 as well.

Comments closed

Permissions And Dynamic SQL

Published 2018-01-08 by Kevin Feasel

Eric Blinn shows that dynamic SQL in stored procedures changes the security paradigm a bit:

Security was controlled by granting EXECUTE permissions only to appropriate stored procedures and by explicitly not granting permission to any tables or views within the database.

One of the procedures was getting a bad query plan and timing out. This is when I was called in. The procedure was performing a search based on an unknown number of up to 10 search parameters. The code was filled with many AND/OR combinations to account for the users’ ability to include any combination of search parameters. I found this procedure to be a prime candidate for dynamic SQL where I would create the select statement including only the search parameters entered by the user into the WHERE clause.

Immediately upon testing the users started to receive SELECT permission denied errors. It turns out that when you change to dynamic SQL and call your statement through sp_ExecuteSQL that the permissions are lost. Our options were to grant explicit select permission on the objects or to refactor the code such that it does not use dynamic SQL anymore.

The best solution here would probably be to use a certificate to sign the procedure and give that certificate user rights to select from the tables used in dynamic SQL.

Comments closed

Logging Variable Values In SSIS Packages

Published 2018-01-08 by Kevin Feasel

Andy Leonard shows two methods for logging variable values in Integration Services packages:

During the December 2017 delivery of Expert SSIS, I was asked if there is an SSIS Catalog Logging Mode that will display the value of variables. I responded that I wasn’t aware of a logging level that accomplishes this, but then – as sometimes happens – I could not let it go. It is an excellent question. So I dug in…

I second his notion that you should learn how to use script tasks and script components.  They’re not very difficult to pick up and even a basic knowledge will benefit you greatly.

Comments closed