How to alter a large Azure table without timeout

Question

I am trying to alter a field definition in a large Azure table (20 Gb, 500M+ records)

from nvarchar(max) to nvarchar(16) using

ALTER TABLE [dbo].[MyTable] ALTER COLUMN [MyField] [nvarchar](16)

but I cannot find a way to execute this command without running into a timeout. From Visual Studio and SQL Server Management Studio I get a

Msg 10054, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

after about 30 min. When I run the command from the Azure Management Portal after a few minutes I get :

Msg -2, Level 11, State 0, Line 0 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

How can such a long running query be executed on SQL Azure?

Answer 1

Try the v12 "WITH (ONLINE = ON)" option for your alter statement. See here for examples:

http://sqlperformance.com/2015/02/sql-performance/more-online-operations