Jeroen Smans

24 Mar 2022 Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options, but it requires a bit of tinkering. Tinkering that we gladly explain in this blog.

24 Feb 2022 Sorting matrices in Power BI

A client asked to track which content on SharePoint was performing well. One of their requests – sorting the matrices by different values – for the Power BI report turned out to be as tricky to implement, as it seemed straightforward when I first heard it. A rare occurrence! Let’s explore the solution to this conundrum.

08 Feb 2021 Defining your Azure infrastructure as code with Bicep

Azure Resource Manager templates are great. Managing the resulting JSON files not so much. So Microsoft launched Azure Bicep. Read how it will help with maintaining your infrastructure as code.

10 Aug 2020 How to send emails with an SMTP server in Azure Databricks

A client asked if we could provide a simple form of monitoring on a part of a provided solution. For this particular source, our client asked us to send a simple email with record counts to a mailing list. No problem! Let's get to work

18 May 2020 XML fun on Azure SQL

While XML has been suitable as a SQL Server native datatype since SQL 2005, for one reason or the other it’s not often used - with only a very few working examples. This blog post is meant for T-SQL teams looking for a working XML example on SQL Server, including schema binding and XML indexes.

17 Mar 2020 Kohera’s Modern Data Platform: Choosing the model – Pt.1

Kohera’s Modern Data platform does not come with a canned data model by default. This makes the framework extremely flexible to adapt to a variety of project workflows, but it also means during the Analysis phase we will need to think about how to structure the data before continuing. A blogpost.

30 Jan 2020 Should I use serverless for all my Azure SQL Databases?

Azure SQL Database serverless is a new compute tier made for single databases. This new model automatically scales the compute based on the actual workload per second. This means that you only have to pay for the compute resources (memory & vCores) you use. But there's more...

15 Jan 2020 How to create a Time Table in Power BI in a few simple steps

In a previous blogpost, we already explained how to make a date table in Power BI with DAX. Besides the dates you can add extra properties for each date: year, month-names, weekday-names,... But what about hours and minutes? In this blog and video, we'll show you how to create time tables in Power BI.

07 Jan 2020 De Tijdloze 2019: de follow-up

31 december was het zo ver, we zaten aan de radio gekluisterd om de Tijdloze op de voet te volgen. Op plaats 93 noteerden we onze eerste voltreffer en hoe dichter we bij de top 10 kwamen, hoe beter onze voorspelling . Negen van de tien nummers uit de top 10 hadden we voorspeld en drie hiervan hadden de voorspelde ranking.

27 Dec 2019 De Tijdloze van StuBru: onze Power BI-voorspelling

Waar we vorige jaren begin januari in actie schoten, staken we nu reeds in december de handen uit de mouwen. Aan de hand van een predictieve analyse doen we een poging om De Tijdloze 2019 te voorspellen, niet enkel de top 3 maar de ganse lijst.

12 Dec 2019 Custom email notification in Azure Data Factory

Tired of logging on to Azure Data Factory every day and checking the status of your pipeline(s)? In this blog, Data Wizard Wout Cardoen shows you how to develop an automatic email that alerts you when a pipeline fails.

03 Dec 2019 Creating a Secure Databricks Environment

Azure Databricks is a cloud native (Big) Data analytics service, offered as a managed PaaS environment. It’s designed to hide the underlying distributed systems and networking complexity as much as possible from the end user, so you can focus on developing rather than having to stress over infrastructure management.

18 Nov 2019 Azure Synapse Analytics: Where Azure DWH, Spark & ADF meet

When I started to jump from DBA to Data Architect, I thought that these roles would diverge further and further until they became two separate roles and functions. The current evolution Microsoft launched proved me wrong though. Enter Azure Synapse Analytics.

30 Oct 2019 Prevent lock escalation on indexed views

Recently our data wizard Thomas Costers was at a customer who created an indexed view, which spanned multiple tables as part of a release. Because you can't disable lock escalation for an indexed view as you can for a table, he went looking for another solution.

27 Jun 2019 How To video: Visualise your Fitbit data in Power BI

In our latest How To video, our BI wizard Frederik Aerts, integrates the data of his Fitbit into Microsoft Power BI. There are different ways to do it... Enjoy!!

30 Apr 2019 Does Persisting sampling rate work on Databases with compatibility levels below 130?

When you create or update statistics (without specifying a sample rate) the sample rate is automatically calculated by SQL Server. If you would want this statistic (or the whole table) to use a Flat sample rate, say 5 percent, then you could specify a persisting sample rate after updating your stats. Read all about it in this blog.

06 Mar 2019 SQL Server Plan Guides: A Walkthrough, Part 2

In the first part of this blog series on SQL Server plan guides, we looked into the basic plan guide setup. In this part we will be taking a closer look at somethings you cannot do as well, as how to add a plan guide to procedures, and how to find queries that are using plan guides.

12 Feb 2019 SQL Server Plan Guides: A Walkthrough Part 1

Plan guides are some strange things, and can be very high risk. This post does not have the intention to get all you fine people to implement plan guides everywhere, it should be one of the last resort options in your toolkit.

14 Jan 2019 Data Lake Security and Governance best practices

Data Lakes are the foundations of the new data platform, enabling companies to represent their data in an uniform and consumable way. In this document, we’ll describe how to setup a Data Lake in such a way that it will become the efficient Data Lake that users are looking for.

09 Jan 2019 Power BI and Python: scraping De Tijdloze 100 website

As you might know, Python is now available as a preview feature in Power BI. I used it to complete the data in our Power BI dashboard of the radio show "The Tijdloze 100". The data was mostly coming from a web-page, but in the latest edition all data was not available for Power BI.

07 Jan 2019 Update: De Tijdloze Power BI

Doe jij dat ook, op het einde van het jaar luisteren naar een top 100 of 1000? Bijna elke radiozender blikt terug met een muzieklijst van nummers van het voorbije jaar of telt af naar de grootse hit allertijden. Ondertussen hebben we ook de gegevens van de meest recente editie 2018 in ons Power BI rapport geladen.

02 Jan 2019 Migrating to newer SQL Server versions

SQL Server 2017 is not that new anymore, but, as with each new release of SQL Server, most people are hesitating to start migrating to this new version for a variety of reasons. Let's have a look at why you shouldn't hesitate to migrate....

01 Jan 2019 Validate your SQL environment the easy way

Every DBA probably knows and should even be using DBAtools stuff. Lately Chrissy Lemaire and her team also added DBAchecks, a validation tool, to it. A separate powershell module , based mainly on pester checks.

19 Dec 2018 Queries are running slower after migrating?

There's a solution for everything. If your queries are running slower after migrating, you can use the Query Store to force lower compatibility plans.The query store is a very easy way to track the queries happening in your database, ...

13 Dec 2018 When the SQL Server Agent overrides default behavior: QUOTED_IDENTIFIER

For a customer, I recently migrated one of their databases from 2012 to 2017, whilst putting compression on all the indexes. The process of recreating the Indexes on 8 new filegroups took 3 hours for a 400GB database.

07 Dec 2018 A Historically correct approach on the evolution of the Microsoft Data Platform

As we are nearing the end of a very interesting year, I was mesmerizing about the changes we saw this year. This took me back to what I’d later would call the very beginning of what would become my personal IT-Career, ...

28 Nov 2018 How to Automatically add measures to Power BI

Some time ago I wrote a blogpost to retrieve all the measures from your tabular cube or from your open Power BI files. I made a simple addition to this blog, enjoy! And don't forget to watch the how-to video.

21 Nov 2018 Connecting to an (Azure) database from within Databricks

I made a very useful ELT program in python and wanted it do run inside a Databricks cluster. Databricks on Azure fully supports python 3, so I thought I was up for a walk in the park. Trying to import the database connection classes, already gave a small hint of the troubles ahead...

20 Nov 2018 OPINION – Is SSRS on Azure happening?

Is SSRS on Azure happening? Well as always in Azure it depends… If you mean that ‘SQL Service Reporting Services’ is going to be provided on Azure, the answer is NO as SSRS is a product name as well as a part of SQL Server. But...

07 Nov 2018 How To: Azure SQL DB Cross database queries using synonyms

When helping a fellow dba on dba.stackexchange.com, I came across an interesting subject. What about synonyms in Azure SQL DB? We know that synonyms that stay in the current database scope work, but how do we get cross db synonyms to work?

06 Nov 2018 Make your own SQL Server Docker Container

Recently I needed to have a SQL Server 2008 R2 instance to do some testing. I had to install it on my laptop, but I wasn't a big fan of installing such an old version locally. But I found a solution!

03 Oct 2018 Using CTAS to efficiently load large tables

An Azure data warehouse is a real powerhouse. Personally, I consider it to be one of the most performant components that can be used and build into the Lambda architecture. Ludicrous power comes with a drawback of course and that drawback is potential inefficiency.

03 Sep 2018 Waarom werken bij Kohera?

Een passie voor alles wat IT-gerelateerd is en de goesting om continu bij te leren. Twee eigenschappen die Dirk Deckers en Philippe Van Rillaer kenmerken. Lees hier het relaas van hun eerste werkjaar als Koheriaan.

02 Aug 2018 Tackle parameter sniffing in SQL Server 2017 and Azure SQL Database

SQL Server 2017 and Azure SQL Database introduced a whole new set of query improvements regarding performance. These modifications are all part of the so called “adaptive query processing feature family”, which includes three major changes. I'll focus on the batch mode adaptive joins.

27 Jun 2018 World Cup Russia: Discover our Power BI dashboard yourself

You may have seen it pop up throughout our blogs and webinars in the past few months: the World Cup 2018 in Russia! Philippe Van Rillaer and I created a dashboard in Power BI about the historical results of previous World Cups, as well as results of the qualifiers for this year’s World Cup, the current teams that qualified, etc.

26 Jun 2018 SQLGrillen 2018: “Fun for the whole SQL family”

On June 22 a delegation of fourteen Koherians went to Lingen in Germany to attend the event SQLGrillen 2018. Colleague Randi Vertongen joined the club. Read his report of a day full of databases, bratwurst and beer.

09 May 2018 How-To create an ultra-fast, concurrent DataMart for Self-service reports on Azure DWH and Azure DB

With Analysis Services, you can mashup and combine data from multiple data sources, define metrics, and secure your data in a single, trusted semantic data model. The data model provides an easier and faster way for your users to browse massive amounts of data with client applications.

07 May 2018 Adding Flemish Seasonality to your dataset in SQL

One of the most challenging tasks in data mining or machine learning is how to get seasonality into the mix. Therefore I created a construct that can insert the seasonality for the Flemish part of Belgium in SQL. With some minor alterations, this can easily be adapted for the Brussels or Walloon regions.

07 May 2018 Azure SQL Datawarehouse “Gen 2”: a mayor game changer

Moving Azure SQL Datawarehouse to the cloud made it available for the majority of all companies, enabling them to use this workhorse as a powerful engine to drive their analytical needs. Now the "Gen 2" is five times faster than the already blazing fast Gen1.

26 Mar 2018 Security features in SQL Server 2017

SQL Server has many features that support creating secure database applications. In this blogpost I’ll guide you through the latest security features in SQL Server 2017: Common Language Runtime (CLR), dynamic data masking, row level security and always encrypted.

12 Mar 2018 Clusterless Availability Groups

SQL Server 2017 came with a couple of enhancements regarding Always On Availability Groups. The most radical change came with the introduction of “Read-scale availability groups” or the support for Availability Groups without an underlying cluster.

02 Mar 2018 World Cup Russia dashboard in Power BI: importing the teams from multiple web pages

While working on our World Cup dashboard my colleague Philippe Van Rillaer and I wanted to import data from tables spread out over multiple Wikipedia pages. With Power BI of course...

21 Feb 2018 There’s always an insight with Power BI: CONCATENATEX

Not so long ago, I was working with a client who had a specific Power BI request. They wanted to do a lookup of a column in another table, but if there were multiple hits, they wanted to display all the matching rows in the same cell. I came with a solution using ConcentrateX.

06 Feb 2018 Azure Data Architectures: Lambda

Are you familiar with the lambda architecture? It actually splits data flows into two components, receiving data centrally and doing as little as possible processing before copying and splitting the data stream into two streams, namely the real time and batch layers.

16 Jan 2018 Efficient and clear-cut monitoring of your databases with SQL Operations Studio

A while ago, Microsoft released a preview version of SQL Operations Studio. We've tested the preview version to get acquainted with it.

08 Jan 2018 Cortana Intelligence in a nutshell – Part 2

In October 2017 at the dataMinds conference (former SQL User Days) I gave a presentation about Data Lake Analytics. One of my slides contained the following topic: Fight of the Big Data Stores. Quite a nice topic to write something about.

06 Dec 2017 Latest Power BI update: share your portal with guest users

Power BI's mission is crystal clear: extract crucial intelligence from data so companies can use it to grow or strengthen their business. The latest update to the software further amplifies this creed.

01 Dec 2017 Stem op Sam als Plato-ambassadeur 2018

PLATO is het netwerk voor ondernemers & experten van Voka West-Vlaanderen waar netwerken, inspiratie, kennis en ervaring delen centraal staan. Dit jaar gaat het netwerk op zoek naar de Plato-deelnemer die zijn of haar enthousiasme over Plato het meeste uitdraagt en zich een jaar lang Plato-ambassadeur mag noemen.

20 Nov 2017 Cortana Intelligence in a Nutshell — Part 1

One of the first things we explain to our future customers, is what is available and what is possible with the Cortana Intelligence Suite. With following story we're discussing each product and we give a real-time usage example.

30 Oct 2017 Don’t be afraid to visualise complex business processes

In Business Intelligence we extract data from anywhere. Beside the technical structures that define how the data is stored, we also need to comprehend the business processes behind that structure. It’s all in the name, right?

16 Oct 2017 Use PowerShell to get all the Measures from a 2016 Tabular Cube

With Analysis services 2016 and Analysis Services Management Object (AMO) we now have some powerful tooling to automate our cube with, for example, PowerShell. In fact, it's now even (relatively) easy to retrieve all the measures from a cube in just a few seconds.

02 Oct 2017 Een veilige SQL-database in vijf stappen dankzij Azure

Data beveiligen is de uitdaging van de eeuw. Er zijn tal van momenten en manieren waarop iemand met slechte bedoelingen aan je gegevens kan komen. Je infrastructuur wordt rechtstreeks aangevallen of gebruikers worden overtuigd om schadelijk software te installeren.

18 Sep 2017 How to create your own Power BI visual

In this video our Senior BI Consultant Frederik Aerts is discussing how to start with the creation of your own custom Power BI visual. You can choose from a wide range of default visuals, but you can also create them yourself.

04 Sep 2017 Graph Data in SQL 2017

One of the new features in SQL 2017 is a way to save Graph Data. First time I heard the term “graph” I was stunned… Graph Data? First thing, I thought that came into my mind: “Why would you save charts (graphs)”. Nevertheless, that is not what Graph Data is. When we are speaking about Graph Data we are not speaking about tables anymore...

27 Jun 2017 SQL Server Problem Tombola: MAXDOP 1

As you all may know, many vendors tend to force you to put the MAXDOP of your SQL Server to 1 (SAP, SHAREPOINT, AX). Today I would like to demonstrate you the actual impact on your server of setting your MAXDOP to 1.

06 Jun 2017 Securing Data with Transparent Data Encryption (TDE)

Securing sensitive data is a critical concern for organizations of all types and sizes. In this blog post you will get an overview of the possibilities, availability, pros and cons, and evolution of Transparent Data Encryption (TDE).

30 May 2017 SQL Server problem tombola: too many indexes

When auditing SQL Server databases, one of the more common problems I see is that people add too many indexes to their tables. This has a significant performance impact on your SQL Server.

16 May 2017 Don’t let master data become the “Master of Disaster” – Introduction to MDM/MDS

What is Master Data Management? Let me start by telling you what it is not. MDM is not a pure technological solution, or a quick, pre-developed GUI to maintain data. To ensure sanitized master data, you need to include fundamental changes which are required in business processes.

09 May 2017 Formatting time variable in SQL Server

A colleague of mine recently had an issue when trying to format a variable of the time data type. He was trying to return the time in the HH:mm format, but got a NULL value.

02 May 2017 New syntax in SQL 2016

SQL 2016 has been released now already since a couple of months. But did you know the existing of following new syntax in SQL 2016 or vNext?

25 Apr 2017 SQL Server problem tombola: giant reads

A SQL Server is as fast as you tune your workload. One of the things I try to do, is tune the workload, a.k.a. the queries executing on my SQL Server, in order to get a better performing server.

18 Apr 2017 An overview of working with files in SQL Server

When you are responsible for databases on a project (as an architect, administrator or developer), you sometimes have to store documents (Files) which are linked to structured data. These data often have to be accessible outside SQL Server (Windows API’s). Storing BLOBs in the database, especially the ones over 1MB, can consume large amounts of file space and expensive server resources. Within SQL Server and Azure, there are different solutions to solve this kind of issues.

04 Apr 2017 Protect your most valuable business asset

Big data has become big business. Almost every company has transformed into a digital company these days. But how do you turn large volumes of information to your advantage?

21 Mar 2017 The endless possibilities of Power BI, that’s what makes us happy

We’ve already demonstrated once or twice that with Power BI, you can do much more than just make a simple analysis of your company data. To us, possibilities are truly endless, as long as you do some creative thinking. And today is no different.

14 Mar 2017 R Services: advanced analytics binnen SQL Server 2016

In een vorige blogpost kon je lezen wat R is, wanneer en waarvoor je R kunt gebruiken en hoe je ermee van start gaat. Vandaag vertellen we je hoe je met deze meest gebruikte open source programmeertaal een statistische analyse en een grafische presentatie van jouw gegevens maakt. Plus, waarom dat nuttig is.

07 Mar 2017 Securing the data tier of an Azure SQL Server Database – Part 2

One of the most prominent questions when working with PaaS databases is how to secure them. In the previous blodpost of these defence series, I talked about limiting access to objects through firewalls and encryption. Today I'll discuss protecting and auditing your data. Also, I’ll add some extra resources for a complete overview of the available security features on an Azure SQL Sever Database.

28 Feb 2017 Securing the data tier of an Azure SQL Sever Database – Part 1

One of the most prominent questions when working with PaaS databases is how to secure them. In these defence series, I will talk about limiting access to objects, protecting your data and auditing. Ok, let’s get started with the first layer. Enjoy!

14 Feb 2017 Romantiek met Power BI

Valentijn, je bent er voor of er tegen. Voorstander of niet, wij lieten ons met plezier meeslepen door de zeemzoete sfeer om nog eens wat hits in Power BI te stoppen.

14 Feb 2017 Social Science – Text mining on Twitter data

In my previous blog post I talked about how to get Twitter data into Power BI. This article will discuss how you can apply text mining on that data with some R code, together with some Power BI visuals.

07 Feb 2017 Social Science – Get Twitter data in Power BI

Ever wanted to do some analytics on tweets with a certain hashtag (#) or from a certain user (@)? Don’t look any further, there is a great tool called Microsoft Power BI. In this blog post, I’ll be explaining how to get twitter data into your Power BI. Let’s go!

31 Jan 2017 Azure SQL Database autotuning is not just a wizard

SQL Server provides custom recommendations by using the Azure SQL Database Advisor, enabling maximal performance. The SQL Database Advisor makes recommendations for creating and dropping indexes, parameterizing queries, and fixing schema issues.

24 Jan 2017 Automated deployment of SSRS reports (PowerShell)

It all began with a client who wanted to distribute reports with minimal effort and make them usable by non-technical users.

17 Jan 2017 Why invest in data science?

As you might have noticed, data science is not just a buzz word anymore. It has become serious business! Why?

03 Jan 2017 Power BI – Ranking with ‘Other’ group

Not so long ago a client asked me to rebuild a report of theirs in Power BI. It contained the top 5 countries ordered by number of visitors and … an Other group at the end.

20 Dec 2016 Power BI – From data to information

We live in a world where an enormous amount of data is available. But having access to the data is not the same as having insight. If we combine this data access with the right tools and a little bit of effort, you might be able to transform your data into information in no time.

13 Dec 2016 SSIS – Boosting Range Lookups with a script component

Did you ever needed to use a lookup in SSIS that was not the standard equi-join and there was no possibility to do the lookup directly in the source?

06 Dec 2016 Power BI on-premise

Although Microsoft always had said that bringing Power BI on-premise was on the road map, no clear time frame could be given. That was the case at least up until recently, when Microsoft announced the Technical Preview of the Power BI on-premise at SQLPass and Ignite.

18 Oct 2016 R[101]

What is the buzz about ‘R’? What is R, how can I use it, where do I use it, how do I start? I had all the same questions about it, the first time I heard of it. That’s why to explain what the possibilities are in R and what you can use it for.

20 Sep 2016 Triggering SQL agent jobs from Excel

A client requested whether it was possible to launch an SSIS package flow from Excel. The reason behind it was that the load of data in question was only used once a year and just over a period of one or two weeks. The actual code used to do this was no longer than four lines of VBA code in Excel.

13 Sep 2016 SSIS Lookup component – Beware the partial cache

After publishing my previous blog about making a lookup case insensitive, I received a question from my most loyal reader, my wife. Just like me, she is a BI developer, so that makes her the perfect proofreader. After reading my post, she wondered what would happen if you used a partial cache mode, and the input data didn’t match the casing of the lookup data. So I started investigating…

06 Sep 2016 Dynamic data column position for flat files in SSIS

Scenario: your customer has determined that your SSIS source for his new project is a CSV file with a header row followed by the data rows. All is developed and tests were made, all is success. After a few days of testing the customer wants the business user to have the ability to change the position of the data columns as the customer needs.

30 Aug 2016 Why is the SSIS Lookup Component Case Sensitive (or isn’t it)?

Many websites and blog posts will tell you that the SSIS Lookup Component is Case Sensitive (CS). And while they are correct that there isn’t a direct property to change the case sensitivity of the component, it can however be used in a non-case sensitive way.

24 Aug 2016 Create a date table in Power BI

One of our most-read blogpost of all times without a doubt is the one in which Frederik Vandeputte explains how you can create a Power BI date table in just two steps. To make it even easier for you, we made a step-by-step video to build your own date table in just a few minutes. 

23 Aug 2016 Database Lifecycle Management (DLM)

Controlling database deployment, managing (measured) deployment to production of database changes and keeping an overview of these changes can be challenging. As an answer to this problem, I’ve been searching for a well-working approach to implement Database Lifecycle Management (DLM), of which the target would be to use this approach on all projects.

16 Aug 2016 Power BI – Gateways to heaven

Although the Power BI gateways have been around for some time now, they weren't really easy to use. Depending on what you needed to do - refresh your data from the Power BI or use the live connection method - you had to install a different gateway. Next to the annoyance of having to install two separate software packages for your Power BI cloud solution, you would think there is no real issue. Wouldn't it be for the fact that you can't install both packages on the same machine.

09 Aug 2016 Coaching

Data warehousing has been a hot topic for a couple of years now. In the beginning this was especially something for larger organisations with vast amounts of data spread all over the company. But over time we have seen a shift in type of organisations needing data warehouses solutions. Also smaller organisations are seeing the benefits of having data stored centrally and integrated.As we see a shift in type of organisation, we also see a shift in type of project.

02 Aug 2016 Natively Stored Procs and In-memory tables: NextGen loading!

Today I was creating a dummy database and wanted to fill this with a certain amount of data. Since I was going to use this database for demo purposes, I created them with foreign keys, primary keys and some indexes. I found that the way I was loading data was quite slow, so I decided to load the data into my database using Natively Stored Procedures and in-memory tables, since this should be the fastest way. The result really baffled me!

19 Jul 2016 Checking your SQL Server maximum memory settings

One of the SQL Server settings with critical impact that is often misconfigured is the maximum memory setting. SQL Server will attempt to take as much memory as possible and is not always eager to release it. This results in the operating system being forced to start actively using the page file. While SQL Server might seem perfectly happy and show good page life and buffer hit ratios, the operating system might in fact be suffering.

11 Jul 2016 Index maintenance on a VLDB – how to tame the beast

Normal day to day maintenance of large data volumes can be difficult. In a previous post I talked about how to handle DBCC CheckDB on a multi-terabyte database. Today I will tackle another issue I came across while dealing with multi-terabyte databases: index maintenance.

05 Jul 2016 Make your Named Instance look like a Default Instance

Recently I was working for a big international customer who, according to policy installs, Named Instances of SQL Server only. During a huge project deployment, an obstacle occurred: this customer had hardcoded the SQL connection string which pointed to the local Default Instance (even worse '.').

28 Jun 2016 How to check the uncheckable: DBCC on VLDB

One of our customers had problems maintaining some monster databases (VLDB). These have about 2 to 4 TB of data and more than 90,000 tables. Building a good maintenance solution for such databases isn't easy. For most of my clients I use the Ola Hallengren solution, which has proved to be excellent before but it just wasn’t suitable here.

21 Jun 2016 How to get your business ready for the cloud?

Cloud computing is no longer just a buzzword, it is here to stay. Companies should prepare by looking into cloud technology and getting their cloud strategy sorted out. If you look at cloud computing in its most basic form, you could simply call it store stuff on the internet. Although this is partially true, the cloud has much more to offer.

14 Jun 2016 Data warehouse and self-service BI tools: friends or foes?

Looking at the BI marketplace today, a lot of products and vendors are telling us that we don't need data warehouses to create those flashy and easy-to-use reports and dashboards. Although from a technical point of view this statement could be valid, in the real world you are not only using the data warehouse to gather information in one location. In the real world we see that a data warehouse does much more than that.

31 May 2016 Creating the database application

Azure and DevOps have become quite the rage in the information technology world. With the increasing desire for companies to build and deploy code faster, and the need for scalability the DevOps philosophy gets more focus as a path to accomplishing these goals. However, applying DevOps in a cloud environment means a shift in the way IT-Pros have been working over the years.

18 May 2016 Migration gone wrong – fall back scenario

Today I’ll be handling an issue I had when migrating a SQL Server 2000 database to SQL Server 2012. We migrated the database from the old, grumpy, and more importantly, unsupported Windows Server 2003 and SQL Server 2005 instance. All was well, and we had no instant issues when users started working with the application. The migration was seamless, we did not expect any future problems. Even the upgrade advisor did not give us any errors!

10 May 2016 Patch Management

If you would ask me what one of the most forgotten but essential elements of database security and stability is, it would be keeping all of the involved software in the database environment up to date. That might seem trivial, but experience shows that in a lot of environments this is often positioned in “the gray area”. Especially when a customer has a lot of third party vendors bringing their own databases. In this time of exploit kits enabling even the non-technically savvy to launch sophisticated attacks, patch management takes on a new importance. Especially when you consider the fact that getting access to any database, is considered hitting the jackpot by a hacker.

03 May 2016 Trace flags SQL Server’s transformer like Tuning

Just like Windows when you install a SQL Server, it’s more a generalist then a specialist. Trace flags is the road to follow when you as a DBA want to tune your SQL server to look like either a F1 car for OLTP or an Optimus prime monster-truck-like-Australian road-train for DWH queries. Both of them are extremely powerful in their own field, but the key is to use that power correctly.

26 Apr 2016 SQL Server Integration Services – loading Excel files

As BI consultants we regularly get the question to load one or more Excel files. Files tend to have different layouts, almost always consist of multiple worksheets and columns often contain different data types, making it more complex to load an Excel file than it would initially appear. After lots of #$@&%*! I grew tired of being frustrated, so I started looking for a solution that would simplify loading intricate Excel files. Here's what I came up with.

19 Apr 2016 When does StretchDB become cheaper than your local SAN?

This blogpost is an addendum to my previous blog “SQL 2016 - 5 (Real) reasons to upgrade, part 2” where I explain about StretchDB. We know that StretchDB is an awesome feature, but what willit cost and how does it compare with your local SAN storage?

12 Apr 2016 New features in SQL 2016: Row Level Security

As a running up to the new release of SQL 2016 I would like to share some research I did about the new features that will be available. Last time I talked about Dynamic Data Masking. This time I will discuss another very interesting new feature namely Row Level Security. We know Row Level Security on Analysis Service level. But Microsoft now also implemented it on Database Service level.

30 Mar 2016 SQL 2016 – 5 (Real) reasons to upgrade, part 2

My personal favorite reason to upgrade to SQL2016: StretchDB StretchDb provides cost-effective availability for cold data Stretch warm and cold transactional data dynamically from SQL Server to Microsoft Azure with SQL Server Stretch Database. Unlike typical cold data storage, your data is always online and available to query.

24 Mar 2016 SQL 2016 – 5 (Real) reasons to upgrade, part 1

In my role as a consultant, many customers ask me for real reasons why to upgrade their existing SQL platforms to the next iteration of SQL Server. As migrating their existing environments always contain a risk, they are often looking for good reasons to upgrade or to stay at their current build level.

15 Mar 2016 Query Store: Here is the bacon!

Whenever I go to a burger restaurant & I want to order myself a nice and tasty burger. The first thing I do is look at the menu and ask myself the question: “Where is the bacon?”. I did the same thing while looking at the menu of the new up and coming SQL Server 2016, and found the nice tasty bacon in the form of a feature called Query Store!

18 Feb 2016 Power BI voor iedereen door iedereen

Niemand minder dan Satya Nadella, de CEO van Microsoft himself, kondigde begin deze maand Power BI Publish to Web aan. Dat is een nieuwe functie in de analytische visualisatietool Power BI, waarmee je vanaf nu gemakkelijk online gegevens kunt delen. Het enige wat je daarvoor moet doen is in Power BI een link naar een rapport of visualisatie van je gegevens creëren.

09 Feb 2016 Scripting Resource Pools and Resource Groups

Unfortunately, SQL Server’s management studio doesn’t provide you with a simple way to script your resource pools and resource groups. If you want to do this you’ll first have to script all the groups, and then the pools one by one in the correct order. To recreate them you have to do the same but in a reverse order.

02 Feb 2016 New features in SQL 2016: Dynamic Data Masking

As a running up to the new release of SQL 2016 I would like to share some research I did about the new features that will be available. Today I want to talk about Dynamic Data Masking. It is a nifty, small but quite intresting new feature.

01 Feb 2016 The SQL Server Misconfiguration Chronicles part 3: Database Files

In the third part of the misconfiguration we will handle the database files. If you create a database SQL Server will look at your model database and create the file structure as specified in your model database. By default this will mean that you will create 1 data file with a size of 4 MB and 1 log file with a size of 1 MB. These files will be able to grow automatically by auto growth, where the data file will grow in steps of 1MB and your log file will increase with 10% each growth.

19 Jan 2016 The SQL Server Misconfiguration Chronicles part 2: Instance Memory Settings

In the first part of the SQL Server Misconfiguration Chronicles we handled the database level issue of AutoClose & AutoShrink. The second part of the misconfiguration chronicles will handle the default memory settings of a freshly installed SQL Server instance. SQL Server by default configures the instance memory settings to take a maximum memory of 2147483647MB which is about 2 Petabytes.

12 Jan 2016 The SQL Server Misconfiguration Chronicles part 1: Database AutoClose & AutoShrink

In the SQL Server Consulting world you see a lot of different environments, but mostly you see the same basic misconfigurations for their SQL Server Enviroment. The following blogposts will show you the most common issues with the configuration of SQL Server, they will tell you what the issue is, why it is a bad configuration and how you can solve it.

16 Dec 2015 Providing a SQL Server Express edition with a solid maintenance plan

One of the challenges of using the free express editions of SQL Server is the lack of the SQL Agent. This means that creating a functioning maintenance plan is a bit more of a challenge then on a regular SQL Server instance. Luckily for us Ola Hallengren created his maintenance plan in such a way that it can easily be altered to make it usable for SQL express editions.

07 Dec 2015 Emailing SSRS reports on a scheduled basis, without using subscriptions (or attachments)

In the Microsft BI world, SSRS is a very much integrated product. We can find SSRS reports in many companies. A question that we encounter often as BI consultants is to render some of these SSRS reports on a scheduled basis and save them in a shared location or email them as a pdf file. Subscriptions can do this for you. However, with subscriptions, we are missing some flexibility, like dynamic report names or sending the rendered report only when certain conditions are met.

24 Nov 2015 Visual tool for turning business requirements into reality

“Visualize to involve, write to describe" In business intelligence we extract data from anywhere. Beside the technical structures that define how the data is stored, we also need to understand the business processes behind that structure. It’s all in the name, right

17 Nov 2015 Overlooked Properties in SSIS: Lookup Component Cache Mode

SSIS has so many properties that some are overlooked or ignored. In this article we will have a closer look at one of them: the “cache mode” setting of the Lookup Component. As the name indicates, the “Cache mode” lets you choose which form of caching the Lookup component will use. The available modes are Full, Partial and None. By default “Full Cache” mode is selected. This is a good default value, but there are of course situations in which you might want to change it, otherwise there would be no need for the other modes.

09 Nov 2015 TEMPDB: The Ghost of Version Store

Close to Halloween we noticed that some SQL Servers had started acting spooky, as if they wanted to add to the sentiment of Halloween. Last week I had one SQL Server doing exactly that. The server had been running perfectly for the past months but that day its TempdDB was starting to fill up. Of course this required some investigation, and I found that VersionStore was the problem for my unexpected TempDB growth. The strange thing was that one database was taking up almost all of my tempdb space in version store, but it did not have any open transactions. Spooky stuff indeed!

16 Oct 2015 Stad Turnhout maakte een betaalbaar rapport over z’n personeelskost

Het stadsbestuur van Turnhout wilde weten hoeveel de werkelijke personeelskost verschilde van de gebudgetteerde kost. Zo kon het zijn toekomstige budgetten nog verfijnder opstellen en daarnaast ook sneller en correct rapporteren aan de gemeenteraad.

15 Oct 2015 Patiëntengegevens kunnen niet langer verloren gaan in het Ziekenhuis Oost-Limburg

Werken met een elektronisch patiëntendossier maakt het leven veel gemakkelijker voor iedereen in het Ziekenhuis Oost-Limburg. Het heeft wel een belangrijke implicatie: wat er ook gebeurt, de digitale gegevens mogen niet verloren gaan. De wet vereist ook dat patiëntengegevens tenminste 30 jaar worden bijgehouden. Daarom ging het ziekenhuis op zoek naar een oplossing die hen volledige garanties zou geven op lange termijn, tegen de laagst mogelijke kost.

13 Oct 2015 How to create a date table in Power BI in 2 simple steps

With the introduction of "Calculated Columns" in Power BI Desktop things got easier. In this blogpost I will show you how to create a complete date table is just 2 simple steps.

05 Oct 2015 Creating a scalable tempdb for your cloud servers

When deploying to the cloud one of the hardest challenges is to get tempdb to scale in the same way as your virtual server. Why is this, well as cloud machines are scalable and thus can change configurations during their lifecycle the number of tempdb files should be able to scale accordingly, so the amount of tempdb files becomes a dynamic value. Luckily for us, this parameter changes only after a shut down and restart of your virtual machine, so if we had a script that could adjust our tempdb needs every time the configuration changes we could deploy this and be more at ease.

29 Sep 2015 Master Data Services in real life

Master Data Services is one of the hidden gems in the Microsoft BI Tool stack. As the name suggests the idea is to use the tool to technically support Master Data Management. But what we see in real life is that Master Data Services, although being a good tool, isn’t up to standards compared to the master data management tools offered by other big vendors like IBM and SAP. This doesn’t mean that you shouldn’t use Master Data Services (MDS). MDS has a big added value in lots of Business Intelligence and Data Warehousing projects.

17 Sep 2015 Zien ze het BI-bos nog door de bomen bij Microsoft?

Deze vraag krijg ik de laatste tijd vaak voorgeschoteld van klanten die advies vragen welke richting ze uit moeten met hun Business Intelligence. Sinds de overname van Datazen en de grote update van Power BI afgelopen zomer beginnen alle puzzelstukjes in elkaar te vallen en kunnen we spreken van een compleet BI-platform dat zeer concurrentieel is ten opzichte van de andere spelers in de markt.

10 Sep 2015 Migrating Access 2007 to SQL Server 2014

There comes a time in every SQL Server dba’s life when they will have to consolidate an MS Access database to an SQL Server. It's inevitable and destiny decided that it was my turn today. A heavily used Access database which had almost grown beyond its capacity sealed my fate. It had about 1.2 GB of data and had become extremely slow at executing queries, which was something we could no longer ignore. This was the first Access database I was going to migrate, so I started by doing some research on the good old internet. Many people suggest that making SSIS packages is the easiest way to do this, while others recommend using openrowset or opendatasource. I chose the openrowset technique.

28 Aug 2015 What to expect from Datazen

What to expect from this tool, recently acquired by Microsoft? Here are some of the features that make Datazen a great and enterprise ready application.

18 Aug 2015 ETL with Direct Database Access versus API/JSON

There is a large variety in possible data sources from which we can extract and that number is not likely going to decrease. The ETL path that has to be taken for each source can vary heavily depending on all kinds of factors including application architecture, company security, departmental policies, etcetera. Middle-sized to large companies tend to use multiple applications from which data can be extracted to feed the greedy monster that is BI.

04 Aug 2015 Excel in SSIS: fixing the wrong data types

Imagine your client gives you an Excel file as the SSIS source for a new project. When you load the data you find that the data type is not aligning as expected and some of the rows are not filled with the data provided. You then try to change the input data type in the Excel source component, but you can’t seem to get the output you want. So what's the problem? Well, let’s take a look at the input.

27 Jul 2015 How to check if you’re running on a Windows Core Edition

While writing my SQL Server template installer, I ran into a big problem – I had to determine if my server was a core edition or not, based on the Windows edition. The biggest challenge was that it had to be as OS-independent as possible. Our customers run several OSs, so I needed something that could run on Windows 2008 R2, Windows 2012 R2 and be future-proof for Windows 2016.

03 Jul 2015 Chaos in the library

The uniqueidentifier datatype is a 16-byte binary value used as a globally uniqueidentifier (GUID). Developers use it in database models for assigning identifiers that must be unique in a network of many computers at many sites. Also, developers exploit this uniqueidentifier data type inappropriately, assuming this is always the best way to enforce uniqueness in their data model

05 Jun 2015 Executing a stored proc with openrowset query in a SQL Server job across the CMS

Today I came across an issue with my Central Management Server. I have a database on my CMS which has a few tables with details about the server state (memory, disk space, etc.). I wrote a few stored procs to gather this data using openrowset in combination with the server list obtained from the msdb on the CMS.

20 May 2015 SQL Server Upgrade Advisor 2014

Wondering where to start with your dinosaur database servers that are still floating around somewhere in your highly state-of-the-art environment? Oh yes, everyone else may have forgotten about them, but these machines still exist. While everybody is happily doing their job, you still need to maintain the old servers. After all, you're the DBA and your job is to keep these machines alive.

20 Apr 2015 Hesitating about the cloud will cost you flexibility (and money)

Cloud computing is fully mature, so we believe that many of your SQL Server systems could be running more efficiently in the cloud. We are convinced that a cloud model, whether hybrid or full cloud, is in fact the best solution for most of our customers. The only questions that remain are: when are you going to migrate and with which model?

06 Apr 2015 Hacking the Central Management Studio (CMS)

With this post I want to show you how you can make a central management server (CMS) selective for different user accounts so that you can use public and management roles, only showing the servers that are necessary to do your job. This enables you to only maintain one CMS, but show different servers (and even groups) dependent of your role (operator, junior DBA, Sys DBA,…) these roles are only limited by your needs and imagination. First I looked into Row- and Cell-Level Security

18 Mar 2015 Boosting self-service BI: Microsoft Power BI

Susan walks into her office and wants to assign a job to Tom about absenteeism. But ironically it seems he's not at work because he's ill. So she decides to try doing the job herself. She takes her laptop and opens Power BI Designer for the first time. It looks enough like Excel to make her confident about using it. In no time she works through the data that the IT department has prepared, creates a report about employees and absenteeism over time and publishes it to the internal HR portal.

16 Feb 2015 Transfer AdventureWorksDW2014 into a Document DB Collection

The goal of this blogpost is to transfer a dataset from the Adventureworks DB and query it in the same way that you would on your own DWH, but running on a DocumentDB NoSQL database. This blogpost is not intended to compare any RDBMS based data warehousing to Document-based data warehousing. It’s purely to demonstrate that you can actually migrate a dataset from a pure RDBMS data structure and transfer it into a document-based NoSQL Engine and run data warehouse queries.

09 Feb 2015 Checking availability group status using a Central Management Server

Today I want to show you another powerful way to use your Central Management Server. The CMS can also be used to execute OPENROWSET queries over your complete SQL Server Farm. Now I can hear you all say: “Why would you want to do that?” and “OPENROWSET, you know that you shouldn’t implement that for queries that have to run frequently!”. And to be honest, in most of the cases you’d be right. But it can come in extremely useful in some situations.

31 Jan 2015 How to get Google Analytics data in Power Query

Microsoft Power Query is an Excel add-in that helps you gather, transform and analyse data from various sources. It's a very powerful tool and a must have for anyone interested in BI, no matter how experienced. Microsoft also releases frequent updates of this add-in, including new source connections. Google Analytics is a powerful tool for monitoring and analyzing your website traffic. It helps you know who is visiting, what they are looking for and how they are getting there.

10 Dec 2014 How I migrate SQL Server to a new server

While working on various SQL Migrations these are my findings on how to migrate a database between servers with no downtime on the SQL server side.

09 Dec 2014 The dangers of copying script components in SSIS

In this blog post I want to explain something weird that happened in a previous SQL Server Integration Services (SSIS) project. A lot of different SSIS packages had to be developed. Often very similar code was used within the same package.

10 Nov 2014 Creating my first data warehouse from scratch: the analysis

It all started about a month ago, I got my first big assignment as a junior. The assignment was quite simple, design and implement a datawarehouse from scratch from a production system of an enterprise. Although the description of my assignment was quite simple, putting this assignment into practice proved to be a lot of work.

07 Aug 2014 Unique identifier as primary key

What is better? Using GUIDs or Integer values? This is been an age long (religious) debate and there are advocates in both camps stressing on the disadvantages of the other. Both implementations have their advantages and disadvantages. In this blogpost, we'll see that the ultimate SQL Server answer is also valid for this debate: it all depends!

11 Jul 2014 Nasty ballooning kills your SQL performance

This post might sound a bit like a game of Cluedo, but it happened on a production SQL Server… The issue showed itself on a hosted production VM with 64GB of Ram, where SQL could allocate memory from 32GB to 55GB. The agreements with the hosting partner clearly mentioned that while the CPU could be overcommitted, memory couldn’t. All my alerts suddenly started to show that the machine was using all the available and became completely unresponsive (I couldn’t even open the Event Viewer to unload the logs). Remote connection with management studio gave me this worrisome error:

25 Jan 2014 How to connect to IBM DB2 from Power Query

SQL Server 2014 has been released since the 1st of April and there are some really cool features that might help you and your organization and can definitely justify an upgrade. In my job as a SQL consultant, I have a lot of different customers and none of them is currently working on SQL Server 2014 or is planning to upgrade to this new version. I find it really hard to convince my clients to upgrade or even install it for a brand new installation. Why is it so hard to convince people to start using SQL Server 2014…? After questioning my customers I figured out these 5 reasons?