Logs, logs everywhere

· 2min · logging

Logs, logs everywhere

Our team recently migrated a large web application from On-Prem to Azure. This involved a complete redesign of our build and deployment pipeline, including the introduction of Azure Elastic Jobs (currently in preview) to deploy schema changes to our tenant databases.

To run the jobs, we use an Elastic Job Agent which involved hooking it up to a control database which manages the jobs, jobsteps and executions. As soon as we connected an agent to a control database we noticed that the audit logs for that database rapidly grew in size. We’re talking ~24 entries per second. Which meant our blob storage was growing by 300MB/hr.

I found just one mention of someone else online encountering the same thing and the solution was never revealed :( So after a (lengthy) discussion with Azure Support, we established that:

PowerShell Set-AzSqlDatabaseAudit
    -ResourceGroupName <Resource Group>
    -ServerName <SQL Server>
    -DatabaseName <Database>
    -PredicateExpression "application_name = 'Microsoft Azure SQL Database elastic jobs - control' AND statement NOT LIKE '%sys.sp_cloud_connection_set_sds%' AND statement NOT LIKE '%with nonterminal_executions as (%' AND statement NOT LIKE '%root_job_execution_id%'"
    -BlobStorageTargetState Enabled
    -StorageAccountResourceId "/subscriptions/<Subscription Id>/resourceGroups/<Resource Group>/providers/Microsoft.Storage/storageAccounts/<Audit Storage Account>

Disclaimer: My predicate expression is awful. Given some more time I would’ve looked into how to escape special characters a bit better and made this more specific.

Anyway, I hope this helps someone out.