Jon Davis is our special guest author at visualnuggets.com and is an active participant in the VISUAL community. Jon is a Software Engineer/Systems Analyst at Helwig Carbon Products - who recently upgraded to VISUAL 9 from VISUAL 7 on a SQL Server platform.
I want to sincerely thank Jon for sharing his upgrade thought process and experience.
Connect with Jon Davis online at LinkedIn
Helwig Carbon Products is a mid-sized manufacturing company, makers of specialty carbon products, has been using VISUAL for almost 15 years. This past year we upgraded from VISUAL 7.0.0 to 9.0.3, doing all the upgrade work internally. Our IT department consist of three employees and it took roughly 3-4 months to prepare and execute the upgrade.
We have 75 user licenses and have added a lot of customizations to VISUAL over the years. Most of our customizations are done through Macros or internally built applications that connect to the Visual database directly.
We also use SQL Server Reporting Services (SSRS) as our reporting solution and our database is SQL Server 2008 R2. Our client machines are running Windows 7, mainly because VISUAL 7.0.0 doesn’t work in Windows 10 and we had to hold off on our Windows 10 upgrades until we moved to VISUAL 9 (which does support Windows 10).
The biggest hurdles to overcome in the upgrade were caused by the schema changes in 9.0.3 especially because we’re a single-site company that was using 7.0.0 which didn’t include multi-site functionality.
We also had several applications and reports that referenced the VISUAL database directly and all had to be reviewed to see if the schema changes affected them. Fortunately, the “look and feel” between VISUAL 7 and 9 are roughly the same so little training was required for end users.
To prepare yourself for the upgrade you’ll first have to set up a test database and application server. We ended up having to run through the test upgrade process roughly 6 times before we were even able to get the actual upgrade script to run successfully, so having a test system will be necessary.
The first thing to note is that the upgrade caused our database file to more than double in size and it caused most of our problems. In our case, we have nearly 20 years of data in our VISUAL database (some was migrated from our old ERP system) so our DB file went from 70GB to 200GB.
If you can purge any data in your VISUAL system before upgrading it would be highly recommended.
Our Considerations & Decisions Made
A lot of the steps you’ll need to take depend on how your current system is set up, so I’ll just run down a few things that affected us or you might need to review in your environment
- We had our Macros stored on the client machines as VMS files and would be switching to saving Macros in the Database after we upgraded. VISUAL has a built-in process for migrating from files to the database
- We never used User Profiles, so we never had any set up or assigned to users. Because database macros are applied to users via their profiles we had to set up a series of profiles after the upgrade was completed. We speed up the process by scripting this in SQL queries
- We are a single-site company but because 9.0.3 uses multi-site we had to assign all users to a site. In the upgrade process it’ll ask you what you want your default site to be, but it didn’t automatically add all users to that site. We created a script to run after the upgrade to update all users to the correct site.
- Preferences are now stored in the database instead of the INI files on the client machines. We had to add some new Preferences to some users because of changes in functionality that were different than in 7.0.0. We had to add the following preferences to maintain the functionality as it was in 7.0.0 (but your experience may differ)
- SuppressZeroLines in Shipping Entry (without this a packlist will include all lines on the order, even if they aren’t being shipped in that packlist)
- ConsolTaxesByID in InvoiceGeneration (Invoice taxes were showing line by line instead of summarized)
- NewMaterialMode in Inventory Transactions (without this a user cannot issue new material to a work order)
- Review if you have any customizations in the VISUAL database schema itself. This was an issue we had in a prior upgrade but have since resolved. We currently never change any of the data tables in the VISUAL database and instead maintain a second database (but on the same instance so it can be referenced in the same SQL query as the VISUAL database) outside of VISUAL. If you have schema changes in your VISUAL's DB you’ll have to undo these before you run the upgrade script
It took us roughly three months from start to finish. The complexity of your customizations and your specific environment will greatly change this for you. Our actual project timeline looked like this:
- Discuss client installs with IT department to decide on deploy strategy
- Set Up copy of current DB on Test system and run VISUAL 9 upgrade scripts
- Confirm upgrade worked with several users
- Convert text file based macros into VISUAL 9.0 DB versions and confirm they work
- Database Compare to see what custom Triggers, Indexes, etc. did not transfer and need to be rebuilt
- Discuss training strategy with managers
- Begin user testing in VISUAL 9.0 test environment
- Train users for VISUAL 9
- Deploy (Weekend TBD by IT department)
- Back Up of Database and Server
- Friday After Hours
- Install 9.0 on server and run conversion utility
- Friday After Hours
- Install clients
- During the week whenever a user is done for the week
- Super user testing
- Sunday morning
- Go/No Go
- Sunday 3:00pm
- Back Up of Database and Server
Our VISUAL Upgrade Steps
As for the details on our upgrade process...here are the step-by-step instructions we followed on our actual upgrade. I’ve included some notes to help clarify some steps:
- Back up Database
- Disable Database Jobs that will interfere with upgrade
- Make sure SQL Sentry monitoring is off (This was monitoring software that constantly pings the database and checks for issues)
- Turn off all scheduled tasks on automated server (These are scripts that run against our VISUAL database and our automated Work Order Printing and Costing Utilities)
- Turn off Pick Ticket Printing service (We have our own custom-built software that prints Pick Tickets)
- Confirm all backups are off (We don’t want backups running during the upgrade and slowing things down.)
- Install VISUAL 9 on VISUAL Server
- Uninstall VISUAL 7
- Uninstall Gupta Runtimes
- Uninstall Macros (you probably won’t have to do this depending on if and how you use macros)
- Install V9 Runtimes
- Install V9 Application Server
- Run “Before update scripts” (These scripts basically removed our custom indexes and triggers from the database, so they don’t interfere)
- Run V7.0.0 to 9.0.3 upgrade
- Install V9 Macros on VISUAL
- Install V9 version of Macros on VISUAL (including VMS files)
- APPLICATION GLOBAL MAINTENANCE -> Default “Store Macros in Database” (Check this box)
- Convert file Macros into DB Macros
- Copy all VMS files from Project Folder onto VISUAL
- Go to V9 -> Admin -> Load Macros
- Remove VMS files
- Import MACRO_PROFILE table
- Update all users with Default Profiles
- Run “Macro Scripts” in VISUAL Users List and Scripts.xlsx (These were the scripts I mentioned that added the profiles to VISUAL and added those profiles to the correct users)
- Set Default Profile permissions all to “Yes” (Including menu security and program security) (This was because we had a default profile we never used before but will now be using it to set macro permissions on all users)
- Update all users with Site ID 1
- Run scripts in “Site ID Scripts” tab of VISUAL Users List and Scripts.xlsx
- Insert InvoiceGeneration Preference Script
- Run scripts in “Preferences Scripts” tab of VISUAL Users List and Scripts.xlsx
- Copy over updated QRPs
- Copy updated V:\modified QRP files (This was the directory where all of our customized QRPs are stored, they were prepared ahead of time to work in VISUAL 9 so they needed to be copied over existing ones)
- Rename Accounting/QRP folders (We already converted all of the QRPs for the Report Writer, so we changed the directory to point to VISUAL 9 updated QRPs)
- Match To Standard on all Custom QRPs
- Update Order Acknowledgement QRP to new V9 directory (Ord Ack QRP is stored on Client machines to avoid conflicts when you share the QRP amongst multiple users)
- Update Preferences
- Admin -> Application Global Maintenance -> Options -> Preferences -> Preferred C/O Entry = Order Management
- Deploy SSRS Reports
- DEPLOY ALL APPS THAT REFERENCE NEW SCHEMA
- (This was a list of our custom applications that reference the VISUAL Database, unique to our company)
- Give SQL Service account permissions to the following tables: (If you have custom apps and reports that reference VISUAL you may need to add those permissions to these new tables. Not included in this list is CUSTOMER_ENTITY and CUSTOMER_SITE)
- PART_SITE (select)
- EMPLOYEE_SITE (select)
- Enable all things turned off in Step 2
- Deploy MacrosDeployment to all PCs (A lot of our VISUAL Macros just call .NET dll applications so these updated DLL apps need to be deployed alongside the new VISUAL)
Since VISUAL doesn’t have an MSI or any solution to easily mass install clients on multi PCs we used a piece of software called “Auto IT” to semi-automate the process.
After our GO LIVE Upgrade, we had a few things to work out, mostly because VISUAL 9.0.3 was a little tighter on data integrity with certain things VISUAL 7 didn’t care about. Namely, how our terms were set up for customers and our allocations of Customer Orders to Work Orders.
Most of our issues stemmed from custom macros needed to be tweaked to work with VISUAL 9 but we did have a handful of actual bugs in the VISUAL 9.0.3 code that needed to be resolved with Infor Incidents. Most of those issues have been resolved as of now (4 months after our upgrade) but we are still waiting on one or two more to be fixed.
All in all, the process took a lot of preparation, but the actual upgrade was deployed over a weekend.
following me on twitter.