coding, hacking, startups, computer security, technology and more

Upgrading Microsoft RMS With Linux Backed MS SQL 2017 Instance

Retail Management System was a point-of-sale system from Microsoft. Originally called Quicksell 2000 it was acquired by Microsoft from Quicksell Commerce in California somewhere around 2005. Like many things Microsoft end up acquiring the product was eventually left to rot and has now been discontinued from future sales. It's a pity because it was a solid product with a solid ecosystem, it just wasn't a good fit for Microsoft.

Of course, as is common with small to medium sized businesses - whatever works gets used at least until it breaks and so there's a lot of these systems still being used in production today. A long standing client of mine had to recently go about upgrading their primary Microsoft SQL database server and while the server had been providing many years of reliable uninterruptable service it was just starting to become problematic. The final straw was when the PSU suddenly failed one night. The PSU was replaced but it was time to upgrade to a newer machine. A new poweredge server was purchased from Dell Computer Corp. Then the fun and games began...

Bear in mind we're talking about a small 'mom-and-pop' style business here and in order to survive, thrifty is the name of the game. They will typically run whatever technical solution they can get away with for as long as they can get away with it. The old server was running Windows 2000 - not a big problem thou since the sole purpose of the server is just to run SQL server. This meant the new server was providing a massive leap forwards in terms of the technology which would be used. Installing Windows 2000 was no-go at this stage so we had to look at what our options were. Purchasing a newer version of Windows meant more cost for them. I had recently read the news of Microsoft releasing the first 'stable' release of SQL Server 2017 for Linux. It seemed like the perfect opportunity to go test the waters.

The latest copy of Debian Linux was installed on the server and Microsoft SQL Server for Linux was then installed. Here was the test.. I restored a copy of the live database from the SQL Server 2005 instance to the 2017 instance running on Linux. The restore operation went without a hitch - impressive! Next up was the real test thou, I reconfigured one of the existing POS systems to point at the SQL 2017 database running on the Linux server. The application started up like it didn't even realise anything underlying had been changed. So far, so good!

A further barrage of real world tests were conducted and everything seemed to work like the database server was still running on Windows. Seemed to good to be true so for the next couple of weeks I ran the two systems side by side in case we needed to fallback in the event of something going wrong, but nothing did go wrong. Everything worked without a hitch. All the machines on the network functioned exactly how they did as if they were still being supported by a database server running Windows. I don't often tip my hat to Microsoft but you have to hand it to them - it's pretty amazing that you can migrate such a complex piece of software from one OS to another and have it work almost exactly the same. Yes, I've read the technical details of how they architected a platform abstraction layer so they could re-use much of the existing code but it's still an incredible achievement nonetheless.

It's been over 6 months now and the Linux server running SQL server 2017 is actually more stable than the Windows one was. There's no need for a clunky UI and everything is accessible remotely via a terminal which makes it even easier to support. The backup application which was used on Windows was dumped and replaced with a shell script run via cron which performed the necessary. Mozy Pro cli then takes care of the offsite backup aspect.

This was just one of those times a crazy plan came together and went off without a hitch. I felt I had to write about it here since during the migration process I actually found there wasn't much information on the internetz about SQL Server on Linux. The conclusion: it works, and it works really well too!