‘Unable to reconnect to database: Timeout expired’ error when using SQLPackage.exe to deploy to Azure SQL

I have trying to update a Azure hosted SQL DB using Release Management and the SSDT SQLPackage tools. All worked fine on my test Azure instance, but then I wanted to deploy to production I got the following error

*** An error occurred during deployment plan generation. Deployment cannot continue.
Failed to import target model [dbname]. Detailed message Unable to reconnect to database: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Unable to reconnect to database: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The wait operation timed out

Turns out SQLPackage.exe was connecting OK, as if I entered an invalid password it gave a different error, so it had made the connection and then died.

Seems I am not alone in seeing this problem, and most people seem to suggest changing a timeout in the registry or your exported DACPAC. However, neither of these techniques worked for me.

I compare my test and production Azure DB instances, and found the issue. My test SQL DB was created using a SQL Export from the production Azure subscription imported into my MSDN one. I had done a quick ‘next > next > next’ import and the DB has been setup as an Standard (S2) service tier. The production DB had been running as the old retired Web service tier, but had recently been swapped to a Basic tier (it is a very small DB). When I re-imported my backup, but this time set it to be a Basic tier I got exactly the the same error message.

So on my test DB I changed it’s service tier from Basic to Standard (S0) and my deployment worked. The same solution work for my production DB.

Now the S0 instance is just over 2x the cost of a Basic , so if I was really penny pinching I could consider moving it back to Basic now the deployment is done. I did try this, and the deployment error returned; so I think it feels a false economy as want a stable release pipeline until Microsoft sort out why I cannot use SSDT to deploy to a basic instance

2 Replies to “‘Unable to reconnect to database: Timeout expired’ error when using SQLPackage.exe to deploy to Azure SQL”

  1. Hello Richard A key difference with the new performance tiers and performance levels is the I/O limitations. You can see this post for more details: http://msdn.microsoft.com/en-us/library/azure/dn741336.aspx. From the Azure Portal the recommendation is to use Standard S2 when importing. This recommendation is based on choosing the correct performance tier to match the workload. Once you have deployed your schema you can change the database back to Basic. With the new hourly billing this has a much smaller financial impact than it did when there was only per day billing. Now this does not mean you cannot use Basic as you are doing, it just means we need to adjust the command timeouts used by SQLPackage and VS. Today the only way of doing this is to change some registry values on your machine. Below is a list of settings based on the tool or API you are using: SQLPackage.exe, DacFx API & VS 2010: HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\10.0\SQLDB\Database\QueryTimeoutSeconds VS 2012: HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\11.0\SQLDB\Database\QueryTimeoutSecond VS 2013: HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\12.0\SQLDB\Database\QueryTimeoutSeconds Try increasing it from 60 seconds to 300 seconds. This should allow you to successfully deploy to a Basic database. If you are still having issues try increasing the value. Lonny

Comments are closed.