Notifications 2.0

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

Replication: Snapshot Agent fails on date conversion

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 
		([Day] ASC,
		SomeId ASC
		)
	)
GO

 
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

Setting up replication from on-premise SQL Server to Azure SQL DB

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

Alert on Login on Production environment

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

Easy pivot: From key-value pairs to columns

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.

 
Key-Value pairs
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

Running maintenance on Azure SQL databases

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.

 
Platform setup
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

Application Authentication via https using NTLM

We at Roadmap take security very seriously. Everything we do and everything we touch or make, we think about security as it is part of how we work. In fact: it is in our DNA.

Recently we have been working on a way to generate monthly reports for our customers in a sweet automatic way. To be able to download these reports the report server need to verify the given NTLM authentication credentials which are provided by the application. This was a bit of a challenge as it did not work in the way we would assume.

When you want to download a file, the .NET Framework provides you and easy and simple code snippet, which works straight away:


        public void DownloadAwesomeness()
        {
            var url = new Uri("https://DomainNameHere");
            var location = @"C:\Temp\Destination.txt";
            
            using (var client = new WebClient())
            {
                client.DownloadFile(url, location);

                Console.WriteLine("We are done!");
                Console.Read();
            }
        }

This works perfectly. How hard can it be to add some extra credentials to the WebClient? As the WebClient has a Credentials property, we will set these.


        public void DownloadAwesomeness()
        {
            var url = new Uri("https://DomainNameHere");
            var location = @"C:\Temp\Destination.txt";

            using (var client = new WebClient())
            {
                client.Credentials = new NetworkCredential("username", "password", "domain");
                client.DownloadFile(url, location);

                Console.WriteLine("We are done!");
                Console.Read();
            }
        }

When you will run this you will notice that the client.DownloadFile(url, location); will throw an ‘403 – Access Denied’. Before you start changing any usernames and/or password because you are doubting yourself if you did something wrong, let me help you out here: You didn’t do anything wrong. More interesting is; what is actually going on? What is happening with the credentials? Continue reading Application Authentication via https using NTLM

SSRS Reporting automation with .NET

SQL Server Reporting Services (or SSRS for short) is a great way to create an overview or analysis of your data, that you can share with other people as a report. But what if you have a report that you need to share with a large group of people, but they need it with 50 different parameters (like CustomerID for example), and they want to receive it in Excel or PDF? Are you manually going to execute the report with 50 different parameters, export them to the specific file format, and email those files? I don’t think so. Automating this process is easy if you write a small tool for this, and if you use the “Report Server Web Service URL”.

 
ReportServers vs Reports
Before we’re diving into the .NET code, first let’s see what the difference is between the URL’s “http:// [servername] :80/ReportServer” and “http:// [servername] :80/Reports”. If you navigate to your SSRS server, you’ll be redirected to “http:// [servername] :80/Reports”. This is the default webinterface that you use to open reports, manage subscriptions, etc:

 
If you go to “http:// [servername] :80/ReportServer”, you’ll end up in the webservice of SSRS. This allows you to open reports, and as a bonus: add parameters to your http request, so you can automatically execute reports from a URL. This is also called the “SSRS Virtual Directory”:

 
Building a URL
Now that we know that we need to use the webservice, we can start building our URL. Continue reading SSRS Reporting automation with .NET