Open Source

Aggregated data models

Relational database modelling is very different from the types of data structures that application developers use. The use of data structures modelled by developers to solve different problem domains has led to a move away from relational modelling towards aggregate models. Most of this is inspired by Domain Driven Design. An aggregate is a collection of data that we interact with as a unit. These aggregates form the boundaries for ACID operations, where Key Values, Documents and Column Family can be seen as forms of an aggregator-oriented database.

Aggregates make it easier for the database to manage data storage on a cluster, as the data unit can now be on any computer. Aggregator-oriented databases work best when most data interactions are performed with the same aggregate, e.g. when a profile needs to be retrieved with all its details. It is better to store the profile as an aggregation object and use these aggregates to retrieve profile details.

Distribution models

Aggregator-oriented databases facilitate the distribution of data because the distribution mechanism only has to move the aggregate and doesn’t have to worry about related data, since all related data is contained in the aggregate itself. There are two main types of data distribution:

Sharding

Sharding distributes different data across multiple servers so that each server acts as a single source for a subset of data.

Replication

Replication copies data across multiple servers so that the same data can be found in multiple locations. Replication takes two forms:

Master-slave replication makes one node the authoritative copy, processing writes, while slaves are synchronised with the master and may process reads.

Peer-to-peer replication allows writes to any node. Nodes coordinate to synchronise their copies of the data.

Master-slave replication reduces the likelihood of update conflicts, but peer-to-peer replication avoids writing all operations to a single server, thus avoiding a single point of failure. A system can use one or both techniques.

CAP Theorem

In distributed systems, the following three aspects are important:

• Consistency
• Availability
• Partition tolerance

Eric Brewer has established the CAP theorem, which states that in any distributed system we can only choose two of the three options. Many NoSQL databases try to provide options where a setup can be chosen to set up the database according to your requirements. For example, if you consider Riak as a distributed key-value database, there are essentially the three variables

r

Number of nodes to respond to a read request before it is considered successful

w

number of nodes to respond to a write request before it is considered successful

n

Number of nodes on which the data is replicated, also called replication factor

In a Riak cluster with 5 nodes, we can adjust the values for r, w and n so that the system is very consistent by setting r = 5 and w = 5. However, by doing this we have made the cluster vulnerable to network partitions, as no write is possible if only one node is not responding. We can make the same cluster highly available for writes or reads by setting r = 1 and w = 1. However, now consistency may be affected as some nodes may not have the latest copy of the data. The CAP theorem states that when you get a network partition, you have to balance the availability of data against the consistency of data. Durability can also be weighed against latency, especially if you want to survive failures with replicated data.

Often with relational databases you needed little understanding of these requirements; now they become important again. So you may have been used to using transactions in relational databases. In NoSQL databases, however, these are no longer available to you and you have to think about how they should be implemented. Does the writing have to be transaction-safe? Or is it acceptable for data to be lost from time to time? Finally, sometimes an external transaction manager like ZooKeeper can be helpful.

Different types of NoSQL databases

NoSQL databases can be roughly divided into four types:

Selecting the NoSQL database

What all NoSQL databases have in common is that they don’t enforce a particular schema. Unlike strong-schema relational databases, schema changes do not need to be stored along with the source code that accesses those changes. Schema-less databases can tolerate changes in the implied schema, so they do not require downtime to migrate; they are therefore especially popular for systems that need to be available 24/7.

But how do we choose the right NoSQL database from so many? In the following we can only give you some general criteria:

Key-value databases

are generally useful for storing sessions, user profiles and settings. However, if relationships between the stored data are to be queried or multiple keys are to be edited simultaneously, we would avoid key-value databases.

Document databases

are generally useful for content management systems and e-commerce applications. However, we would avoid using document databases if complex transactions are required or multiple operations or queries are to be made for different aggregate structures.

Column Family Stores

are generally useful for content management systems, and high volume writes such as log aggregation. We would avoid using Column Family Stores databases that are in early development and whose query patterns may still change.

Graph databases

are well suited for problem areas where we need to connect data such as social networks, geospatial data, routing information as well as recommender system.

Conclusion

The rise of NoSQL databases did not lead to the demise of relational databases. They can coexist well. Often, different data storage technologies are used to store the data to match your structure and required query.

Hosting strategy

There are essentially three different hosting variants within the research company:

Society-wide infrastructure

Infrastructure, which is used by most of the research projects and administrations across the institutes, is to be provided by the research company’s central IT.

Institute-wide infrastructure

Infrastructure that is required for the special research areas of one institute or that needs IT support on site should be provided by the IT of the respective institute.

Operational and geo-redundancy

These are mainly produced through institute cooperation or through cooperation between individual institutes and the IT of the research society. In terms of technology, floating IPs and the Virtual Router Redundancy Protocol (VRRP) are used for this, with decisions being made on the basis of BGP announcements.

Multiple critical vulnerabilities in Microsoft Exchange servers

The German Federal Office for Information Security (BSI) rates the current vulnerabilities in MS Exchange as a business-critical IT threat situation with massive impairments of regular operations [1]. But that is not enough:

«In many infrastructures, Exchange servers have a large number of permissions in the Active Directory by default (sometimes unjustified). It is conceivable that further attacks with the rights of a taken-over Exchange server could potentially compromise the entire domain with little effort. It must also be taken into account that the vulnerability could also be exploited from the local network if, for example, an attacker gains access to Outlook Web Access via an infected client.»

– BSI: Mehrere Schwachstellen in MS Exchange, 17.03.2021

The Computer Emergency Response Team of the Federal Administration (CERT-Bund) considers a compromise of an Outlook Web Access server (OWA) accessible from the Internet to be probable as of 26 February [2]. If admins now apply the security patches, the known security gaps will be closed, but a possible malware will not be eliminated. In addition, it is necessary to check whether servers in the domain have already been the target of such an attack and, for example, whether backdoors have already been opened.

Even today, about 12,000 of 56,000 Exchange servers with open OWA in Germany are said to be vulnerable to ProxyLogon [3] and another 9,000 Exchange servers have been taken offline or prevented from accessing OWA from the Internet in the last two weeks [4]:

Source: CERT-Bund

Only a month ago, the CERT-Bund warned that even one year after the release of the security update, 31-63% of Exchange servers in Germany with OWA openly accessible from the internet were still vulnerable to the critical vulnerability CVE-2020-0688 [5].

Problems also with Microsoft 365 and Azure Cloud

Several Microsoft services failed on the evening of 15 March 2021. The problems were caused by an automatically deleted key in the Azure Active Directory (AAD). This prevented users from logging in for hours. This also affected other services that are based on the Azure Cloud, including Microsoft Teams, XBox Streams and Microsoft Dynamics. In September 2020 and February 2019, there were already outages of Microsoft cloud services lasting several hours, which were also due to errors in the Azure Active Directory Service (AAD) [6].

However, these were by no means the only problems with Microsoft 365 and the Azure Cloud: according to a study by Sapio Research on behalf of Vectra AI [7] of 1,112 IT security decision-makers in companies that use Office 365 and have more than a thousand employees, attackers can take over Office 365 accounts in most companies.

The reason for the study was that remote working has become normal as a result of the global Corona pandemic and companies have quickly moved to the cloud. Office 365 has been a common choice. However, with 258 million users, they have also become a tempting target for cyber attacks. According to the survey, in the last 12 months

• 82% have experienced an increased security risk to their organisation
• 70% have seen the takeover of accounts of authorised users, with an average of seven accounts hijacked
• 58% have seen a widening gap between the capabilities of attackers and defenders.

Most respondents see a growing threat in moving data to the cloud. Above all, the effort required to secure the infrastructure was initially underestimated.

Microsoft vs. privacy

Finally, the State Commissioner for Data Protection in Mecklenburg-Western Pomerania and the State Audit Office are now demanding that the state government stop using Microsoft products with immediate effect:

«Among others, products of the company Microsoft are affected. However, a legally compliant use of these products solely on the basis of standard data protection clauses is not possible due to the principles established by the European Court of Justice. Without further security measures, personal data would be transmitted to servers located in the USA.»

– Press release of the State Commissioner for Data Protection and Freedom of Information Mecklenburg-Western Pomerania, 17.03.2021 [8].

In fact, however, this demand does not come as a surprise: The Conference of the Independent Data Protection Authorities of the Federation and the States already pointed out these dangers in 2015 [9].

Consequently, the State Audit Office of Mecklenburg-Western Pomerania concludes that software that cannot be used in compliance with the law cannot be operated economically.

Outlook

The news of the last few days make it clear that Microsoft can be operated neither securely nor reliably even if the operating costs were significantly increased. Finally, Microsoft services are not likely to be used in a legally compliant manner in Europe. With open source alternatives, more transparency, security and digital sovereignty could be achieved again. For example, last year we began advising a large German research institution on alternatives to Microsoft 365: Microsoft alternatives – migration to open source technologies. In the process, we are gradually replacing Microsoft products with OpenSource products over several years.

Update: 13 April 2021

There are again vulnerabilities in Microsoft Exchange Server, for which Microsoft has published updates for Exchange Server as part of its patch day [10]. This should be installed immediately. The BSI considers this to be a security vulnerability with increased monitoring of anomalies with temporary impairment of regular operation [11].

Update: 23. Juli 2021

In the press release, the State Commissioner for Data Protection of Lower Saxony, Barbara Thiel, continues to be critical of the use of Microsoft 365:

«We have not yet issued a corresponding order or prohibition, but it is true that we regard the use of these products as very critical. … Due to the overall situation described I can still only strongly advise against the use of Office 365 from the data protection perspective.»

– Press release of the State Commissioner for Data Protection of Lower Saxony, Barbara Thiel, 22 July 2021 [12]

Get in touch

I will be happy to answer your questions and create a customised offer for the migration from Microsoft 365 to OpenSource alternatives.

I will also be happy to call you!

Request now

Technical innovation

Unlike traditional solutions that rely on thousands of individual tile files served from a complex infrastructure, PMTiles bundles vector map data into a single, efficiently indexed file that can be hosted anywhere from traditional web servers to object stores with no special configuration required.

This approach enables progressive loading so that maps can be rendered quickly at variable zoom levels while maintaining the rich detail and interactive features that users expect from modern map solutions.

Democratisation

How is Protomaps democratising digital cartography in practice?

Real-World Applications

Let’s take a look at various applications where protomaps can prove to be transformative:

Small municipalities

Cities can replace their commercial mapping system with a Protomaps-based solution to display area information, infrastructure projects and municipal resources. The self-hosted implementation not only eliminated recurring licence costs, but also the ability to add local landmarks and municipal boundaries to the map that were previously difficult to highlight with commercial services.

Offline map

Protomaps can enable the creation of offline-enabled mapping tools in areas with spotty or no internet connectivity. By distributing PMTiles files containing detailed local and regional maps, interactive mapping tools can be accessed without the need for constant internet access.

Privacy-focused applications

For example, healthcare provider networks can use protomaps to create facility location tools, with user location data stored only on the device.

Specialised tools for businesses

PMTiles enables companies to create highly specialised maps with industry-specific symbology and data visualisation that could not be offered by commercial map providers, while ensuring that the maps are accessible on mobile devices even in remote areas without mobile coverage. For example, in forestry, maps with special vegetation and topography layers can be developed for field workers.

Limitations

PMTiles is intended for the web-based display of large, mostly static data sets,

• based on a web platform and not on a local desktop application.
• where the information to be explored totals more than a few megabytes – more than can be loaded at once for a pleasant website experience.
• whose data set changes at most daily or never.

If your application does not have these three features, there are simpler alternatives to PMTiles:

GeoJSON

If you are creating a web-based map with static information, but your data is small, you should provide it as a single GeoJSON file.

With MapLibre, it’s as simple as adding a GeoJSON source.

This saves you the hassle of converting your data into tiles, and you can use the same map design and interaction techniques as you would with tiled data.

PostGIS

If you are creating a web-based map for a large dataset that is dynamic and frequently updated by users, you should store your features in a transactional database.

While it is possible to update a PMTiles file regularly, this requires the file to be reloaded each time it is saved. While this may be fine for daily updates, any higher frequency requires an inefficient amount of data transfer.

PostGIS is the industry standard for transactional geographic feature databases. Popular methods for retrieving tile data from PostGIS are pg_tileserv, martin and the raw function ST_asMVT.

A major challenge for web maps, including PostGIS-based maps, is the generalisation of data for tiles with lower zoom. One method is to selectively omit attribute data in zoom levels to make overviews brighter at lower zoom levels.

GeoParquet

If you are exploring large, static datasets but do not require publication on the internet, you can avoid tiling and visualise the files directly with desktop software.

Tiling with tools like tippecanoe requires the prior calculation of general overview tiles and is designed for retrieving small, optimised pieces of data over the internet. If the network is not the bottleneck and you have your dataset locally, QGIS is an excellent open source solution for visualisation and map creation.

Together with GeoJSON and FlatGeobuf, GeoParquet is a new format that can efficiently store large datasets and is interoperable with open source data tools. The GeoParquet 1.0.0 specification has been supported since GDAL 3.8.0, the GeoParquet 1.1.0 specification since GDAL 3.9.0.

Lonboard

Tools such as Lonboard enable the visualisation of GeoParquet in Jupyter notebooks. It is possible to publish these on the web using hosted notebooks, although transferring tens or hundreds of megabytes results in more latency than tile maps. For local data, however, GeoParquet and Lonboard is a great solution for exploratory data analysis, saving you the trouble of converting to a network-optimised, tiled format.

Future directions

The standard libraries continue to evolve:

PMTiles

The main library for processing the PMTiles format

• Client-side implementations for JavaScript, Python, Dart , Rust and Go.
• Server-side implementations and pmtiles CLI.

Integrations with common mapping libraries:

• MapLibre GL JS
• Leaflet
• OpenLayers

Protomaps Basemaps

creates a cartographic ‘base map’ from OpenStreetMap and other data sources as well as MapLibre styles for display in a browser.

basemaps-flavors

Map themes and styles.

However, the ecosystem around Protomaps is also growing:

tippecanoe

Tool for creating vector tiles from GeoJSON and other geodata formats.

PMTiles tile inspector

Tool for analysing and troubleshooting PMTiles files.

osmextract

Tool for extracting regional OpenStreetMap data for use with protomaps.

Maputnik

An open source visual editor for the MapLibre Style Specification and PMTiles sources.

The focus of Protomaps is exclusively tile-based cartography and interactive visualization. However, there are also extensions for geocoding and routing:

• Geocoding
  • Nominatim
  • Photon
  • Pelias
• Routing
  • Valhalla
  • OpenTripPlanner