Thursday, November 10, 2011

My experience with (SQL) Azure. Azure Reporting and Azure Data Sync Preview.

In the past I have blogged about my first experience with SQL Azure and my first experience with SQL Azure Data Sync. In this blog I will give an update. A lot is changed. So time for an update.
  1. All Azure services are combined in one portal: The Azure Management portal.
    In this portal you have access to all relevant Azure services. For instance Database for SQL Azure, Data Sync for the Azure Data Sync framework and Reporting for Azure Reporting Services. I'm using Database, Data Sync and Reporting at this moment. 
  2. The performance of the management portal differs between the different Azure services. Database and Reporting responds with an acceptable performance. The Data Synce response times is too slow. A new management portal will be introduced in the Service release Q4 2011 in Metro Style. I do not like that every time the complete look and feel of the management portal is changed. Think before you deploy.
  3. Firewall still only supports IP version 4. IP version 6 adresses are not allowed.
  4. SQL Azure and Azure Reporting Server is now available in datacenters all over the world.
  5. Maximum database size is 50 Gb. This will increase to 150 GB as of service release Q4 2011.
  6. Database backup can be made by creating a copy of your database with next command:
    CREATE DATABASE Database1B AS COPY OF Database1A.
    Be aware that you now pay for an additional database because you created a second database. As alternative you can use BACPAC to store the backup in Azure Blob storage.
  7. SQL Profiler is still not supported but here you can vote to get it higher on the priority list. A lot of DMV's are added to get insight in query performance. However you can't get insight in query details for one application of user connection.
  8. The interface of Data Sync Preview version is much improved in comparison with the CTP2 version. However as mentioned in point 1, the performance is bad. For instance the refresh of  schema information.
  9. Data Synchronization of column names with spaces is not supported in the preview version. This will be fixed in the service release of end November 2011.
  10. You can synchronize a subset of a table by selecting the columns you need to sync and you can  configure row filtering by defining a filter clause.
  11. Changing a sync group is still not possible. This is a missing feature. You need to remove the sync group and create a new one. This is a lot of manual work.
  12. Changes in your data schema is still not support in Azure Data Sync.
  13. Deploy of on-premise reports to Azure reporting is really simple. You can deploy your on-premise reports to Azure reporting without changing the report. You only need to change the reporting server to which you want to deploy. Reports are the same only the URL is different.
  14. Management portal for Reporting looks good and contains all basic functions you need.
  15. Rendering of Azure reports are slow in comparison with SSRS on premise reports. I have uploaded some of my on premise reports to Azure reporting. I have uploaded my test database to SQL Azure. Azure Reports are genereated on the Azure database. This works great. Only the performance is different. After downloading the execution log, I could see that the performance decrease was caused by the rendering process. On my premise SSRS server it took 50 milliseconds to render this report. In Azure Reporting is took 6 seconds to render. To be clear, I used the same report on the same dataset. Click here to see that somebody else encoutered the same performance decrease. What can I do to improve the performance of my reports on the Azure Reporting server? 
  16. As you can see a lot is changed and good progress is made in the last year with the Azure platform. A big shift is made, SQL Azure is now in the lead in comparison to SQL Server on- premise. For instance SQL Azure is already running on SQL 2012 (codename Denali). Engine version 11.0.1476. SQL 2008 R2 is version 10.50.2500
  17. Performance of applications, reports etc. is something I always keep in mind. A lot is done the last year to give the user more insight in how his application\reports are running in the Azure platform.  It is not a black box anymore, but it is still dark gray. For a commercial use it should be clear why and how you can improve performance.

1 comment:

clive.boulton said...

Awesomely useful progress post. Thx