SQL Specialist - Laboratorium Instrumentation producer

An organisation that's large in producing and selling al kinds of laboratory related products, produces several very complex instruments.
As part of normal instrument activities, these instruments perform all kinds of internal measurements to make sure the system itself works well. These measurements are used for various analytics, including usage and wear of components. Based on these analytics, the instruments are improved and maintenance of these instruments is planned more efficiently. Instead of regular maintenance based on time or hours used, maintenance is only done when really needed. Also, specialistic parts that require a long production time, are created just in time before they are needed. This way they are available when really needed, immensely reducing downtime for the instrument. 

What started as a nice trial, soon grew into a successful product. Due to this fast growth, this organisation ran into several blocking performance issues, which all seemed to concentrate around the databases.

This organisation asked me to consult the databases and improve them where possible. Based on my advice, the database setup, datamodel, queries and self made applications are immensely improved. Additionally I re-opened the communication Iines with the large and very capable international DBA team that helped out improving the database even further, mainly on regular maintenance, setup and backup level. Here I acted as a liaison between the DBA team and the developers as well as the systems operational support team. By result, all performance issues were solved.

While investigating and improving the databases, I noted that especially the PostgreSQL user management was poor, insecure and not complying to internal security regulations. Once the biggest performance issues were solved, I was asked to work on improving the security in a workable way. This resulted in the installation and setup of an open source solution called ldap2pg, which synchronises all users within one or more Active Directory group(s) as users into the database and provides them with a fixed set of roles. This way user and password management can be handled safely by Active Directory, consistent with the existing SQL Server setup and in line with security guidelines.

Now that the systems were at a consistently good performing level and database connections were secure, the organisation felt a need to reduce licence costs. I was asked to research the possibilities of migrating the SQL Server databases to a cheaper Open Source solution, having PostgreSQL, MySQL and Aurora as the most feasible candidates, but Cockroach DB was taken along in the research as well. The result of this study is a recommendation on the most useful solution including a recommendation on the approach for the migration.

Coming from that same need to reduce costs, I was also asked to research Open Telemetry as an option to collect performance related metrics from the database. Resulting in a fully working, tested and documented setup of Open Telemetry, including a large set of custom built queries to collect database metrics. Including Datadog reports that provide insight in performance bottlenecks.