VFP9SP2 app, MySQL(MariaDB) secure database on web
Scenario: customer uses my software but has an extremely slow connection to the remote database and as such, queries involving large, sometimes-changing lookup data is slow. Solution: pull data locally for quick lookup. This works if I can do some sort of Sync operation to make sure the local copy matches the remote copy.
The Primary Keys are varchar-40 GUID, and there's a timestamp field as well. My thought was to use those 2 fields for the sync. If it exists in both and the remote timestamp is newer, update the local record. If it exists in the remote but not locally, add it local. If it exists in the local but not in the remote, delete it from local. Any new adds or updates to existing records will then be done to the remote database and then echoed locally.
I was thinking I'd setup MariaDB locally for this local database.
Comments on that plan of attack?
tia, --Mike
How do you modify the timestamp in local mode, or what constitutes a change that you would change the local and not update the backend instead?
On Wed, Jul 12, 2017 at 12:51 PM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
VFP9SP2 app, MySQL(MariaDB) secure database on web
Scenario: customer uses my software but has an extremely slow connection to the remote database and as such, queries involving large, sometimes-changing lookup data is slow. Solution: pull data locally for quick lookup. This works if I can do some sort of Sync operation to make sure the local copy matches the remote copy.
The Primary Keys are varchar-40 GUID, and there's a timestamp field as well. My thought was to use those 2 fields for the sync. If it exists in both and the remote timestamp is newer, update the local record. If it exists in the remote but not locally, add it local. If it exists in the local but not in the remote, delete it from local. Any new adds or updates to existing records will then be done to the remote database and then echoed locally.
I was thinking I'd setup MariaDB locally for this local database.
Comments on that plan of attack?
tia, --Mike
[excessive quoting removed by server]
On 2017-07-12 14:05, Stephen Russell wrote:
How do you modify the timestamp in local mode, or what constitutes a change that you would change the local and not update the backend instead?
You wouldn't change the local only; all changes would be done on the remote first and then copied locally. That's what I meant when I said "Any new adds or updates to existing records will then be done to the remote database and then echoed locally."
Why put up the data off site then now that you know it is a kluge?
Ever considered this if you have to have two? http://www.techrepublic.com/article/how-to-set-up-database-replication-with-...
I would only have one single database server for the client myself and if they have such poor Internet then I'd do it local. All that extra spaghetti to keep it local and sync it sounds like a wart that will only grow over time.
On Wed, Jul 12, 2017 at 1:11 PM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 2017-07-12 14:05, Stephen Russell wrote:
How do you modify the timestamp in local mode, or what constitutes a change that you would change the local and not update the backend instead?
You wouldn't change the local only; all changes would be done on the remote first and then copied locally. That's what I meant when I said "Any new adds or updates to existing records will then be done to the remote database and then echoed locally."
[excessive quoting removed by server]
On 2017-07-12 14:30, Stephen Russell wrote:
Why put up the data off site then now that you know it is a kluge?
Ever considered this if you have to have two? http://www.techrepublic.com/article/how-to-set-up-database-replication-with-...
I would only have one single database server for the client myself and if they have such poor Internet then I'd do it local. All that extra spaghetti to keep it local and sync it sounds like a wart that will only grow over time.
Thanks for the link, Steve! As for why not doing it local? These folks will be accessing the app and their data from various locations, so hosting it on a database they can all share is key. I'm guessing I could setup the database locally on each, and setup the replication on each to communicate to the common web database? This system uses GUIDs as the PKs for nearly all tables.
On 2017-07-12 14:30, Stephen Russell wrote:
Why put up the data off site then now that you know it is a kluge?
Ever considered this if you have to have two? http://www.techrepublic.com/article/how-to-set-up-database-replication-with-...
I would only have one single database server for the client myself and if they have such poor Internet then I'd do it local. All that extra spaghetti to keep it local and sync it sounds like a wart that will only grow over time.
From the article:
"The easiest means of database backup: You'd be hard pressed to find an easier method of getting a real-time database backup than replication. And with MariaDB, setting up replication is incredibly simple. Give this a try, and you can rest easier knowing you always have a backup database to use."
This works IF it means the Father and Son can each work on their local database (in their own homes/offices) and then have those changes echoed to the WEB database. (Now you see the challenge???) This is NOT for a simple 1-user-in-1-location scenario.
On Wed, Jul 12, 2017 at 3:58 PM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 2017-07-12 14:30, Stephen Russell wrote:
Why put up the data off site then now that you know it is a kluge?
Ever considered this if you have to have two? http://www.techrepublic.com/article/how-to-set-up-database- replication-with-mariadb/
I would only have one single database server for the client myself and if they have such poor Internet then I'd do it local. All that extra spaghetti to keep it local and sync it sounds like a wart that will only grow over time.
From the article:
"The easiest means of database backup: You'd be hard pressed to find an easier method of getting a real-time database backup than replication. And with MariaDB, setting up replication is incredibly simple. Give this a try, and you can rest easier knowing you always have a backup database to use."
This works IF it means the Father and Son can each work on their local database (in their own homes/offices) and then have those changes echoed to the WEB database. (Now you see the challenge???) This is NOT for a simple 1-user-in-1-location scenario.
I have no MariaDB experience but doing this in SQL Server is not easy as this reads. They are more taking to the idea of local country DBs that interconnect but you are suggesting the same thing I beleive between a few locals and 1 Master.
On 2017-07-12 17:50, Stephen Russell wrote:
I have no MariaDB experience but doing this in SQL Server is not easy as this reads. They are more taking to the idea of local country DBs that interconnect but you are suggesting the same thing I beleive between a few locals and 1 Master.
Honestly, I found M$ SQL Server to be overly complicated on a great many things. That was part of the appeal of MySQL...far less geeky and easy to configure/use.
Easy, Fast, Secure. Pick any two.
Sql Server, Oracle, DB2, Postgres are all complex because data is not easy when done properly. Learning how to index properly for FAST data retrieval from billion row tables is what takes skill. Skill is also knowing how much time tweaking an index will take is also required when you have windows for maintenance at low use points in the week. I believe that Marina is much the same way.
As a heads up when you have multiple SQL centers around the world generating the next key is a little more complicated. If you were going to have them just around the country for local offices you too will run into this as well. I am just trying to give you a heads up on potential issues that will pop up.
If I were doing this I'd attempt to follow how other leaders in computing systems have done this. You are selling the cloud so it only makes sense that this too will be driven by cloud-oriented technology.
Good luck.
On Wed, Jul 12, 2017 at 5:13 PM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 2017-07-12 17:50, Stephen Russell wrote:
I have no MariaDB experience but doing this in SQL Server is not easy as this reads. They are more taking to the idea of local country DBs that interconnect but you are suggesting the same thing I beleive between a few locals and 1 Master.
Honestly, I found M$ SQL Server to be overly complicated on a great many things. That was part of the appeal of MySQL...far less geeky and easy to configure/use.
[excessive quoting removed by server]
"It depends." - Ted Roche
Why reinvent the wheel? https://mariadb.com/kb/en/mariadb/setting-up-replication/
What is an acceptable delay in the replication process? And what is the size of the data and how much data is changing daily? Hourly?
-Kevin
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of mbsoftwaresolutions@mbsoftwaresolutions.com Sent: Wednesday, July 12, 2017 1:52 PM To: ProFox Subject: Syncing data locally from a remote database
VFP9SP2 app, MySQL(MariaDB) secure database on web
Scenario: customer uses my software but has an extremely slow connection to the remote database and as such, queries involving large, sometimes-changing lookup data is slow. Solution: pull data locally for quick lookup. This works if I can do some sort of Sync operation to make sure the local copy matches the remote copy.
The Primary Keys are varchar-40 GUID, and there's a timestamp field as well. My thought was to use those 2 fields for the sync. If it exists in both and the remote timestamp is newer, update the local record. If it exists in the remote but not locally, add it local. If it exists in the local but not in the remote, delete it from local. Any new adds or updates to existing records will then be done to the remote database and then echoed locally.
I was thinking I'd setup MariaDB locally for this local database.
Comments on that plan of attack?
tia, --Mike
[excessive quoting removed by server]
On 2017-07-12 14:05, Kevin J Cully wrote:
Why reinvent the wheel? https://mariadb.com/kb/en/mariadb/setting-up-replication/
What is an acceptable delay in the replication process? And what is the size of the data and how much data is changing daily? Hourly?
-Kevin
I only wanted to really do this as an added option/feature, not the defacto standard. I want to limit my setup time with the new clients if possible. Hence, this "roll your own" concept.
Another possibility: do not synchronize anything, access remotely with Remote Desktop
Jose Enrique Llopis
-----Mensaje original----- De: ProFox [mailto:profox-bounces@leafe.com] En nombre de mbsoftwaresolutions@mbsoftwaresolutions.com Enviado el: miércoles, 12 de julio de 2017 19:52 Para: ProFox Asunto: Syncing data locally from a remote database
VFP9SP2 app, MySQL(MariaDB) secure database on web
Scenario: customer uses my software but has an extremely slow connection to the remote database and as such, queries involving large, sometimes-changing lookup data is slow. Solution: pull data locally for quick lookup. This works if I can do some sort of Sync operation to make sure the local copy matches the remote copy.
The Primary Keys are varchar-40 GUID, and there's a timestamp field as well. My thought was to use those 2 fields for the sync. If it exists in both and the remote timestamp is newer, update the local record. If it exists in the remote but not locally, add it local. If it exists in the local but not in the remote, delete it from local. Any new adds or updates to existing records will then be done to the remote database and then echoed locally.
I was thinking I'd setup MariaDB locally for this local database.
Comments on that plan of attack?
tia, --Mike
[excessive quoting removed by server]
Use a web service, or RDP (or similar) sessions to a machine on the same network as the remote db. Accessing databases of any kind directly over the public internet is a bad idea IMO
-
Alan Bourke -
José Enrique Llopis -
Kevin J Cully -
mbsoftwaresolutions＠mbsoftwaresolutions.com -
Stephen Russell