From HF Classic to: HFCS or Sql Server?

Hello guys.
I have an old WD system compounded by 4 apps with HF Classic as DB. It was running ok for several years in a small office. Now, the client has a better and greater infraestructure and wants some modifications to the system, being the main the change of the DB to Sql Server. Actually, there's no need for doing this but the client always has the reason.
I never used HFCS before, but I guess the performance will be better (any remark about this?), so, I want some reasons for the client to see it would be better HFCS than Sql Server.
Can you please let me know your remarks about this?
A first reason by myself: the client must pay for the Sql Server native connector.
TIA

Hi,

Switching to SQL Server will cause many additional problems in the program. You need to tell your customer that this will not only add time but also increase costs.

Kind regards,
Guenter Predl
office@windev.at

Thanks Guenter for the reply.
Now, something related to this: Switching to HFCS will force to me to make some changes in the programs? Or everything will be more or less transparent?
I've seen a lot of years ago a project of yours, for connecting to a Classic and CS database with no much trouble, if I'm not wrong...

Hi Raul, I understood from your first posting that you want to switch from HyperFile Classis to Microsoft SQL Server Switching from Classic to HFSQL C/S gives much less troubles, of course.

Kind regards,
Guenter Predl
office@windev.at

If your app only uses HREADxxx, VIEWS and things like that switch to HF C/S or MSSSQL don't improve performance but if you use queries you can have performance gains.
HF Classic have a lot of security problems.

The easyest change is to HF C/S, to change to MSSQL, start by reading the limitations of the native access to see if you use some of this features
[doc.windev.com]

In my experience if they already use MSSQL don't bother to discuss this issue. Their IT guys don't want to install, manage and learn another DB engine. you will lose the battle and probably the client.
Just do some testing, try to predict some (a lot if you don't have experience in MSSQL) extra time to change your app and tell them how much it will cost. The price is normally the best way to explain.

It's difficult to tell you how hard it will be but i'm sure it's not only buy and use the native access, the work involved depends a lot on how your app is made and what you want to achieve.

Regards

Paulo Oliveira

Raul,

My 2 cents..

I have an app that about a dozen companies use. It will run either HF Classic or HFCS. When a customer wants to switch from Classic to Server for me it takes all of 10 minutes of set up and importing the data files to the server. The largest company that uses it has 88 users with 35-40 on all day at any given time, 1.4 million records after 12 years with the DB size at 1.4gb. I have no performance issues and best of all HFCS is FREE. When I update the data file I run the WDModfic tool with code and has worked with no issues for years.

I am a hobbyist and do not do this for a living so I like to use things that do not require a lot of my time.

Good luck and no matter what path you take it will work one just is a little longer then the other.

Dennis

Raul,

I agree with Dennis when it comes to switching from HF-classic to HFSQL. Most of the time your app needs change at all, except for opening the database connection.
I use an inifile to hold the settings llike server IP and so on and then do HOpenConnection() followed by HChangeConnection() to get things done. Check the help for more info, but that's basicly all.
If you have to do this once, you can manually copy all hf-classic files to the hfsql\db\your-datebase map and restart the hfsql. Files don't need any conversion and can be used as-is.
If you app also has option to make backups and so on, these part need to be rewritten of course. But al reguler data-handling will work.

Going to Micrsoft Sql server is another thing.
- you need some export/import tool to copy all existing data. In the end I wrote one myself.
- you can start with the standard OLEDB driver (free) or choose to use the native driver (747 euro once and 199 each version upgrade). I would make that decision from the start and personally go for the native driver.
- dates/times/datetimes can be a pain in the…. timefields are createde as datetime in the database. I used the standard driver first which saves 1900-01-01 as the datepart for a time, after that I switched to the native driver which saves 2000-01-01. Costed me quite some time to find out why sorting acted strange after that.
- The behaviour of NULL values needs to be looked at and tested.
- Blob fields are read immediately, where as in HFSQL the WD framework reads them only when actually used. So SELECT * must be avoided.
- Consider to replace H-commands for queries when reading data, to gain performance. (but that's also a good thing when using HFSQL)
- Overall you have to test your entire app very carefully to be sure it still works as expected.
- It's my experience that at least 10% of the queries and data handling needs to be rewritten.

However
- when using the native driver, performance was about 25-40% better. All users noticed this from the start, after the switch from hfsql to mssql!
- backup is now not of my concern anymore. IT-guys are very capable of backing up Microsoft SQL and are now responsible for that.
- when database servers are updated or moved, again, IT can do that on it's own. No need for 'special' HFSQL knowledge.
- external tools like PowerBI (or even Excel if you like) can easily get access to the data. No need for PCSoft's odbc driver.

Arie

Hello Pao, Dennis, Guenter and Arie. Thanks a lot for all your remarks about this matter. All of them are very useful and clear in order to face this scalability issue.
The only remaining thing is the hardest one: the client desicion

Best regards!