Migrating to SQL server... looking for some tips.

[EzLogic]

In the process of moving back end data of one of our large application to SQL 2012 express.


currently:
ERP applications, built in VFP 9 SP2 application with native foxpro tables/dbc
ERP (warehouse management/inventory management/CRM application/Invoicing/ordering/fulfillment/ 296 forms
20 prgs (some are prg classes)
108 reports
17 classes (vcx) etc..

all controls are classed, and i do have a main bizobj class that handles alot of things for reporting, data writing, etc..
but, still all of the form do have their own methods/events that i have codes in them that some are for data handling/seeking/etc..

current tables are normalized very well (in my opinion)

Successfully, I wrote a small prg that will:
1- take each table and recreates it in SQL including the data structures
2- Recreate indexes for each table
3- uploaded/migrated the dbf data to each sql 2012 table using bulkxml load etc..

i know how to get data from sql, update, insert, etc.. all the goodies.
sweet.

Client count: 20 different businesses use this particular product (and growing)

Challenge:
only about 12 of the clients currently use the ERP with heavy data loading and alot of transactions.. the others have really minimal transactions.
those clients, most of them have about 4-10 warehouses (distribution offices) across the US, 50+ users, FoxWeb connection for web presence/portal, 4000+ customers they have.

those clients (the 12 master dealers), they are doing a lot of transactions especially on few tables (serial table, sales details, commission tables, residual tables, inventory transfer detail tables, purchase order receipt tables "serialized inventory").. So, In a year or so, some of those guys would need to archive some data etc.. and i am worried about performance etc..

And some, are already experiencing a slight performance in the data access part..

I am debating on keeping 2 version (dbf 'lite version') and (pro 'sql version') of the ERP.

The reason: I will charge different amount per ERP for the SQL version, than the DBF version. I am thinking, it is a marketing tactics and some of the 'lite' customers, have minimal hardware and do not have the $ to spend on descent servers (or workstation). and they have 2 or 3 users only..


what are your thoughts on handling 2 different data reading in one application.
would you have 2 different projects?
would you write the codes separately to handle data?

if llSql 
   && goSQLBizObj will handle data reading
   lcCursor = goSQLBizObj.Seek(lcString,lcField,lcTable)
   if used(lcCursor)
      select (lcCursor)
      if !eof()
          && we're on the record now...   
      endif 
        
  
else
  select myTable
  set order to myIndex
  if seek(lcString) 
     && found
   endif
endif

or should i just make the entire app SQL dependent?

i am lost on making that decision.
1- hybrid app for lite version and for sql version (a lot of coding and maintaining dbfs or sql depending on client and settings)
2- just go sql for all clients and migrate them and easier app to maintain and more robust
3- keep almost everything foxpro, and just move the big tables to sql so it is both for everyone?

any thoughts on this matter would be greatly appreciated...





Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[Mike Lewis]

Hi Ali,

I've been down this road many times now. I have to tell you that it is far from simple. I expect you already know that.

On the other hand, the fact that you've got as far as you have is a good sign.

To answer your specific question, I would personally be very hostile to the idea of two separate versions, one with DBFs and one with SQL tables. I started down that road on my very first SQL Server migration (back in 2001, if I remember right), and all I got was double the number of headaches. My advice would be a single version, with the front end in VFP and the back end in SQL Server.

Now, having said that, the decision isn't only a technical one. You will have marketing factors to take into account, and perhaps other things you don't know about yet. Nevertheless, you should really aim to get all your customers using the same application and the same type of database if at all possible.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[EzLogic]

Thanks for the response Mike.
as always, i value your input.

I do anticipate it being a very challenging process. i do.
and i am leaning toward your thoughts of having 1 unified app. too much to maintain 2 apps of the same kind. i agree.

due to the amount of time this will take to "convert" to full sql, i have thought of this (early thoughts still).

1- keep 90% of the app with native vfp
2- take the 10% of the app (the culprit tables) and move those to sql.

those tables get updated once a month with import (carrier commissions/residuals/etc..) and reporting is done on them.. that's it.. so, it is import, reconcile, and bulk upload, then report on them..

you have any thoughts on this?



Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[Nigel Gomm]

Ali,

If the problem is performance querying very large tables over a network .... another, home grown, option would be to have a "server" written in VFP monitoring a "message queue" (in fact a new dbf) running on the same machine as the dbfs, performing the query (against local dbfs), and returning the resultset in the "message queue". For large resultsets return the name of a temp dbf (in the shared network folder) with the resultset that your app on the client pc can open and use.

Sounds flakey i know and no one would ever design such a system at the outset but i was faced with a similar issue a while ago and implemented this and it's working well.

It does mean you don't have to rewrite the whole app to use SQL server and you can choose which customers to turn this on for.

if you are interested i can go into more depth and share some code.

hth

n

 

[craigber]

Some good, and IMO, bad advice given here.

- One app that just does SQL Server is a great idea. Another option is to create a true data layer that can switch between VFP & SQL via configuration.
- I would not go with the idea of some data in SQL Server and some in VFP. At some point, the business needs will demand all the data in one location.
- I would not charge more for SQL version unless it has additional features
- If you go with the messaging option, do not write your own messaging system. They are difficult to do so that they scale and have great stability. Windows has message queuing built-in, but it probably overkill for this app. Messaging is good when you have flaky connections and/or can wait for results/processing.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Olaf Doschke]

I wouldn't go for option 3 for the reasons craig already gave, sooner or later the demand to have all data in one place will come, also for additional systems.

Option 1 is good for those clients, who can't afford SQL Server, but there are different license forms and sql2012 offeres express and localdb options.

The easiest thing to do hybrid in my oppinion is to use cursoradapter. You need two base classes for native and odbc access and then can go for both database types. But that means more testing and you could only make use of general sql syntax valid for both vfp and mssql or you need to write methods creating db specific sql in the two different branches of cursoradapters.

Bye, Olaf.

 

[craigber]

There is another option here...

A true data tier. All data access goes through that data tier. If you build it right, using Interfaces for the abstract classes and then instantiate different concrete classes based on whether you're using SQL Server or native VFP data. The cursors they return will be identical, so you can use the same one throughout the application and not have to use generic SQL statements. You'll only need to do double testing on the data classes.

One more suggestion. As you rebuild the app, do it vertically, not horizontally. So, don't replace all the data access first. Replace say, customer management, then invoicing, etc.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[EzLogic]

Thanks for the reply guys.

Craig, i agree with you. the challenge is, this application is in production, and clients have "customizations" as their business is evolving day by day, thus it is constantly being enhanced, or added feature, or reports, day by day, as their business is changing, or the carrier requirements are changing (it is in the Wireless Industry/Cellular Phones).

so, what i am doing to maintain the app, is i created a class (DataBizObjClass.prg), that is handleing the data access either from DBF or from SQL, by setting a property to the class.

then, my form handles the rest the same way. one form at a time. so, if the client needs an urgent change, they don't see the new changes, as the property is still set to DBF.

Ali Koumaiha
TeknoSoft Inc.
Michigan