Long running Stored procedure without keeping connection opened to Azure database

Question

We have very long running stored procedure doing ETL work in order to load data from raw table into star schema (Fact - Dimensions) in Azure database.

This stored procedure takes around 10 hours to 20 hours running over 10 million rows (using MERGE statement).

At the moment, we run stored procedure from C# code (ADO.NET) with keeping CommandTimeout = 0 (forever). But sometime the connection is dropped as the connection to Azure database is unstable.

Is it possible to run stored procedure on database level without keeping connection opened all the time, and then log the process of Stored procedure in the Progress table to track down the progress?

I see some recommendations:

Agent Job, seems not possible on Azure database as it does not support at the moment.
SqlCommand.BeginExecuteNonQuery: I am not sure 100% BeginExecuteNonQuery still keeps connection opened under the hood or not.

Is there any other way to do this?

Any chance you could break it up into smaller executions? Like maybe 50,000 at a time. — Joe Enos, Jun 28 '17 at 14:27
If you run MERGE statement on 40M rows table, it take lots of time and it's hard to break down in chunk, complicated. I would like to find simple solution for this. Also, it depend on how powerful your database is, which tier your database is on? — cuongle, Jun 28 '17 at 14:30
Most likely whatever you do - sql server will terminate your procedure execution whenever it detects connection has been closed (and yes, BeginExecuteNonQuery will keep connection open). — Evk, Jun 28 '17 at 14:37
@Evk: Thanks, what's your suggestion in this case? Can SSIS will solve the problem? — cuongle, Jun 28 '17 at 14:40
Nothing comes to mind really, except make it run faster :) I dealt with many databases with hundreds of millions rows in tables but never even heard of procedures running for 10-20 hours. Running it on server with stable connection to target database is also not possible it seems, given cloud nature of azure database. Those runbooks mentioned below seems to be just automation tool, so probably won't help either. Real sql agent at least runs on the same machine and won't have problems with "connection" to server. — Evk, Jun 28 '17 at 14:44
@Evk: Unfortunately, SQL Agent is not available on Azure, how you deal with hundreds of millions rows with Store Procedure? — cuongle, Jun 28 '17 at 14:48
It depends on situation of course. I don't remember I ever run MERGE on such a huge table though. I suppose you already tried to speed it up with usual means (looking at execution plan, having correct indexes and so on). Here is also a question where OP runs MERGE against 300M tables and wants to speed it up (though in his case it "only" takes 40 minutes still): https://stackoverflow.com/q/6001980/5311735. — Evk, Jun 28 '17 at 14:52
I'd strongly suggest doing what Alberto Morillo has posted down below. On top of that, I would still tune the query (MERGE is notorious as a resource pig) and possibly break up the process into smaller transactions. — Grant Fritchey, Jun 28 '17 at 15:43
even if agent is available,why do you think there wont be time outs,i recommend breaking into batches — TheGameiswar, Jun 29 '17 at 04:47
FYI,there is azure managed instance in preview.This is same as sqlazure database(like backups,availabilty),but you can get all features which are available in onprem like agent,clr and so on — TheGameiswar, Jun 29 '17 at 04:51
How are you getting on with this? Did you ever post the query (with DDL, sample data etc) — wBob, Jul 05 '17 at 12:29
@wBob: No action at the moment, The SPs is complicated as it executes others around 20 SPs to do ETL transformation — cuongle, Jul 05 '17 at 12:54

score 3 · Answer 1 · answered Jun 28 '17 at 14:35

You can use Azure Automation runbook.

https://azure.microsoft.com/en-us/blog/azure-automation-your-sql-agent-in-the-cloud/?cdn=disable

https://gallery.technet.microsoft.com/scriptcenter/How-to-use-a-SQL-Command-be77f9d2

https://azure.microsoft.com/en-us/blog/azure-automation-runbook-management/?cdn=disable

http://www.sqlservercentral.com/articles/Azure+SQL+database/117804/

Hope this helps.

Regards,

Alberto Morillo

wBob · Answer 2 · 2017-07-13T10:00:36.257

Azure Data Factory has a Stored Procedure task which could do this. It has a timeout property in the policy section which is optional. If you leave it out, it defaults to infinite:

"policy": {
           "concurrency": 1,
           "retry": 3
           },

If you specify the timeout as 0 when creating the activity, you'll see it disappear when you provision the task in the portal. You could also try specify the timeout at 1 day (24 hours), eg "timeout": "1.00:00:00", although I haven't tested it times out correctly.

You could also set the timeout to 0 in the connection string although again I haven't tested this option, eg

{
  "name": "AzureSqlLinkedService",
  "properties": {
    "type": "AzureSqlDatabase",
    "typeProperties": {
      "connectionString": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>@<servername>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=0"
    }
  }
}

I would regard this as more straightforward than Azure Automation but that's a personal choice. Maybe try both options and see which works best for you.

I agree with some of the other comments being made about the MERGE taking too long for that volume of records. I suspect either your table does not have appropriate indexing to support the MERGE or you're either running too low a service tier. What service tier are you running on, eg Basic,Standard, Premium (P1-P15). Consider raising a separate question with the DDL of your table including indexes and some sample data, the MERGE statement and service tier, I'm sure that can go faster.

As a test / quick fix, you could always refactor the MERGE as the appropriate INSERT / UPDATE / DELETE - I bet it goes faster. Let us know.

The connection between Azure Data Factory and Azure database should be stable. If it isn't you can raise support tickets. However for cloud architecture (and really any architecture) you need to make good design decisions which allow for the possibility of things going wrong. That means architecturally, you have to design for the possibility of the connection dropping, or the job failing. Example is make sure your job is restartable from the point of failure, make sure the error reporting is good etc.

Also, from experience, given your data volumes (which I regard as low), this job is taking far too long. There must be an issue with it or the design. It is my strongest recommendation that you attempt to resolve this issue.

But how is using Data Factory different? It is also just automation tool (as I understand) and it will still run procedure on same machine as you would, with the same possible connection interrupts. — Evk, Jun 30 '17 at 06:58
Azure Data Factory is mainly an orchestration tool (with some ability to do transform) designed for long-running workflows - hence the policy and timeout attributes. I would strongly advise you to post your DDL including indexes, some sample data and the `MERGE` statement as a separate question and someone will help you. Can you also provide the other info requested on tier please? — wBob, Jun 30 '17 at 08:17
The code is big in order to post in here, the connection between data factory and Azure database is stable? If you have long running stored procedure, I am not sure whether the connection from data factory can be dropped or not, Agent Job on VM is the best option, I think — cuongle, Jul 13 '17 at 09:20

Long running Stored procedure without keeping connection opened to Azure database

2 Answers2