Using MS SQL server instead of PostgreSQL

A Survey Solutions user Alex Burton has sent us the following question:

We’ve got MS SQL installed and want to get it working with Survey Solutions on our own server instead of using postgresql, …

Let me answer in two parts:

Part 1. While the two systems are called ‘databases’ it doesn’t mean that you can simply hack one out and replace with another. (this trick exists in programming, and is known as ‘drop-in replacement’, but this is not such a case). This is because the code of Survey Solutions may be specific to one or another DBMS, or rely on a particular capability of the system, limit, behavior on default, which differs between them.

Thus it is not an arbitrary requirement “to have some sort of a database”, but a very specific requirement “to have that database” and thus it is a prerequisite for installation.

Note also, that this is a prerequisite of the current version of Survey Solutions. A future version of Survey Solutions may utilize a different database, or even a different type of a storage reservoir. Thus always check the requirements and the prerequisites for the server to determine the correct software that needs to be installed alongside the Survey Solutions.

This doesn’t preclude one from still having multiple DBs on the same machine or network if that is so required for data analytics.

Part 2. It is not specified in the question, but based on the previous interactions with users the subtle motivation for such questions is that the user wants to access the data stored by Survey Solutions directly, bypassing the API. This is absolutely the worst idea, which will lead sooner or later to the DB being corrupt beyond repair either because :

  • the user code is not anticipating the internal complexities of the relationships between various DB objects, or because
  • the format of storage changes from version to version, or because
  • our migration code, which updates the database from version to version, will fail to process the hacked-in values, or
  • other similar and diverse reasons.

While this may not be the case of Alex, but I felt it important to mention this to prevent disasters caused by this approach in the same discussion.

Only if such direct access is announced as supported and documented, it will be safe to utilize it. Until then, please use the API, which is responsible for maintaining the internal data consistency.

Best, Sergiy