Database recommendations?

[dontommy]

Hey
New to the forum.. hey

My work station have for years been different variants of linux.
However on the server side, I am running Windows - and only because of the MS SQL Server.
The best part of MS SQL Server, is for me, the SQL Agent Jobs. Where I easy can call a number of python scripts, and stored procedure in the correct order. And get a nice overview over the different agent jobs running, and see where they fail etc etc.

However I want to change my server to linux - and for that I need a database system, that have features and nice overview like the MS SQL Server and SQL Agent Jobs.
And have a nice GUI tool, to work in.

What can you guys recommend? MariaDB, mySQL, Postgre or another?
Let me hear your thoughts, thanks

(And no MS SQL Server for Linux, is not an option for me)

 

[arochester]

To some extent it depends on what you are using it for and how much data you are carrying.

http://www.yolinux.com/TUTORIALS/Li...sidered the most advanced. ... More items...

 

[f33dm3bits]

Why is MS-SQL Server for Linux not an option for you, if you have experience with MS-SQL why no use what you know? If you want a real database system like MS-SQL you could have a look at Oracle.

 

[KGIII]

I use MySQL and MariaDB, though I seldom need to interact with 'em. I've even got a small site that uses SQLite, which runs like a champ. When you say a nice GUI to work with, what exactly are you wanting to do with it? I use phpMyAdmin (for example), which can't really be referred to as a 'nice GUI' but it meets my simple needs.

 

[captain-sensible]

I use MySQL and MariaDB, though I seldom need to interact with 'em. I've even got a small site that uses SQLite, which runs like a champ. When you say a nice GUI to work with, what exactly are you wanting to do with it? I use phpMyAdmin (for example), which can't really be referred to as a 'nice GUI' but it meets my simple needs.

I also like SQLIte3 it has apparently capacity circa 281TB surely thats enough for most web s ; MySQl has user db password and user but doesn't add anything to whats using it. What would you say are thr downsides of Sqlite3 ?

 

[KGIII]

What would you say are thr downsides of Sqlite3 ?

Last I knew, it was limited to just 2 GB? Other than that, it was great. If that has been changed, I'd not be surprised to see more projects adopt it. It's fast, doesn't require installation, it's easily transferred, etc... It's a great system.

 

[captain-sensible]

on sqlite site they are now quoting > 200TB : https://sqlite.org/limits.html

 

[KGIII]

That's impressive. I'd expect to see some projects offering it as a solution/choice. I know you can configure WordPress to use SQlite with a plugin, and WP runs a giant chunk of the 'net.

 

[captain-sensible]

i've got WP on apache local host (for research ) with sqlite plugin ;this one:

=== SQLite Integration ===
Contributors: kjmtsh
Plugin Name: SQLite Integration
Plugin URI: http://dogwood.skr.jp/wordpress/sqlite-integration/
Tags: database, SQLite, PDO
Author: Kojima Toshiyasu
Author URI: http://dogwood.skr.jp/
Requires at least: 3.3
Tested up to: 4.1.1
Stable tag: 1.8.1
License: GPLv2
License URI: http://www.gnu.org/licenses/gpl-2.0.html

last time i looked it wasn't officially available . Also i found it doesn't work with e-commerce eg woocommerce. I use sqlite with all my web don't see the point of MySQl

 

[KGIII]

last time i looked it wasn't officially available

No, you need a plugin - as you have done. It's not native.

Also i found it doesn't work with e-commerce eg woocommerce.

That's going to be a showstopper for greater adoption. Those are essential plugins for some folks and are entire ecosystems.

But, maybe... We need to find a way to make it trendy, and that'll lead to greater adoption. Heck, it's even ACID compliant, I'm pretty sure. I'm pretty sure you can even host it on an external server. I'm not so sure if you can run it across clusters, but there's probably a way.

 

[dbdemon]

I'm not so sure if you can run it across clusters, but there's probably a way.

Yes, you can, with distributed sqlite a.k.a. dqlite:

Canonical Dqlite | Canonical Dqlite

Dqlite is a fast, embedded, persistent SQL database with Raft consensus that is perfect for fault-tolerant IoT and Edge devices.

dqlite.io

It's important to note SQLite's limitations and only use it for appropriate use-cases:

Appropriate Uses For SQLite

TL;DR: Don't use it if the data is separated from the application by a network, if there are many concurrent writers, or big data (terabyte area). In those situations, you should use a proper client-server database system.

 

[dbdemon]

Back to the original question about alternatives to MS SQL and SQL Agent Jobs:

The best part of MS SQL Server, is for me, the SQL Agent Jobs. Where I easy can call a number of python scripts, and stored procedure in the correct order. And get a nice overview over the different agent jobs running, and see where they fail etc etc.

However I want to change my server to linux - and for that I need a database system, that have features and nice overview like the MS SQL Server and SQL Agent Jobs.
And have a nice GUI tool, to work in.

What can you guys recommend? MariaDB, mySQL, Postgre or another?
Let me hear your thoughts, thanks

MariaDB has quite extensive documentation for people who are migrating from SQL Server:

Migrating to MariaDB from SQL Server

mariadb.com

I'm not familiar with SQL Agent Jobs, but it sounds a bit like what I'm trying to do in a little Github project here: https://github.com/DiamondLightSource/db-reports

It doesn't have a GUI or anything yet, but that is a long-term goal. My current plan is to integrate it with Ansible.

 

[Diputs]

To some extent it depends on what you are using it for and how much data you are carrying.

http://www.yolinux.com/TUTORIALS/LinuxDatabases.html#:~:text=List of Linux Databases and Database Related Links,considered the most advanced. ... More items...

Now that's an understatement

 

[Diputs]

Why is MS-SQL Server for Linux not an option for you, if you have experience with MS-SQL why no use what you know? If you want a real database system like MS-SQL you could have a look at Oracle.

Wasn't going to say it first, but database on Linux ... I have only 1 answer: Oracle
But then you'd need the correct distro

 

[Diputs]

Last I knew, it was limited to just 2 GB? Other than that, it was great. If that has been changed, I'd not be surprised to see more projects adopt it. It's fast, doesn't require installation, it's easily transferred, etc... It's a great system.

2GB ? Haha, that's pathetic

That's not a database, it's a data mini-collection

 

[KGIII]

2GB ? Haha, that's pathetic

That's not a database, it's a data mini-collection

They appear to have fixed that limit, according to a comment in the thread.

Conceptually, it's a neat project. I do have a private site with a to-do list and it uses SQLite. Man, I need to nuke that site or actually use it.

 

[dos2unix]

You can actually download and run Oracle on Linux.

https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html

My next choice would be postgresql.

MySql/MariaDB is faster if you have 20 users or less, but it doesn't scale well.

Postgresql is enterprise ready. Once you get past 25 users or so, it's usually faster than MySQl.
It has a lot more plugins, and procedural languages than MySql.

Postgresql supports table sizes up to 32TB.

Major Companies Using PostgreSQL: Purposes & Examples

Want to know which companies use PostgreSQL? Learn why they choose this solution.

learnsql.com

Apple, IMDB, Spotify, Instragram, Twitch, Reddit, even NASA uses postgresql.

 

[Diputs]

You can actually download and run Oracle on Linux.

https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html

Oracle For The Win

 

[APTI]

I have been using mysql/maria and I have zoneminder security cameras using it, my point of sale system, and LCARS my linux computer control system. All running mysql / maria and they all run concurrently without problems for years. Constant use including some web based inquiries. myphpadmin is nice but I never really use it.

 

[KGIII]

Oracle...

As has been said by people less kind than myself:

One Raging A**hole Called Larry Ellison

Before you laugh it off, I have personal reasons to be annoyed. My company hired them to incorporate their product into our systems. They failed to do so. We gave them additional time, a lot of it. They failed to do so. I kicked them out for breach of contract - and because my employees were tired of dealing with them.

So, Oracle sued me.

We won but ended up paying all the legal fees.

Oracle owns MySQL as well, for those who do not know. As I recall, that's why we have LibreOffice. That same deal included OpenOffice. So, it got forked. It was a SUN thing.