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