Integration between VFP db and a CRM system with a SQL2005 backend

[chriscboy]

Hi,

We have been using a VFP database in our company for over 8 years. In a few months time the Marketing dept is introducing a CRM system (Sage MME) which is going to be installed on SQL Server 2005. The idea is the CRM is going to replace many aspects of our existing database, mainly company,contacts,sites,quotes and orders. However this is not going to be a turnkey system and we are going to need to run the VFP database and the CRM system side by side. After talks with the suppliers of the CRM solution, it has been decided that any changes made to the VFP database should be propogated to the SQL server and visa versa.

This is my first time integrating with another DBMS and I would like some advice. The CRM supplier has suggested that we use ADO to send any changes to the SQL Server using DML statements. I am assuming I can write some code that will do this on the various INSERT,UPDATE and DELETE triggers on the tables we need to sync.

What would you recommend doing in a situation like this, and what sort of performance hit is the VFP application going to take when we make changes to the tables? The VFP app is run directly from the server and we have approx 50 users.

 

[Scott24x7]

If it does not need to be "real time" then process as batch at the end of the day... this will minimize the amount of code you have to write, and simplify your sync. You can then use SQL import functionality, and automate that from a "update" table.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Mike Lewis]

Chris,

A lot depends on whether you want to "push" or "pull" the data.

By push, I mean that you will write VFP code that uploads the data from your existing database, either in real time or as a batch, say, at the end of the day.

By pull, I mean the opposite. Your code will run on SQL server, and will fetch the VFP data, again either real time or in a batch.

Given your experience of VFP programming, you'll probably go for the push option. If you do, my advice would be to forget about ADO. The CRM vendor probably only recommended it because he didn't realise how powerful VFP is. In my opinion, ADO is great on platforms that don't have VFP's pre-eminent database facilities, such as VB, but is vastly more complicated than the features built into VFP.

Instead of ADO, you should look at SQL Pass-through (using SQLEXEC(), etc), or perhaps remote views or cursor adapters. These are all relatively straightforward, and although some people in this forum get quite fanatical about their relative advantages and disadvantages, they are all completely suitable for what you want to do.

My own preference would be to use SQL pass-though, and to write some code that will programmatically generate the required INSERT and UPDATE statements to move your data across to the server. But that's just my personal choice.

As for performance hit, the work of sending the updates up to the server will impose a load on your existing database, but it's hard to say how bad it will be. Everything depends on the volatility of the data (that is, the degree to which new data is inserted and existing records are updated).

I hope this has given you a start. Come back if you have any specific questions.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[chriscboy]

Thanks for the answers. The syncronisation of the two databases will need to be done in real time.

Mike,

I like your idea about using SQL pass through. Out of remote views, SQL pass through, and cursor adaptors, which would be the best performance wise?

We are using VFP7 so assuming we upgraded to VFP8 we could use cursor adaptors, but would they be slower because of class instantation for each update?

Thanks for the info.

Chris

 

[Mike Lewis]

Chris,

There is really no substantial difference in performance betwen SPT, remote views and cursor adapters.

Although SPT has the least overhead of the three methods, under the skin, they all do the same thing. The bulk of the performance hit will be the time taken to send the request over the network to the server, and for the server to process it. The differences between SPT, RVs and CAs within VFP will be very small in the comparison.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[baltman]

Mike,

I've been integrating with a SQL Server DB and was unable to figure out how to get an asynchronous connection with a SQL VIEW so I switched over to SQLSTRINGCONNECT and SQLEXEC. Did I miss something? Help isn't very clear on the topic.

If SQL VIEW cannot be asynchronous, that would be a major difference.

Thanks,
Brian

 

[Mike Lewis]

Brian,

What is a "SQL VIEW"? Do you perhaps mean a remote view? If so, these are by their nature asynchronous.

When you open or requery a RV, the records are received in batches. Once the first batch has arrived, you can start processing it while you are waiting for subsequent batches.

You can control the flow of records by altering the FetchSize property. This determines the number of records in a batch, the default being 100. Either change FetchSize programmatically via DBSETPROP(), or edit the "Number of records to fetch at a time" in the Advanced Options dialogue in the View Designer.

I hope this helps. If I've misunderstood your question, my apologies.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[baltman]

Mike,

Views created and stored in a DBC via "CREATE SQL VIEW ConnName REMOTE as SELECT..." The app freezes until the server responds which takes 15-30 seconds do to the filters, joins, amount of data (and current lack of appropriate indexes). Pulling the result down may be asynchronous but waiting for the server is a major problem.

SQLEXEC doesn't seem to have that problem. I can issue a preliminary SQLEXEC on form init and allow the user to go about their business until the data becomes available.

I'm probably just missing something simple...

Thanks,
Brian

 

[Mike Lewis]

Brian,

So, what you're saying is that it is taking a long time for the first record to be received from the server. In that case, the fact of the view being asynchronous isn't really relevant. The problem is that the query is simply taking too long to execute, not that the data is taking too long to come across the network.

I'd guess that the fact that the SQLEXEC() isn't taking so long has got nothing to do with your choice of remote view vs SQL pass-through. After all, a remote view is nothing more than a SELECT statement in a fancy overcoat. Behind the scenes, VFP is doing the same thing with a RV that you are doing with SQLEXEC().

Mike Yearwood might be right about the locks on the DBC, but that should be easy to verify. My approach would be to monitor the network traffic, to see if there is an unexpected delay between the command being sent to the server and the data coming back. That should tell you if the problem lies with the back end or something within VFP.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk