none
Move To SQL Server - any real advantages in this case ? RRS feed

  • Question

  • I have a client who uses a fairly complex Access application to run their business (portfolio management).

    The application was reliable until they employed users via VPN. This caused database corruption.

    They are now moving the VPN users to use RDP and they are establishing a Terminal Services box for at least 10 users.

    They are curious about migrating to SQL Server as well, but their data volume is fairly low (< 1 million rows for any table).

    Since the RDP implementation is likely to eliminate the corruption issue, is it fruitless to sell them on migration to SQL Server as the backend ?

    Saturday, August 4, 2018 8:37 AM

All replies

  • I agree that the database corruption will likely be eliminated. It could still crop up at another time; all it takes is one flaky NIC or one aging switch.

    All other advantages of SQL Server BE still stand.


    -Tom. Microsoft Access MVP

    Saturday, August 4, 2018 4:28 PM
  • I have to concur. In fact between Access and the data file there will not be any network at all – so Terminal Services tends to be a VERY reliable set. You STILL have to ensure that each remote user gets their OWN separate copy of the FE, but other than this issue, TS is a very reliable setup. If performance ad table sizes are not an issue, then I cannot think of a more reliable setup then using TS.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Saturday, August 4, 2018 11:39 PM
  • All other advantages of SQL Server BE still stand.


    -Tom. Microsoft Access MVP

    Thanks Tom...

    and can you enumerate those advantages please ?

    Sunday, August 5, 2018 9:15 AM
  • These are the most important ones for me:

    1. Security. Cannot steal the BE. Ability for fine-grained data security.

    2. Performance. Is not guaranteed, but the possibility of better performance exists.

    3. Monitoring. Profiler and other tools.

    4. Stability. No corruption.


    -Tom. Microsoft Access MVP

    Sunday, August 5, 2018 4:13 PM
  • Thanks Albert and Tom.

    From what I've been able to gleen from other experts is that RDP/TS will only create a problem with Access if the RDP sesssion "times-out" and that session had an Access form open in an EDIT mode.

    Thus to resolve this issue the TS timeout should be set to infinity (no time-out) and that all users should be forced to gracefully exit the RDP session via a script which prompts them to insure they have closed all open Access forms.

    Given the above, it does not appear that a migration to SQL Server is prudent or cost-justified in this case.

    Monday, August 6, 2018 9:56 AM