IPTD-324
Access database stabilization pathway
Description
Establish the server space and procedures for the most efficient conversion (pro-actively or as needed) of MS Access DBs to Microsoft SQL Server. This solution path is aimed only at the back-end tables and would not improve or replace the Access front ends that may exist. The solution will still require ongoing expertise in the program representing the original builders who will be needed to ensure testing of each conversion guided by DOIT.
Project Justification
Programs lose time and sometimes and data in each of the Access breakdowns. DOIT faces unrealistic expectations to fix systems it never sanctioned or maybe even knew about. Creating a solution pathway can help programs be ready for breakdowns pro-actively, save time and manage expectations including understanding DOITs limits to fixing or replacing program-built, non-enterprise systems.
Activity
Show:
Completed guidance published at: Data Migration (Access) – NJ DEP | Division of Information Technology (state.nj.us)
latest draft at SSMA for Access_published_6-30-23.docx
5th meeting:
Neal added the “things to watch for” document now attached and is working on the overall guide.
Once the guide is done Knute will produce some DEPnet space with guide and materials for self-help. We agreed today to structure this as 100% self-help with no need to notify DOIT or record a project. I will however set up guidance to be in touch with the LTC before undertaking a conversion and to check the available list of data sources and to add a record if the target of conversion is not yet listed.
4th meeting:
Pilots:
No concerns so far from Leeds points (Julie Nguyen.?)
George Bergdomas (AQES) - CPU inventory - no recent feedback.
Scott Columberk?? - Fish Track DB, missing some data after the conversion. Neal following up to see if there is anything about the process or use of tools to avoid this, or if this all just part of looking at the result for errors. (Such as row count checks, etc.)
Patrick Harlowe- Green Acres - State Acquisitions (“State ACQ”) DB (very old), connects to 5 or 6 access files (DBs). needs more tweaking- optimizing (pass-through query) program seeking contractor to make the needed tweaks.
Neal to share what is drafted by June 5th. Aimed at technical guide for Access people. Why to convert, and then how.
Knute has been sharing the concept 1 on 1 with LTCs, but no interest yet.
Knute will loop appropriate LTCs in to four pilots/examples, and await Neal’s guidance before soliciting all LTCs for a program to try it entirely themselves (with our awareness and ability to back them up).
third meeting - Neal working with staff at Leeds point as a pilot with Neal doing the manual conversion. Neal is further testing a DB using the conversion tool from George Bergdomas (AQES) - inventory of PCs/Laptops (calls them CPUs). To be followed by George trying the conversion himself. Knute to continue sharing with LTC’s to seek eager pilot opportunities. Once Neal feels confident about the process, we can schedule a demo and full release of the self-help opportunity through Core Team.
Neal, Kurt and Knute met for second time. Kurt has established the VM DEP will need to host all SQL Server instances. Neal found and employed a Microsoft conversion tool (Access>SQL Server) on some first test cases (3 OPRA DBs that regularly get corrupted). He will work with Matt Coefer to have the conversion tested. Knute has the Database/Data source inventory from Kasak and will explore further conversion to Sharepoint/PowerApps for development to capture both sources and systems and their related info as part of documenting and sharing the DEP IT landscape to support new IT project assessments. Knute will continue the conversation with LTCs individually and aim for a group update when appropriate to brief all on the final product. Neal is anticipating needing input from all LTCs to decide how to split up the SQL server instances (roughly one per leadership area).