Issue setting up automation account for SQL DB autoscaling

MoTaar 290 Reputation points
2024-03-06T13:15:25.1133333+00:00

Hello I am trying to set an automation account up for SQL DB autoscaling as in this article:

https://www.sqlshack.com/azure-automation-automatic-scaling-azure-sql-databases-based-on-cpu-usage-threshold/

But I am getting this error when testing the runbook:

Invalid URI: The hostname could not be parsed.
System.Management.Automation.RuntimeException: Cannot index into a null array.
   at CallSite.Target(Closure , CallSite , Object , Int32 )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
   at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
System.Management.Automation.RuntimeException: Cannot index into a null array.
   at CallSite.Target(Closure , CallSite , Object , Int32 )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
   at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
System.Management.Automation.RuntimeException: Index operation failed; the array index evaluated to null.
   at CallSite.Target(Closure , CallSite , Object , Object )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
   at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
Invalid URI: The hostname could not be parsed.
System.Management.Automation.MethodInvocationException: Exception calling "DownloadFile" with "2" argument(s): "Value cannot be null. (Parameter 'address')"
 ---> System.ArgumentNullException: Value cannot be null. (Parameter 'address')
   at System.ArgumentNullException.Throw(String paramName)
   at System.ArgumentNullException.ThrowIfNull(Object argument, String paramName)
   at System.Net.WebClient.DownloadFile(Uri address, String fileName)
   at CallSite.Target(Closure , CallSite , Object , Object , Object )
   --- End of inner exception stack trace ---
   at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception exception, Type typeToThrow, String methodName, Int32 numArgs, MemberInfo memberInfo)
   at CallSite.Target(Closure , CallSite , Object , Object , Object )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2)
   at System.Management.Automation.Interpreter.DynamicInstruction`4.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
Invalid URI: The hostname could not be parsed.
System.Management.Automation.MethodInvocationException: Exception calling "DownloadFile" with "2" argument(s): "Value cannot be null. (Parameter 'address')"
 ---> System.ArgumentNullException: Value cannot be null. (Parameter 'address')
   at System.ArgumentNullException.Throw(String paramName)
   at System.ArgumentNullException.ThrowIfNull(Object argument, String paramName)
   at System.Net.WebClient.DownloadFile(Uri address, String fileName)
   at CallSite.Target(Closure , CallSite , Object , Object , Object )
   --- End of inner exception stack trace ---
   at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception exception, Type typeToThrow, String methodName, Int32 numArgs, MemberInfo memberInfo)
   at CallSite.Target(Closure , CallSite , Object , Object , Object )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2)
   at System.Management.Automation.Interpreter.DynamicInstruction`4.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
The path 'C:\Users\ContainerUser\AppData\Local\Temp\.zip' either does not exist or is not a valid file system path.
The path 'C:\Users\ContainerUser\AppData\Local\Temp\.zip' either does not exist or is not a valid file system path.
The specified module 'C:\Users\ContainerUser\AppData\Local\Temp\b848a581-034c-458e-9a88-6870357e06ed\.psd1' was not loaded because no valid module file was found in any module directory.
The specified module 'C:\Users\ContainerUser\AppData\Local\Temp\b848a581-034c-458e-9a88-6870357e06ed\.psd1' was not loaded because no valid module file was found in any module director

System.Management.Automation.RuntimeException: The expression after '&' in a pipeline element produced an object that was not valid. It must result in a command name, a script block, or a CommandInfo object.
   at System.Management.Automation.PipelineOps.AddCommand(PipelineProcessor pipe, CommandParameterInternal[] commandElements, CommandBaseAst commandBaseAst, CommandRedirection[] redirections, ExecutionContext context)
   at System.Management.Automation.PipelineOps.InvokePipeline(Object input, Boolean ignoreInput, CommandParameterInternal[][] pipeElements, CommandBaseAst[] pipeElementAsts, CommandRedirection[][] commandRedirections, FunctionContext funcContext)
   at System.Management.Automation.Interpreter.ActionCallInstruction`6.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
Invalid URI: The hostname could not be parsed.
Importing Array of modules : 
Invalid URI: The hostname could not be parsed.
System.Management.Automation.RuntimeException: Cannot index into a null array.
   at CallSite.Target(Closure , CallSite , Object , Int32 )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
   at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
Invalid URI: The hostname could not be parsed.
Invalid URI: The hostname could not be parsed.
Invalid URI: The hostname could not be parsed.
Invalid URI: The hostname could not be parsed.
Invalid URI: The hostname could not be parsed.
Invalid URI: The hostname could not be parsed.
Azure SQL Database
Azure Automation
Azure Automation
An Azure service that is used to automate, configure, and install updates across hybrid environments.
1,120 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,886 Reputation points MVP
    2024-03-06T14:29:55.62+00:00

    That article is now obsolete. The article instructions are based on Run As accounts and that type of connection is not supported since September 2023.

    Instead, you can try to schedule the execution of a stored procedure using Azure Automation and from that stored procedure you can run an ALTER DATABASE statement to scale up/down the database as needed.

    ALTER DATABASE [YourDBName] MODIFY (EDITION='Standard', SERVICE_OBJECTIVE='S2')
    0 comments