AforAlex
Posts: 85
Joined: Thu May 31, 2012 7:56 pm

SQLServer from the Raspberry Pi?

Wed Apr 23, 2014 9:00 pm

Hi,

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

User avatar
DougieLawson
Posts: 36322
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: SQLServer from the Raspberry Pi?

Wed Apr 23, 2014 9:12 pm

Both databases appear to support JDBC, so crafting something in Java may be the easiest way.

It depends how much control you have over your MSSQLserver system and whether you can create "linked databases" on there.

The biggest problem with lifting data from one database manager and updating it in another is the "heuristic" damage. What do you do to resolve data inconsistency when you push your updated data back to MSSQL? Or do you leave the tables on that system with a long term lock (take them offline?) while the data is away being updated on your MySQL system. That problem has always been a major issue until you get real-time bi-directional updating so that any update on MSSQL is immediately mirrored on MySQL and likewise any update on MySQL is mirrored on MSSQL. (And I'm not talking about something that runs async, it, normally, has to be synchronous and may have to lock the data on BOTH servers for the duration.)

If you can't do that consider what happens when a row has something like an account balance. The MSSQL system pulls the value and increments or decrements it. Meanwhile you've got the same row down on MySQL because you're doing an interest calculation and updating balances with an annual interest payment or an annual charge. The data goes back to MSSQL and one of three things happens: 1. nothing, it's an unaffected row. 2. The MSSQL row is greater than the MySQL row or 3. The MSSQL row is less than the MySQL row. How do you decide which row has the right value? It gets ever more complex when the columns updated on MSSQL aren't the same as the ones update on MySQL until you reach a point where it is 100% impossible to reconcile. That can be resolved by not sending whole rows but only sending updates (which are then re-run on the other server).

Searching for "MSSQL MySQL replication" turns up http://www.symmetricds.org/ but I've not looked whether you could build and install that on your Raspberry Pi. It appears to be a Java based tool so you may be in luck.
Note: Having anything humorous in your signature is completely banned on this forum. Wear a tin-foil hat and you'll get a ban.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

AforAlex
Posts: 85
Joined: Thu May 31, 2012 7:56 pm

Re: SQLServer from the Raspberry Pi?

Wed Apr 23, 2014 9:52 pm

Many thanks for your reply, luckily the nature of the data doesn't require such data integrity. I work at a college and am looking at whether the Pi can be used in classrooms to help monitor attendance.

The idea would be to transfer the timetable data over to the Pi (a task that would take approx every 5 minutes, consist of a couple of thousand rows of data, a couple of hundred Kb in size and exports in fractions of a second). The Pi would have a scanner attached, and would simply log the cards swiped in, try and use the timetable information that its got stored locally to try and return a meaningful message along the lines of confirming the students name, or the expected class etc, every couple of minutes it would then try and post that information back to the SQL Server (to a temporary table). The system would need to be reasonably resilient and would have to be able to cope with not being connected 100% of the time to the network, and in the event of no network access would need to be able to keep going (not indefinitely, but half a day between transfers would be more than acceptable)

The data of which students/staff were scanned in at what location would then be analysed and attendance would get updated and reports produced .... but this last processing of the data would be done on the SQL Server (and recorded against our students MIS system).

JDBC might be the way that I will have to go, a tab out of my comfort zone as not used JDBC or Java before but might be something I have to learn :-(

Thanks

User avatar
DougieLawson
Posts: 36322
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: SQLServer from the Raspberry Pi?

Wed Apr 23, 2014 10:19 pm

In that case you could use a single extract of timetable data, run once per day (off hours) so at, say, 21:00 on a Monday you pull the data that will be used on Tuesday. It can become a read only data source for reference. Yesterday's copy has good enough integrity unless the data is extremely volatile. (Which if I remember my encounter with the timetabling system when I was at high school, (my A-level maths teacher was chief timetabler) once built it was rapidly set in concrete.)

The transaction data becomes more interesting. Do you really need to hold the swipe card data on the Raspberry Pi? How about having a system on the RPi that posts a swipe card read to the Microsoft system and that central server does all of the updating with integrity.

My favourite small systems transaction manager comes to mind for doing that: MQTT. http://en.wikipedia.org/wiki/MQ_Telemetry_Transport http://mqtt.org http://mosquitto.org

MQTT is a one-to-one or one-to-many (broadcast) or many-to-one (data collection) messaging system. You have two types of MQTT connector. The MQTT consumer, that subscribes to a "topic" and receives any messages published to there. The MQTT supplier that publishes messages on a topic. It's versatile and the scope for MQTT is unlimited (it has a payload limit but it's massive), there's a hierarchy for topics and sub-topics including wild-carding (so you can skip a hierarchical level). In the middle is a messaging broker which handles the receipt of published messages and the push to subscribers. There are various quality of service parms that define message persistence, disposition and integrity. Because it is a queuing system you don't need to be connected all the time. Messages can be queued for delivery at the next time the connection is available (although that's an area I've not yet explored because I've always worked with permanently connected brokers).

The biggest real-world example of MQTT is Facebook's messenger.

I run it on both of my RPi systems, my Arduino and on Ubuntu. There's a ready built python library, C/C++ and Java for MQTT (plus a bunch of other languages including a REXX sample that I wrote).

sudo apt-get install mosquitto
will get the open source broker installed and running.
Note: Having anything humorous in your signature is completely banned on this forum. Wear a tin-foil hat and you'll get a ban.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

Return to “Advanced users”