Sage 100 Standard vs Sage Premium

We have Sage 100 (MAS200) with providex database.   We are thinking of getting a sql based version of Sage whether than be 100 or 500. 

My question is very general.  It is generally accepted that having an "open database" is superior to a Proprietary.  Am I ask the obvious question of why? Does it mean that 3rd party programming is easier and more transferable to another sql programmer.  What additional perks come with having a sql server database.

thanks much

P park

Parents
  • 0

    Troubleshooting data file issues requires that you have someone that knows SQL (editing field values, deleting orphaned records, etc.)

  • 0 in reply to BShockley

    Yes that is true.  Thank you.   Is the availability of programmers easier in creating modifications with a sql database or is that irrelevant to having Providex vs SQL

  • 0 in reply to paula2013

    What types of modifications do you need?  User-defined scripts are written using the BOI which is available in SQL or non-SQL versions.

  • 0 in reply to BShockley

    We have a custom module written for our MAS200 (Sage 100) Providex software.  If we had the sql database, with either Sage 100 or Sage 500 would it be any easier to transfer the code to another programmer to maintain.  I am under the impression or myth that having SQL gives more flexibility in that way.  So I have to ask, why would a mid size company ever choose a sql version if it requires more hardware.  Is this for companies that would have an inhouse SQL programmer and database expert?

  • 0 in reply to paula2013

    You are mistaken on a few points. Having the SQL back end helps your reports run a lot faster, that's about it. The ProvideX version processes faster, even benchmarks released by Sage some time ago confirmed it. As far as processing speed goes the SQL version is barely faster than the Standard version. The Advanced version (client/server) is the fastest as far as processing speed goes. You would not be able to transfer the code from Sage 100 to Sage 500 because all the tables and fields are completely different, well except for the name Sage in the title. Personally if you are looking at moving you should look at X3. Sage is pretty "hot and bothered" about X3 so I expect them to get the lion share of development efforts.  

    Now if you say you have a custom mode written for you Sage 100 Advanced then you should probably stay there to get max ROI.  Plus a lot of flexibility is being added to Sage 100 as far as scripting ability goes. I would ride it out a few more years.

  • 0 in reply to BigLouie

    Hi BigLouie,

    I hope you don't mind rehashing this discussion.  We are currently looking to upgrade from Sage 100C Advanced 2016 to the 2018 build and are experiencing the same quandary about going to Premium SQL.  I have read many articles speaking to the benefits of the SQL back end.  Although I don't fully understand how this would help us specifically, I do get SQL is easier to query.  Do you know how this works for backups.  Is it possible to do transactional rollbacks if needed?  I understand F9 (GL query) doesn't integrate with Premium.  Have you had experience with F9 vs. what's available in Premium?  My biggest concern is around the transaction processing speed.  Can you quantify this for us?  Are we talking milliseconds, seconds or minutes longer?  We have a handful of keyboard shortcut users, will they be disappointed moving to Premium?  We initially started looking at Premium to resolve some of our glitchy issues with printer servers and cloud performance/connectivity to the Sage server.  I'm not sure if Premium would be the answer for this, however, speed is definitely a deciding factor.

    Also, any input on custom reports transferring from Advanced to Premium or same issue applies with new database and reports wouldn't work?

    Any input you can give would be greatly appreciated.  Not many specifics are available on this topic and it would be great to speak with someone who's lived it.

  • 0 in reply to garrettOps

    Garrett, can you provide more details. How many users do you generally have logged in at any one time. Do you have any remote users. 

  • 0 in reply to BigLouie

    We have around 27 users active at any given time.  Everyone accesses the SAGE server via terminal servers.  Sage is currently setup in a cloud environment.

  • 0 in reply to garrettOps

    I highly recommend looking into going with Premium.  Not only do you have that ability to restore to a specific point in time (depending on transactional backup schedule) you also have the ability to write and schedule backend SQL queries to generate reports. 

    As an example one of the reports that was manually being kicked off by my Accounting department would take anywhere from 30-120 minutes now takes 2-5 minutes with a SQL job that is on a nightly scheduled SQL job.

  • 0 in reply to garrettOps

    Existing custom reports can translate from Advanced to Premium (with a few obscure and rare tiny caveats / exceptions which have work-arounds).

  • 0 in reply to garrettOps

    We are on 100c 2016 Advanced with around 35 concurrent users including maybe 7 remote (using Terminal Svcs, probably 3 at any one time at the most) and considering a move to Premium, but I'm afraid I've gotten some bad advice.  The issue we're hoping to fix is in data entry, where the user will enter something into a field (common examples are a comment on a sales order header, or checking the Drop Ship box on sales order lines), then go to a different tab, and when they return to the original tab the change is gone (comment is blank; DS is not checked).  I have not been able to duplicate these problems as they are very sporadic and not confined to one user or workstation, but over the last couple of years they keep cropping up every so often.  My guess is there is a network issue, which I have tried to address with new switches, cables, &c., but with no luck.

    So to get to the point, I was advised that since a SQL db is a transactional database, it does some kind of error/dropped-connection checking that would reduce or eliminate these problems, whereas the 'flat-file' Advanced/Providex db would not.  Is there any basis for this?  Any other advice for me?

Reply
  • 0 in reply to garrettOps

    We are on 100c 2016 Advanced with around 35 concurrent users including maybe 7 remote (using Terminal Svcs, probably 3 at any one time at the most) and considering a move to Premium, but I'm afraid I've gotten some bad advice.  The issue we're hoping to fix is in data entry, where the user will enter something into a field (common examples are a comment on a sales order header, or checking the Drop Ship box on sales order lines), then go to a different tab, and when they return to the original tab the change is gone (comment is blank; DS is not checked).  I have not been able to duplicate these problems as they are very sporadic and not confined to one user or workstation, but over the last couple of years they keep cropping up every so often.  My guess is there is a network issue, which I have tried to address with new switches, cables, &c., but with no luck.

    So to get to the point, I was advised that since a SQL db is a transactional database, it does some kind of error/dropped-connection checking that would reduce or eliminate these problems, whereas the 'flat-file' Advanced/Providex db would not.  Is there any basis for this?  Any other advice for me?

Children
  • 0 in reply to neatstripe

    Until you click Accept, the data for a new order only exists in memory.  SQL does indeed have better data integrity (with no keyed file errors being possible, nor badly formatted dates, both of which can happen with Providex).

    Network errors would present differently.

    It sounds like you have some custom programmed behaviour (scripts / enhancements), bad memory on the Sage server, or glitchy panels.  Look for errors in Windows Event Viewer, Sage SY_ActivityLog... and consider upgrading to v2018.

    I'm a fan of SQL generally, and if you're on subscription (100c) the only additional software cost should be the SQL licenses.

  • 0 in reply to neatstripe

    We have experienced the exact same issue on order entry. We just switched to 2018 Premium and so far, it seems to be corrected (not dropping data before accept is pressed). No idea if it is related to SQL but wanted to share that feedback.

  • 0 in reply to BenwKBS

    Thank you both for the input.  It sounds like the issue might be corrected by upgrading to 2018, whether Premium or not, since that would address any glitchy panels and custom programming, correct?  We are moving to new server hardware shortly, so that should take care of any bad memory issues.  My only concern with switching to SQL is whether it will justify the additional cost of the SQL licenses, since as I understand it, it isn't faster (and could even be a little slower) in the UI, and might - but only might - fix the data entry problem.  Other than that I'm all for it, as it would certainly make my life easier!

  • 0 in reply to neatstripe

    So when they are navigating around the Data Entry screen it is not saving records.

    I would rebuild ALL the SO and AR module files.  Use both options Integrity Check and Optimize options.  Do this more then once until no more errors are reported in the rebuild logs.

    I would call it in to your local support. 

  • 0 in reply to neatstripe

    Until you click Accept, nothing is written to the SQL / Providex data files.  Clicking to a new tab doesn't save the data... it is held in memory at this stage. 

    What you describe may be a display glitch (not uncommon) or corrupted memory (rare).  If you save and reload the transaction (or save then look at the raw data files), and the data is wrong / missing, I would suspect corrupted memory.  If saved correctly, that would be a display glitch.