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

Răspunsuri

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
  • Hello,

    Having the Access front end stored on a VM may help to avoid transient errors and minimize latency between the front end and the database, but having an Azure VM have some additional costs.

    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


    You can use OneDrive to share the latest version of the Access front end with other users.


    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com




    vineri, 27 iulie 2018 12:45
  • Thank you once again Alberto for your assistance, 

    I have also been looking / thinking about VM 

    (Can you confirm this is a good article below)

    https://docs.microsoft.com/en-us/azure/migrate/contoso-migration-assessment

    Do you know of any financial comparison of using VM Plus and Azure SQL Database, with the front end access also on the VM I presume

    (This which would avoid data transfer costs from portal to the front end machines),

     

    and comparing Azure SQL Database connected to the front end (which involves the data transfer costs)

    Do you know of any similar experiences for companies that looked at both options, and did a quantitative analysis to prove which is the more economical option.

    For example, the higher VM + Azure SQL Database  + smaller internet traffic costs 

    are cheaper/more expensive (and significantly or marginally?)

    To that of an Azure SQL Database + higher data transfer costs

    Very much appreciated for your input.... and please just call out if the questions are too many!

    Thank you kindly again.

    Simon


    Thank you in Advance

    sâmbătă, 28 iulie 2018 00:19
  • Extremely extremely useful thank you Alberto.

    It has helped further with the initial recommendations.

    Thank you.

    Simon


    Thank you in Advance

    luni, 30 iulie 2018 01:31
  • Hello,

    Thank you for visiting Azure forums Simon. Keep sharing with us any challenges you face on your transition to the cloud.



    Have a great day!



    Regards,

    Alberto Morillo
    SQLCoffee.com

    luni, 30 iulie 2018 02:11
  • Hi Alberto, 

    I have enabled automatic Tuning, does this seem correct.  I used the recommended Azure settings.

    If I remember correctly, statistics will gather information over time when queries are run in order to allow better performing queries

    https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-statistics

    ALTER DATABASE <yourdatawarehousename> SET AUTO_CREATE_STATISTICS ON

    Then of course I believe we have to have regular statistics collections... I'll try to read the document more to understand this area more and what is required

    Thank you again.


    Thank you in Advance

    luni, 30 iulie 2018 08:55
  • Hello,

    When you rebuild indexes statistics are updates for those indexes.

     

    https://geeks.ms/davidjrh/2015/10/08/rebuilding-sql-database-indexes-using-azure-automation/

    You can also run update statistics with full scan with Azure Automation to update all statistics including those no related with indexes. You need to maintain statistics regularly despite you the database has auto update statistics enable. This is very important for query performance.

     

    Run these maintenance window (during non-production hours) of your database.

    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

     


    luni, 30 iulie 2018 18:31
  • Thank you once again Alberto, 

    I have run both on the SQL Server and Azure SQL Server, and defragmentation looks fine?


    Thank you in Advance

    marți, 31 iulie 2018 01:32
  • I am also reading a bit more on defragmentation

    https://www.mssqltips.com/sqlservertip/4331/sql-server-index-fragmentation-overview/


    Thank you in Advance

    marți, 31 iulie 2018 01:33
  • Hi Alberto, 

    I have followed this instruction booklet, and slightly updated

    https://docs.microsoft.com/en-us/azure/automation/automation-create-standalone-account

    https://docs.microsoft.com/en-us/azure/azure-resource-manager/management-groups-create

    I got an error message while running, could it be because the word View is in the table name.

    List of reserved words... VIEW is in it... so maybe i have to rename the table?


    


    In any event, it took 26 seconds to save the main form... 

    I'll try to learn sql server profile to see what data is coming across and maybe replicate a crosstab query if possible on the local sql view, and one in portal azure... 

    The next test if performance cannot improve, is trying a VM with the Azure SQL Database already set up?

    Thank you in Advance




    marți, 31 iulie 2018 02:15
  • I had commented that maybe the word "View" is the problem

    But so is time, and I have some tables called Job Time with a space.  Similar to Dashboard View with a space.  So they should both either be an issue, or none.

    I will get support to check this, because unsure of the error message reason.



    Thank you in Advance

    marți, 31 iulie 2018 04:42