Visual Studio 2013 Publish Database to Azure

Question

I have created a new "Database" project in Visual Studio 2013. I have set the Target platform to "Windows Azure SQL Database". The project is nearly empty, with the exception of one .sql file to create a Schema.

When I try to publish the project, it takes several minutes and ends with:

Creating publish preview... Failed to import target model [database_name]. 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.

I have tested the connection string, and it works.

What do I need to do to publish to Azure? Thanks.

Busboy

I am facing the same problem when trying to publish database using the basic tier. Having my database in Standard tier working OK. – Ceiba

Answer 1

Like Hesham mentioned in the comments, I also had this issue with the new Basic tier of Azure SQL Database. Switching the tier to Standard S0 size fixed the issue. So if you're having issues with the Basic tier, try scaling up to publish, then scale back down when you're finished.

Fransis

This is the only thing that works for me. THANK YOU! – Joycejoycelin

I also had to update the Initialization Connection Timeout to 0 get get decent performance during a publish as @stringfellow answer below indicates. imgur.com/1KYjfTQ – Minima

I have experienced this problem even on the "standard" size, and had to use Premium. It is very annoying to wait over an hour only to have it fail. Why is this still a problem? – Syncope

@SmackJack I haven't had the issue on S0 with the latest SSDT updates. Sometimes it does take a while, but usually that's on the order of 5 mins. Are you using VS2015 Update 2 and the latest SSDT? – Fransis

@Fransis Yes, VS 2015 Update 2... I have not installed SSDT separately. – Syncope

Answer 2

Check this answer from MSDN forum, worked with me perfectly!

In order to change the command timeouts used in Visual Studio 2013 you will need to change the following registry setting:

HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\12.0\SQLDB\Database\QueryTimeoutSeconds

Source:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7e869f10-529b-41af-b54f-709a420308f6/publish-database-to-a-new-basic-scale-db-from-vs2013-times-out?forum=ssdsgetstarted

Ceiba

Sorry, but this is not an acceptable answer. I have a database with ZERO tables, and a single SCHEMA. It should not take 5 minutes and time out. Increasing the timeout is NOT a solution. – Busboy

Update: I originally set the timeout to 300 seconds and it still failed. Then I set the timeout to 0, and the job completed in about 2 minutes. Bizarre. – Matherne

Whether you LIKE the thought it may take you a long time to deploy to Azure is a completely different issue that we can't help you with, only Microsoft can. – Nasser

+1 The following from the MS SQL Support Blog says the same thing ... worked perfectly: blogs.msdn.com/b/sqlblog/archive/2014/11/13/… – Phares

Key point from that blog: The reason why this happens is because the Publish (or Generate Script) operation reaches the CPU limit of Basic edition and ultimately times out. – Bass

Answer 3

I had experienced the same problem and was able to resolve it by changing the 'Connect Timout' value to 0 in the 'Publish Database' dialog.

1. In the 'Target database connection:' field, click 'Edit...'.
2. In 'Connection Properties' dialog, click 'Advanced...'.
3. In the 'Initialization' section, set 'Connect Timeout' to 0.

Link to screencapture...I don't yet have enough points to publish an image. :)

My project had been taking 2-3 minutes before failing with the timeout. After the setting change, it successfully published within a minute.

I hope that helps.

Legitimate

I went with Paul's answer but I'm voting for this one. This seems like the best answer b/c Paul's answer results in an increase in your monthly bill. – Longstanding

A combination of this one and the one from @hesham did the trick for me, although I'm sure upgrading to larger database tier would have done the trick as well. – Mccormac