Automate tasks in SQL Server
Configure automatic deployment for Azure SQL Database
Describe deployment models in Azure
Azure Resource Manager templates have the benefit of being able to deploy a full set of resources in one single declarative template.
You can build dependencies into the templates, as well as using parameters to change deployment values at deployment time.
As mentioned above, there are two programming models that are used for cloud deployments: imperative and declarative.
Azure Resource Manager templates
Azure Resource Manager templates allow you to create and deploy an entire infrastructure in a declarative framework
PowerShell
PowerShell provides a core module known as Az, which has child resource providers for nearly all Azure services
Azure CLI
The Azure Command Line Interface, or CLI, is similar to PowerShell in that it can be used either imperatively or declaratively.
Azure portal
The Azure portal is a graphical interface to Azure Azure Resource Manager.
Using Azure DevOps to deploy templates
In Azure DevOps, deployments are carried out using Azure Pipelines.
Azure Pipelines are a fully featured continuous integration and continuous delivery service (CI/CD), which allows you to automate the build, testing, and deployment of your code.
Continuous integration
Continuous integration is a development methodology that focused on making small changes to code and frequent code check-ins to the version control system.
Continuous delivery builds on the continuous integration and automates the delivery of code changes to the underlying infrastructure.
Create an Azure Resource Manager template
An Azure Resource Manager template is a JSON (JavaScript Object Notation) document that describes the resources that will be deployed within an Azure Resource Group.
The schema file, which is referenced on the first line the above example describes the version of the template language. ‘
This file is supplied by Microsoft to define the Azure API.
One of the most important things to note about the structure of the resources section of the above template is the dependsOn option.
This option allows you to build a dependency structure into your template.
Parameters can accept values from outside the template by user interaction, a file, or CI/CD pipelines. Variables can be defined within the template and are used for simplification, since you can define a complicated expression once, and then use it throughout the template.
In addition to the internal version in the template, your source control system should version your templates.
You can also configure your templates to be automatically deployed from GitHub.
Deploy an Azure Resource Manager template
In most cases, the database administrator will not be writing their own Azure Resource Manager template from scratch.
You may either build them from the Azure portal or using a template from the Quickstart templates that are provided by Microsoft on GitHub
Schedule tasks using SQL Server Agent
In SQL Server and Azure SQL managed instance, the SQL Server Agent service allows you to schedule jobs to perform these maintenance tasks.
Create a SQL Server maintenance plan
Typical activities that you can schedule for regular SQL Server maintenance include:
· Database and transaction log backups
· Database Consistency Checks
· Index maintenance
· Statistics updates
Maintenance plans are created as Integration Services packages, which allow you to schedule your maintenance activities
What is a proxy account?
A proxy account is an account with stored credentials that can be used by the SQL Server Agent to execute steps of a job as a specific user.
The login information for this user as stored as a credential in the SQL Server instance.
Multi-server automation
In a multi-server environment, the SQL Server Agent provides the option of designating one server as a master server that can execute jobs on other servers, designated as target servers.
The master server stores a master copy of the jobs and distributes the jobs to the target servers.
Target servers connect to the master server periodically to update their schedule of jobs.
Describe task status notifications
One important part of automation is providing notifications in the event of job failure or if certain system errors are encountered.
SQL Server Agent provides this functionality through a group of objects.
Alerting is most commonly done via email using the Database Mail functionality of SQL Server.
The other agent objects that are used in this workflow are:
· Operators — alias for people or group who receives notifications.
· Notifications — notify an operator of the completion, success, or failure of a job.
· Alerts — are assigned to an operator, for either a notification or a defined error condition.
Alerts
SQL Server Agent alerts allow you to be proactive with monitoring of your SQL Server.
The agent reads the SQL Server error log and when it finds an error number for which an alert has been defined, it notifies an operator.
In addition to monitoring the SQL Server error log, you can set up alerts to monitor SQL Server Performance conditions, as well as Windows Management Instrumentation (WMI) events.
Manage Azure PaaS resources using automated methods
Implement Azure policy
Group Policies, or GPOs, have been used by Windows server administrators for a long time, to manage security, provide consistency across the Windows Server environment in your organization.
Some examples of group policies are enforcement of password complexity, mapping shared network drives and configuring networked printers
Policies are assigned to a specific scope, which could be a management group (a group of subscriptions that are managed together), a subscription, a resource group, or even an individual resource.
Use Azure subscriptions and tag Azure resources
Tags are simply metadata that are used to better describe your Azure resources.
These tags are stored as key:value pairs and appear in the Azure portal associated with your Azure resources.
Azure supports applying up to 15 tags to each Azure resource.
Describe Azure automation
Azure offers several ways to automate processes.
Azure Functions and Logic Apps are both Azure services that enable serverless workloads.
Both services create workflows that are a collection of steps to execute complex tasks
Azure Automation allows for process automation, configuration management, full integration with Azure platform options (such as role-based access control and AAD) and can manage Azure and on-premises resources.
Overview of Azure automation components
The components of Automation you will need to use to execute automated tasks are as follows:
· Runbooks — Runbooks are the unit of execution in Azure automation. Runbooks can be defined as one of three types: a graphical runbook based on PowerShell, a PowerShell script, or Python script. PowerShell runbooks are most commonly used to manage Azure SQL resources.
· Modules — Azure Automation defines an execution context for the PowerShell or Python code you are executing in your runbook. In order to execute your code, you need to import the supporting modules. For example, if you needed to run the Get-AzSqlDatabase PowerShell cmdlet, you would need to import the Az.SQL PowerShell module into your automation account.
· Credentials — Credentials store sensitive information that runbooks or configurations can use at runtime.
· Schedules — Schedules are linked to runbooks and trigger a runbook at a specific time.
Build an automation runbook
In order to build an automation runbook, you need to first create an automation account.
Note that this process can optionally create an Azure Run As account, which creates a service principal in your Azure Active Directory.
This service principal provides authentication for Azure Automation to access Azure Resources.
Describe elastic jobs in Azure SQL database
The introduction of elastic jobs allows you to run a set of T-SQL scripts against a collection of servers or databases as a one-time job, or by using a defined schedule.
Elastic jobs work similarly to SQL Server Agent jobs, except that they are limited to executing T-SQL.
The jobs work across all tiers of Azure SQL Database (excluding managed instance, which has a SQL Server Agent).
To configure Elastic Jobs, you need a Job Agent and database dedicated to managing your jobs.
The agent is free, but the database is required to be a paid database.
The recommended service tier is S1 or higher, and the optimum service tier will be dependent on the number of jobs you are executing and the frequency of those jobs.
