This article was recently also published on devjef.wordpress.com:
Power BI gives you a great advantage, for example when you use it to get quick insights into your data. That’s one of the reasons our business analyst fell in love with this tool pretty quickly after she started using it. It almost completely replaced Tableau and Excel-based reporting in her daily routine.
Another great feature of Power BI is that there are so many data sources and connectors (adapters) to get data from all kinds of sources. But some of these connectors almost made me pull my hair out yesterday, so I’d like to share this learning with you, and hopefully it will save you some time and frustration 🙂
Continue reading Creating an online Power BI Dashboard, using an Excel file hosted on SharePoint as source
For those of you who use Azure today, the security discussion must have been a thing on some occasion. Explaining to managers (and possibly colleagues) that Azure is a lot more secure than a(n) (on-premise) data center, and that Azure is easier to maintain and scalable. Trust me, we’ve all been there!
But besides the physical security, there’s also the digital security. In the world of today it’s easier to find a data-breach on the news, then it is to find an item about a bank robbery. So how can you secure your data in Azure in an easy but solid way, without the hassle of changing your applications?
Encryption could be one of your tools to achieve a secure infrastructure and/or applications. But encryption is a challenge for pretty much everyone. Almost every day we hear about companies not doing it right, or not doing it at all. But luckily, Azure helps us with setting this up with just the click of a button.
Okay, okay, you got me. Maybe a few button clicks…
Continue reading Easy data encryption in Azure
Running all of your databases in Azure SQL DB (the PaaS solution, also known as Azure SQL database) is a wonderful thing, except for one thing: you still need to run database maintenance. And because the SQL Server Agent is not available (which is a shame in my opinion), you have to find a way around that.
In one of my previous posts (Running maintenance on Azure SQL databases), I showed you how to leverage the power of the database maintenance solution, written by Ola Hallengren (Website | @olahallengren). But now that we’re moving into Azure completely, we’re losing that work-around. So how did we fix this?
Continue reading Running database maintenance on Azure SQL DB with Azure Automation
In one of my previous posts, I described how to setup replication to an Azure SQL database. This works like a charm, and I still highly recommend using this when you want to migrate data from an on-premise server (or Azure VM) to a Azure SQL db (PaaS).
But in our environment, we use SQL Server 2016 and contained databases for some of our datasets. Unfortunately (but totally understandable), you can’t setup replication from a contained database. So how do you deal with this? For our use-case, I’ve written a script to automatically change the database from contained to non-contained. And because I’m probably not the only one who needs to do this, I’d like to share it with you.
Continue reading Automatically convert contained database to non-contained for replication
Roadmap is growing, more data is coming in and more travelers are becoming dependend on our services which is a great! However, everything comes with a price. The huge amount of data that Roadmap is currently processing is going to increase with the time and therefore, it is absolutely important to have a stable platform in place that can handle and support the growth.
To achieve this goal we have taken a closer look into our backend processes and namely, how the current flow works with different types of data that we process as well as into the bottlenecks that we face when it comes to communicating to the travelers.
We have learned that we need to change a big part of our system to be stable, scalable and high available. Our main focus was based on two core processes – Timeline and Notifications.
Within this blog I will dive into the migration of Notifications; How did this work in the past, what kind of issues did we encounter and how did we gain control over these issues in the current process, bearing in mind we want to extend the process in a most efficient way in the (near) future. Continue reading Notifications 2.0
In the previous post I wrote, I explained how to setup replication from an on-premise SQL Server instance to an Azure SQL database. While doing this, I came across a very strange issue (or maybe even bug) when setting up replication.
The problem child
After working on reproducing the issue for a day, and trying to reduce the issue to a small-scale problem, I came to the conclusion that the problem was (probably) caused by a single primary key on a table in the database:
CREATE TABLE dbo.BuggedTable
([Day] DATE NOT NULL,
SomeId VARCHAR(50) NOT NULL,
Amount INT NOT NULL,
CONSTRAINT PK_BuggedTable PRIMARY KEY CLUSTERED
Creating publication & subscriber
The setup of the publication and subscriber wasn’t that difficult. As I said before, there are a few things you need to configure differently then you would do for SQL Server to SQL Server replication.
So I won’t talk you through the whole process again, but refer you to the articles instead. Continue reading Replication: Snapshot Agent fails on date conversion
For performance reasons we are looking for a way to split our write-operations from our read operations. A good way to do that is by duplicating the database onto another server (on-premise) or platform (Azure SQL DB). When you look at the options (Availability Groups (AG), database mirroring, clustering, replication, Azure sync groups, etc.) the easiest way to quickly duplicate a database and keep the data up-to-date at the same time is SQL Server replication.
Replication to another on-premise instance is easy. You just follow the steps in the wizard, it works out-of-the-box, and the chances of this process failing are small. With replicating data to an Azure SQL database it’s a bit more of a struggle. Just one single word took me a few HOURS of investigation and a lot of swearing… Continue reading Setting up replication from on-premise SQL Server to Azure SQL DB
Almost one year ago, Roadmap was ISO 27001 certified. This was a great accomplishment for a startup of only a half a year old at that time. We weren’t entirely issue-free of course, we had a couple of minor issues to fix, but only a couple. In general, we were in control. One of these minor points was that we needed to get an alert when someone logged on to a server in the production environment. Why? Because people shouldn’t. There is no need to be on a production server for writing or deploying software, because we use Octopus deploy for deployments. The only developer who we’d expect to find logging on to servers is the one on call, he or she is in the ‘ops’ role of DevOps. And of course we’d expect Jeffrey, our dba.
Recently, someone asked me just how we created such an alert, so I decided to share how we did it.
Our first attempt of to get the alert was to leverage the windows event with id 4624 together with LogicMonitor (https://www.logicmonitor.com). It is great tool to monitor your servers, you should check it out. My thought was that I could use LogicMonitor to check the event log and warn whenever a user logs on. But the log was full of these events; Windows didn’t just log the logon events from me and my colleagues, but also from AD accounts that we use to run services / tools. We needed to filter the logins to see if there were some strange logins. Unfortunately, I was unable to filter to just the events to the level I needed, despite LogicMonitors powerful capabilities on filtering. Continue reading Alert on Login on Production environment
If there is one thing I pretty much hate doing in T-SQL it’s PIVOT and UNPIVOT. Even though I used it a few times in the last couple of years, it’s an adventure to find out how it works every time. And I know a lot of people struggle with this part of T-SQL, so let’s take a look at a (hopefully) simple example.
The idea of using key-value pairs to store data isn’t new, but with the rapid development of cloud solutions like Azure and the hype around NoSQL databases, using key-value pairs to store data got a big boost. Especially developers (in my experience) love using key-value pair to store their data, because it’s easy for them to consume the data in an application. But it gives the database professional an extra challenge because we’re used to retrieve columns with values instead of a record per value. So how can we turn those key-value pairs into rows? Continue reading Easy pivot: From key-value pairs to columns
To keep your data healthy and topfit, we all know you need to run something called database maintenance on your databases. This prevents things like performance problems or unrecoverable data from happening, and that is sort of the core business of DBA’s. And when we look at how this can be performed on a regular basis, an on-premise instance is a bit easier to maintain than an Azure SQL database. That’s why I want to share our experience with you on that, hoping that it can save you some time when you start implementing this.
In order to get a better understanding of why certain choices were made, I want to share a very basic version of the setup of our environment.
Our platform consists of 2 mayor parts: a private cloud part (that we see as “on-premise”), and an Azure part. Those 2 parts combined make our platform, and this platform hosts all of our services, applications, databases, etc. The development approach we use is everything should be designed with cloud-first in mind, but only if it’s the right tool for the job, and with the exclusion of some data.
The databases we use in Azure are all what Microsoft calls “Azure SQL databases”. This means that there are no virtual machines of any kind are running on our Azure-part of the platform, also known as DBaas (Database as a Service). Continue reading Running maintenance on Azure SQL databases