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. For example, a Logic App can be created to populate a table in an Azure SQL Database when an entry is made in a SharePoint list.

For more complete control and granularity of your automation, 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. One of the unique benefits of Azure Automation is that it can manage resources within Azure or on-premises VMs.

For example, if you have a VM that is normally kept in a down state for cost savings (except when it needs to be used), you have the ability within Azure Automation, using a feature called hybrid runbooks, to execute a script to start the VM, then kick off a SQL Server backup from within the VM, and finally shut down the VM.

Overview of Azure automation components

Azure Automation supports both automation and configuration management activities.

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.

https://docs.microsoft.com/en-us/azure/automation/automation-intro

SQL Injection Attack

SQL injection has become a common issue with database-driven websites. It occurs when a malefactor executes a SQL query to the database via the input data from the client to server. SQL commands are inserted into data-plane input (for example, instead of the login or password) in order to run predefined SQL commands.

A successful SQL injection exploit can read sensitive data from the database, modify (insert, update or delete) database data, execute administration operations (such as shutdown) on the database, recover the content of a given file, and, in some cases, issue commands to the operating system.

For example, a web form on a website might request a user’s account name and then send it to the database in order to pull up the associated account information using dynamic SQL like this:

1. SELECT * FROM users WHERE account = ‘“ + userProvidedAccountNumber +”’;

While this works for users who are properly entering their account number, it leaves a hole for attackers. For example, if someone decided to provide an account number of “‘ or ‘1’ = ‘1’”, that would result in a query string of:

1. SELECT * FROM users WHERE account = ‘’ or ‘1’ = ‘1’;

Because ‘1’ = ‘1’ always evaluates to TRUE, the database will return the data for all users instead of just a single user.

The vulnerability to this type of cyber security attack depends on the fact that SQL makes no real distinction between the control and data planes. Therefore, SQL injections work mostly if a website uses dynamic SQL. Additionally, SQL injection is very common with PHP and ASP applications due to the prevalence of older functional interfaces. J2EE and ASP.NET applications are less likely to have easily exploited SQL injections because of the nature of the programmatic interfaces available.

In order to protect yourself from a SQL injection attacks, apply least0privilege model of permissions in your databases. Stick to stored procedures (make sure that these procedures don’t include any dynamic SQL) and prepared statements (parameterized queries).

The code that is executed against the database must be strong enough to prevent injection attacks. In addition, validate input data against a white list at the application level.

https://www.techopedia.com/definition/29781/sql-injection-attack

Resource Pools are where resources are assigned in Resource Governor.

A Resource Pool is a logical grouping of management servers and/or gateway servers used to distribute work among themselves and take over work from a failed member. In other words, they provide high availability and scalability for workflows. When designing a management group, considerations must be made for monitoring of network devices, Linux/UNIX systems, and other workloads that are designed to take advantage of a Resource Pool.

https://docs.microsoft.com/en-us/system-center/scom/plan-resource-pool-design?view=sc-om-2019

Load data into Azure SQL

After a database or instance is deployed, verified, and configured, the next logical step is to bring in data.

Many options are available for loading data into Azure SQL. Some overlap with what's available on-premises.

Bulk Copy Program (bcp) is a common tool for connecting to Azure SQL from on-premises, and for connecting to Azure SQL from an Azure virtual machine. You can then use it to move data into Azure SQL.

https://docs.microsoft.com/en-us/learn/modules/azure-sql-deploy-configure/7-load-data

Purchasing model

The Azure SQL purchasing model provides two options:

• Based on virtual cores (vCore-based)

• Based on database transaction units (DTU- based)

The DTU model isn't available in Azure SQL Managed Instance.

Microsoft recommends the vCore-based model because it allows you to independently select compute and storage resources. The DTU-based model is a bundled measure of compute, storage, and I/O resources.

In the vCore model, you pay for:

• Compute resources. (The service tier + the number of vCores and the amount of memory + the generation of hardware.)

• The type and amount of data and log storage.

• Backup storage location. (Read-access geo-redundant storage (RA-GRS), Zone-redundant storage (ZRS), or locally-redundant storage (LRS)).

The vCore model also allows you to use Azure Hybrid Benefit for SQL Server and/or reserved capacity (pay in advance) to save money. Neither of these options is available in the DTU model.

https://docs.microsoft.com/en-us/azure/azure-sql/database/purchasing-models

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. You can deploy Azure resources using Azure Resource Manager templates in two ways... The first method calls a PowerShell script as shown above. The second approach defines tasks that stage your artifacts (the templates themselves and any required secrets) and then deploys the templates. One task stages the artifacts and the other tasks deploys the templates.

https://azure.microsoft.com/en-us/overview/what-is-devops/

Platform as a Service (PaaS) provides a complete development and deployment environment in the cloud, which can be used for simple cloud-based applications as well as for advanced enterprise applications.

As shown in the image above, PaaS includes cloud provided operating system and management of your solution. For example, in Azure SQL Database and Azure SQL Managed Instance, the high availability, OS and SQL Server features, and backups are all provided by the Azure platform.

Active Geo-Replication

Geo-replication is a business continuity feature that asynchronously replicates a database to up to four secondary replicas. As transactions are committed to the primary (and its replicas within the same region), the transactions are sent to the secondaries to be replayed. Because this communication is done asynchronously, the calling application does not have to wait for the secondary replica to commit the transaction prior to SQL Server returning control to the caller.

The secondary databases are readable and can be used to offload read-only workloads, thus freeing up resources for transactional workloads on the primary or placing data closer to your end users. Furthermore, the secondary databases can be in the same region as the primary or in another Azure region.

With geo-replication you can initiate a failover either manually by the user or from the application. If a failover occurs, you potentially will need to update application connection strings to reflect the new endpoint of what is now the primary database.

Failover Groups

Failover groups are built on top of the technology used in geo-replication, but provide a single endpoint for connection. The major reason for using failover groups is that the technology provides endpoints, which can be utilized to route traffic to the appropriate replica. Your application can then connect after a failover without connection string changes.

https://docs.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview?tabs=single-database

SQL Server provides several hundred dynamic management objects. These objects contain system information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. Dynamic management views and functions return internal data about the state of the database or the instance. Dynamic Management Objects can be either views (DMVs) or functions (DMFs), but most people use the acronym DMV to refer to both types of object. There are two levels of DMVs: server scoped and database scoped. Server scoped objects require VIEW SERVER STATE permission on the server, and database scoped objects require the VIEW DATABASE

STATE permission within the database. The names of the DMVs are all prefixed with sys.dm_ followed by the functional area and then the specific function of the object. SQL Server supports three categories of DMVs:

• Database-related dynamic management objects

• Query execution related dynamic management objects

• Transaction related dynamic management objects

You can see a large number of queries to monitor server and database performance on the page in the docs called Monitoring Performance Azure SQL Database Using Dynamic Management Views.

Azure SQL Database has a slightly different set of the DMVs available than SQL Server; some objects are available only in Azure SQL Database, while other objects are only available in SQL Server. Some are scoped at the server level and are not applicable in the Azure model (the waits_stats DMV below is an example of a server-scoped DMV), while others are specific to Azure SQL Database, like sys.dm_db_resource_stats and provide Azure-specific information that is not available in (or relevant to) SQL Server.

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views?view=sql-server-ver15

The Azure ecosystem offers several different methods in which to provision a new SQL Server instance on an Azure virtual machine. Each method provides different capabilities, such as the ability to have a repeatable process (via scripting languages like PowerShell) or a method to accomplish the goal without needing an understanding of programming constructs, via the Azure portal.

Deploying via PowerShell/CLI

PowerShell is an object-orienting programming language that can be used to quickly and efficiently create a virtual machine (as well as other resources) within the Azure ecosystem. Using PowerShell gives you granular control over various aspects of the virtual machine such as size, name, IP address, and even storage.

The image above shows a PowerShell script that defines parameters and creates an Azure resource group and a virtual machine with a predefined virtual network.

Azure Command Line Interface (CLI) is another method of using scripting to create Azure resources, including virtual machines. Its simplistic nature allows you to accomplish the same goal as with PowerShell in fewer lines of actual code.

As the image shows, the CLI is less verbose in terms of the amount of code required to create new resources.

https://docs.microsoft.com/en-us/azure/azure-resource-manager/templates/deploy-cli

Azure Policy can be used to enforce tagging values and rules on resources.

You use Azure Policy to enforce tagging rules and conventions. By creating a policy, you avoid the scenario of resources being deployed to your subscription that don't have the expected tags for your organization. Instead of manually applying tags or searching for resources that aren't compliant, you create a policy that automatically applies the needed tags during deployment. Tags can also now be applied to existing resources with the new Modify effect and a remediation task. The following section shows example policies for tags.

https://docs.microsoft.com/en-us/azure/azure-resource-manager/management/tag-policies