T-SQL Tuesday Retrospective #002: A Puzzling Situation

For the second T-SQL Tuesday ever — again, hosted by Adam Machanic — we were asked one of three options, and I elected to go with the first one:

Describe a confusing situation you encountered, and explain how you debugged the problem and what the resolution was.

This invitation was originally posted on 4 January 2010, which was shortly before my spouse and I emigrated to Canada from South Africa, and I have a story about one of my first challenges there.

As a consequence of hitting the ground running in Canada, we found ourselves as part owners of a medical clinic in rural Saskatchewan. If anyone ever asks me about my experience as a project manager, I should point them to this.

For those of you readers not from rural Saskatchewan, it’s good to know that we took the security of our patients’ electronic medical records (EMR) seriously. After taking over the clinic, we put out a tender for a new EMR product that satisfied medico-legal requirements and was easy for the physicians and staff to use. After a lot of demos, we decided on the one we wanted to use. Even better, the vendor assured us that they would be able to migrate the old EMR database as part of the agreement.

So, when it came time to migrate the database to their new product (in the middle of nowhere with a very slow ADSL line) it was clear at 10pm — two hours after the migration started — that the vendor was getting nowhere. They had sent a consultant to install the new EMR software on our server, but the data migration itself was going to require uploading the database over the Internet to their head office through the secure VPN, converting it, then moving it back. By my estimate it would take more than a week just to upload it. They had until 7am the following morning when the staff arrived.

After some to-and-fro with the technical team via phone and email using the consultant as our go-between, it came to pass that the old EMR used a SQL Server 2000 (compatibility level 80) database, and that was the reason they needed to upload it, as SQL Server 2000 would not install on our new server which was running Windows Server 2008 R2. They couldn’t attach nor restore the database to the new server either, because of compatibility issues with the old EMR software.

What they needed was a SQL Server 2000 instance onsite in rural Saskatchewan that they could manipulate. I remember thinking to myself, “Is that all?”

I took out my trusty Asus laptop (which I still have to this day), fired up Windows Virtual PC running Windows XP, and installed SQL Server 2000 using the license key they provided. I copied over the database from the old EMR and attached it for them, and then gave them remote desktop access.

I believe it was around 6 or 7am when they finally finished the conversion process, at which point it was a simple case of backing up the new SQL Server 2000 database, restoring it to SQL Server 2008 R2 on the new server, then changing the compatibility level.

I can’t imagine what the solution would have been for the vendor had I not been around to help. I’m obviously pleased I was able to solve this problem fairly easily, but it got me thinking very deeply about documentation, support, and data migrations. We may think we have the best plans in place, but may not consider things like 4Mbps ADSL and a 500GB database that doesn’t compress because it contains scanned images.

I value experiences like this, and I also value the idiom “be prepared.” I had my laptop with me even though I didn’t expect we’d need it. I had Windows Virtual PC installed even though I hadn’t used it for months. I had the SQL Server 2000 installation media on a drive I brought with me from South Africa. I kept asking the technical team if I could help, even though they said they had everything under control.

If there’s anything for you to take away from this, it’s to keep an open mind when a technical problem presents itself. I’ve often heard technical people say that they aren’t creative, and I say that’s nonsense. Technical people have to come up with creative solutions all the time. Sure, it’s not going to hang in an art gallery, but when (for example) the health of thousands of people is on the line, it still counts.

Leave your thoughts in the comments below.

Photo by Nikita Kachanovsky on Unsplash.