Solutions to call Azure Ms SQL database from linux systems with basic tools (no installs) [Architecture]

Question

Asking an architectural question:

We are build a software solution fully on client resources to monitor/manage scattered microservers (mostly linux)

We would like to go for a installation-free solution on the server side and a plain serverless Azure database to act as a heart of our system. We have chose Azure because security, deployability and flexibility, db will be on users own account.

Even Azure DB has nice external connectivity, seems it must go trough odbc protocols. Tools not natively available in linux distributions.

Sure there is sqlcmd, and it works great (indeed pilot is built on it) but it lack portability and rarely the dependencies are present in the system

Another way we see, is to build a http end point translator to close the bridge, but again, that would be another piece of software running around.

Objetive: call (mostly SPs, we could go 100% SPs) from linux enviroments directly on Azure DBs with no need of installation on linux severs

Any recomendation?

Answer 1

You can access SQL server externally, via applications, only through: ADO.NET, JDBC, ODBC, PHP, and Go. All of them will require to install its respective driver for Linux. You can access SQL server through Azure data studio in Linux but it's not for devs , just to admin and it's just... slow . I would suggest you try PHP, it's not that hard and you'll probably find a dev that can do that.

Answer 2

HARD REQUIREMENTS: nothing should be installed on Linux (just a script running as best with cron)

I don't think there's a no-install solution that is guaranteed to work on any arbitrary Linux server. You already assume the shell for your script is installed as well as cron. Deploying the script and cron job is arguably an install in itself but I suspect you mean binaries when you say nothing should be installed.

IMHO, your best bet to address the hard requirement is to rely only on the most commonly installed packages like curl and call those out as prerequisites for your solution to function. Calling a REST API via curl will probably have the broadest coverage in the wild with already-installed packages.

Objetive: call (mostly SPs, we could go 100% SPs) from linux enviroments directly on Azure DBs with no need of installation on linux severs

PowerShell for Linux could be a script-only solution but it requires installation of that package. One can call Azure SQL Database procs from PowerShell easily.

Answer 3

thank DanGuzman, shell and cron, so far, we have no business case identified without those two

Mostly will be self contained cloud solutions for WP, Joomla, Drupal and similar (for the scenario A, public), the other bizCase is a self develop Ubuntu installation by client already deployed over 3000~4000 instances and with a daily refresh of about 50 of them.

On both cases bash, cron and ftp access is an standard, on first scenario most of cloud providers WILL NOT install any dependency, but you can run anything you upload trough ftp. On second scenatrion, having to install new packages is simple a PITA very unlikely.

Indeed deployment of teh script and prograimg it via cron (or self executed) can be see as an installation process, but one that is possible and allowed to be done ;-)

Calling a REST API via curl will probably have the broadest coverage in the wild with already-installed packages.

That will be awesome GREAT "!!
But I am almost sure it can not be used "directly" onto Azure DBs...
(Mr. Nadella, please, I know it is too late for Saint Claus :-)

[remember: each user/client has its own, as in it is own premises/cloud, database, neither the database must be centralized]

So far, a pilot, includes a "middleware" server which is called by curl, so script generate the "instruction" code in BASE64, calls the middleware, this decode the BASE64 and calls the AzureSQL using sqlcmd, and returns to the client-script.

This middleware seems pretty unnecessary if you obey the logic of its work, but needed as a glue. That is what we try to get rid of.

Could a C++ program using the ODBC driver be compiled as a self executable distributable via ftp? (we don't have so much experience with developing that level, but looking forward the option of a external developer)

Answer 4

But I am almost sure it can not be used "directly" onto Azure DBs.

Correct. To avoid a database driver dependency, I'm suggesting a service layer to insert into the database of your choice instead of connecting directly to the database from the client. That will also be more secure.

I can't speak to how to develop a Linux ODBC app without a dependency on the Linux ODBC Driver Manager.