TSQL2SDAY #101 Invitation: My Essential SQL Server Tools

tsql2sdayThe Why

If you’re not familiar, T-SQL Tuesday is a blogging party hosted by a different person each month. It’s a creation of Adam Machanic (b|l|t), and it’s been going on for ages now! Basically the host selects a topic, defines the rules (those are almost always the same), and then everyone else blogs about said topic. Once the deadline is reached, the host summarizes each of the submitted posts on their site/blog.

T-SQL Tuesday #101

This is the second time I host a T-SQL Tuesday, and hopefully not the last

The What

This month I get to pick the topic, and I am going to go with:

The Essential SQL Server Tools in my stack

Besides SQL Server Management Studio and Visual Studio Data Tools we all have our own set of tools that we use for everyday chores and tasks. But how do we get to know which tools are out there, if not for other professionals telling us about them? Does it have to a fully fledged with certification and all? Certainly not! If there’s some github project out there, that is helping you be double as productive, let us know about it. You can even boast about something you’ve built yourself – if you think others will benefit from using it.

Basically I think, that by establishing awareness about what kinds of tools that are out there, new professionals will not have as steep a curve getting the pace up, as they would have had. But I suspect that even some veteran guys could have an “a-ha” moment from reading the summary.

Additionally, you can (read: should) share how you came to depend on said tool – and of course you are encouraged to give credit, where credit is due in terms of making you aware of the tool.

Another approach for this topic, is to approach it as kind of A Day in the Life of kind of blog post, as has been done before by Erin Stellato (b|l|t). Writing with the specific angle to describing how your everyday is made easier by the use of your tool stack.

The How

There’s only a few rules for T-SQL Tuesday:

  • Your post must be published on Tuesday April 10th 2018 between 00:00 GMT  and 23:59 GMT.
  • Your post must contain the T-SQL Tuesday logo (see above) at the top and the image must link back to this blog post.
  • Trackbacks should work, but if they don’t, please put a link to your post in the comments section so I (and everyone else) can see your contribution!
  • Tweet about your post using the #tsql2sday hashtag
  • Include “T-SQL Tuesday #101” in your blog post’s title.
  • Optionally add @vestergaardj to your tweet, to make it harder for me to miss 😉

If you want to host a topic of your own:

  • Contact Adam Machanic (b|l|t) and tell him you’d like to host a T-SQL Tuesday from your blog.


Azure Saturday hos Microsoft i Kgs. Lyngby

En lørdag i Kgs. Lyngby, i selskab med 35 talere, der kommer med massiv viden på hver deres område, for kun en plovmand. Det lyder næste for godt til at være sandt – men det er det ikke, og faktisk er det ikke kun om lørdagen der sker ting og sager.

Der vil være fire (4) forskellige workshops, for en merpris, d. 30. og 31. august og selve konferencen er d. 1. og 2. september. Forvirret? Interesseret?
Så se mere på http://azuresaturday.dk

Her er et lille udsnit af de indlæg der vil være at vælge imellem:



T-SQL Tuesday #87 – Fixing Old Problems with Shiny New Toys

Matt Gordon (b|l|t) is hosting this months TSQL2SDAY which is on the topic fixing an old issue/problem with shiny new toys. I am really happy about this topic, as it offers the opportunity to display not only the short comings of earlier versions, but also the progress made by the SQL Server team.

Story Line

My contribution to this blog party, is going to be about SQL Server 2016 SP1, which is the edition of SQL Server we implemented the solution on. Before I was part of the company, there had been numerous attempts to solve what was know as the Zero Sales Issue. Working with Retailer/POS data some customers are interested in identifying if a product didn’t sell on any given combination of date, product and store, and in such case calculate some sort of lost potential for that store (on that specific date for that specific product). For some of our customers, this quickly becomes a super massive matrix, as we serve 3.500 stores on top of (for some) app. 5.000 products. The calculations were to be conducted on a two year running period (730 days). With this example we end up with a combination of 3.500 store x 5.000 products x 730 days = 12.7B rows, just for this particular costumer; We have potentially (at the moment of writing) 50+ customers.
Generating a table of this magnitude, along filling in the gaps of the days that actually have sale was previously a too time consuming task to offer this kind of analysis in our portfolio. Enter SQL Server 2016.

With SQL Server 2016 we were able to generate the table and fill in the blanks that was needed in order to do the calculation (Yay Window Functions!). After that, we are offering not only one (1) but three (3) different calculations on top of the data. Whenever a blank (a case of zero sale) is encountered, we calculate the average sales value of the same product in the same store over the last 7, 14 and 28 days. In addition to this, we also add a filtering mechanism, such that the client can focus on products that are “normally” selling on all days in the selected period. Products that are sold on rare occasions are not displaying the issue of Zero Sale, as this is supposed to identify if and when a store fails to offer the product in question. Empty shelves for a top selling product I think everyone can acknowledge is a serious issue.

Tech specs

The setup is sort of attached to our regular data import and is split out on a separate server of its own. We are currently migrating from on-premises to a more cloud based solution. Not sure when we will be fully in the cloud, in time I guess.

The server itself is a pretty standard setup, currently running on a Standard DS13 v2 Virtual Machine in Azure (8 cores and 56 GB memory). On top of that we’ve added a couple of striped disks in order to serve both data and sql-temp operations better. Read more on how to stripe disks on an Azure VM here.

This about covers the “hardware” specs of this setup and the rest is comprised of some in-memory tables, column store indexes as well as a fairly straight forward SSIS package.


In previous attempts (years back) the time consumed by doing the calculations was way over limit, hence the product/feature wasn’t offered to the market. This is particularly troublesome if it’s a client request. With column store indexes as the main contributor we are now able to accommodate for this type of calculation although not on the fly, in a more reasonable time frame which is all good and well when your data update is on a weekly basis.


Speaking at #SqlNexus – May 2017

I received notice last night, that the program committee has selected my abstract for the SqlNexus event coming up in May.
The event itself is the biggest SQL related event in Denmark (maybe in the Nordics) and was visited by over 500 attendees last year. There was a ton of great sessions, pre-cons and community happenings (Kahoot, Beer etc.). So if you are anywhere around Copenhagen in May and into SQL Server, you should definitely attend – register here.

My session will be about DevOps for BI. In more detail this session will cover the main three (3) tools of the MS BI stack; SSIS, SSRS and SSAS. Each of these tools produce an output/artifact for deployment, and the main goal is to make this available to the end-user with as little hustle as possible. For each of the three, there is a separate approach, which we will go over in detail in this session; How to build and deploy each of the components and how to automate this, all with Microsoft tools such as Visual Studio Online, Team Foundation Server, PowerShell to name a few.

A part of the session will cover the not so technical part of the transition, that the organization needs to address, in order to support what is quickly becoming the new black.

Here is the session abstract:

If you are releasing database changes, new reports, cubes or SSIS packages on a regular basis, you’ve probably offered up your share of blood, toil, tears and sweat on getting them delivered into production in working condition.

DevOps is a way to bridge the gap between developers and IT professionals and for that we need to address the tool-chain to support the practices. Microsoft offers a set of tools that’ll help you on your journey towards the end goal: Maximize predictability, efficiency, security and maintainability of operational processes.

We will in detail be looking at:

Agile Development Frame of Mind

  • Visual Studio Online (tool)
  • Feature/PBI/WI (concept)

Team Foundation Server

  • Code Branching (concept)
  • Build Agents (tool)


  • Microsoft’s Glue (tool)



Looking back at my 2016 Community Engagement

2016 was one of my most productive years in terms of blogging (34 posts), public speaking at several SqlSaturdays as well as UG’s (meeting some great people out there), community engagement and session selection committee for SqlSaturday, SqlNexus and Pass Summit. I certainly hope to improve my efforts and engagement in 2017, but I am also well aware, that this might just well be the maximum of what I can squeeze in, having a family w/ three wonderful kids to look after as well for the time being. They do take a lot after me, so I also hope to introduce some of them as co-programmers on select projects the next year.

A few years back…

Here is the list of my blog posts from the year 2016, where I also managed to complete this challenge that Tim Ford (b|l|t) put out in January 2016 as well as participate in a couple of tsql2sdays (and host one too).

Q4 (10 posts)

T-SQL Tuesday #85- SSAS(MD) Backup Galore
SqlSaturday Prague Round-Up
How to fire up Azure Analysis Services
Which Database Servers are hit from my Analysis Services Instance(s)
How to back up your VM’s in Azure on a schedule
Testing your SSAS partitioning strategy w/ PowerShell
Speaking at #SqlSat569 / #SqlSatPrague
Raspberry PI Halloween Project: Creepy Greeting Skull
Testing Your #SSAS Cubes w/ Visual Studio
Continuous Delivery w/ Analysis Services and Visual Studio Online

Q3 (3 posts)

#SqlSaturday Denmark – September 17th 2016
Creating a Striped Disk on Your Azure VM
Speaking at #SqlSatGothenburg

Q2 (10 posts)

#SqlSatDublin (#SqlSat501) – ReCap
SQL Server 2016 RTM available as Azure VM
Slides from Pro Techniques Talk at MsBIP
How to Query SSASMD using #DAX
Speaking at Microsoft BI Professionals Denmark
How to Quickly Remove Snippet Line Numbers in Visual Studio
#TSQL2SDAY #77 – Favorite SQL Server Feature – RoundUp
Do You Speak Azurian?
Invitation to #TSQL2SDAY #77 – Favorite SQL Server Feature
New EU Legislation potentially cripples #SQLServer2016

Q1 (11 posts)

SSAS Compare by Red-Gate
Danish SQL Server 2016 Launch Event – SQLNexus
Unpacking the Raspberry PI 3
Using PowerShell to get current Data/Log/Temp and Backup Directories from SSAS
Filtering Tables in #SQLServer Management Studio
My First IoT Project – Part 1
Speaking at #SqlSat501 in Dublin
Jump into #PowerBI
A Hello World Custom Assembly in SSAS
Permission Scope in Analysis Services