Scale up or down SQL Azure Database Using DF V2


  • All right, let me start with the background of what exactly I have been trying to do

    Want to create a pipeline which essentially invokes a SSIS package doing the heavy data transformations and hence there are good chance that SQL database might run out of DTUs. So, before invoking the SSIS package, want to insert an element in the pipeline which firsts scales up the database say to S4, waits for this activity to be completed and kicks off the SSIS package execution and at the end, scales down the db back to S0. 

    Now, came across this excellent link which almost does it, however wondering to integrate this inside the DF pipeline. So far have created a stored proc which generates the script to be run on the master database which would scale up or down the database. So, in the DF pipeline have achieved it by adding a execute SP block.

    Now the next part, i.e. reading output of the first block and executing that output (which is a SQL script) on a master database (have connection established to it already). Is there any activity in Azure DF v2 using which I can simply execute the output of SP or SQL script on the database? Do not want to do it using Lookup activity which essentially needs a dataset. And again, I can still go ahead and create another SP in master database and use same execute SP activity from the pipeline pointing it to master database but do not really intend to do it because of two reasons i.e. It creates the dependency for pipeline of having SPs in place and second, not really sure if it would a good idea to create custom SP in master database.

    [Note: Not sure if this would be the best way to achieve simple scale up or down SQL database before executing SSIS packages on DF so alternatives are welcome too]

    Thanks in advance.

    Bhushan Gawale | RapidCircle

    Wednesday, April 18, 2018 3:16 PM

All replies

  • With SSIS in ADF, your heavy data transformations are actually done on Azure-SSIS IR (cluster of VMs in ADF dedicated for SSIS executions), not on Azure SQL DB hosting SSISDB.  Consequently, if you want to ensure a high performance for your transformations, you need to scale up/out your Azure-SSIS IR.
    Thursday, April 19, 2018 2:36 AM
  • Thanks for the reply Sandy, let me clarify the scenario a bit - there are certain queries which are being triggered from SSIS packages's activities and certain stored procedures being executed as well, now these stored procs would end up doing operations on large tables which causes the SQL Azure database DTU spikes and hence wanted to scale it up before SSIS starts executing. 

    Bhushan Gawale | RapidCircle

    Thursday, April 19, 2018 2:40 AM