locked
Migrating Access database to SQL Azure RRS feed

  • Question

  • Hi,

    As suggested from Access team blog, I've tried to use SSMA for Access 4.2 to migrate my existing Access db to SQL Azure. However, it seems the result is not that good in term of response time.

    I am not sure whether the problem lies on the connectivity issue or the structure of my migrated db is not optimized.

    Could somebody advise for this (especially SQL Azure team)?

    • Moved by Brad Calder Saturday, March 26, 2011 6:27 PM SQL question (From:Windows Azure Storage)
    Friday, October 8, 2010 2:47 PM

Answers

  • Hi Daniell,

    Performance in SQL Server and SQL Azure is anything but automated and should be considered heavily when writing applications. As Arunraj indicated you have tools at your disposal to measure performance and tune your SQL statements that are available in SQL Azure (although certain tools are not available, like SQL Profiler). Above and beyond tuning your SQL statements, you can actually measure the effects of your Internet connectivity; take a look at this tool (http://enzosqlbaseline.codeplex.com) to look at the difference in the number of packets returned by a SQL statement against SQL Server and SQL Azure. Since a connection to SQL Azure is always encrypted, you will generally see more packets going through the wire. If you return 1 row, you won't see much of a difference. When you return 100,000 rows... more packets = slower...

    While SQL Server and SQL Azure are basically the same RDBMS engine, they are usually configured differently on premise than in the cloud. For example SQL Azure forces a degree of parallelism of 1 on all statements which cannot be changed. There is also the notion of a query governor that will limit your ability to execute SQL statements that are too resource intensive in SQL Azure since it is a shared/multitenant environment.

    So in summary, performance against SQL Azure will almost always be different than against an on-premise database for reasons that are beyond your control. This typically means that developers should place a stronger emphasis on techniques and principles over which they have control, such as caching, retry logic (to account for network issues), compression of data, lazy loading of properties (for the Entity Framework) and so on.

     


    Herve Roggero, Blue Syntax MVP SQL Azure Co-Author: Pro SQL Azure
    Thursday, June 2, 2011 6:41 PM

All replies

  • "it seems the result is not that good in term of response time." Could you explain this, please?
    mysorian
    Saturday, October 9, 2010 2:52 AM
  • considering a table containing 100000 records are both stored in my local SQL server and SQL Azure. It took me only 2 seconds to retrieve all data in my Management Studio. While querying to SQL Azure took 1 minutes, 10 seconds. I understand that it may due to my internet connectivity issue. Let's not talk over it since it's subjective. But I just want to ensure if my tables / queries that was migrated using the SSMA tools are all optimized to my SQL Azure, so that the performance on the server side is guarantee.
    Tuesday, October 12, 2010 9:44 AM
  • I am not sure if you came across this situation and I am not sure if this could be the reason.

    http://social.msdn.microsoft.com/Forums/en-US/windowsazuredata/thread/78321613-887a-4981-819f-f42ec90ac080


    mysorian
    Wednesday, October 13, 2010 3:24 AM
  • Hi Daniell,

    SQL Azure Query optimizer works similar to SQL Server, so you can run your queries with Execution Plan and Statistics enabled and review the Execution and Add required indexes and statistics.

    SSMA Migrates your objects, but it doesn't optimize your database automatically.

     


    Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
    Thursday, June 2, 2011 3:00 AM
  • Hi Daniell,

    Performance in SQL Server and SQL Azure is anything but automated and should be considered heavily when writing applications. As Arunraj indicated you have tools at your disposal to measure performance and tune your SQL statements that are available in SQL Azure (although certain tools are not available, like SQL Profiler). Above and beyond tuning your SQL statements, you can actually measure the effects of your Internet connectivity; take a look at this tool (http://enzosqlbaseline.codeplex.com) to look at the difference in the number of packets returned by a SQL statement against SQL Server and SQL Azure. Since a connection to SQL Azure is always encrypted, you will generally see more packets going through the wire. If you return 1 row, you won't see much of a difference. When you return 100,000 rows... more packets = slower...

    While SQL Server and SQL Azure are basically the same RDBMS engine, they are usually configured differently on premise than in the cloud. For example SQL Azure forces a degree of parallelism of 1 on all statements which cannot be changed. There is also the notion of a query governor that will limit your ability to execute SQL statements that are too resource intensive in SQL Azure since it is a shared/multitenant environment.

    So in summary, performance against SQL Azure will almost always be different than against an on-premise database for reasons that are beyond your control. This typically means that developers should place a stronger emphasis on techniques and principles over which they have control, such as caching, retry logic (to account for network issues), compression of data, lazy loading of properties (for the Entity Framework) and so on.

     


    Herve Roggero, Blue Syntax MVP SQL Azure Co-Author: Pro SQL Azure
    Thursday, June 2, 2011 6:41 PM