Log Shipping

Log shipping has been around since the early days of SQL Server. The feature is based on backup, copy, and restore and is one of the simplest methods of achieving HADR for SQL Server. Log shipping is primarily used for disaster recovery, but it could also be used to enhance local availability.

Log shipping, like AGs, provides database-level protection, which means you still need to account for SQL Server Agent jobs, linked servers, instance-level logins, etc. There is no abstraction provided natively by log shipping, so a switch to another server participating in log shipping must be able to tolerate a name change.

If that is not possible, there are methods such as a DNS alias, which can be configured at the network layer to try to mitigate the name change issues.

The log shipping mechanism is simple: first, take a full backup of the source database on the primary server, restore it in a loading state (STANDBY or NORECOVERY) on another instance known as a secondary server or warm standby. This new copy of the database is known as a secondary database. An automated process built into SQL Server will then automatically backup the primary database’s transaction log, copy the backup to the standby server, and finally, restore the backup onto the standby.

The SQL Server HADR features are not the only options to enhance IaaS availability. There are some features in Azure that should also be considered.

https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/business-continuity-high-availability-disaster-recovery-hadr-overview

The Temporal Table feature allows you to use the history table to recover data that may have been deleted or updated.

Temporal tables are a programmability feature of Azure SQL Database and Azure SQL Managed Instance that allows you to track and analyze the full history of changes in your data, without the need for custom coding. Temporal tables keep data closely related to time context so that stored facts can be interpreted as valid only within the specific period. This property of temporal tables allows for efficient time-based analysis and getting insights from data evolution.

https://docs.microsoft.com/en-us/azure/azure-sql/temporal-tables

In order to build an automation runbook, you need to first create an automation account. The image below shows this process in the Azure portal, after selecting Azure Automation from the Azure Marketplace.

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.

Configuration management

Azure Automation also supports using PowerShell Desired State Configuration (DSC) to manage changes in configuration across VMs in your environment. DSC operates as an extension to VMs and provides a consistent configuration state across all of the VMs that the configuration is applied to. Azure Automation integrates with DSC, allowing you to automatically update configuration across physical and virtual machines, and includes reporting capabilities.

https://docs.microsoft.com/en-us/mem/configmgr/core/understand/configuration-manager-on-azure

The listener requires the creation of an Azure load balancer and has some additional configuration in the WSFC related to the load balancer.

Load balancing refers to evenly distributing load (incoming network traffic) across a group of backend resources or servers.

Azure Load Balancer operates at layer four of the Open Systems Interconnection (OSI) model. It's the single point of contact for clients. Load Balancer distributes inbound flows that arrive at the load balancer's front end to backend pool instances. These flows are according to configured load balancing rules and health probes. The backend pool instances can be Azure Virtual Machines or instances in a virtual machine scale set.

A public load balancer can provide outbound connections for virtual machines (VMs) inside your virtual network. These connections are accomplished by translating their private IP addresses to public IP addresses. Public Load Balancers are used to load balance internet traffic to your VMs.

An internal (or private) load balancer is used where private IPs are needed at the frontend only. Internal load balancers are used to load balance traffic inside a virtual network. A load balancer frontend can be accessed from an on-premises network in a hybrid scenario.

https://docs.microsoft.com/en-us/azure/load-balancer/load-balancer-overview

Performance overhead of the Query Store

Collecting data in any system has an execution cost of CPU cycles, memory, and disk utilization that is known as observer overhead. The Query Store is designed to minimize the impact of its data collection. Data for the Query Store is written to memory for each new query, and each execution of an existing query. If this information was written to disk for each execution, the performance overhead would be significant, so SQL Server uses a setting called DATA_FLUSH_INTERVAL_SECONDS to control the frequency of flushing the Query Store data to disk. By default, the data is flushed every 15 minutes, but this value is a user configurable setting per database. This process of collecting the query and runtime information and writing it to disk is shown in the image below:

The other important setting to note in the Query Store options is Max Size (MB), which sets the amount of storage for the data collected. The default value is 100 MB and is commonly increased to around 1-2 GB depending on the volume of queries executed against a database. Some workloads that have a high number of unique ad-hoc queries, which is characteristic of applications written in Entity Framework, may see high data volume. If the size of the data stored on disk exceeds the Max Size (MB) the Query Store will go into read-only mode until more space is added, or cleanup happens. The default value for time-based cleanup is 30 days, but the size-based cleanup mode option will remove older queries as the Query Store approaches its max size. You will want to strike a balance between the amount of data you keep and the amount of space being consumed on disk.

The other setting you should note is Query_Capture_Mode which defaults to Auto. This value means queries with insignificant compilation time and duration are ignored, along with infrequent queries. This default was changed in SQL Server 2019, and in Azure SQL. The older default was All, which captures all queries executed. There are also options of none (collect no queries) and Custom, which was also introduced in SQL Server 2019, which allows you to use metrics such as execution count, compile CPU time, and execution time to limit which queries are captured. This setting is useful for a database where most of the queries are unique, as these unique queries can cause the Query Store to grow rapidly in size.

Plan forcing in the Query Store

Another benefit of the Query Store is the ability to force a given execution plan for a query. Plan forcing in the Query Store also drives the automatic tuning feature that was introduced in SQL Server 2017, which uses the last known good execution plan for a given query after a performance regression occurs. Since the query can store multiple execution plans for a given query, you can have the database engine force a known good plan for a given query. Plan forcing should be used for queries that have suddenly changed execution plans and have experienced significant regression in execution time. Plan forcing offers a quick mitigation for a performance problem, but you should always investigate what caused the performance regression and look to resolve the cause of the execution plan variability. Those fixes could be adding an index or looking to rewrite part of a query.

https://docs.microsoft.com/en-us/azure/mysql/concepts-query-store

Deploying via the portal

The process to create a singleton database through the Azure portal is straightforward. While in the portal, on the left-hand navigation menu, select “SQL Databases”. In the resulting slide out dialog, click “Create”:

In the blade in the image below, you’ll notice that the subscription should already be provided for you. You will need to supply the following information:

• Resource Group – If there is an existing resource group that you wish to use, you may select it from the drop-down list. You can click on the “Create new” option if you wish to create a new resource group for this Azure SQL Database.

• Database Name – You must provide a database name.

• Server – Each database must reside on a logical server. If you have one already in existence in the appropriate region, you may choose to use it. Otherwise, you can click on the “Create new” and follow the prompts to create a new logical server to house the database.

• Determine whether to use an elastic pool.

• Determine the appropriate compute resources needed. By default, it will be a Gen5, 2vCore, with 32 GB of storage until something else is selected. Click on “Configure database” to view alternate configuration options.

The image below shows the portal blade in which you can configure the database options. Here you will notice that the service tier is General Purpose and compute tier is Provisioned. Provisioned implies that the compute resources are pre-allocated and billed per hour based on the number of configured vCores. Serverless is billed per second based on the number of vCores utilized.

https://www.sqlshack.com/deploying-azure-databases-for-mysql-server-using-azure-portal/

Logins and Users

No matter the mode of authentication that is used, a login name used to access your SQL database is set up as a login within the instance. Those logins are set up at the instance level of SQL Server and stored in the master database. However, you can configure contained users, which are added at the database level. These users can be configured as SQL Server Authentication users as well as either Windows Authentication users or Azure Active Directory users (depending on which platform you are using). In order to create these users, the database must be configured for partial containment, which is configured by default in Azure SQL Database, and optionally configurable in SQL Server.

These users only have access to the database that the user is set up with. For the purposes of Azure SQL Database, it is considered a best practice to create users at the scope of the user database, and not in the master database as shown below.

1. SQL
2. CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;
3. GO

The create user statement is executed in the context of the user database. In the example above, the user is an Azure Active Directory user as indicated with the FROM EXTERNAL PROVIDER syntax.

If logins are created at the instance level in SQL Server, a user should then be created within the database, which maps the user to the server-based login as shown in the following example.

1. SQL
2. USE Master
3. GO
4.  
5. CREATE LOGIN demo WITH PASSWORD = 'Pa55.w.rd'
6. GO
7.  
8. USE WideWorldImporters
9. GO
10.  
11. CREATE USER demo FROM LOGIN demo
12. GO

The login is first created in the master database, and then in the WideWorldImporters database a user is created to map to that user. Logins are used to access the SQL Server or the Azure SQL Database, but to do any work within a database, the login must be mapped to a username. The username is used for all authentication.

Logins and usernames are the most important security principals you need to be aware of, but the next sections describe some of the other concepts and terms when dealing with authorization.

https://techcommunity.microsoft.com/t5/azure-database-support-blog/create-sql-login-and-sql-user-on-your-azure-sql-db/ba-p/368813

The single quotation mark in the input field is a telltale sign that this is a SQL injection attack. The quotation mark is used to escape outside the SQL code’s input field and the text that follows is used to directly manipulate the SQL command sent from the web application to the database.

https://www.w3schools.com/sql/sql_injection.asp

SQL Managed Instance is a PaaS deployment option of Azure SQL. It gives you an instance of SQL Server but removes much of the overhead of managing a virtual machine. Most of the features available in SQL Server are available in SQL Managed Instance. This option is ideal for customers who want to use instance-scoped features and want to move to Azure without rearchitecting their applications. (Instance-scoped features are features that are tied to an instance of SQL Server, as opposed to features that are tied to a database in an instance of SQL Server.)

SQL Managed Instance instance-scoped features include SQL Server Agent, Service Broker, common language runtime (CLR), Database Mail, linked servers, distributed transactions (preview), and Machine Learning Services. SQL Managed Instance allows you to access instance-scoped features.

https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/sql-managed-instance-paas-overview

You can't write to local files because your instance is hosted in Azure.

Auditing

Auditing can help you maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate potential security violations.

Traditional SQL Server auditing by using Transact-SQL (T-SQL) is available, with some differences, only in an Azure SQL managed instance. The primary differences are:

• With CREATE SERVER AUDIT, you can use new syntax TO URL and TO EXTERNAL MONITOR to specify an Azure Blob storage container and enable event hub and Azure Monitor logs targets, respectively.

• TO FILE, shutdown option, and queue_delay=0 are not supported in Azure SQL.

As an alternative to SQL Server audit, Azure SQL Database has Azure SQL auditing. It's powered by SQL Server audit and, like SQL Server, it supports Audit Action Groups and Actions. Azure SQL auditing tracks database and server events, and it writes events to an audit log in either Azure Blob storage, Azure Monitor logs (also referred to as Log Analytics), or to an event hub. If you point to an Azure Blob storage account, you can store and analyze your results in XEvents files. With Log Analytics, you unlock the ability to query your logs with Kusto Query Language (KQL) and take advantage of the Log Analytics auditing dashboards.

https://docs.microsoft.com/en-ca/learn/modules/azure-sql-secure-data/7-security-management