Can Active Geo-Replication be used to separate read and write loads for performance?

Question

We're having trouble getting the performance we need out of SQL Azure, and I'm curious if anyone has tried using Active Geo-Replication to split read and write loads for performance, either successfully or unsuccessfully?

One of our databases seems to be a great fit for this. The only writes occurring on this database are from an application that downloads data updates as xml files from a 3rd party service; it processes those files to update the data. Files come in frequently and the app executes anywhere from a couple hundred to a couple thousand inserts and updates each minute. The app is provided by our data provider. Improvements to their updater, like batching insert & update statements, is not an option right now. Their updater is written in perl and uses ODBC. We've updated to the latest ODBC Driver 11 for SQL Server to enable connection retries in the connection string.

Reads occur from our .Net web service. The queries it executes are pretty heavy with lots of joins. We've done a fair amount of tuning and caching to reduce the load on our DB. When we were running it on SQL Server installed on some middle-of-the-road leased server hardware we were well within capacity. Now we're over on SQL Azure and seeing slow processing even at a Premium P2 level. Going to P3 is cost prohibitive at this time.

Earlier today I set up the active readable secondary, and I'm not seeing what I expect. The only thing connecting to our writable master is the updater app. Our web service is connecting to the readable secondary. However, Azure's portal show all the load on our master:

I also note that our average DTU percent used is up about 20% since setting up the active secondary.

If I execute SELECT SUM(execution_count) FROM sys.dm_exec_query_stats I get close to the same number of total executions on each DB. If I sit and refresh SELECT * FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) I also see a similar number of queries running on each DB. Furthermore, our read queries use views we created, and I am seeing our queries on each DB - not just queries the updating app runs (it has no idea about our views).

Can anyone help me understand what is going on here? If those 2 queries didn't show executions on the secondary, I'd say we aren't even connecting to it. We went from one P2 DB at like 60-70% DTU to two P2 DBs with one at like 90% DTU and one at 0% DTU. So now we're paying twice as much and appears we have even less resources available to us.

Are we wrong in thinking that we should have 2x P2 total DTU's here? Does Azure only give you one database's worth of resources for an active geo-replicated set? Is the stats somehow combined that I'm not really seeing what's hitting each individual database with those queries?

Answer 1

The problem with our secondary not receiving queries was that our connection string used a user id of 'user@primary-server'. Even though the host name was for the secondary server, the connection still went to the primary server. Once we used 'user@secondary-server' our read load went to the secondary as expected.

As for the usefulness of splitting load this way.. So far it looks like using two P1's in this manner gives us about the same capacity, perhaps a slight bit more than a single P2 (we haven't run benchmarks, just judging off used capacity with our normal traffic load). One advantage this setup offers is that we can put either or both of the databases at P2 to achieve a level of resources between a P2 and a P3 database (useful since it is a quite large difference in price between those two).