SQL Azure distributing heavy read queries for reporting

Question

We are using SQL Azure for our application and need some inputs on how to handle queries that scan a lot data for reporting. Our application is both read/write intensive and so we don't want the report queries to block the rest of the operations.

To avoid connection pooling issues caused by long running queries we put the code that queries the DB for reporting onto a worker role. This still does not avoid the database getting hit with a bunch of read only queries.

Is there something we are missing here - Could we setup a read only replica which all the reporting calls hit?

Any suggestions would be greatly appreciated.

Answer 1

Have a look at SQL Azure Data Sync. It will allow you to incrementally update your reporting database.

here are a couple of links to get you started

http://msdn.microsoft.com/en-us/library/hh667301.aspx

http://social.technet.microsoft.com/wiki/contents/articles/1821.sql-data-sync-overview.aspx

I think it is still in CTP though.