Want to replicate Filemaker data to web/SQL database.

I have Filemaker server and a Filemaker database that we are using as our in-house database. I just finished developing a subscription based database for the web with nearly identical tables as our in-house database and I would like to set up a data migration plan to move data from the in-house Filemaker database to the web/SQL database on a nightly basis. I currently am using a plan to re-fresh the on-line data monthly, but the updates need to be more regular / daily. Has anyone had to deal with something like this before? Any suggestions would be greatly appreciated!
johnmoedAsked:
Who is Participating?
 
billmercerConnect With a Mentor Commented:
If you want the process to originate from the SQL server, then aside from ODBC, publishing your data as a web page is the only real approach. Use Filemaker web publishing to present the needed data, then you could create some sort of user agent on the SQL server that hits the page on a regular basis and extracts the data from the HTML table. I know this can be done, but I can't tell you how to do it.
 

If you want to have the fastest possible update, you can put a Filemaker client on the SQL server, and schedule it to run, generate export files in some predetermined folder on the server, then use a stored procedure or something to import the files. I think that would be the most efficient approach, and probably the easiest to program as well.


0
 
lesouefConnect With a Mentor Commented:
There is a planning plug-in you can use to trigger actions at regular intervals (can't remember the name now, but can find it back if you like).
What I do is use the NT schedule service on a PC which runs 24h/day to launch filemaker with a database which has a startup script which does the SQL queries to write to mysql, then closes filemaker. I mark the records loaded in SQL to avoid to do them twice.
0
 
johnmoedAuthor Commented:
I was thinking that I would be able to use a nightly SQL script or something that would connect to Filemaker via ODBC. Anything that was new or modified after the last update would be pulled over to SQL and tagged so it wouldn't be pulled twice. Not sure if this is the right way to do it or even how to do it, if this is the route that I will take. If you can remember the name of the plug in that you use, I would like to investigate that option.
Thanks,
John
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
lesouefCommented:
Actually, there are 2 which can do the job.
http://www.wmotion.com/events.html
http://www.troi.com/software/activatorplugin.html
Of course, you could use FM as ODBC server and read data from yr sql application, but it will be slower, FM is very slow as ODBC server unless yr database is really small, and you have to leave a client open 24h/day also.
This may have been improved with v7, but I haven't used it yet in such a situation.
0
 
billmercerCommented:
Generally speaking the way you described would be the "right way". And if you are only transferring a small amount of data at a time, the ODBC option is viable. But Filemaker ODBC is SLOW, and that's the main drawback.

I currently have a system that synchronizes data from a Filemaker database to an MS SQL server automatically several times per day, in basically the same way Lesouef describes. It runs completely unsupervised multiple times per day, and is much faster than using ODBC for the same purpose. The job is scheduled to run every four hours using nnCron Lite from http://www.nncron.ru

Another option would be to publish the filemaker data as a web page, and have your SQL machine pull the data from there on a scheduled basis.

Lesouef, I can confirm that FM 7 ODBC performance is significantly improved. It's now only kind of slow, instead of slower than a myopic slug in a vat of molasses.;)

0
 
lesouefCommented:
myopic ... molasses: one may read this before lunch! bon appétit!
so definitly not fast...
0
 
johnmoedAuthor Commented:
Sorry that I have not gotten back to you on this yet, I have been very busy but I plan to allocate some time later today to explore your suggestions. Which option do you think is the best over ODBC? My database is huge, so it is probably not a good option to use ODBC. If I can schedule an SQL script on the SQL Server to pull new data off of the Filemaker database, that would probably be the best option.
Thanks,
John
0
 
johnmoedAuthor Commented:
I have been working on a script to export the required fields from Filemaker into files that can then be imported on the SQL server. I haven't had time to test them yet, but I think this is the right way to go.

Thanks!
0
 
billmercerCommented:
Thanks! I'd be interested to hear your results.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.