query MSSql from linux

[scud]

Hi
I need a bit of advice if anyone can help.
I would like to schedule a query to a MSSQL DB (windows server) from a Linux machine, and save the result to a Mysql DB on the linux server.
What kind of ( free/cheap )tools would I need to allow this.
many thanks

 

[scud]

Managed to do it with an etl

 

[envo]

And the ETL you chose? Was it free? Cheap? What advice would you have for others who want to attempt the same thing?

 

[Nod]

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools

 

[shadow_man]

And the ETL you chose? Was it free? Cheap? What advice would you have for others who want to attempt the same thing?

Awesomely documented as usual... Really helpful for the next guy with the same issue...

 

[envo]

Awesomely documented as usual... Really helpful for the next guy with the same issue...

Yep, this resource is definitely helping the community, the instructions were so clear and thoughful, I gave him a reputation boost even and blogged about it...

 

[scud]

Nod's link is perfect if you want to setup the MSSql ODBC drivers on a linux box.
Once ODBC is setup you can connect directly to the MSSql server.

or for centOS / RHEL use this tutorial

I use this ODBC driver now, the copying of databases gets done in my own application which is php based.

If you want a quick solution use an ETL like Pentaho. has a fairly high learning curve but very powerful and free.
I found this project which helped get me started initially.

 

[zippy]

Wouldn’t it be easier to write a service of some sort(RESTful, SOAP etc., depending on what skill sets you have access to) and then your Linux application can use this service? The upside is that it’s a service that you can use from pretty much any application.

It’s also a more secure option as you don’t need to expose your myssql database and therefore better practice and more in line with how the this sort of thing is done in the real world

 

[scud]

Wouldn’t it be easier to write a service of some sort(RESTful, SOAP etc., depending on what skill sets you have access to) and then your Linux application can use this service? The upside is that it’s a service that you can use from pretty much any application.

It’s also a more secure option as you don’t need to expose your myssql database and therefore better practice and more in line with how the this sort of thing is done in the real world

That would be 100% better, but I only have direct access to the MSSQL DB. not ideal.

 

[Hamster]

I would've scheduled a python script with Cron tbh.

 

[SBSP]

I would've scheduled a python script with Cron tbh.

+1 as simple as that. ODBC and all that sounds like extra trouble.

With PHP you can also do it, Just point it to the MySQL DB on the and dump the data to a file on a regular basis using cron.
Plenty of examples out there on how to use MySQL and PHP.

 

[scud]

+1 as simple as that. ODBC and all that sounds like extra trouble.

You just need something like FreeTDS if you want Linux to connect to mssql . Nice python starter script here https://github.com/Nick011/MSSQL-to-MySQL