none
Using Azure and Virtual Machine with disc storage to store/access the Access 2016 backend file

    Întrebare

  • Hi Azure Forum

    I have been looking into how to migrate Access backend into SQL Azure Database



    But

    Alternatively 

    can we use Azure with setup Virtual Machine with disc storage, or simply disc storage to store/access the Access 2016 backend file


    But of course this back end access file will continually be access, updated.... is this concept valid, or simply sql Database is the proper course

    Thank you kindly

    Simon

    • Mutat de VeeraGiri Babu joi, 12 iulie 2018 07:41 Better suited here than Storage
    joi, 12 iulie 2018 02:34

Toate mesajele

  • Hello,

    Applications and databases on the cloud need to handle temporary service interruptions however Microsoft Access is not made to handle those service interruptions. As you may already know, one of the most commented causes of Microsoft Access database corruption is interrupted data changes due to network service failure. To learn more about temporary service interruptions inherent to the cloud, please read the documentation below:

    https://docs.microsoft.com/en-us/aspnet/aspnet/overview/developing-apps-with-windows-azure/building-real-world-cloud-apps-with-windows-azure/transient-fault-handling

    My suggestion is to move your database to Azure SQL database, create linked tables to access databases, and keep the Access front end on the laptops and PCs of all users.


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com



    joi, 12 iulie 2018 04:07
  • Thank you again Alberto for that insight.



    Hence the use of an Azure SQL Database instead of the SQL Database is the first step.

    and creating linked tables.

    That is after I resolve trying to get the installation of SSMA for Access as per my last dialogue.

    It seems that other users have had issues with the migration when i do a search within these forums.



    But in terms of my (nieve) questions, would it be possible to simply set up some storage space, attached to a virtual machine in the portal and simply place the backend of the database there, and link as you indicate the tables to this file.



    But i believe this is your answer to the question:Applications and databases on the cloud need to handle temporary service interruptions however Microsoft Access is not made to handle those service interruptions. 



    Thank you again.


    joi, 12 iulie 2018 06:30
  • Hi Alberto, 

    I have been reading up in order to try and follow your guidance.

    In the fms handy guides, they use an SQL Server with the "ODBC Driver 13 for SQL Server"

    On the current SQL Database shell i have created, and the current dsn using this ODBC file, I also went to Access and clicked the import , then link tables, and it showed me all the system tables (as I have not migrated the current access database into the sql server in azure)

    If I was to create an Azure SQL Server, and migrate the files instead to here, 

    firstly would this same ODBC Driver 13 for SQL Server work, and secondly, 

    can you briefly explain the differences between the Azure SQL Server and SQL Server for my knowledge.

    Thank you again.

    Simon

    joi, 12 iulie 2018 07:02
  • Hello Simon,

    If your current backend is not an Access database file but a SQL Server file (with a .MDF file and a .LDF file) then yes, you can then have that database of an Azure VM with fixed disk storage. However, have you considered the cost of an Azure VM compared with the cost of an Azure SQL Database?


    About the differences between SQL Server and Azure SQL Database, please read the following documentation/resources:


    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-features

    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-paas-vs-sql-server-iaas


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com



    joi, 12 iulie 2018 11:04
  • Thank you very kindly Alberto, 

    As a brief look, it seems that the Azure SQL Database is the most cost efficient, and a good option due its features.

    I will try and read and understand the differences a bit more coming from a non database background.  

    Thank you in advance, and look forward to your other replies in the posts.

    Thank you.

    Simon

    joi, 12 iulie 2018 11:42
  • Hello,


    Thank you for letting me know that I have questions left to answer.

    You can use the same ODBC driver. It is fully compatible with Azure SQL Database.

    Please allow me to make two recommendations for you once you have your databases migrated to Azure SQL:

    1. Defrag indexes and update statistics. https://blogs.msdn.microsoft.com/azuresqldbsupport/2016/07/03/how-to-maintain-azure-sql-indexes-and-statistics/
    2. Enable automatic tuning. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com


    joi, 12 iulie 2018 14:27
  • Thank you immensely Alberto for your knowledge input

    It is undoubtedly a step up in complexity from a standalone version on the desktop to hosting the access database in the cloud.

    Now that you have opened some further thoughts, I imagine we can store the access fronted also in maybe a VM with discstorage as we discussed above, and have a webfront end that links to the frontend access database fields.

    Would this reduce the risk of temporary service interruptions, and other issues you not.

    Other non-options, I believe we can rule out is Microsoft Onedrive to do a similar job of hosting the access back end, because it is a file share server.

    apologies for my enthusiasm in all these question, but it seems a truly impressive offering Azure

    Thank you in advance.

    Simon

    vineri, 13 iulie 2018 00:14