How to upgrade databases in Sage 500 ERP?
As of this writing, the following upgrade information applies:
- Version 6.30 through 7.20 can be upgraded directly to version 7.30
- Version 7.05 through 7.30 can be upgraded directly to version 7.40
- Version 7.30 through 7.40 can be upgraded directly to version 2013 (7.50) or to version 2014 (7.60)
SQL Server Versions:
- Version 6.30 and 7.x are supported on Microsoft SQL Server 2000
- Version 7.05 is also supported on Microsoft SQL Server 2005.
- Version 7.20 is supported on Microsoft SQL Server 2000 and Microsoft SQL Server 2005
- Version 7.30 is supported on Microsoft SQL Server 2005 and Microsoft SQL Server 2008
- Version 7.40 is supported on Microsoft SQL Server 2005 SP4, Microsoft SQL Server 2008 SP2, and Microsoft SQL Server 2008 R2
- Version 2013 (7.50) is supported on Microsoft SQL Server 2005 SP4 , Microsoft SQL Server 2008 SP3, and Microsoft SQL Server 2008 R2 SP2, and Microsoft SQL Server 2012
- Version 2014 (7.60) is supported on Microsoft SQL Server 2008 SP3, and Microsoft SQL Server 2008 R2 SP2, and Microsoft SQL Server 2012
Note: In version 7.00 the system(MAS500_sys) and eTimesheets(MAS500_pt) database objects have been integrated into the application database
Upgrading Native Schema Customizations:
When upgrading databases with customized schema in the application or system database, create the custom upgrade scripts using the Sage 500 ERP Database Comparison Utility as described in the Installation and System Configuration guide. In the case of native schema modifications, compare and upgrade one version at a time. For example, to upgrade from 6.30 to 7.40, compare and upgrade to 7.05, then 7.20, and so forth. All native stored procedures and triggers are always overwritten during an upgrade, so modifications must be upgraded to these objects and alter or create them as required within the upgraded databases.
- Install the Database Utilities for the next subsequent version.
- Compare databases, creating the upgrade script.
- Upgrade with the custom script.
- Verify database consistency, install client and test, etc.
- Repeat step one.
General Upgrade Procedures:
Follow these general steps to upgrade the Sage 500 ER system:
- Post all open batches in the system. If necessary, create batches in Account Receivables, Process Invoices and Select Shipments, and then post (these are type SB(System Batches))
- Make backups and archive of all databases (app, sys, iapp, pt, pl).
- Restore the databases to a test server or to test databases (using a different name) so the production environment is not affected. Review the Sage 500 ERP Installation and System Configuration Guide for instructions on backing up and restoring the databases and using the Sage 500 ERP Database Synchronization Utility.
- Run DBCC CHECKDB against the databases and verify their consistency.
- Rebuild indexes on the databases (DBCC DBREINDEX) if this hasn’t been done recently. This reinforces index consistency.
- Run DBCC UPDATEUSAGE against the databases. This updates the used and free space information for the databases, review Microsoft SQL Server Books Online for more detail.
- Upgrade Microsoft SQL Server if necessary. Review Microsoft SQL Server Books Online for methods and recommendations. After upgrading, repeat steps four through six.
- Verify enough free database space exists in the databases according to the instructions in the Installation and System Configuration Guide or the Sage 500 ERP Compatibility and Resource Guide.
- Verify that the tempdb database has been allocated 1 GIG minimum or 1/3 the size of the application database, whichever is larger, and that the proper growth parameters are set. Review the Installation and System Configuration Guide or the Sage 500 ERP Compatibility and Resource Guide for additional information.
- Set the proper database options (generally Create/Update Statistics, Torn Page Detection and Compatibility Level). Review the Sage 500 ERP Compatibility and Resource Guide or the Sage 500 ERP Installation and System Configuration Guide for the current version for additional information.
- Set the appropriate database recovery model. Review the Sage 500 ERP Compatibility and Resource Guide or Microsoft SQL Server Books Online for additional information on the types of recovery models.
- If the application database is large (greater than 10 GB) but the data volumes are not exceptional, execute the sp_spaceused ‘tciMaintAuditLog’ and sp_spaceused ‘tciDBActivityLog’ queries against the application database.
- If the audit log is exceptionally large, consider purging some of the activity by selecting the Audit Log option in the purge feature in each module.
- If the database activity log is exceptionally large, remove any errant rows that may have been inserted.
- Back up the databases if any changes have been made.
- Upgrade the databases using the Sage 500 ERP Database Upgrade Utility. Optionally, install new databases with the Sage 500 ERP Express Install utility. Select the test server or test databases for upgrade.
- Back up any native customized Crystal reports on the client machine, they will be overwritten
- Review the Sage 500 ERP Compatibility and Resource Guide for information on supported client platforms. Install the new Sage 500 ERP client software on a single client workstation and test the new databases to verify the results of the upgrade.
- Run DBCC CHECKDB on the upgraded databases, and review the output. For more information about DBCC CHECKDB, review Microsoft SQL Server Books Online.
- Against the upgraded database(s) , rebuild indexes.
- Test functionality on the upgraded databases with the upgraded client workstation.
- Back up the databases, restore them to the production server or as the production database set on the secondary Microsoft SQL Server, synchronize the databases using the Sage 500 ERP Database Synchronization Utility, test them again with the upgraded client, and then proceed to upgrade the remainder of the client workstations. The backup and restore procedure is detailed in the Sage 500 ERP Installation and System Configuration Guide. This procedure can vary according to the version of Microsoft SQL Server.
For more Help: Call Sage Technical Support Phone Number at 1 888 489 0694
Resolution for Issue ‘How to upgrade databases in Sage 500 ERP?’ available: Yes (Solved).
Source: Sage Community forums.
Disclaimer: Sage Accounting Solution is an independent provider of database-related services and is not affiliated with Sage or Intuit.
How to upgrade databases in Sage 500 ERP?: this issue or error code is a known issue related to Sage accounting products. Support for this issue is available either by self-service or paid support options. Experts are available to resolve your Sage issue to ensure minimal downtime and continue running your business. First try to resolve the issue yourself by looking for a resolution described below. If you are experiencing too many issues, you may want to import Sage 50 to Quickbooks. More info Call Sage Tech Support Phone Number at 1 888 489 0694 .