0

I'm advising a small non-tech company that outsources their software, hardware, and support services to a local IT provider. This company is preparing to migrate several of their internal applications from an in-house managed server to a cloud-based provider.

One of these applications is a business-critical MS Access-based inventory management tool. This tool is unfortunately built and maintained by one person located overseas, so we expect very little support for any custom development and have to treat the application as a black box.

Sales employees are currently accessing the in-house Access application via VPN and virtual terminals, connections to which have been EXTREMELY unstable. Additionally, the application can hang for up to half an hour intermittently, which has gotten worse over the 2 years they have been using it. One of the primary goals of this cloud migration are to solve these performance/stability issues.

Final note: their IT provider is looking to use Anchor as a cloud vendor.

My question: How can we best resolve these performance issues by moving "to the cloud?"

|improve this question
  • 2
    Could you specify the design of the Access application? Is it split into front end and back end? Are the VBA code modules locked? Does it have extensive VBA coding? One of the best things you can do with access is scale it up to SQL Server and use linked tables to access the data. However it requires significant development effort. If connections are flaky and it randomly hangs yet is a key system... it needs to have a lot of dev work to make it safer. Probably most MS Access/SQL developers could do it but may take a few weeks to a few months depending on the size of it. – blobbles Dec 5 '16 at 1:33
  • @blobbles I am afraid I don't know the design - for my understanding, there is extensive coding that would need to be redeveloped if they were to move from Access. I assume it is split, as there are multiple users connecting through VM to the server backend. – Piers Mana Dec 5 '16 at 1:45
  • @blobbles Do you have any references for "scaling up with SQL Server?" Does that mean being able to maintain front-end usage after migrating data to SQL Server? – Piers Mana Dec 5 '16 at 1:46
  • Yep, that is exactly what it means. However, its not quite as simple as it sounds, particularly if there are DB specific functions coded into the VBA modules. But in theory, you can put the back end tables/data on SQL Server while leaving the front end in MS Access. This requires considerable less development work than redeveloping an entire application, while giving you a quantum leap in terms of performance/scalability/consistency/locking. – blobbles Dec 5 '16 at 1:50
  • The design means you have a front end application which (usually best practice) is regularly downloaded to users PCs while the back end sits in SQL Server. Versioning needs to be handled to ensure users have the latest version of the front end application, but can be done easily. There is even an upsizing tool in MS access that can help you: support.office.com/en-us/article/… – blobbles Dec 5 '16 at 1:54
2

The "best" solution which we found was:
1. migrate the Access tables to an SQL tables 2. Migrate view & stored procedures to the SQL server 3. make the MS-Access run from a network location. This way there are two Ms-Access: one for the development work, the other for everyday use. This way ms-access can scale.
As the developer who has done this I can tell you this:
Migrating the tables is pretty strait-forward (there's even an option for that in Access). Then you need to check that the data types have stayed the way the are supposed to. SQL might use a data size which is bigger then it should be (currency shouldn't be money, it should be decimal (18,2); avoid floats if you don't need to; use the smallest data type possible; strings have migrated correctly; etc). Also: when Access uses SQL as it's back end it can only have one column as a primary key, otherwise it doesn't link properly. So if you had a primary key which was composed of two columns, you need to add to that table an identity column.
Once that is established you have a split Access application which means better availability. Yet, you need to migrate the views and stored proc: basically copy & paste the code form Access to SQL. Since the syntax is different you need to check for the following pitfalls:
(1) for example, now there's IIF in SQL so that sort that part out.
(2) parameters which are incorporated in the stored proc are used in a different syntax in SQL so that's also something to be aware of.
(3) Avoid ORDER BY in views. (don't use the SQL option, just don't use it and do the sorting in Access)
(4) Last but not least: in Access the "true" is -1, in SQL it is 1. It is very important to check that the WHERE clause says WHERE flag <> 0, and not flag = -1. Good luck!

|improve this answer
  • What was involved in migrating the views etc. to SQL Server? Was that developer effort or are there migration tools that can do most of the work? – Piers Mana Dec 5 '16 at 3:27
  • 1
    I've edited my answer to answer your questions. – Hila DG Dec 5 '16 at 4:20
  • Thank you! That's a lot of good considerations to work with. – Piers Mana Dec 5 '16 at 4:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.