Azure SQL Database – Beginner’s Guide to Replication

I ran into a similiar problem for an accounting package. Instead of trying to work out a way to move the SQL data around it proved to be much easier (and more cost effective) to provide a way to remotely access the application.

I opted for a TS 2008 box. This includes a large number of international users with terrible bandwidth compared to the states.

EDIT - As syneticon-dj points out there are still potential issues associated with using TS on a satellite link. You would definitely need to look at the latency of your link for a TS solution to work. Some satellite links provide around 300 millisecond latency, others may be over a second.

I am the author of the blog post mentioned above by Andrew.

The new service tiers scale (ie. limit) I/O, CPU and memory by tier. Higher levels/tiers provide more resources. There are no documented limits by resource type (only vague DTUs), though my tests provide some indicative I/O numbers.

UPDATE: I have now tested the memory limits by Service Tier, see: http://cbailiss.wordpress.com/2014/11/11/azure-sql-database-memory-limits-by-service-tier/

In terms of optimising design, I would first see which resource type I am hitting the limit on. The sys.resource_stats DMV in the master database has columns that show the %age resource usage (e.g. % CPU) you are using over time in that service tier/level. If you are able to run a test for say at least half an hour, then check that table and see if one of those columns is max'd at 100% then you have an idea of where you are bound. Of course, resolving that may or may not be easy/possible, but you have a starting point. The sys.dm_db_resource_stats is a newer DMV which has data for the last hour at 15 second intervals so would be useful for shorter tests.

If the I/O is the limiting factor, then for a small database I would expect performance to increase dramatically once the data is in the buffer pool. That was a very clear pattern in the tests I undertook and blogged, e.g. see the dramatic performance increase in this sequential read test on Basic Edition: http://cbailiss.files.wordpress.com/2014/07/2014-07-04-seqsel-metrics1.png taken from this post: http://cbailiss.wordpress.com/2014/07/06/microsoft-azure-sql-database-performance-tests-sequential-selects/

Of course, if/when the buffer pool is cleared, then performance will drop off again. That should in theory happen less with the new tiers - since the guaranteed performance levels in the new tiers are supposed to prevent noisy neighbours / load balancing database failovers that happen in Web/Business.
However, as far as I know, there are no documented numbers / guarantees about the buffer pool size that is allocated to each database in the new service tiers.

Hope that helps

Chris