Remoting in Azure Automation Runbooks (SQL DB Creation sample)

I find that using PowerShell remoting in my Azure automation runbooks is sometimes more convenient, as it eliminates the need to install and update additional PowerShell modules on my OMS hybrid runbook worker. For future reference, I wanted to capture an example of a simple approach to PowerShell remoting I find intuitive.

The activities in this relatively simple example of remoting in an Azure Automation runbook include the following:

  • Retrieves user and password info from Azure automation variables, then creates a PsCredential object
  • Remotes from the worker where it is run (the HRW in my case) to a SQL 2014 or 2016 server. (The name of the server and SQL instance are supplied in the $RemoteComputer and $SQLInstance parameters of the runbook)
  • Loads the SQL PowerShell module
  • Creates the SQL database with the default settings, named per the $DatabaseName runbook parameter
  • This sample also includes a trace log to demonstrate where the code executes (on the remote SQL server)



# Retrieve admin user and password, create credential object
$strScriptUser = Get-AutomationVariable -Name 'ContosoAdminUser'
$strPass = Get-AutomationVariable -Name 'ContosoAdminPassword'
$PSS = ConvertTo-SecureString $strPass -AsPlainText -Force
$cred = new-object $strScriptUser,$PSS

#Invoke script block on the remote SQL server
Invoke-Command -Computername $RemoteComputer -Credential $cred -ScriptBlock {

# Import SQL Server Module called SQLPS
Import-Module SQLPS -DisableNameChecking

# Simple log to prove we are remoting
# Logs to c:\Windows\Temp\remoting.txt
$CurrDate = Get-Date
$message = "We remoted to $env:ComputerName on $CurrDate"
$file = "c:\Windows\Temp\remoting.txt"
$Message | Add-Content -Path $file

# Your SQL Server Instance Name
$SqlInst = "$using:SqlInstance"
$Srvr = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SqlInst

# Database PSDB with default settings
# By assuming that this database does not yet exist in current instance
$DBName = "$using:DatabaseName"
$db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database($Srvr, $DBName)

Print Friendly
Posted in Blog Tagged with: , , ,

Leave a Reply

%d bloggers like this: