Unsolved How to sync MSSQL with Maria DB
-
I have a requirement for syncing (say every 10 minutes) a Maria DB database with MS SQL 2012 server over a VPN connection.
My LAMP server and VPN are up but I have no idea how to talk to the MS SQL server from the Linux box.
Can someone give me some guidance please?
-
I would start by looking for "mysql mssql sync" on Google, as MariaDB is "just" a fork of MySQL.
This could help:
http://stackoverflow.com/questions/1951174/sync-between-sql-server-and-mysql-server -
@thwr said in How to sync MSSQL with Maria DB:
I would start by looking for "mysql mssql sync" on Google, as MariaDB is "just" a fork of MySQL.
This could help:
http://stackoverflow.com/questions/1951174/sync-between-sql-server-and-mysql-serverYou can setup a linked server in MSSQL pointing at the MariaDB and then run a scheduled stored proc that imports your data.
-
@JaredBusch said in How to sync MSSQL with Maria DB:
@thwr said in How to sync MSSQL with Maria DB:
I would start by looking for "mysql mssql sync" on Google, as MariaDB is "just" a fork of MySQL.
This could help:
http://stackoverflow.com/questions/1951174/sync-between-sql-server-and-mysql-serverYou can setup a linked server in MSSQL pointing at the MariaDB and then run a scheduled stored proc that imports your data.
That's what the SO post said
-
@thwr i assumed, but did not click through to read it.
-
Thanks everyone for your help!
Linked MSSQL server may not be viable in my situation as the MSSQL server is maintained by our service provider on their own network and I won't be able to setup anything on their network.
However, I did find something here http://dominik.szynk.net/centos-6-x-and-microsoft-sql-server-odbc-linux-driver/ which seems to be the way to in my case.
-
There can be complicates, MS SQL and MariaDB are not directly compatible - sometimes you can sync and sometimes you cannot. They can contain different data. This might be simple or extremely complex.
Let's back up... why do you want to do this? What is driving you to use two different database platforms for the same data?
-
@scottalanmiller
Let me explain. We are into wind farm development industry and recently, I have been tasked to develop an in-house system to collect and store data from our various Wind farms so that our research team could use this data to further improve our wind modelling software.At the moment, we are downloading a "sub-set" of this data from a dashboard provided by our turbine manufacturer by going through a VPN tunnel to their network. When I discussed this requirement with our turbine manufacturer, they gave us read access to our specific database on their MS SQL based database server. Since our researchers are using LAMP for development works I setup a LAMP server using Cent OS 7 and connected to this remote database using the Linux ODBC drivers supplied by Microsoft over a vpnc session.
Now I could connect to the remote MSSQL server using the isql command and query the database.
However, I still need to figure out a way to sync this remote database to my local mysql database.
"Sync" may not be the right word to use as I would need to copy only a specific set of tables every 10 minutes in one direction (MSSQL -> MYSQL)
Would this be possible without buying any third party software tools (like https://dbconvert.com/mysql/mssql/)?
-
@sn Oh yes, I think the use of the term sync here might be the issue. You don't want to connect these two disparate databases, you don't actually want IT to be involved at all. This is a task for your developers to grab the data that they want (every ten minutes) via a query of their own making and to put that data into their own database via their own code. You (as the IT Pro) should not be engages for this process (except possibly to pop their code into the crontab to run every ten minutes) at all... this is purely a task for your developers because it's all work that should be being done by their code and only they know how it ultimately makes sense to be retrieved and stored.
I think that using the term "sync" and approaching this like an IT Pro instead of as a developer is making a simple problem and task seem like something complex and weird.
-
Another way to think of it...
This is not about MS SQL Server or MariaDB at all, it's about pulling data from an external data source and putting that data into a local data source that you control. Word it in that more generic way and it becomes obvious that this is a developer task and a simple one. It's only when the databases are specified that it suggests that maybe the IT department can use some database tools of some sort to do this... and in some special cases maybe they could, but that's the wrong approach and would not work even 50% of the time and would not remain reliable.
Doing a database to database sync would break if the databases get changed or updated.
-
I tried to do this about 10 years ago to deal with transitioning a company we had bought out, to "sync" two mutually incompatible pieces of software. The biggest issue is primarily the column types are so different in some cases, I had to write a custom piece of software to even deal with it. It was a nightmare.
I can tell you too that the MySQL Workbench tools for converting MSSQL data to MySQL/MariaDB don't work worth a damn unless your MSSQL schema is very simple and has no defaults. For some reason MySQL/MariaDB doesn't allow more than one current date for column default, but MSSQL does. So, if you've got a table with more than one of them, you're screwed for conversion, and that's just one example.
Now, going back the otherway is an even bigger nightmare, so if you're thinking of syncing back and forth, it is not going to happen.
After we got it working the primary project then our primary software just began inserting/updating directly into the MSSQL database and our MySQL databases, and reading only from MySQL. Of course, this meant anyone using old things which used the MSSQL data, it was effectively read-only, but that was fine for our situation. The next project became to just get rid of that old software completely to avoid MSSQL at all.
I'd setup a local MSSQL database and connect to that, and essentially slave it to the remote one, or master-master if you need to sync it both ways.
So, really, either use only MSSQL or only MySQL/MariaDB, seeing how you already seem to have MSSQL, I'd stick with it. You can find some pretty generic connectors out there too (as you mentioned) or just connect to the remote one. It's not worth it to try to do what you are trying.
-
I hope this resource will help you.
-
Did any of these resources end up getting you to where you needed to be? Or are you still looking for more guidance?
-
@scottalanmiller said in How to sync MSSQL with Maria DB:
Did any of these resources end up getting you to where you needed to be? Or are you still looking for more guidance?
We wrote a PHP script and at the moment it is working the way we wanted. Script basically polls the MS SQL database every 10mins and looks for new records and copies to our local MariaDB. It was a great learning curve though
Thanks everyone for your help and suggestions!
-
@sn said in How to sync MSSQL with Maria DB:
@scottalanmiller said in How to sync MSSQL with Maria DB:
Did any of these resources end up getting you to where you needed to be? Or are you still looking for more guidance?
We wrote a PHP script and at the moment it is working the way we wanted. Script basically polls the MS SQL database every 10mins and looks for new records and copies to our local MariaDB. It was a great learning curve though
Thanks everyone for your help and suggestions!
Yup, that's probably about the best way to handle this.
