Stephen Smith's Blog

Musings on Machine Learning…

Archive for September 2014

Sage 300 ERP 2016 SQL Server Only

with 21 comments

Introduction

If you were able to attend the Sage 300 ERP roadmap sessions at Sage Summit you would have heard that the next major release of Sage 300 ERP (named 2016 but released in 2015) will be dropping support for Pervasive.SQL and Oracle as database servers. This means the only supported database will be Microsoft SQL Server. Although we will support several versions of SQL Server long with the Azure SQL flavor.

The intent of this article is to help make sure everyone has plenty of advanced warning about this change. To help explain the rationale behind this decision, and to help people formulate migration plan if you aren’t already running SQL Server.

Pervasive.SQL

PervasiveLogo

The first Windows version of Sage 300 ERP (then called CA-Accpac/2000) was released supporting one database which was good old Btrieve 6.15. We all have fond memories of those days when the world was much simpler, we just needed a simple robust database manager without any other real concerns. At that time we had a good bundling deal with Btrieve so we could include a database engine with every System Manager. At that time Btrieve was owned by Novell. At that point in time Btrieve was a good low cost database manager that supported transactioning, it was used by many ERPs, and was relatively easy to install and administer. Novell sold off Btrieve back to its original developers and that evolved into Pervasive.SQL and last year that was acquired by Actian.

Pervasive.SQL still has the same qualities that Btrieve 6.15 had, but it hasn’t really kept up with its competitors. SQL Server now has a free edition and Microsoft is much more favorable to doing bundling deals. Plus there are now many better low cost database alternatives such as SQLLite and MySQL.

Over that past years the higher end databases have become much easier to install and manage. Long gone are all the configurable parameters that would plague SQL Server installations (i.e. the defaults now work for most cases). So now Pervasive.SQL isn’t as easy to use.

Anyway Btrieve was the first database that Sage 300 ERP supported, and I think a lot of people have fond memories of Btrieve, but today it doesn’t seem to have a place anymore.

A lot of Sage 300 ERP installations require integrations to many other products, and nearly none of these support Pervasive.SQL. Hence if you want integration with BI tools, or other ERP related software, you are almost always forced to use SQL Server anyway. In the early days of Sage 300, SQL Server was very expensive and most products supported Btrieve as a low cost alternative, but today the need for that has disappeared and we are one of the last vendors to still be supporting Pervasive.SQL.

Oracle

Oracle1

We’ve had Oracle support for a while now. However the sales numbers have never really justified the resources required to support this platform. Oracle tends to be the database of choice for larger companies that tend to be bigger than Sage 300’s target market. We’ve made a few government and large company sales because we support Oracle, but generally these customers would have been as well served by SQL Server.

Our perspective is that the demand for Oracle has waned and that they are really pursuing larger and larger companies and moving further and further away from our market segment.

Multiple Product Integrations

Most Sage 300 ERP sites these days involve multiple products working together such as Sage CRM and Sage HRMS. Generally people only want to work with one database system and the common one across the various products is SQL Server. Most products support a choice of databases, like Sage CRM supports Oracle and SQL Server and Sage HRMS supports FoxPro and SQL Server. To get a more uniform experience across all these products really only works well if you choose SQL Server. It’s generally nicer to have just one set up database operations for things like backup.

Further when you start to use more advanced cross product reporting tools, these can only do their job if all the products are based on the same database engine (so that SQL joins can work properly, etc.).

Database Architecture

The Sage 300 ERP architecture is still the same and supports multiple databases, whether we support another database than SQL Server in the future will depend on the future of the database market. It might be a lighter weight SQL engine like SQLLite is best. Or one of the new NoSQL databases that are becoming popular like HBase or MongoDB. Certainly the NoSQL databases support capabilities that SQL Server can only dream of. Similarly products like SQLLite also run on all the mobile an alternate operating systems opening up all sorts of other possibilities. Chances are these will be introduced in a hybrid manner combined with SQL Server rather than as solutions that handle 100% of our system’s needs.

sql-server-logo

For the short term we will be concentrating on SQL Server which means can use some features that are more specific to SQL Server. However one of our keys to success has been sticking to the core SQL engine functionality. That we work fine with SQL Express and Azure SQL (unlike a number of competitors). So we will be careful to ensure anything we do doesn’t break our database independence or our flexibility in supporting all flavors of SQL Server.

Moving to SQL

If you are running an unsupported database and want to move to Sage 300 ERP 2016 then you will need to convert the database. To convert from an unsupported database like Pervasive.SQL, DB2 or Oracle, you need to run Database Dump on your databases, create SQL databases for these in SQL Management Studio, create the entries in Database Setup and then run Database Load. Make sure that you update and test your backup/restore and disaster recovery plans to ensure that you are still protected.

The conversion must be done before upgrading, since the 2016 version doesn’t include the unsupported database drivers and can’t access these databases and hence can’t do a Database Dump on them.

If you leave Pervasive, DB2 or Oracle databases in Database Setup then these won’t show up in any sign on dialogs. We’ve changed the message when you run the desktop, so that if you don’t have any databases because they are unsupported, why this is the case and to let you run Database Setup.

If you don’t want to switch to SQL Server, it just means the last version you can upgrade to is Sage 300 ERP 2014. This will be supported for its normal lifecycle. When it goes out of support, of course your software will still operate. But you won’t be able to get any new Service Packs or Hotfixes. This should present a quite large window on when to switch. These days, nearly all new sales are SQL Server and the number of SQL installs is the largest share and of course every one already running SQL Server won’t be affected.

Summary

The database world is changing and Sage 300 ERP needs to change with it. That’s why we are making these changes. We hope that converting your Pervasive or Oracle databases to SQL Server won’t be too painful and that you will get quite a few long term benefits from this move.

 

Advertisements

Written by smist08

September 2, 2014 at 12:23 am