Automation and The Migration of Data In An Air-Gapped Environment

The Scottish Longitudinal Study (SLS) is a large-scale linkage study created using data from administrative and statistical sources, accessed via a safe haven. The SLS technical team recently migrated our data from a SQL Server to Postgres database as part of our switch to a Linux environment. Operating within a safe setting created unique challenges, as most migration solutions rely on internet connections to assist the user with installation and setup processes. Without the internet, options included building/installing from source code, writing custom Python scripts, and required perseverance in finding solutions that both fit our area of expertise, while achieving the desired result. Data validation was an important consideration after migration, to ensure source and destination data matched. As the architecture differed, and functions available in SQL Server had to be re-written to work in Postgres, we created custom Python scripts to achieve a majority of the conversion. For the remainder, we divided the work of manually converting .sql queries and employed a code review to ensure data integrity between the old and new systems. The combination of scripted and manual efforts allowed us to successfully migrate our data with a high degree of confidence that we had not lost any data, and were aware of the small number of differences we did encounter. Working through these issues, we came up with several workarounds and scripts to complete the tasks, allowing us to now share our successes and lessons learned.