Azure SQL Connections are Timing Out

Question

Ok, in an attempt to move my app to the cloud I've moved a local SQL database to Azure SQL. The problem is that the connection to that new Azure SQL database is so 'flakey' I'm about to bring it back in house.

The task is to loop and create a a total of about 481K records in the database.

The connection string is

"Server=tcp:xxx,1433;Initial Catalog=xx;Persist Security Info=False;User ID=xx;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;ConnectRetryCount=255;"

The SQL query it is running each time is not complicated. Just inserting three values into three columns. (column and values changed to protect some internal workings)

Insert Into TheTable (C1, C2, C3) VALUES ('V1', 'V2', 'V3')

but at random points it throws this.

System.ComponentModel.Win32Exception (0x80004005): The wait operation timed outExecution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at XX in D:\PATHOFTHEFILE:line 420

Note that

1) I'm opening and closing the connection each time I create a record and closing it in the next step.

2) There's no one else hitting the database except me.

3) The database service is set for S1.

4) Yeah - I get the irony that the program is crashing on line 420. I'm trying to find ways to drug test the code.

Questions

1) Is there anything wrong with my connection string? The documentation says that I should use a Timeout of 30 when connecting to an Azure SQL database. Frankly the code ran better (or at least lived longer) when I had the timeout set for 0.

2) At first I tried using a single connection to handle the loop through the entire 481K INSERT statements. Is that a bad design? How long will Azure SQL reliability hold a connection?

3) I'm not getting a warm fuzzy feeling about the ability to build rock solid apps on Azure SQL. Can someone point me to a good reference about the difference between building for local SQL vs Azure SQL. I've gone through everything I can find and there just didn't seem to be that much out there.

4) I like the fact that I can connect to Azure SQL with MMC. But there are (generically speaking) all kinds of monitoring info I can't get from MMC anymore. Anyone have a link to something that can help me really understand what's going on in the database without using that dreadful Azure Portal

UPDATE #1

Guilty as charged

public static void RunSQL(string SQLString)
        {

        int a = 0;

        SqlCommand Command = new SqlCommand(SQLString, TheConnection);
        try
            {
            a = Command.ExecuteNonQuery();
            }
        catch (Exception ex)
            {

            Notifications.EventLogging.ProcessEvent(SQLString + " go boom " + ex.InnerException + ex.Message + ex.StackTrace);
            Notifications.EventLogging.ProcessEvent("Time Of Death" + DateTime.Now);
            Console.ReadKey();

            }

Answer 1

Azure SQL instances are hosted on shared infrastructure. Azure will throttle requests to ensure that all instances on a server can meet the minimum SLA. In this life, death and taxes are guaranteed, but Azure SQL Connections are not.

To deal with this, you need to have automatic retry. Microsoft has provided a few options over the years, beginning with the now deprecated ReliableSqlConnection class. The preferred way to talk to Azure SQL these days is with Entity Framework 6.x, which has automatic retry built in.

In practice, an Azure SQL database that sees light and sporadic traffic rarely sees a throttle event. I've had developer friends who have deployed production code hitting an Azure SQL database, used raw SqlConnections and SqlCommands, and seemed genuinely surprised when I told them that connections aren't guaranteed. They'd never run across it! But if you're hitting the hell out of a server (as you are doing), I've seen it happen enough to be noticeable.

EDIT 12-6-2018: I have experienced this problem myself in the last few months. After combing through the logs, the culprit was spikes in database traffic that maxed out the DTU limit for my SQL Server. Retry is not necessarily a sufficient solution in this case because automatic retry effectively helps choke your SQL DB. To check and see if you are falling victim to DTU throttling, go to your Azure SQL DB's Overview tab, look at the resource utilization graph, and make SURE you select Max as the metric. It defaults to Avg, and this can hide spikes in DTU traffic.

Edit 8-6-2019: If you're maxing out DTUs and want to know what's causing it, there are a few places to look on the Azure SQL Management blade in the Azure Portal:

1. Query performance insights. You can use the tools here to find your top resource-consuming queries that have run in a given time period. This is a good place to start. Make sure to check through all metrics.
2. Performance recommendations. If you're missing an index, it's probably listed here.
3. Metrics. Check through all listed. Make sure to set your aggregation to Max.

This should give you good (or even great) indicators of what's going wrong.