T-SQL Tuesday #130

Publish on: Classify at: ;
T-SQL Tuesday Logo

My invitation to you is I want to know what you have automated to make your life easier? This can be anything creating a SQL Server Agent job to automated running a daily report or using dbatools to manage your servers. I’m curious what challenges you’ve found at your job and what you’ve done to make things better. If you haven’t had a chance to automate some part of your job, what would you like to automate and what are your hurdles? If you’re interested in some help or advice, let us know. I love #SqlFamily, and I’d love to see what we can do to help out.

T-SQL Tuesday Host, Elizabeth Noble (https://sqlzelda.wordpress.com/2020/09/01/t-sql-tuesday-130-automate-your-stress-away/)

I started a new job nearly four months ago as a program manager at Microsoft. There have been some stresses, mainly the ones you’d expect when starting at a new company, in a new role, and relocating across the country. Thankfully, much of those stresses were temporary and life has settled in. Now in the day-to-day, I am frequently learning and creating explanations for concepts, things I very much enjoy doing. I’ve managed to reduce the stresses associated with quickly building test environments and managing resources for learning/creating by automating Azure cleanup and beginning to work with containers.

Local Containers

Frankly, my container deployment scripts are a mess and the automation isn’t nearly where I want it. However, by deploying SQL Server containers with host directories as the data volume I can direct a sparkly new SQL Server at a preset environment with undisturbed copies of AdventureWorks or other databases waiting for it.

The foundation of this setup is that:

  • A container is created with the data volume mounted to a host directory. The desired sample databases imported and any other configuration applied.
  • The container is stopped and the host directory is copied to a notable location. This copy becomes the demo environment source.
  • For future environment needs, the host directory copy can be cloned and mounted to the data volume of a new container.

How is this going to improve? Practice, reading, and practice.

My Reading List

Azure Resource Clean-up

Microsoft puts a lot of effort into making it easy for you to log on to portal.azure.com and spin up a VM, SQL Database, Function, or other instance. The Visual Studio Enterprise subscription with $150 credit per month can go quickly if you’re not careful to stop or remove big ticket items as soon as you’re done with them. Even if you have a virtually unlimited Azure account connected to a credit card, expense account, or a trust fund – you probably don’t want to accidentally leave something running longer than it is needed.

With an Azure Automation account and a Powershell workflow runbook I’m able to use resource tags to set resources for autodeletion by date or immediately that evening.

The input parameters are the tag name that identifies resources and the subscription that you would like to remove resources from. The tag name has 2 options for utilization in this script – by setting it to “true” for deletion that evening or by setting it to a specific date to delete on.

On resources that want to be picked up by the runbook for deletion, I would add the tag name I set on the runbook schedule in either the immediate or date-specific format.

Tag “remove-this” is set to “true”, deletes resource that evening
Tag “remove-this” is set to September 10th, 2020, deletes resource at 9pm on Sept 10th, 2020

Getting Started with Azure Automation

If you’re not familiar with Azure Automation and this sounds like it would be useful to you, here are 2 resources to get you started:

The Full Runbook Code

The PowerShell contained in the runbook is available in a Gist here as well as below.

workflow ResourceCleaner
{
    Param
    (
        [Parameter(Mandatory=$true)]
        [String]
        $TargetTagName,
        [Parameter(Mandatory=$true)]
        [String]
        $SubscriptionName
    )
    $connectionName = "AzureRunAsConnection"

    try
    {
         # Get the connection
        $servicePrincipalConnection=Get-AutomationConnection -Name $connectionName

        "Logging in to Azure..."
        Connect-AzAccount `
        -ServicePrincipal `
        -TenantId $servicePrincipalConnection.TenantId `
        -ApplicationId $servicePrincipalConnection.ApplicationId `
        -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint   
    }
    catch 
    {
        if (!$servicePrincipalConnection)
        {
            $ErrorMessage = "Connection $connectionName not found."
            throw $ErrorMessage
        } else{
            Write-Error -Message $_.Exception
            throw $_.Exception
        }
    }


    if($SubscriptionName)
    {
        try
        {
            $Subscriptions = Get-AzSubscription -SubscriptionName $SubscriptionName
        }
        catch
        {
            $ErrorMessage = "Subscription $SubscriptionName not found."
            throw $ErrorMessage
        }
    }

    Set-AzContext -Subscription $SubscriptionName

    $GetDateMMddyyyy = Get-Date -Format "MM/dd/yyyy"
    "Removing resources for $GetDateMMddyyyy"
    $ResourcesToRemove = Get-AzResource -TagName $TargetTagName -TagValue $GetDateMMddyyyy
    $ResourcesToRemove | ForEach-Object {
        $ResourceName = $_.Name
        "Removing $ResourceName"
        Remove-AzResource -ResourceId $_.ResourceId -Force
    }
    
    $RemoveTrue = "true"
    "Removing marked resources"
    $ResourcesToRemove = Get-AzResource -TagName $TargetTagName -TagValue $RemoveTrue
    $ResourcesToRemove | ForEach-Object {
        $ResourceName = $_.Name
        "Removing $ResourceName"
        Remove-AzResource -ResourceId $_.ResourceId -Force
    }
}

Automate and Iterate

Philosophically, it’s important to remember that automation is not a zero cost investment. The time up front and the maintenance tasks may be reduced compared to the cost of the originalk task – but even if they aren’t less time – they should have lessened the stress. As Elizabeth shared in her example, the cost of setting up the CI/CD pipeline is worth it chiefly because the control reduces the stress that used to follow the day of a deployment. When you’re automating don’t look to save time, look to save stress.

  • rushed tasks
  • tasks that conflict with other responsibilities
  • high error-rate tasks
  • high-risk tasks