The “if it ain’t broke, don’t fix” mentality is becoming less relevant in these heady days of SaaS. In a mature business, set in their legacy ways, this mindset may begin to feel like a barrier to progress. Some may respond; “Well we’ve used the same system since those halcyon days of Windows 98 yore and it is still working…”
That said, once all support for your system ended years ago, you might also be starting to get the feeling that any time soon an update to your computer will be like a stake through the heart of your once beloved software.
“It ain’t broke, but it might be after the next Windows Update.”
You find that you start to get a little panicky whenever it’s slow to open or save something. And by degrees, you develop a facial tic and a nervous digit that drums on the desk; one that seemingly you alone are oblivious to; although, you do become increasingly aware of annoying drumming from your colleagues – most of whom appear to also have facial tics.
Churlish jokes in the office about the app are already commonplace and when someone new starts, you feel more than a little embarrassed when showing them around the application; it’s teeny tiny windows on your epic 4K monitor looking suddenly quite comical. Why are they pointing and sniggering? Oh, they’re not, you just imagined it…
“But not changing has saved my business a lot of money over the years”
To be fair, not upgrading or switching may have saved your business a lot of money over the years, but the rapidity of OS updates are suddenly making things seem a little precarious. The by now belligerent application is sluggish to start and hangs with a disconcerting frequency. Your back office server begins to fill up with paranoid copies of the app’s folder…
Then you visit a competitor. They use a shiny new SaaS app, and having a play on it is like the warm summer New Zealand sun on your face after a long miserable UK winter. It has modern UX and is fast and sleek. Data eases its way swiftly and effortlessly about the UI, and you realise that although your legacy system isn’t broken (yet), not only is it holding your business back, but everyone in your office avoids using it and they seem to die a little inside every time they have to. Your dark sunken eyes stare longingly at the bright and joyful faces of the people around you in your competitor’s office.
Most worrying of all however, is the notion that when your system does break – and it will, it may well spell disaster for your business. Years of business continuity undone in an instant.
“It’s not broke, but is it time to fix it..?”
so how do i get my data out?
So, you’ve resolved to migrate to a shiny new SaaS app. You’ll never have to worry about backups, updates or server hardware again. Things will be great, but there’s just one minor problem: First and foremost you have somehow to liberate 15 years of priceless business data from the antiquated storage engine in your current system. A database that is not only unsupported by the vendor, but whose active community of users have long since scattered to the four winds.
You search on the internet and over and over again, finding yourself at ancient, desolate forums whose terminal post typically reads something along the lines of:
“Someone please please help! How do I get my data from xxx into SQL server…”
Digital tumbleweed blows across the best viewed in Internet Explorer 800×600 desert(ed) site.
Oh dear. The facial tic is back. Through your mind’s eye, the fancy physical packaging that your software originally shipped in catches fire – with your data inside – and explodes.
reductio ad absurdum
Ok – ok, enough hyperbole, but the problem is a very real and concerning one, especially for mature businesses with large amounts of priceless historical data who are keen to modernise.
Here at Dynamo6, we are constantly pushing the boundaries of what SaaS apps can do for our clients. Be it syncing data from Mailchimp lists to Salesforce leads, or triggering invoices in Xero from entries in Harvest.
The good news is, not only can we help to connect disparate SaaS apps, we can also help to get your data imported into them in the first place.
There’s a lot of talk about what’s hot, but how do you migrate from what’s not?
We have a lot of experience with what’s hot in Internetland, but we also have plenty of experience helping clients with what’s no longer hot. This means that we can help when it comes to prising your data out of crusty old legacy systems to use in modern SaaS apps.
Projects involving data migration are of a slightly different flavour to regular ones; apart from anything else, they are run-once, with the focus firmly on the end result rather than the technology or infrastructure. We will work closely with you to maximise the amount of data that is migrated across from your legacy system by way of scripts, procedures, queries and more.
case study: migrating to Cliniko
Recently, we were approached by a medical practice who were looking to extract 15 years of patient data from their legacy system: Visual FoxPro based practice management system, and import it into cloud-based Cliniko.
Microsoft officially terminated FoxPro support back in 2007, but the client continued with the application for a further 10 years, which is a compelling example of the digital inertia that can be a problem facing many established businesses.
the requirement
The client’s data was stored in a large database comprising in excess of 20,000 patient records plus oodles of supporting data in more than 400 tables. We were tasked with finding a way to extract the said data, map as much as possible to the Cliniko schema and import it, either by use of the Cliniko API or CSV file import.
accessing
The first step was to assess export and connectivity options available within the application itself. At this point, it was important to outline what was possible and carefully manage the client’s expectations. Straight away we found that the limited export options available suffered from the standard 254 character memo field limitation (which also hampers getting data out of the Access engine). This limitation causes all memo fields to be ignored in queries and text exports unless explicitly cast to less than 255 characters. Since a lot of key information – some it highly verbose – was stored in memo fields, an alternative approach had to be found.
understanding
A big concern with a project like this is the extent of the vendor lock-in. Typically, FoxPro tables are stored as FPT files and fortunately this time, we found that it was possible to connect via ODBC/ADO to the database. Subsequently, we were then able to use a scripting language to run queries against it and generate outputs – although the database engine’s age did display some idiosyncrasies to be sure, such as lack of direct support for recordset paging and the consequent memory issues.
Another challenge with a database of this size was simply the reverse engineering aspect. Hundreds of tables (some of which had hundreds of fields) precluded the use of manual means, so this also had to be automated.
In spite of the size, however, the database was a standard relational database with consistent naming conventions for tables, field-names and keys, which helped greatly when constructing the queries for the various export components that were created.
mapping and export of data
The size and complexity of the FoxPro database precluded it from mapping directly to Cliniko and as only very basic imports of flattened data were actually supported, we needed to find another way to get all required data in – or at least linked. The core contact data could be imported via a straightforward text file import, so it was a relatively simple matter to flatten it to a CSV file.
For related records and attachments we created a PDF report with multiple sections for each flavour of record, and sections for embedded PDFs and images. In some cases, the reports were over 70 pages, but it was decided that they could easily be uploaded to Cliniko and attached to the patient records as part of the appointment booking process by the front of house staff. And the client was more than satisfied with this solution, allowing as it did simple access to all the historic data.
final analysis
The process for migrating data inevitably varies wildly between clients. Vendor lock-in aside, the limiting factor will often be the difference in granularity between the source schema and the destination one. In the case of the Cliniko schema, it did support much more than we were ultimately able to import, due to limitations of their API. But Cliniko is adding more and more functionality all the time – had the API supported more at the time of the project, we would have used it to map and directly import much more supporting data and perhaps even the attachments. In spite of this, the client was very happy with the final result and is now using their shiny new app. The facial tic is merely a fading memory.
data migration
If you’re considering migrating from your legacy system and would like to know more about working with Dynamo6, contact us.