Overview of proposed phases:

1. Prep work
   • Setup dev & test environment
   • Evaluate and choose among available tools
   • Understanding Frontends and Backend
2. Analysis of front- and backend
3. Migration
   • 'Raw data'
   • Structure of tables, Indexes, Constraints
4. Adaptations of frontends
5. Missing/additional features/implementations
   • Security / auditing
   • Access 2016
   • Lookup Column re-implementation
   • Offline deployment
   • Automatic backup / replication
6. Testing
   • Functional test of frontends
   • Performance test
   • Concurrency / scalability tests
   • End-user / acceptance test / sign-off
7. Going live
   • Capacity building & 'development process' for DHA
   • Setup of production environment
   • Migration of productive system
8. Post-migration observations

While initially the focus of this scoping lied on the 'raw data', it turned out to be the simplest and best to validate topic. In a nutshell data exports from before and after the migration can be compared to ensure a complete coverage of the migration. Detailed description of various lessons-learned and migration issues are documented under Access-Findings.

Besides the raw data itself it is also necessary to validate the structure of each table so that they do not differ (too much). Obviously if the above data comparison is already successful, then the number of columns and their datatypes match at least roughly.

Nevertheless even if the data itself already matches it is important to understand which datatype was mapped how for the continued use of frontends. Customs scripts together with a manual comparison enable this side-by-side comparison of the structure for each table. Additionally such an "external representation" also helps to understand the data model itself. E.g. it allows quick adhoc searches like "where and how many decimal datatypes exist?" or "show me all Lookup Column RowSource definitions across the whole database".

Beyond the table structure itself also Index definitions are relevant. A non-migrated index can have a severe impact on the performance. Manual spot checks are straight forward, but an automated way similar to the above testing of table structure is possible.

Constraints also need to be considered during testing. As these are translated from Access expressions into T-SQL statements (Transact-SQL, the SQL dialect of MSSQL), conversion errors can occur. A manual spot-check should quickly reveal problems, but more automated ways to help setting up a side-by-side comparison are possible.

As the MDW (workgroup file) is only supported in MDB format (Access 2003) and is not available with a MSSQL backend, a new approach into the current security/auditing feature need to be written.

For now the best way forward is a Trusted ODBC connection from the Access frontend to the MSSQL with automatic authentication of (Windows) users against the Active Directory. Everybody with a valid Windows account and being part of an Active Directory group would be automatically authenticated.

Once a connection is established, the current user can be retrieved from MSSQL and then used throughout the frontend. It might even be possible to overwrite the default Access function CurrentUser() with a custom global function.

Below is a prototype for this global function (not work-able as it is). This function requires an ODBC passthrough query to query MSSQL for the current user name (select SUSER_SNAME() as username;): Dim current_user As Variant Public Function CurrentUser() As String If current_user = Nothing Then Dim qdf As QueryDef Dim rst As Recordset Set qdf = CurrentDb.QueryDefs("current_user") Set rst = qdf.OpenRecordset current_user = rst!CurrentUser Else CurrentUser = current_user End If End Function

The above code runs in the frontend similar to what is currently used. An alternative could be to offload the calculation of create&update user&timestamp to the server. After this change a conversion to the

to be defined:

• exact integration with Active Directory
• different DHA-MIS groups (psm, de, admin)
• read/write permissions in MSSQL

Currently Access 2003 is required to run DHA-MIS. However this version is no longer supported by Microsoft and has reached its end of life. A migration to the latest version Access 2016 is a worthwhile exercise to do now to ensure a stable environment.

• Save in new .accdb file format
• Deactivate VB reference to Active X Data objects (to remove usage of ADO; otherwise reference to e.g. Recordset is ambiguous between DAO and ADO)
• Reimplement security / auditing as above

Lookup Columns are not automatically migrated and Access/Microsoft doesn't document well how Lookup Columns work with Linked Tables. However this feature is too convenient to simply loose. Investigations and re-implementations are required to make sure this feature continues to exist.

For performance improvements, fewer disturbance with others, demonstration purpose.

For a successful migration of the technical implementation various aspects need to be addressed. For both backend and frontends, multiple tests are required to reach a certain level of confidence that the migration was successful.

Roughly these tests can be broken down into testing the functional and non-functional requirements. Especially due to the complexity of the frontends these tests should be carefully picked as a full and comprehensive walkthrough of the frontends with a side-by-side comparison between old and new environment is unrealistic. Such a "100% test coverage" is desirable, but due to complexity almost impossible to reach within a reasonable time frame. Carefully selecting tests and defining a test plan upfront help to reach "good enough".

The assumption for the first phase of the migration is that the frontends can stay 'as they are'. While this is true for many areas some code adjustments are required. For a definite answer the test coverage needs to be as high as possible. A list of all Forms, Queries, and Switchboard menu items was generated. Every Form should be tested with at least the basic create/read/update/delete actions and the results documented.

Across all frontends ~85 forms with ~580 queries are defined.

The encountered errors/bugs that have been already identified and corrected are documented in Access-Frontend_Findings

Performance is the most critical factor. Both PSM and the quarterly data entry require a performant system to be productive. During this evaluation significant slowdowns in selected areas have been encountered. Unfortunately there is no silver bullet to identify and address the most important parts without detailed knowledge of the implementation. It will come down to step through the frontends one by one.

Tests showed that once the data processing can be done server-side in MSSQL, then no significant drop in execution time is experienced (although in general the turnaround time is usually slighter higher than in the Access-only environment).

Experience and thoughts about improving speed of queries are documented in Access-Frontend_Findings.

With the current design of the frontends the performance is (slightly) slower with the MSSQL backend. In some/most (?) cases this might not be relevant (e.g. the input mask when defining new concepts takes 3 seconds longer to open), but in other cases this can be significant. Additionally reports that are already now known to run for a longer time should not take 'forever' after the migration.

With the walkthrough of the frontends critical performance hiccups need to be document and then addressed.

For all performance tests it is important to differentiate between 'local only' tests where frontend & server are running on the same system and 'network' test where they are separated. The network tests are more important for the end result, yet introduces another variable to the test as the performance of the overall network traffic could be slow.

Ideally there is a real-time view of the current status of the network, but it already helps if early morning or late in the afternoon the network is known to be less busy.

Additionally when moving the database to a server on the network, the performance of the server itself needs to strong enough. E.g. bottlenecks in memory or local disk speed can affect the frontend tests as well.

Once all the above tests provide satisfactorily results, real users with real usage patterns should test the system. Here it can be fruitful if high-performance data entry clerks will enter some data at the same time while the performance is monitored on by frontends and backends.

Concurrency issues are hard to test and potentially reproduce. But as it is already clear that there are differences between Access and MSSQL backends in terms of record locking and AutoNumber fields, tests need to account for this.

• Single-user test with long running queries/reports
• Multi-user test with many concurrent edits

After all above tests produce acceptable results, then final end-user tests should be considered.

The PSM usage could be done in a side-by-side fashion where starting from the same baseline dataset, the same data is entered in both environments. If both systems produce the same results in enough use cases, then the test is successful.

For data entry of the quarterly supervision data, a mass data entry with multiple high performing clerks will give enough insights for a final sign-off.

Prior to going live with the new environment a couple of pre-requisites have to be met. Only once the technical foundation is ready and well-understood and only once the current developers know how to use the new tools, the final migration should be approached.

Setup and familiarize with new environment/tools

• MSSQL
• Access
• Active Directory

Impact in DHA-internal development process

• Documenting, source code control
• Version management
• Release of new versions
• User management

Migration and evaluation period

1. Install & prepare runtime environment for MSSQL and Active Directory
2. Cleanup and change current backend DB
3. Prepare updated frontends and make them available
4. Take current backend DB offline/readonly
5. Migrate data according to Access-to-SQL-Server-via-SSMA on a local system
6. Upload migrated data to MSSQL
7. Start using the new frontends