DP-300 — frequently asked questions

This is a complex scenario because the logical server is a management container. When a logical server is deleted, the databases within it are also deleted. However, Azure SQL Database automatically creates backups. The restoration process does not involve restoring the server itself. Instead, you must use the backups of the deleted database to create a new database on a different, existing logical server. This is typically done via the Azure portal by navigating to the subscription, finding the 'Deleted databases' blade, selecting the specific database, and choosing a target server for the restore operation. This process leverages the automated long-term retention (LTR) or standard geo-redundant backups.

The first and most critical step is to create a 'Job database'. This is a dedicated Azure SQL Database (either single or pooled) that acts as the metadata repository for the Elastic Job agent. The agent itself is an Azure resource that you create and configure to point to this Job database. This database stores all the information about the jobs, their schedules, target groups, and execution history. Without first provisioning and designating this database, the Elastic Job agent cannot be created or function.

The most secure approach is to avoid exposing the Managed Instance to the public internet entirely. The recommended solution is to establish a secure, private connection from the developers' on-premises network to the Azure Virtual Network (VNet) where the Managed Instance is deployed. This is typically achieved using a Site-to-Site VPN or Azure ExpressRoute. This ensures that all TDS (Tabular Data Stream) traffic travels over a private, encrypted tunnel and never traverses the public internet, significantly reducing the attack surface compared to using public endpoints with IP firewall rules.

To run a multiserver job step under a specific Windows account, you must configure a Credential and a Proxy on the Target Server (the server that executes the job). First, create a Credential that maps to the desired Windows account. Second, create a SQL Server Agent Proxy and associate it with that credential, granting the proxy access to the appropriate subsystem (e.g., Operating System (CmdExec)). Finally, when defining the job step on the Master Server, you specify that the step should run under the context of the proxy account you created on the target server. The job definition is then downloaded to the target server, which uses the proxy to execute the step.

The primary service for this purpose is Azure SQL Auditing. It should be enabled at the database or server level. To capture all changes, you configure the audit action group to include actions like SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP, BATCH_COMPLETED_GROUP, and more granular actions like SELECT, INSERT, UPDATE, DELETE, SCHEMA_OBJECT_CHANGE_GROUP, etc. The audit logs can then be sent to an Azure Storage Account, a Log Analytics workspace, or an Event Hub for further analysis and retention, providing a complete trail of who did what and when.

The correct solution is Azure Monitor for VMs (which is part of Azure Monitor). This service provides a comprehensive monitoring experience by installing the Log Analytics agent (or the newer Azure Monitor Agent) on the Azure VMs. It collects performance counters (CPU, memory, disk I/O), process-level data, and dependency maps. You can then use Azure Workbooks or create custom dashboards in the Azure portal to view metrics from all your SQL Server VMs in a single, consolidated view, making it easier to spot trends and issues across your entire fleet.

The central hub for this is Azure SQL Insights, which is a feature within Azure Monitor. SQL Insights provides a dedicated monitoring experience for Azure SQL. It collects and visualizes key performance metrics, such as CPU percentage, DTU or vCore utilization, storage usage, and session counts. It uses a pre-configured workbook to present this data, allowing a DBA to quickly assess the health of all their SQL resources across different subscriptions and resource groups from a single pane of glass.

The Hyperscale service tier is the one that supports automatic compute scaling. In Hyperscale, you can configure your database to automatically scale compute resources up or down within a defined range based on the observed workload. This is different from the manual scaling of vCores in the General Purpose or Business Critical tiers. The serverless compute tier also automatically scales compute, but it scales down to a paused state during periods of inactivity, which is a different use case focused on cost-saving for intermittent workloads.

You need a minimum of one database-scoped credential. This credential is created within the 'Job database' (the metadata database for the Elastic Job agent). It stores the username and password that the job agent will use to connect to the target databases to execute the job steps. This single credential can be used for all target databases, provided they all share the same login credentials. If the target databases have different credentials, you would need to create multiple database-scoped credentials and specify the correct one for each target group or job step.

Elastic Jobs, which run as an Azure PaaS service, cannot perform interactive authentication required by most Conditional Access (CA) policies (like MFA prompts). To allow the job to connect, you must configure a CA policy that specifically exempts the job's identity. This is done by creating a policy that applies to the target database's server principal but includes an exclusion for the managed identity of the Elastic Job agent (or the specific IP address range of the Azure datacenter where the job agent runs, though using managed identity is more secure). This grants the non-interactive job agent access while still enforcing CA policies for all human users.