Scheduled Tasks in Azure with Azure Automation

If you’re like me, you’ve probably done some work with SQL Server Agent jobs to schedule backups or other tasks like truncating logs. Since I have a preference to log virtually every action in the applications I build, I end up setting up jobs to truncate or otherwise archive logs after a certain period of time. As the forces of the cloud become ever more pervasive, it leads us to the question of how to schedule these same types of tasks in Azure. Luckily like most things, Microsoft has an answer for this, and it is Azure Automation.  Complete coverage of Azure Automation is outside the scope of this article.  For my purposes here, it is suffice to say that Azure Automation is a is a SaaS offering that provides the ability to run Powershell scripts called Runbooks that execute tasks.

Runbooks can be authored, scheduled and managed all within the Azure portal either through a graphical editor, or by directly editing code.  I’ll cover the basics here, that will allow you to author your very own “SQL Server Agent job in the cloud”!  We’ll be using the non graphical editing approach.

To get started, create your Automation account.  There are several prerequisites and permissions that you can read about.  Once in the home page for your automation account, select ‘Runbooks’ from the main menu to begin authoring your first Runbook.

 

You’ll then be taken to the landing blade for Runbooks.  Click ‘Add a runbook’ and follow the steps to create a new Runbook.  You have the option here of importing a Runbook as well.  I’ll be creating a Runbook of type ‘Powershell’.  You can optionally select Graphical Editor or a Workflow type.

 

Once this is created, you’ll be taken back to the Runbooks main blade.  Select a Runbook and you’ll be taken to the details blade of that Runbook.  Its here from the details blade that we can edit, schedule and publish our Runbook.    Select ‘Edit’ to begin editing your Runbook code in your browser.

 

The Powershell scripting language that you’ll use allows for parameters to be passed in to your script. I simply modified one of the samples available here that runs a SQL command statement, to delete from my log table where the log records are older than a certain date. The parameters I used include:

1. The name of the server I want to connect to
2. The port I’ll connect on, 1433
3. The database name I’ll run my query against
4. The table I will run my query against
5. A credential ‘Asset’ that you have to configure separately. This is a user name and password for authenticating against the database server and database.  To configure a credential, navigate to the Runbook main blade and follow the steps under the ‘Credentials’ menu.  It will allow you to create a saved user name and password.

Once you’re done editing your runbook code you can either click ‘Revert to Published’ which will discard your changes and roll back to your published version, if you have already published a script.  Or, you can publish the script.  Once you publish the script you can run it manually.  You can see the results of the last job ran in the details blade of the Runbook, by clicking ‘Jobs’.

To attach a schedule to the job, go back to the Runbooks main blade and click ‘Schedules’.   Here I’ve created a schedule called ‘Weekly’.  I can set the schedule in the edit blade.

 

I then attach the schedule to the job in the details blade of either the Runbook.

When you want to run the Runbook manually, click ‘Start’ from the Runbook details blade. You’ll be prompted to provide values for your variables.

That’s it.  Your job is now able to be scheduled and run.  Again, the code I used is modified from the sample here.  You could theoretically pass a variable to determine how far back you want to delete the logs in your table.

<#
.SYNOPSIS
Outputs the number of records in the specified SQL Server database table.

.DESCRIPTION
This runbook demonstrates how to communicate with a SQL Server. Specifically, this runbook
outputs the number of records in the specified SQL Server database table.

In order for this runbook to work, the SQL Server must be accessible from the runbook worker
running this runbook. Make sure the SQL Server allows incoming connections from Azure services
by selecting 'Allow Windows Azure Services' on the SQL Server configuration page in Azure.

This runbook also requires an Automation Credential asset be created before the runbook is
run, which stores the username and password of an account with access to the SQL Server.
That credential should be referenced for the SqlCredential parameter of this runbook.

.PARAMETER SqlServer
String name of the SQL Server to connect to

.PARAMETER SqlServerPort
Integer port to connect to the SQL Server. Default is 1433

.PARAMETER Database
String name of the SQL Server database to connect to

.PARAMETER Table
String name of the database table to output the number of records of

.PARAMETER SqlCredentialAsseet
Credential asset name containing a username and password with access to the SQL Server

.EXAMPLE
Use-SqlCommandSample -SqlServer "somesqlserver.databases.windows.net" -SqlServerPort 1433 -Database "SomeDatabaseName" -Table "SomeTableName" -SqlCredentialAsset sqluserCredentialAsset

.NOTES
AUTHOR: System Center Automation Team  <---- Original Author  #>


param(
[parameter(Mandatory=$True)]
[string] $SqlServer = '',

[parameter(Mandatory=$False)]
[int] $SqlServerPort = 1433,

[parameter(Mandatory=$True)]
[string] $Database = '',

[parameter(Mandatory=$True)]
[string] $Table = 'log',

[parameter(Mandatory=$True)]
[string] $SqlCredentialAsset = ''
)

$SqlCredential = Get-AutomationPSCredential -Name $SqlCredentialAsset

if ($SqlCredential -eq $null)
{
throw "Could not retrieve '$SqlCredentialAsset' credential asset. Check that you created this first in the Automation service."
}
# Get the username and password from the SQL Credential
$SqlUsername = $SqlCredential.UserName
$SqlPass = $SqlCredential.GetNetworkCredential().Password

# Define the connection to the SQL Database
$Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$SqlServer,$SqlServerPort;Database=$Database;User ID=$SqlUsername;Password=$SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")

# Open the SQL connection
$Conn.Open()

# Define the SQL command to run. In this case we are getting the number of rows in the table
$Cmd=new-object system.Data.SqlClient.SqlCommand("delete from $Table where date < DATEADD(day, -180, GETDATE())", $Conn)
$Cmd.CommandTimeout=120

# Execute the SQL command
$Ds=New-Object system.Data.DataSet
$Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
[void]$Da.fill($Ds)

# Output the count
$Ds.Tables.Column1

# Close the SQL connection
$Conn.Close()

How to set the time zone of an Azure web app

To set the time zone of an Azure web app, navigate to the web app and click Application Settings from the Settings menu

Azure Settings

Under the settings blade, add a new app setting with the key value WEBSITE_TIME_ZONE and the value of your desired timezone.

 

 

The values for the time zones can be found in the registry under HKLM\Software\Microsoft\Windows Nt\CurrentVersion\Time Zones.

Multi-Line Code Editing in Visual Studio 2017

Here’s a quick tip if you’ve ever wanted to edit multiple lines of code at once in Visual Studio. Simply position your cursor at a point in your code, then press and hold SHIFT and ALT.  Next, press the up or down arrow to select the lines you want to edit.  When you begin typing you will behold a gift from the gods – editing multiple lines at once!

Multi Select Visual Studio
Multi Select Visual Studio

How to make a tappable frame in Xamarin Forms

To make an entire Xamarin Forms frame tappable, use a FrameGestureRecognizer, and set the Tapped property to your handler.  now the whole thing is tappable!   Inside your Frame Content, you can place a ListView that has multiple components.  For a visual cue, set an outline color on your Frame.  Place multiple Frames in a StackLayout or a Grid and you have a nice set of call to actions.

 <Frame Grid.Row="0" Grid.Column="0" OutlineColor="Gray">
          <Frame.GestureRecognizers>
            <TapGestureRecognizer Tapped="OnFrameTapped" />
          </Frame.GestureRecognizers>
          <Frame.Content>
            <StackLayout Orientation="Vertical" HorizontalOptions="Center">
              <ContentView HeightRequest="80" >
                <Image Source="image.png">
                </Image>
              </ContentView>
              <Label Text="My Frame" HorizontalOptions="Center"></Label>
            </StackLayout>
          </Frame.Content>
 </Frame>

Gorilla player for Xamarin – XAML Preview!

The new Gorilla Player is out, and its great!  What is it?  Its an instant XAML previewer for Xamarin Forms projects!  (There is also a XAML previewer that was announced during the Microsoft Build 2016 conference currently only available in Xamarin Studio).  If you’re tired of the long cycle of designing your XAML then having to compile and deploy to your device or emulator just so you can view your XAML, then this is for you.  Gorilla Player allows you to view your XAML without compiling.  To get set up, all you have to do is complete their BETA registration form then download their package that contains the player source files and app.  The app is a desktop app that runs as a service in the background, acting as a server that listens for connections from your emulator.  After you have the desktop app up and running, navigate to the directory of the player solution project, and open it in Visual Studio.  Build the project and deploy it to your emulator.  Start the player app on your emulator.  It will try to automatically connect to your desktop app.  If it can’t find it, you can optionally connect manually.   In Visual Studio you will see a new toolbar option in Tools > Gorilla Player > Connect to Gorilla Player.  Now when you open XAML files in Visual Studio, you will see a Gorilla Player icon on the source page.

GorillaVS

You should now be able to see your XAML in the emulator!  That’s it for simple XAML.  If you have Xamarin Forms ListViews with bindings, then you can set design time data a number of ways, the simplest I’ve found is to include a JSON file in the root of your project called “SampleData.json”.  In the file, set your design time data by including a JSON object with multiple name value pairs, each with their value set to an array.  The name in each name value pair is the title of your XAML page.  Set the value of the name value pair to an array of objects with properties that conform to your model.  This will be the design time data you see in the XAML previewer.  Don’t forget to set the binding in your ListView to  ItemsSource=”{Binding}” instead of ItemsSource=”{Binding Items}” or whatever your model is bound to in the code behind, when you want to preview using the sample data.

{
  "BlogPage.xaml": [
    {
      "Title": "Blog Post 1",
      "Link": "http://www.example.com",
      "PublishDate": "4/01/2016",
      "XamImageSource": "NoImageAvailable.png",
      "Caption": "This blog post is nice"
    },
    {
      "Title": "Blog Post 2",
      "Link": "http://www.example.com",
      "PublishDate": "4/02/2016",
      "XamImageSource": "NoImageAvailable.png",
      "Caption": "This blog post is greater"
    },
  ],
}

There is an API for Gorilla Player that allows you to import custom .dlls for views that inherit from them, and more.  Head on over to GorillaPlayer.com to check them out.  Happy coding.