From On-Premises to Azure: Your Guide to SQL Agent Job Migration
When shifting your workload to Azure SQL Databases, what happens to those SQL Agent jobs that were the backbone of automated tasks?
Anthony Norwood, Azure Cloud Practice Lead
In the age of cloud computing, where on-premises solutions are transitioning to the cloud, it’s essential for businesses to adapt and ensure their data management strategies are in sync with this digital transformation. One pivotal aspect of this adaptation is the migration of SQL databases to Azure. As organisations shift their workloads to Azure SQL Databases, one question often arises: What happens to those trusty SQL Agent jobs that were the backbone of automated tasks in the on-premises world?
More about our Managed SQL Services
Here are four key strategies that will empower you to continue running your cherished SQL Agent jobs in an Azure SQL Database:
1. Azure Elastic Jobs: Microsoft’s official solution
Microsoft’s official solution, Azure Elastic Jobs, tackles the challenge of running jobs in Azure SQL Databases. However, it’s important to note that this solution is still in preview and not recommended for production use. The preview continues to be updated, though, and as of 8 November 2023 it includes Microsoft Entra ID authorisation and now supports Private Link. Azure Elastic Jobs include components such as the Elastic Job Agent, Elastic Job Database (required to be an Azure SQL Database in the S0 tier or higher), Target Groups, and Jobs. But there’s a limited graphical user interface, and job management can be performed through PowerShell or T-SQL.
2. Azure Logic Apps: A low-code approach
If you prefer a low-code or no-code approach, Azure Logic Apps are an excellent choice. Logic Apps are a collection of ‘Actions’ and ‘Connectors’ that enable you to create jobs with minimal coding effort. Azure Logic Apps come with a SQL Connector that connects to SQL Server Instances, allowing you to run queries or stored procedures. You can choose from different pricing plans, including Consumption Plan or Standard Plan, depending on your needs. However, a Standard Plan is required if you want to access resources integrated with a virtual network (vNET). Logic Apps offer both stateless and stateful workflows, each with varying max runtimes.
Is Microsoft Azure the best place for high-scale workloads?
3. Azure Function Apps: Code flexibility and parameterisation
Azure Function Apps are ideal for those comfortable with coding in languages other than PowerShell. They support various coding languages, including C#, JavaScript, F#, Java, Python, and TypeScript. Function Apps allow you to parameterise tasks and are well-suited for CI/CD pipelines, offering extensive testing capabilities. You can run Function Apps in various ways, such as queues, events, HTTP webhooks, or timers. They support all authentication types and can interact with SQL instances using the SMO .NET Library. Pricing options include Consumption, App Service Plan, and Premium Function App Plan.
4. Azure Automation Runbooks: Embracing PowerShell expertise
If you’re a PowerShell enthusiast, Azure Automation Runbooks might be your top choice. They support Powershell versions 5.1, 7.1, and 7.3 (in preview) and Python. Azure Automation Runbooks can be automatically synchronised from your Azure DevOps or GitHub repositories. You can also install PowerShell Modules into the Azure Automation Account, enabling you to use tools like DBATools. Runbooks support various authentication methods and can be initiated through webhooks or schedules. It’s important to note that the smallest schedule unit is an hour, which can be cumbersome for frequent tasks. You can run runbooks against on-premises SQL Server instances using a Hybrid Worker, making it versatile for hybrid environments.
Read our Managed SQL Security Datasheet
Choosing the right path
The choice between these options depends on your specific needs and familiarity with the tools. Consider Azure Elastic Jobs for testing in a development environment, Logic Apps if you prefer a visual interface, Function Apps for more programming flexibility and cost-effectiveness with vNET integration, and Automation Runbooks if you are experienced in PowerShell or Python and require module imports. These options represent a fresh approach to SQL Agent jobs, aligning with the evolving landscape of cloud computing.
Question?
Our specialists have the answer