Nobody likes to do the same monontonous task over and over again. Well, saying that, maybe some out there do in order to look and feel busy...but I don't, as I nearly always have something else more pressing or fun or interesting to do. By automating those repeatable tasks, it reduces boredom, chance of errors, and stress if you're already a busy bunny.
This is where Automation comes into play, and in Azure we have a few options. This post focuses in Azure automation.
Azure Automation Account
Firstly, you'll need an Automation account to be able to build your processes, not the greatest name I know, as it implies a single login sort of thing, which it just isn't. Open the Azure Marketplace and search for Automation.
Click Create to get yourself started. As with creation of most Azure resources, choose a ResourceGroup, Name and Region as required. You can also choose if you want a system or User Assigned managed identity in the Advanced tab.
Managed Identities
You may want to add a User Identity to the Automation account, which can be used in our Runbooks (later on) that contain the automation routines that we want. This is a good practice to perform, as it allows for much tighter security as you automate more. I.e. you probably won't want one account to have access to everything that you want to automate.
For the purpose of this demo, we're going to keep things simple and use the System Managed Identity, but to create a User Managed Identity, see below.
User Managed Identity (should you want one)
In the Azure Marketplace, search for 'Managed Identities'
Simply Create, choosing the resource group and name required...ie RodUserManagedIdentity1 in my case.
Now we need to add the Identity to our Automation Account.
In the account settings blade, choose Identity, and then 'Add User assigned Managed Identity'. Start typing in the name of the Identity created above, Select it then 'Add'
Modules
Great, thats now got the basics in place. Many of the automation routines that you prefer may (or may not) be powershell based, so you will need the modules to be available to your Automation account. Thankfully, this is made really easy in Azure.
Under Shared Resources, click on ... you guessed it, Modules
Followed by: Add a module then the Browse from Gallery radio button.
Type in sqlserver, and {enter } to search. Highlight the below, and then 'Select'
Back in the original Import module screen, choose the version of the Runtime required. For the type of Runbooks you are using (see below), this is important, so choose 7.2 for the purpose of this blog. The good news is that you can import multiple versions of the same module. Hurrah!
Credentials
At this point, you may have spotted that there is a Credentials option under Shared Resources in the Automation account, this can be used to create Usernames and passwords to use (ie... like a SQL Username and password). This is up to you if you want to choose this method, however using User Identities above where they are supported provides more security.
RunBooks
These are the oily bits that actually define our Automation routines. Lets dive straight in with our spanners. Click on RunBooks.
At the top you have several options.
To start off, take a look at the many, many gallery Runbooks, so select Import a runbook.
Select the Browse from Gallery radio button, and click the link to browse from the gallery. By selecting these, the code can be seen, so you are able to see what sorts of things can be achieved using Runbooks.
However, for now, we'll create one from scratch.
Choose Create a runbook
Fill in some details to save.
You have several types of RunBook to choose from
Powershell
Python
Powershell Workflow
Graphical Powershell
Graphical Powershell Workflow.
We'll choose good 'ol Powershell... as I haven't hammered my keyboard enough today.
Note the Runtime version above, and 7.2. This means that the modules that you installed above need to match the version of the Runbook type you are implementing.
Editing
To Edit your runbook , just chose the Edit option (I'm using Edit in Portal here) from the Runbooks list. We'll come back to that later.
Automation Example
For the purpose of this automation example, we're going to just make a copy of an Azure SQL DB, and then run a little bit of TSQL against it. This is just a very simple task, but something that you might have to do...ie... copy a database and then change some of the data within. In this case, i'm just updating some PII data in a table to be a set value...contrived yes, but shows a potential usecase.
Note, in this example, the Login-User-Password already exists on source/destination with a matching SID so there are no orphaning issues.
We start with, a DB we want to copy 'RodAdventureWorks'on our 'Prod' server, and an empty target 'Dev' server.
Azure Access Control
In our runbook, the automation account is copying a database from one server to another, so naturally that automation account needs to have access to both the source and the destination Azure SQL Servers at the Azure layer as well.
Resource Access
So on both the source and destination servers.. choose Access Control (IAM)
And Add - Add Role Assignment
Choose SQL DB Contributor - Next
Assign Access to - Managed Identity, and click Select members
On the right hand side, now select the Managed Identity, and then the System Managed Identity for our Automation Account, which is just the name of the Automation Account itself.
Click Review and Assign to assign the role.
Great, so we're now prepped.
Lets get Scripting.
The full script is at the end of this post, copy that out and Edit your new Runbook, pasting it in there.
As ever, i'll break it down.
Param(
[Parameter (Mandatory= $true)] [string] $ResourcegroupNameSource,
[Parameter (Mandatory= $false)] [string] $ResourcegroupNameTarget,
[Parameter (Mandatory= $true)] [string] $ServernameSource,
[Parameter (Mandatory= $false)] [string] $ServernameTarget,
[Parameter (Mandatory= $true)] [string] $DBNameSource,
[Parameter (Mandatory= $true)] [string] $DBNameTarget,
[Parameter (Mandatory= $true)] [string] $SQLCred
)
# Setup credentials
$myCredential = Get-AutomationPSCredential -Name $SQLCred
$userName = $myCredential.UserName
$securePassword = $myCredential.Password
$password = $myCredential.GetNetworkCredential().Password
# Ensures you do not inherit an AzContext in your runbook
$null = Disable-AzContextAutosave -Scope Process
# Connect using a Managed Service Identity
try {$AzureConnection = (Connect-AzAccount -Identity).context}
catch {
Write-Output "There is no system-assigned user identity. Aborting."
exit
}
# set and store context
$AzureContext = Set-AzContext -SubscriptionName $AzureConnection.Subscription -DefaultProfile $AzureConnection
Write-Output "Account ID of current context: " $AzureContext.Account.IdTo Start off, we'll set our params of where we want to Copy from/to, and the name of the Automation account Credential ($SQLCred) we are going to use the contains the SQL Server UserID and password for our SQL DBs. This pulls the ID and password from the Automation Account.
We then connect using the Automation Account (System Managed Identity in this case) and set the context of the session to that connection. I output to the screen here just for clarity.
# Set target RG to be source if not supplied.
If (-not $ResourcegroupNameTarget){
$ResourcegroupNameTarget=$ResourcegroupNameSource
}
# Apply a standard naming convention if no name supplied
If (-not $DBNameTarget)
{
$DBNameTarget = "$($DBNameSource)_Dev"
}Little bit of setting some defaults if not supplied, ie, assume Resourcegroupname target is the same as source if target is not explicitly supplied. Also set a target database name of the source name with _Dev trailer if the target DB name is not supplied.
# Copy the DB to the target server.
New-AzSqlDatabaseCopy -ResourceGroupName $ResourcegroupNameSource -ServerName $ServernameSource -DatabaseName $DBNameSource -CopyResourceGroupName $ResourcegroupNameTarget -CopyServerName $ServernameTarget -CopyDatabaseName $DBNameTargetNow the real work. Copy the DB from source to destination, passing in our params.
# Now run a query to obfuscate.
$DBConn = New-Object System.Data.SqlClient.SqlConnection
$DBConn.ConnectionString = "Server = $ServernameTarget.database.windows.net; Database = $DBNameTarget; User ID = $userName; Password = $password;"
$DBConn.Open();
# Create command to update the data
$DBCmd = New-Object System.Data.SqlClient.SqlCommand $DBCmd.Connection = $DBConn
$DBCmd.CommandText = "UPDATE SalesLT.Customer SET FirstName='Hidden', LastName='Data', Phone='000-000-0000'"
$DBResult = $DBCmd.ExecuteNonQuery()
$DBConn.Close() And run a query to update the data.
That's it, written.
Testing
Once you have created your script, you'll need to test it. In the Edit screen, there is a 'Test Pane'
Here you enter in your parameters as needed, and click Start
Note the Run Settings, we are running on Azure here, the alternative is to use what is known as a Hybrid Worker. A little more on that later.
The Run will Queue first
Then Starting...
Then Running...
You are able to refresh the output by clicking Refresh Job Streams.
And hopefully, when all successful.
Publishing
When we are happy with our runbooks, then we publish them so that they can been seen, called, and scheduled. On the Edit Runbook screen, click Publish.
This now gives us the options to Start, schedule, or even call from a Webhook.
Execution
Starting (left) shows as the same entries as Testing, just enter the parameter names required. If you want to avoid entering params each time, then you could add default values into the script and set the parameters to not be mandatory, whatever works best for you.
Scheduling (below) allows you to create a schedule (doh!) and assign params automatically for the executions.
There you have it, your first runbook in Azure. Go check your data, you'll now have an army of customers in your DB all called Hidden Data and a blank phone number.
A few things of note....
Handle those errors, or the aliens may come and probe you.
Although there is very little in the example above...error handling is the boss in Runbooks, especially when you have more complex runbooks in play. Particulally if/when you have child runbooks in the mix. If a child runbook fails, you probably/may want the parent runbook to bug out waving a little white flag as well.
But the above example is simple, what could possibly go wrong? Well, lets see, just off the top of my head...
Source Database being in a paused state
Target Database already existing
Source/target server not being accessible
Insufficent access
etc....
Any of these could leave the run book not operating as you expect, or just sitting there running until timeout etc.
Why haven't you used invoke-sqlcmd?
I know, I installed the sqlserver module too....
The truth is that currently with Powershell 7.2, the module doesn't work (as of Mar 2024), as it errors saying that you require powershell 7.2.1. You can work around this by using Powershell 5.1 (and relevant module version) for your runbooks instead. I've displayed the above technique so that you don't have the reliance on the module either.
Watch out - Fair use is about!
There is a gotcha here, and its a really fat one. Runbooks in Azure that are running in Azure, and not on a Hybrid Worker are subject to a 3 hour time limit. This is down to an internal Microsoft 'Fair Use' policy to stop one person's runbooks from robbing all of the resource for running of other people's. More the majority of people, this should be fine, but something to be aware of.
So basically, if you have a runbook that runs for longer than this, you are going to need to configure a Hybrid Worker Group (as seen in the testing and Start runbook panels above) . Stay tuned for a blog post how to do exactly that.
But I can do that in a standard PS script?
You can, but what if:
You have lots of other elements of automation that you want to tie together, such as...
Deploy the target server first
Set up Azure Networking for the server first
Grant / revoke access to the resource
Anything else where theres a PS code for it?
etc...
Or even want to give the permission to perform the task to someone else, so they can run on demand?
What if you want Resumable automation, to handle reboots, or errors? You can do that by using Powershell Workflow Runbooks, which are more complex, but i'll follow up later on.
Whats stopping you creating the above as modular Runbooks, and then calling them from a master parent runbook? It all starts to make sense.
But think of the cost!
You get 500mins job runtime (the workers behind your runbooks executing) for free, then its $0.002 per min thereafter... so $1 for another 500mins? I'll take that.
Thanks for reading my inane ramblings again.
AutoRod of the third kind.
Complete script.
Param(
[Parameter (Mandatory= $true)] [string] $ResourcegroupNameSource,
[Parameter (Mandatory= $false)] [string] $ResourcegroupNameTarget,
[Parameter (Mandatory= $true)] [string] $ServernameSource,
[Parameter (Mandatory= $false)] [string] $ServernameTarget,
[Parameter (Mandatory= $true)] [string] $DBNameSource,
[Parameter (Mandatory= $true)] [string] $DBNameTarget,
[Parameter (Mandatory= $true)] [string] $SQLCred
)
# Setup credentials
$myCredential = Get-AutomationPSCredential -Name $SQLCred
$userName = $myCredential.UserName
$securePassword = $myCredential.Password
$password = $myCredential.GetNetworkCredential().Password
# Ensures you do not inherit an AzContext in your runbook
$null = Disable-AzContextAutosave -Scope Process
# Connect using a Managed Service Identity
try {$AzureConnection = (Connect-AzAccount -Identity).context}
catch {
Write-Output "There is no system-assigned user identity. Aborting."
exit
}
# set and store context
$AzureContext = Set-AzContext -SubscriptionName $AzureConnection.Subscription -DefaultProfile $AzureConnection
Write-Output "Account ID of current context: " $AzureContext.Account.Id
# Set target RG to be source if not supplied.
If (-not $ResourcegroupNameTarget){
$ResourcegroupNameTarget=$ResourcegroupNameSource
}
# Apply a standard naming convention if no name supplied
If (-not $DBNameTarget)
{
$DBNameTarget = "$($DBNameSource)_Dev"
}
# Copy the DB to the target server.
New-AzSqlDatabaseCopy -ResourceGroupName $ResourcegroupNameSource -ServerName $ServernameSource -DatabaseName $DBNameSource -CopyResourceGroupName $ResourcegroupNameTarget -CopyServerName $ServernameTarget -CopyDatabaseName $DBNameTarget
# Now run a query to obfuscate.
$DBConn = New-Object System.Data.SqlClient.SqlConnection
$DBConn.ConnectionString = "Server = $ServernameTarget.database.windows.net; Database = $DBNameTarget; User ID = $userName; Password = $password;"
$DBConn.Open();
# Create command to update the data
$DBCmd = New-Object System.Data.SqlClient.SqlCommand
$DBCmd.Connection = $DBConn
$DBCmd.CommandText = "UPDATE SalesLT.Customer SET FirstName='Hidden', LastName='Data', Phone='000-000-0000'"
$DBResult = $DBCmd.ExecuteNonQuery()
$DBConn.Close()