Best SQL backend for a application, VFP9, with lots of tables and records

[wtotten]

Hi everyone,

I have a application using VFP native tables. There are about 60 tables and some of the tables have hundreds of thousands and in some cases, millions, of records. I'm being asked by the client to quote them on moving to SQL backend. I use the late Drew Speedie's VMP framework if that matters and VFP SP2.

I'm sure many of you have had to choose between the more expensive backends such as MS SQL Server and the cheaper ones like MySQL and Postgres, and I would like to know what you recommend - if you have some "pros" and "cons" that you can share with me from your experience.

The OS will be Server 2019 - we will be upgrading from Server 2008 R2 to Server 2019 in the next month or so. They are currently using SQL Server 2008 with about 20 CALs for another application. I suspect when we upgrade the servers to Server 2019 we would also be upgrading to the latest SQL Server. For my application they would need another 20 CALs. I do not know yet what the cost is for more CALs.

Speed and reliability and lower maintenance is important. Whatever you care to advise me will be greatly appreciated.

Thank you all,
Bill

 

[Mike Lewis]

Speed and reliability and lower maintenance is important.

Bill, you've given us a good idea of your requirements, but the one criterion you didn't mention is your budget. The fact that the client is an existing Microsoft SQL Server user is a strong argument in favour of continuing with SQL Server, but it's also likely to be the most expensive option.

All the major back-ends should scale up to the data volumes you envisage (but you will want to verify that for yourself). And the fact that you are using Drew Speedie's framework shouldn't affect your decision. In broad terms, the ease or difficulty of adapting your VFP code will be more-or-less the same for all back-ends.

If the cost is a major factor, then MySQL would be a possible choice. Otherwise I would favour SQL Server. It is an established product, with a major company behind it, and with good support and documentation.

Well, that's my opinion, for what it's worth. It will be interesting to see what others think.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Hi Bill,

I don't know VMP. From that information, I only conclude the app is using a framework. And that usually also means data access in an OOP/ORM fashion, so I assume the database design is normalized. This is more important with remote backends than it is for DBFs.

I was in a similar situation two times, 2000 and 2008. 2000 or the years before we were first reworking a LIMS which besides other artifacts had ~100 tables to store measurements and scores for individual product tests and we structured that data in a more general and normalized way. Then in 2008 switching from a still test method driven approach to focus on the main orientation of storage places. That took about a year alone each time. So it was far more complicated than a shop system where you may always process orders fully by the old system until they are completely processed and may only migrate data for archiving it, processing new orders in a new system starting empty. Then you'd only migrate customers and other stem data, which usually is a far smaller percentage of data than the real transactional data.

Mike already asked about the budget, that's not only addressing the CAL or other license costs, but also time budget. The number of tables and records doesn't tell much about how long it takes to write a migration, it will depend very much on whether you change data structures while you're at it or not. More time goes into writing transformations than into the actual migration process. Millions of rows flow from one to another database in minutes, maybe hours, maybe days, but you'll program much more than just an APPEND FROM DBF and TABLEUPDATE() when you change structures on the way.

I understand next month a server migration will be done, it doesn't naturally mean they expect you to be ready by then, it may also mean starting. No idea. Ideally, you could move over and already be on an SQL backend, think alone of SMB and oplocks, I don't know if VMP allows DBF and remote backend use and I obviously don't know whether you also have a portion programmed with inofficial data access directly on DBFs not going through framework data access ways that now needs to be changed to work on a SQL backend.

On the licensing I never needed to care for that, this is the decision of a customer, their own management and DevOps, I know a bit that you can decide between CALs and CPU core licenses and the different types from standard to enterprise play a role. AFAIR MS actually offers free commercial use of the SQL EXpress versions. The MySQL community edition is free but only for non-profit usage and MySQL included in hosting environments as resold database usually also is allowed for commercial use, but of limited value in this environment sharing server resources.

You'll likely fo for MySQL on-premise, server installation only for LAN usage, but just because that's not accessible public and just because it's open-source it's not free. I don't know how Oracle handles audits of that, the company we did the LIMS for was the very usual Microsoft customer with several subscriptions alone for OSe and Office, they had an audit from Microsoft, I guess Oracle will do the same with corporate customers.

Postgres is a real alternative, but anyway, many things speak for MSSQL, maybe changing from CALs to another license model once your customer wants to oprate more databases on that technology basis, also easier to administrate.

In summary, you get a bit hearsay knowledge but such license questions are a long term responsibility I would always suggest my customer to care for, you're neither legally responsible as an employee nor could you guarantee your legal research to be relevant and legally binding for the time and in the future, if you do contract work. So even if they are a small company that's a question for a specialist, either on law or on Server licensing. I would never give a legally binding assertion anyway and it'll be in the interest of that company to license this correctly.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Nigel Gomm]

I went with with MariaDB rather than MySQL.

(it was a fork in the code done by the original mysql developers when Oracle took over.)

You'll find lots of online discussion about the relative merits but the clincher for me was that MariaDB has far fewer reserved words to clash with existing column names....

I switched about 300 customers over the last 18 months from native VFP tables to cloud hosted MariaDB servers and have been pleased with performance and reliability.

n

 

[Mike Lewis]

Just to add another word ...

In my experience, the best thing about migrating to a back-end database is that it gives you an incentive to review - and if necessary re-do - the design of your database. Since you will be creating a program - or some other tool - to actually move the data, it is a good opportunity to improve the design along the way.

In particular, you will need to review your data types. This applies to whichever back-end you choose. For example, if you are moving to SQL Server, you will need to decide which of your VFP character fields will become SQL Server chars and which will become varchars; similarly, for numeric fields, you will have to decide betwen bigint, int, smallint, etc. And so on. Not to mention the issue of empty dates (supported in VFP but not in most other systems).

You might also take the opportunity to ensure that the database is properly normalised, that you have the right number and type of indexes in place, etc.

These are not trivial decisions. They could have a major impact on performance and reliability.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[wtotten]

Thank you everyone for your thoughts and advice.

One of the main reasons for doing this is because the application runs on a LAN and Microsoft's server operating systems since 2008 and forward have made my life difficult with Oplocks and SMB2/3. I get random file corruption (usually header count is off) and I can't determine if it's because of a flaky workstation connection of because the server "burps" on me, or ???.

Another solution, to remove the network communication between the server and the workstations is to run the application via Remote Desktop so that everything stays on the server. Have you guys done this and do you have any suggestions or insights?

I figured this was going to be a large task, but after your replies and more research online, I see that this is fraught with potholes, big and small, and the task is more difficult than I had thought it would be. The data types and blank date fields is one of those issues that I hadn't considered and will take a lot of diligence and redesign to deal with.

Bill