As you may well know, Sage is not formally supporting Sage 500 7.2 any longer and when they did support it, it was not supported for anything beyond SQL Server 2005. We’ve had a number of requests asking if it is possible to run earlier versions of SAGE 500 on more recent versions of SQL Server. We recently supported a customer in regression testing their system running in SQL 2014 and migrating it to production. It is working very well.
In-order to prep for this project, we reviewed the discontinued features of SQL 2008, 2012 and 2014. We were not worried about the deprecated features as those will continue to work, it is just not recommended to continue to use. Based on our knowledge of Sage 500 syntax we recognized two syntax changes and one set of functionality that we would have to cope with. The syntax changes were the With Append clause in the triggers and the RAISERROR statement. The With Append clause was discontinued and the RAISERROR statement changed in syntax. Also DMO was discontinued.
We installed in a VMWare virtual environment, using a new Windows Server 2012 R2, SQL Server 2014 Standard Edition.
Here are the changes that we needed to make in order to get Sage 500 7.2 running on SQL Server 2014:
- After installing SQL Server 2014 and updates, install the SQL Server 2005 Backward Compatibility Pack – DMO. This Microsoft link currently has the download for this pack. The file you will install is SQLServer2005_BC_x64.msi. Use the custom installation and install SQL Distributed Management Objects.
- Restore the production database to the new SQL Server. Alter the DB to run in Compatiblity Mode 120 (SQL 2014). (ALTER DATABASE mas500_app SET COMPATIBLITY_LEVEL = 120
- Run a script to remove the WITH APPEND clauses in all the triggers in the database. This syntax was discontinued in SQL Server 2012.
- Run a script to alter the RAISERROR syntax within all the stored procedures in the database. The RAISERROR syntax changed in SQL Server 2012.
- Allow users to be able to change their passwords by giving them the permission to ALTER LOGIN.
We were not able to get Data Import Manager (DIM) functionality to work. This includes being able to set permissions in the UI for DI User and DI Admin. It also includes creating new DIM jobs and running existing DIM jobs.
The customer uses the following modules – AP, AR, CI, GL, IM, PO, SM, and SO. They are integrated with a number of third party systems. We ran transactions, views and reports within all these modules for regression testing. We processed the full workflow for each of thier integrations. The biggest part of the effort was getting the testing environment setup and the integrations connected within the test environment. Once that was completed, all the regression testing worked without issue (except for DIM).
The benefits we achieved include:
- Enabling 128 GB of RAM for SQL Server Standard Edition
- Moving off an unsupported operating system and SQL Server Edition
- Being able to use Extended Events for tracing with less impact.
- Better performance overall.
Got Questions?
Get in touch with RKL eSolutions and one of our expert Sage 500 consultants will follow up and answer your questions.
