Performance Azure Elastic pool databases

Question

We're running in to a similar issue as mentioned in Azure hits database cpu limits too easily. We are also using the Azure Elastic pool and are creating a new database for each customer. We use EF6-code-first to handle the SQL stuff.

Using the web front-end an user can create a customer. They do this several times a week and sometimes (not always but certainly ones a week) the database is only created partially and we get Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement errors. When we delete this client/db and create again it works fine. Of course this is very annoying. We are now working on catching this error, delete the db and restart the creating in code. This anybody have similar experience and hopefully a better solution?

Also creating the database takes a long time: 3-5 minutes. We only have 18 tables per database. Can we speed this up?

Next step in the workflow is to parse XML-files the user has uploaded. We use EF6 to fill our model and save it to the database. This is really slow. The XML-file contains employee-data we need to fill into several tables (around 15 tables). For example we have a 9.7MB XML-file containing 4416 employees and the '.save()' step takes about 12 min. Taking into account that a client has around 25 XML-files each year and the user in uploading 5 years at once this saving takes too long.

We already looked at the code and withing the boundaries of EF6 we don't think we can optimize it more. We're now looking at the configuration of our Azure subscription but documentation is scares and we cannot figure out what to change to get a better performance.

Any guidance is much appreciated.

Answer 1

I cross-post this question on the Microsoft Azure > Azure SQL Database forum: https://social.msdn.microsoft.com/Forums/en-US/b7c7ad0a-0be4-4b15-8ae6-494181ec60b1/how-to-increase-max-number-of-databases-allowed-without-increasing-pricetier?forum=ssdsgetstarted And the answer is:

You cannot scale partially (only certain resources) to another tier. You cannot scale the maximum number of databases only.

This is not the answer I was hoping for.

Answer 2

I have a cloud application which very similarly creates new customer instances with a new database shard. I have found that the code which creates these Azure databases must be VERY fault tolerant. Here's what I do:

1. Use a very long timeout of at least 5 minutes when you create your database. It takes a long time to return.

2. Wait even longer because even though the script ran and execution returned to your application, the database still isn't available for use yet. I have a simple query that I run against the database every 10 seconds. At first, the query throws an exception when it runs; I catch the exception to prevent the error from bubbling up. After a while, the query will run successfully and you know the database is ready to receive requests. I stop after 50 attempts to prevent an indefinite loop if Azure never successfully creates the table.

Now, it's generally bad practice to use try/catches for regular program flow like this and to hit a resource over and over, however, this is the only approach that I've found that works 100% of the time for this problem.