All posts tagged with sql

Hangfire - .NET background processing made easy

April 29, 2020 - Søren Alsbjerg Hørup

The cloud project I am currently working had the requirement that we needed to ingest, process, and write several gigs of data in a CosmosDB every 15 min.

For the processing part, we needed something that could scale, since the amount of data was proportional to the number of customers we have hooked up to the system.

Since the project consisted mainly of C# .NET Core developers, the initial processing was done using C# using async operations. This worked well, but was not really scalable - one in the team suggested to use for the processing, which turned out was a great fit for our use case. (Wished it was my idea, but it was not…)

Hangfire is an open source .NET Core library which manages distributed background jobs. It does this by starting a Server in the application where jobs can be submitted. Job types include: fire and forget, delayed jobs, recurring jobs, and continuations.

Hangfire uses a database to ensure information and metadata about jobs are persisted. In our case, we simply use an Azure SQL server. Multiple instances of the application hosting the Hangfire server helps with the processing of the jobs.

This architecture makes it possible to e.g. submit a job for each connected customer, which is processed by one or more nodes. If resources becomes a problem, we horizontal scale up the application to include more instances - which can even be done automatically depending on CPU load or other metric. In our case, we use Kubernetes for that.

What I really like about Hangfire is the fact that one can simply start with one instance of the application hosting the Hangfire server, and scale up later if needed.

Oh! and Hangfire comes with its own integrated dashboard that allows one to see submitted jobs. Neat!

Although we are not yet in production, my gut feeling is good on this one. Highly recommended!

Azure Data Studio

August 27, 2019 - Søren Alsbjerg Hørup

I have always used SQL Server Management Studio (SSMS) when interfacing with an MS SQL server or Azure SQL Server. Recently, I have began using Azure Data Studio, an open source alternative from Microsoft (although with limitations).

I have not used it for long, but my experience can be summed up to the following bullets:

  • Round Trip and queries towards the server do not block the UI like in SSMS, but is instead handled in the background as it should be.
  • Even though background processing works without blocking, the UI seldomly show this fact meaning that background completed processing might show up after a couple of seconds without warning.
  • Intellisense is in their for general SQL, but it is not as context aware like in SSMS, e.g. it does not automatically suggest table names unless specified with the full schema.
  • It does not provide much help in the form of designing tables, everything needs to be done by SQL.
  • It provides extensions, similar to VSCode, that can be installed to increase functionality, e.g. connecting to other databases such as Postgres.
  • It has built in support for data charting (although I was unable to get this feature to work)

Azure Data Studio is very nice if you know SQL and frequently use SQL. If however database management / table / schema design is something you seldomly due, and thus require a bit more help in the process, SSMS is in my opinion more attractive due to it provides much more functionality that helps with designing tables and such.

In any case, I think Azure Data Studio will replace SSMS in the future for the majority of users - I will definitely use it for simpler queries that I know by hand.

Linq To Sql .Attach

June 12, 2017 - Søren Alsbjerg Hørup

Linq to SQL is a very nice abstraction when dealing with MSSQL, specifically the ability to conduct Linq queries in C# against MSSQL is pretty sweet. Updating a row through an ORM object, e.g. a HTTP Put, into the DB without doing manual field copying between the tracked entity and the de-serialized entity from the PUT is however a bit troublesome.

.Attach allows one to attach an entity to a Context, however calling SubmitChanges will not submit the changes of the attached object due to it not being marked as modified. Calling Attach(entity, asModified) with asModified = true did not work for me - an exception was thrown.

Apparently, this overload can only be called with asModified = true IFF Update Check is set to Never in the DBML file. This needs to be done for all properties of the given entity class, not sweet, but at-least it avoids the need to manually copy each member to an existing tracked Entity in the Context.

MariaDB: Access from

March 29, 2017 - Søren Alsbjerg Hørup

I recently installed MariaDB, a MySQL fork, on a Linux VM in the cloud for testing and development purposes. I really struggled with getting proper access from my dev machine to the installation in the cloud.

Simply put, I just wanted a totally open SQL database for deving and testing, nothing production wise was needed.

MariaDB is by standard pretty secure: a good thing, and does not allow remote access: also a good thing.

Firstly, one has to edit the proper .cnf file under /etc/mysql/* and set the bind-address from to MariaDB by default listens only on the loopback interface, thus making it impossible to reach it from outside either LAN or WAN.

Next up, one needs to restart the service: service mysql restart which will apply the changed bind-address.

Now it is possible to connect from outside, TCP/IP wise, however, the MariaDB user (such as root) needs to be granted access from outside to be able to actually make a logical connection to the DBMS.

This can be done by issuing the following SQL query (in this case, for root with password xyzw):


Which will grant root access level from anywhere.

To fire off this SQL, I suggest to simply login to the box using SSH and connect to the mysql CLI using:

sudo mysql -u root

and then fire off the query.