Clone Azure SQL Database and Log to Application Insights


This runbook is designed to copy a database and log to Application Insights for audit purposes. A runbook like this might be implemented to regularly keep staging or testing environment database synced with a live production database.


An Azure “RunAsAccount” needs to be created for the Automation account. Documentation can be found here

Finally, to log to Applications Insights, the Custom Events Module (“”) must be imported in the Automation account under the “Modules” pane. The zip file can be found here ApplicationInsightsCustomEvents

Runbook Content

The runbook contents are displayed below

    Copy a production database to a staging database in Azure.  
.PARAMETER SourceServer  
    String name of the source SQL Server you want to copy from 
.PARAMETER SourceResourceGroup  
    String name of Azure resource group the SourceServer is contained in 
.PARAMETER SourceDatabase  
    String name of the source Azure Database to be copied 
.PARAMETER CopyDatabase 
    String name of the new copy of the Azure SourceDatabase   
.PARAMETER CopyResourceGroup 
    String name of Azure resource group the CopyDatabase is contained in 
.PARAMETER CopyServer 
    String name of the destination SQL Server you want to copy to 
.PARAMETER InstrumentationKey 
    Instrumentation key of application insights account to which logs will be stored 
    Use-SqlCommandSample -SourceServer "SourceServername" -SourceResourceGroup "SourceResourceGroupName" -SourceDatabase "SourceDatabaseName" -CopyDatabase "CopyDatabaseName" -$CopyResourceGroup "CopyResourceGroupName" -$CopyServer "$CopyServerName" -InstrumentationKey "123-***234" 
    [string] $SourceServer,  
    [string] $SourceResourceGroup,  
    [string] $SourceDatabase,  
    [string] $CopyDatabase,  
    [string] $CopyResourceGroup = $SourceResourceGroup, 
    [string] $CopyServer = $SourceServer, 
    [string] $InstrumentationKey 
    #Function to log to Applications Insight 
    function LogAppInsight ([string]$message)  
        $dictionary = New-Object 'System.Collections.Generic.Dictionary[string,string]'  
        $dictionary.Add('Message',"$message"| Out-Null  
        Log-ApplicationInsightsEvent -InstrumentationKey $InstrumentationKey -EventName "Azure Automation" -EventDictionary $dictionary 
    #Azure Authentication 
    function Login()  
        $connectionName = "AzureRunAsConnection"  
        try { 
            $servicePrincipalConnection = Get-AutomationConnection -Name $connectionName 
            Write-Verbose "Logging in to Azure..." -Verbose 
            Add-AzureRmAccount ` 
                -ServicePrincipal ` 
                -TenantId $servicePrincipalConnection.TenantId ` 
                -ApplicationId $servicePrincipalConnection.ApplicationId ` 
                -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint | Out-Null  
        }catch {  
            if (!$servicePrincipalConnection)  
                $ErrorMessage = "Connection $connectionName not found." 
                throw $ErrorMessage  
            } else{  
                Write-Error -Message $_.Exception  
                throw $_.Exception  
        Write-Output "Removing old copy..." 
        Remove-AzureRmSqlDatabase -ResourceGroupName $CopyResourceGroup -ServerName $CopyServerName -DatabaseName $CopyDatabaseName -Force 
        Write-Output "Start Copy... Please wait ... " 
        New-AzureRmSqlDatabaseCopy -ResourceGroupName $SourceResourceGroup ` 
            -ServerName $SourceServer ` 
            -DatabaseName $SourceDatabase ` 
            -CopyResourceGroupName $CopyResourceGroup ` 
            -CopyServerName $CopyServer ` 
            -CopyDatabaseName $CopyDatabase 
            LogAppInsight "Success, database copied: $CopyDatabase" 
        Write-Output "Success, database copied: $CopyDatabase" 
    }catch { 
        $ErrorMessage = $_.Exception.Message 
            LogAppInsight "Failed: $ErrorMessage" 
        Write-Output "Failed: $Errormessage" 

Be the first to comment

Leave a Reply

Your email address will not be published.