Hi,
I just wanted to check to see what are people using now a days for SQL. I have always use MSSQL Standard and Express but, I was wondering if I should look at opensource databases like MariaDB or PostgreSQL. I wonder if you guys can shared your experiences.
Thanks
Heh. An invitation to slam Microsoft? I'm in. (I am biased by long exposure to Microsoft, from before Windows existed.)
I use and recommend PostgreSQL. While it is open source, it is commercial-friendly and paid support is available (from EnterpriseDB). Between its leadership on SQL features and standards compliance, and performance, and BSD/MIT-style license, it is the obvious choice for cloud providers (both Google's Big Data and Amazon's RedShift are customized flavors of PostgreSQL). Also, PostgreSQL's timestamptz column type is perfectly compatible with Java's java.util.Date behavior, in both directions (via JDBC parameters).
You can install PostgreSQL on most operating systems. Including Windows, if you must.
While I don't currently use MariaDB, it is a solid choice, also with paid support available. It is somewhat less commercial-friendly, as it has a GPL-style license. So no commercial derivatives can exist, in practice. It has decent SQL standards conformance, if you turn that feature on. (Back-tick identifier quoting is as non-standard as SQL Server's square bracket identifier quoting.) Also, MariaDB's timestamp column type, with precision 3 or greater, is perfectly compatible with Java's java.util.Date behavior, in both directions (via JDBC parameters). (Though there are confused annotations in their online docs suggesting otherwise.)
You can install MariaDB on most operating systems. Including Windows, if you must.
I don't recommend MySQL, at all. Most of its developers switched to, and only contribute to, MariaDB. Including MySQL's founders. Due to the GPL license of MariaDB, MySQL cannot poach any of its new code, and MariaDB has far outstripped MySQL for features and reliability.
I don't recommend Windows for any system that connects to manufacturing systems for production purposes. The risk of compromise accompanying the slightest failure to keep up with modern malware is simply too high. And keeping up with modern malware requires a large enterprise IT group with highly competent staff. (Practically a unicorn.) Where Windows is required for maintenance/programming/troubleshooting of production systems, I recommend a VM on a non-Windows hypervisor, where the hypervisor tightly controls network traffic. I consider any other use of Windows with production systems engineering malpractice.
Microsoft's datetime2 column type is nearly perfectly compatible with Java's java.util.Date behavior, except that MS stores as localtime + timezone, instead of simply storing UTC. Java's java.util.Date is UTC under the hood, and the column's knowledge of its original timezone does not transfer to Ignition. But that is better than not working at all in Java, which was true for several years after MS added this column type.
Some of my rants:
https://forum.inductiveautomation.com/search?q=%40pturmel%20SQL%20Server%20datetime%20order%3Alatest
9 Likes
Thanks
I need to look more into PostgreSQL, but I've been using MariaDB because I'm familiar with it, but on the last job I just finished building in-house (startup is later this year to really test it out), I am still using MariaDB, but instead of using InnoDB for the storage engine which is default, I switched over to MyRocks which was developed by Facebook to try to increase compression to reduce storage and also reduce the number of writes to disk in order to not "burn up" SSDs. We'll see how it ends up working out, but I haven't had any issues with it testing it out here.
I chose this as our customer supplied hardware for the server while not anything I'd recommend, they're running a standalone non-virtualized Windows server with Ignition and MariaDB all on that same server with less than 1TB of storage, so I needed this to be as efficient on storage as possible.
You can read more about it here if you want to dig into details: