pencrypted

Today was to be the day that months of work would finally finish. This was the day that the time collection system was to be migrated to the new server and upgraded to use an SQL database.

The rep from TimeTach, we shall call him Jack, was to show up at 10:00am from Mississauga. Amazingly he was on time and we set right to work. First task was to copy the database files and run a test conversion to ensure that nothing has changed since the last test. They (TimeTach) had tested this before in their office and had found numerous issues,but had worked through them. Naturally I assumed we would be converting the database using the same procedure they had used in the tests. I was wrong. We went the opposite direction. Instead of converting the entire database and correcting the errors later, we only converted the critical tables. This path turned out to be full of issues and it was finally decided to recopy the database to the new server and do a conversion of the entire database, the way they had tested it.

The test conversion worked perfectly. At this point we took a break for lunch as it was approximatly 12:00pm. After lunch we stopped all tasks on both the old Timekeeper server, and the tasks running in Navision. This essentially stopped the databases from updating anymore. Any data from this point till the upgrade was complete would be stored either on the clocks, or in Navision. I should take a break here and explain the data flow both in the old system, and what it should be in the new system.

Timeclocks: Are what employees use to clock in and out of jobs. They wand to their employee badge, then a task code, then a job code.

Timekeeper: Software that is used for payroll and tracking employees on jobs.

Navision: ERP system to keep track of all data related to orders, jobs, hours, employees…pretty much everything aside from actual cad data.

Old System: The old system consists of two servers. One server (CLM-VM) is running both the Timekeeper software, and the Connection Manager software. The other server (Navision) is running Navision. The data from the timeclocks is pulled to the CLM-VM server using Connection Manager, Connection Manager then updates the info to Timekeeper which then exports a data file which Navision then reads in over the network. This then works in reverse to send new jobs to the timeclocks. Timekeeper uses a Foxpro database in this scenario, which is the cause of most problems as it is too slow to read through all the existing jobs. Obviously there are more details, but that is the basic gist of the process.

New System: The new system should consist of one server. All of the above mentioned programs are on one server, but the process is similar. In this scenario, Timekeeper has been updated to a SQL database. Much faster.

New Ghetto System: This is the scenario we were aiming to accomplish today. Timekeeper is updated to SQL and moved to the Navision server. Connection Manager is not as it is too old and no one knows how to move it yet. So all the data to be sent to/from the clocks would still go across the network, but there would be no huge database queries over the network.

From here on out it should have been the simple task of converting the database to SQL and changing a few settings in Navision and Connection Manager to point to the new directories. The conversion went well and we tested an export from Navision to TimeKeeper with great results. At this point we installed TimeKeeper clients onto the human resources dept workstations.

Filled with happiness we started to set Connection Manager to point to the new TimeKeeper server. This is when Jack discovered a critical issue that they had not anticipated, or prepared for. The Connection Manager software was not in fact reading in a file exported from Timekeeper to get the data to send to the clocks. Connection Manager was in fact interfacing directly to the TimeKeeper database to get its data.

I thought this was great as it seemed much more efficient than running another export. Jack did not, as there was no way to get Connection Manager to interface with the database over the network. There was also no way to install Connection Manager onto the new server as no one at TimeTach knew/knows how to. Seeing as it was 6:00pm and we had stopped all new jobs from going to the clocks for six hours, we had no choice but to undo all the work that was done and revert back to the old system. So all in, a waste of an entire day and we made no progress aside from finding out ahuge flaw.

I am shocked that this was not discovered until now, as the company that was doing this upgrade for us, is the exact same company that initially installed the system six years ago. The only thing we gained out of this little adventure today is the knowledge that TimeKeeper SQL at least works with Navision. Although I must say that I am happy to start seeing results from TimeTach, and Jack really did try to work around most of the issues. Unfortunately, you can’t be an expert on everything.

Yay for wasted time.

Note: All server names are not real.