Not quite sure where was best to post this question, so hoping the "advanced" is correct.
I'm looking to connect the RaspberryPi up to a corporate network to do some work. What I would like to do is connect to an SQL Sever and copy over the contents of a table to a locally installed MySQL instance that's installed on the Pi. After doing some work with it I want to then do the reverse and transfer the contents back over to the SQL Server. My primary languages that am wanting to use are Python version 3 and SQL.
The application will have to work offline and won't always have a live connection to the network and SQL Server and hence why I want to transfer the data to access locally.
The intention is also to allow easy scalability and the plan is eventually to have a little army of Pi's (maybe up to 100!!).
I've been having an investigation and have used the 'linked server' facility within SQL Server, and can reasonably easily run a SQL INSERT command that will transfer the data across from SQL Server to Mysql, but it relies on each Pi being set up and linked against SQL Server and hence won't scale up easily ... would work well enough for 1 or 2 Pi's but not for army of Pi's which is ultimately the plan. So this isn't the way to go. Rather I would like the opposite of this where the SQL Server is connected as a 'linked' server and accessible from within my PI (Maybe with in Python 3) but ideally accessible to Mysql. Does anyone know if this possible? and if so how?
Failing this I've thought of one possible option .... it would involve having an export script running on the SQL Server that would export the data to a .csv file which would be located on a network shared drive. The Pi would then check this shared location and when it finds the .csv file would import. The opposite of this could also be done i.e. would check to see whether it can access the shared network location and would export an .csv file which the SQL Server notices and imports. There would be quite a bit of work in doing it this way however and csv files can get a bit messy.
Any thoughts would as always be much appreciated