We are still running btreive.

Can anyone provide me with a couple of pros and cons to swithing to SQL?

I am assumming you are really using Pervasive SQL and are weighing going to MS SQL.  

If you really are on Btrieve, are you looking at going to MS SQL or to Pervasive SQL?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
www.trianglepartners.com

Running PervasiveSQLi but still on btreive.

Pervasive SQL 2000i is the latest Pervasive engine supported by Macola is what btrieve ultimately evolved to.  It is possible that you are running Pervasive SQL AND btrieve, but highly unlikely.  Therefore the rest of this post assumes you are looking at going to MS SQL Server 2000.

From a transactional standpoint, if you are a heads down order entry clerk, AR clerk, etc., you will see little difference, everything takes about the same amount of time.

1) SPEED.  I have seen reports that take 3 hours in a Pervasive environment that take 5 seconds in a MS SQL environment.

2) Flexibility.  With MS SQL Server, you can build a ton of tools such as views, stored procedures, etc. that either don't exist in a Pervasive environment or they exist in a very limited way.

The other major plus in going to MS SQL is that pervasive is going away and it won't be supported, although I have yet to hear of an exact date for that.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
www.trianglepartners.com

I have no cons to converting to SQL.  Only pro's.  The ones that dgillz mentioned plus the backup/restore functionality of SQL, point in time recovery and security of the database files so people don't have outside access to them.

Kevin Scheeler

Biggest MS SQL plus is interoperability of MS Sql with other software products. Third party developers are leaving Pervasive db development in droves and flocking to the MS SQL backend.  If you stay PSQL, you will find it difficult to expand and enhance your Macola installation with third party solutions.

In addition:  Search setup is WAY better in SQL & easier to add on the fly.

Downers:  backups will quickly run your server out of disk space, especially if you have high transaction volume.  Processes such as reset allocations/reset on order/MRP regen that are read/write (for which btrieve is optimized since it is a transactional engine), are dog SH$$ slow in MSSQL.  A 15 minute reset allocated in pervasive 7 takes 1.5 hours in MSSQL 2000 at one site.  They were a conversion site, so maybe the conversion utility for that notorious inventory trax file got hosed on the conversion (sounds like v6 to v7 all over again).  And if you don't have a DBA on site?  Do you provide that role for your clients when they don't?  It's good consulting revenue, but I'd rather do application support than DBA support after hours & weekends.)

I have clients who call me because I logged onto windows as administrator & didn't change the name back & they couldn't log on . . .   Sad but true.  They are probably not candidates for file deletion other than brain failure while trying to exit something they know nothing about.

Check out the current MSSQL pricing.  My understanding is that it is processor based licensing rather that per seat (being out of the hardware biz & related OS is my choice).  For small companies, this could be a prohibitive expenditure to get a server that will last for (ha, ha!) the 5 years its predecessor did.

Since Exact has committed to MSSQL as the platform of choice, that is the obvious solution for companies that need any enhanced functions of ES, but do our small companies really need that overhead & file conversion expense for what it will net them in ROI?  A $5-10K conversion to MSSQL from pervasive & then who knows how much more in 7.6 to ES & retraining/retooling for accounting changes will be HUGE for your 10 user sites !!!!  How can this solution be recommended for existing users?  It is simply overkill for what they need.  Exact is looking for a whole new market space & maybe they think that existing progression users will think that eSynergy is so "hot" that they will convert easily. I am not convinced that exact knows what they are dealing with on the ar/ap open item conversions & GL issues on conversions & I readily admit I haven't done a conversion or attended one of the classes w/o documentation, however, certain inalienable principles exist within implementing a new system that are serious oversights.

We have two different sites running Macola 7.6.1 on both sql and p.sql. The sites support 20-30 users.

We made the decision at one site to upgrade to sql because they were moving from the dos version and we wanted to be on the latest supported platform.

At our other site we simply cannot justify upgrading from p.sql to sql.

Not only is there the cost of the upgrade and the SQL server licensing but also the cost of another server because of the increased overhead of running SQL.

Looking at the performance at both sites, and keeping in mind the extra hardware/software thrown at the SQL site, I am rather underwhelmed at the SQL site's performance. Perhaps some of this is due to the size of our installation - larger installations may see more performance from SQL.

Its my understanding Macola has a rather large percentage of their user base on P.SQL. I doubt they would alienate a large maintenance fee cash cow any time soon.

The only thing at this point that would make us consider upgrading to SQL would be some kind of mission critical addon that supported only SQL. So far we have not hit that point.

It's very possible to run one server for both the SQL Server and the Macola application server for the 20 to 30 user range.  One thing to look at is their transaction processing.  The more transactions they are going to do, the more likely you may want seperate servers but in most of the cases that I deal with, they are on the same server and we haven't had many issues.

As dgillz said, you won't see a performance gain with normal day to day processing but it should really run as well as P.SQL also.  It's everything else built around it that makes it a better product in my opinion.

I do agree that with the number of P.SQL installations, it's not going away anytime soon but they are not really developing anything new for it either.

Kevin Scheeler

Thank you all for this helpful information.  I asked for the Pros and cons and now I will have to weigh your input on both sides.  As you can imagine we are HIGHLY interested in migrating to SQL for the flexibility of making a nicer system for the end users.