Microsoft Azure Database Administrator Associate (DP-300)

A is correct.

Explanation: An elastic pool shares CPU, memory, and storage among the databases in the pool. It is a good fit for multitenant databases where each tenant has its own database. A database that needs more resources can use them, up to the set limits. Balance the workload across databases so that one database doesn't use most of the pool's resources.

B is incorrect: Each single database has its own dedicated resources, even on the same logical server. You scale and manage each database individually, so resources aren't shared.

C is incorrect: Hyperscale addresses large database size and independent scaling of compute and storage. It adds a per terabyte storage cost and doesn't address sharing resources across tenant databases.

D is incorrect: SQL Managed Instance is described as the best option for most migration scenarios. This is a new application, and the requirement is to share resources across databases in a pool.

B is correct.

Explanation: When a serverless database is paused, a connection attempt generates a connection error and then resumes the database. Configure the application to handle the error and include retry logic.

A is incorrect: The autopause delay has a minimum value of 60 minutes, so you can't set it to 30 minutes.

C is incorrect: Manual backups aren't supported in Azure SQL Database, and the platform denies the request. Automated backups run without intervention.

D is incorrect: Serverless isn't fully compatible with features that require constant background processes, and geo-replication is one of them.

C is correct.

Explanation: Azure SQL Managed Instance provides close to 100 percent compatibility with SQL Server, including instance-scoped features such as cross database joins. It brings the database and supporting technologies to a fully managed PaaS experience without requiring an application redesign.

A is incorrect: Azure SQL Database needs extra development to address migration blockers such as SQL Agent jobs, cross database joins, and SSIS. That conflicts with the goal of limiting application changes and developer time.

B is incorrect: SQL Server on IaaS avoids an application overhaul, but it does not provide the fully managed PaaS experience that the company requires.

D is incorrect: This option leaves supporting technologies on-premises instead of bringing them to Azure PaaS. It also does not remove the dependencies that block the migration to Azure SQL Database.

D is correct.

Explanation: Continuous mode scans for new backup files and restores them, so the backup chain can be added during migration. It is recommended for active workloads. Regardless of the mode, the LRS job is canceled automatically after 30 days, so plan to complete the migration within that time.

A is incorrect: Autocomplete mode requires the entire backup chain to be available in advance. It is recommended for passive workloads.

B is incorrect: Continuous mode fits this workload, but the LRS job is automatically canceled after 30 days. The job does have a time limit.

C is incorrect: Autocomplete mode does not support adding backup files during migration. The entire backup chain must be available before the migration starts.

A is correct.

Explanation: ExpressRoute connections tend to cost more, but they provide the best security and lowest latency. All communication flows over a direct secured channel that is independent of the public internet. Cost is a known drawback, and ExpressRoute can't be applied between cloud providers in a multicloud solution.

B is incorrect: An S2S VPN can provide secure two-way communication between the on-premises network and Azure. It isn't the option described as having the best security and lowest latency over a direct channel.

C is incorrect: Azure Arc-enabled SQL Server centralizes management, inventory, and security assessment for SQL Server instances. It isn't a network connectivity method.

D is incorrect: Locking down a storage account with ACLs and Microsoft Entra ID helps protect data that is stored in Azure. It doesn't provide a network channel between the on-premises network and Azure.

A is correct.

Explanation: For a SQL Server instance that is enabled by Azure Arc, discovery and readiness reports run automatically every weekend. You can also trigger an assessment manually at any time. The assessment evaluates feature compatibility and identifies potential migration blockers.

B is incorrect: The weekend run is the automatic schedule, but it is not the only option. A manual assessment can run at any time.

C is incorrect: The assessment identifies migration blockers and provides right-sized recommendations for the target configuration. Starting the migration without current results leaves those items unchecked.

D is incorrect: Assessing the source instance is the first stage. Selecting the target is the second stage, so the assessment does not depend on it.

C is correct.

Explanation: SQL Server on Azure Virtual Machines is an IaaS offering. It gives you access to the operating system, virtual storage, virtual networking, and any software you install in the VM. It also supports Active Directory authentication. You are responsible for OS patching and for SQL Server installation and configuration.

A is incorrect: Azure SQL Database is a PaaS offering. Direct access to the operating system isn't supported in a PaaS model, so the vendor installer can't run.

B is incorrect: Azure SQL Managed Instance is also a PaaS offering. Microsoft manages the operating system, and direct operating system access isn't supported.

D is incorrect: Azure Arc-enabled SQL Server extends Azure management to on-premises or multicloud SQL Servers. It is not a deployment option for moving an application to Azure.

B and D are correct.

Explanation: Choose the highest practical service tier and compute size for maximum transfer performance, and scale down after the migration. Also disable auto update and auto create statistics during the migration.

A is incorrect: A low service tier and compute size during the transfer reduces the resources available for the migration. Scale up before the migration, not after it.

C is incorrect: You should minimize the distance between the BACPAC file and the destination data center. Storing it in a different region increases the distance.

B is correct.

Explanation: Enabling the public endpoint takes two steps by two separate roles. The database administrator runs the PowerShell script. A network administrator with permissions in scope Microsoft.Network/* opens port 3342 on the NSG and provides a UDR route to avoid asymmetric routing.

A is incorrect: Port 1433 is not the port that the public endpoint uses. Port 1433 outbound is a transactional replication requirement when the publisher or distributor is on a managed instance and the subscriber is on-premises.

C is incorrect: Port 445 outbound provides access to the Azure file share that transactional replication uses. It does not open the public endpoint.

D is incorrect: The database administrator role is scoped to Microsoft.Sql/managedInstances/*, which does not include network permissions. Separation of duties requires a separate network administrator for the NSG and route changes.

D is correct.

Explanation: Randomized encryption is recommended for columns with a few well-known distinct values that could be guessed. Randomized encryption limits you to returning the column in results, which meets the requirement. Storing the column master key in a key store such as Azure Key Vault keeps it away from the database engine. The database engine never stores the column master key.

A is incorrect: Deterministic encryption always encrypts a value the same way. Values that are few and well known could be guessed from the encrypted data.

B is incorrect: TDE encrypts data at the page level and does not encrypt at the column level. Anyone with the appropriate permissions can still read the data, which does not protect it from DBAs.

C is incorrect: Randomized encryption is correct, but you should never generate the keys on the server that hosts your database. The key could potentially be extracted from memory on that server.

C is correct.

Explanation: Database-level firewall rules grant or block specific IP addresses for specific databases. You configure them with T-SQL by running sp_set_database_firewall_rule from within the user database.

A is incorrect: A server-level firewall rule lets users connect to all databases on the server. The vendor IP address would reach the other databases.

B is incorrect: sp_set_firewall_rule creates a server-level rule from the master database. Server-level rules apply to all databases on the server.

D is incorrect: Virtual network rules allow traffic from a specific Azure Virtual Network and apply at the server level, not the database level. They also do not scope access to one database.

D is correct.

Explanation: Enabling server auditing and database auditing together is appropriate when a specific database needs a different storage account, retention period, or Log Analytics workspace. The server policy continues to audit the other databases. The database policy applies the separate settings to the one database. Both audits exist at the same time.

A is incorrect: Server policies automatically cover new and existing databases. Moving every database to database-level auditing adds configuration work and is not needed for one exception.

B is incorrect: Server-level auditing alone is recommended for databases that have no special requirements. This database needs a different destination and retention period, so it needs its own database-level policy.

C is incorrect: The data_sensitivity_information field records sensitivity labels of the data a query returns. It does not change the audit destination or the retention period.

C is correct.

Explanation: Row-level security uses a security policy that invokes an inline table-valued function as a predicate. The predicate compares the user name with the TenantName value and allows TenantAdmin to see all rows. The control is applied at the database level, so application changes are minimal. Users can query the table directly and see only their own rows. Limit the ability of users to run unplanned queries, because a specially crafted WHERE clause can cause information leakage through a side-channel attack.

A is incorrect: Dynamic Data Masking obfuscates column values. It does not remove rows from query results.

B is incorrect: Auditing records events such as queries. It does not restrict which rows a user can read.

D is incorrect: Sensitivity labels describe the sensitivity of columns. They do not enforce access to rows.

A and C are correct.

Explanation: Dynamic Data Masking must be defined on the columns before it can obfuscate their values. When a user without the UNMASK permission runs SQL Server Import and Export, the exported data file contains masked data. Both actions are required. Remember that unmasked data is always visible to high privileged users, so do not run the export as one of them.

B is incorrect: The UNMASK permission lets a user retrieve unmasked data from masked columns. The export would contain the actual values.

D is incorrect: Row-level security restricts access to rows. Setting its state to OFF makes users see all rows. It does not mask column values.

D is correct.

Explanation: The principle of least privilege means giving an application only the permissions it needs. If an application accesses all data through stored procedures, it needs only EXECUTE on those procedures. Because the procedures and tables have the same owner and the procedures don't use dynamic SQL, the ownership chain lets the procedures access the tables.

A is incorrect: Direct DML permissions on the tables give the application more access than it needs, because the procedures already reach the tables through the ownership chain.

B is incorrect: db_datareader reads every table and view, and db_datawriter changes data in every table and view. Both are broader than needed.

C is incorrect: CONTROL grants all rights to an object, including the ability to delete it. This is the opposite of least privilege.

B and D are correct.

Explanation: To connect SQL Server to Azure Key Vault, you first create a normal SQL Server login in the instance. You then create a credential and map it to the login. The identity of the credential is the name of the key vault. The secret of the credential is the application ID from Azure Key Vault.

A is incorrect: Key Vault data has its own RBAC policies, separate from the Azure subscription. A subscription admin role does not give access to the key vault unless access is explicitly granted.

C is incorrect: The database engine never stores a column master key. It holds only metadata about where the key is stored, and the key must be in a key store such as Azure Key Vault.

B is correct.

Explanation: The SQL Server service account must have permission to access the certificate. If the correct access is not provided, restarting the SQL Server service fails. Grant the service account access to the certificate and restart the service.

A is incorrect: The certificate must be in the local computer certificate store or the current user certificate store. The local computer store meets the requirement.

C is incorrect: You must run SQL Server Configuration Manager with a local administrator account to install certificates for SQL Server. This is a requirement, not a cause of failure.

D is incorrect: Selecting Encrypt connection in SQL Server Management Studio is a client-side step that you use to test the connection after configuration. It does not affect whether the service restarts.

A is correct.

Explanation: Ownership chaining lets a user run a procedure that accesses a table with the same owner without direct table permissions. Dynamic SQL runs outside the context of the calling procedure, which breaks the chain. The user running the dynamic SQL then needs permissions on the tables and views that the code uses.

B is incorrect: The user's EXECUTE permission comes from the role's grant on the Sales schema. The error is about SELECT on Production.Product, not EXECUTE on the procedure.

C is incorrect: Adding the role to db_owner would grant broad administrative access and doesn't address the broken chain. It also conflicts with the principle of least privilege.

D is incorrect: Membership in the public role doesn't create a DENY. By default, the public role has no permissions granted to it.

B and D are correct.

Explanation: Azure SQL Database doesn't provide SQL Server Agent or Windows Task Scheduler, so you must schedule index maintenance by other methods. Azure Automation runbooks and Azure SQL elastic jobs are two of those methods. A SQL Server Agent job on a SQL Server in an Azure Virtual Machine that calls the database remotely is a third method.

A is incorrect: Azure SQL Database doesn't give you access to SQL Server Agent, so you can't create the job in the database itself.

C is incorrect: Azure SQL Database doesn't give you access to Windows Task Scheduler, and you don't have a server of your own that hosts the database.

D is correct.

Explanation: Resource Governor uses a classifier function to place connections into workload groups. Each workload group uses one resource pool, and you can set limits on the pool. Classifying the maintenance login into its own group and pool limits the resources for the rebuilds. Test the classifier function for efficiency, because it runs every time a connection is established.

A is incorrect: The internal pool is reserved for critical SQL Server functions and cannot be restricted.

B is incorrect: Changes to a resource pool affect only new sessions, so they do not restrict a process that is already running.

C is incorrect: The maximum CPU percent applies only when there is CPU contention. Without contention, the workload can use up to 100 percent of the available CPU cycles.

B is correct.

Explanation: Azure allocates a fixed ratio of CPU cores to memory. Constrained cores reduce the vCPU count and the software licensing cost while you keep the full amount of memory, storage, and I/O bandwidth. This fits database workloads that are not CPU-intensive.

A is incorrect: More vCPUs increases the number of cores, and SQL Server licenses are based on cores.

C is incorrect: Ultra disk addresses storage latency. It does not change the number of cores that are licensed.

D is incorrect: A smaller size with fewer cores also reduces memory because of the fixed core-to-memory ratio, which does not meet the memory requirement.

C is correct.

Explanation: When SQL Server is installed on Windows, the database engine creates its own Performance Monitor counters. SQL Server-specific metrics must be gathered from the virtual machine itself. In larger environments, you can forward Performance Monitor results to Azure Monitor to get a single view across many servers.

A is incorrect: The default Azure Monitor metrics for a virtual machine are operating system metrics. Their namespace is the virtual machine host, not SQL Server, so SQL Server-specific metrics aren't available in the portal.

B is incorrect: Monitoring Insights collects extra data points such as storage latency, available memory, and disk capacity. These are not SQL Server database engine counters.

D is incorrect: The SQL virtual machine resource provider shows how much space the database files and transaction log file use. It also manages features such as automated patching and storage configuration. It doesn't collect Performance Monitor counters.

B is correct.

Explanation: The common guidance is to rebuild an index when fragmentation is 30 percent or more. A rebuild also updates the statistics on the index. An online rebuild builds a new index in parallel with the existing one, so it requires more space.

A is incorrect: Reorganizing is the common guidance for fragmentation of at least 5 percent and less than 30 percent. A reorganization doesn't update statistics.

C is incorrect: Disabling AUTO_CREATE_STATISTICS doesn't reduce fragmentation. It is recommended to keep this option enabled so that the query optimizer can create statistics for query predicate columns.

D is incorrect: CREATE STATISTICS creates additional statistics to improve specific query plans. It doesn't remove fragmentation from the index.

B is correct.

Explanation: Dynamic Thresholds learn the historical behavior of a metric and raise an alert when the resource operates in an abnormal manner. They can detect seasonality in a workload and adjust the alerting accordingly.

A is incorrect: A static threshold requires you to provide a fixed value. It doesn't learn historical behavior or adjust for seasonality.

C is incorrect: An action group defines how to respond to an alert, such as a notification or an automation. It doesn't define the alert condition. Defining an action group is optional.

D is incorrect: Archiving to Azure Storage keeps metrics beyond the 93 days that active metrics are retained. It doesn't create an alert condition.

C is correct.

Explanation: A pool of 10 P30 disks has the sum of the IOPS and the sum of the capacity of all disks, which is a 10-TB volume with 50,000 IOPS. Do not configure redundancy in the pool, because the Azure framework keeps three copies of each disk in synchronous replication.

A is incorrect: Redundancy in the pool limits performance, and Azure already provides disk redundancy.

B is incorrect: Read caching reduces trips to the disk, but it does not increase the capacity or IOPS limit of a single P30 disk.

D is incorrect: Standard HDD is suitable for backups and infrequently accessed file storage, not for production SQL Server data files.

A and D are correct.

Explanation: Premium SSD disks support read caching, which reduces the number of trips to the disk for read-heavy workloads, so enable it on the data file volume. TempDB is recreated when the server restarts, so placing it on the local SSD does not risk data loss.

B is incorrect: Do not enable any caching on the log file volume.

C is incorrect: Create a separate volume for data files and transaction log files instead of sharing one volume.

C is correct.

Explanation: A resumable index build lets you pause the operation and restart it later at the point where it was paused. Resumable index is supported only with online operations, so you must set both RESUMABLE=ON and ONLINE=ON. Use ALTER INDEX with the PAUSE clause to stop the build.

A is incorrect: Resumable index is supported only with online operations. ONLINE=OFF does not meet this requirement.

B is incorrect: Ending the session is the only option in versions before SQL Server 2019. If the system rolls back the session, you must restart the build from the beginning.

D is incorrect: sys.index_resumable_operations is a system view that you query to check the status of a resumable online index. It does not pause the operation.

B is correct.

Explanation: PAGELATCH_UP waits on data pages 2:1:1 can indicate TempDB contention on Page Free Space (PFS) pages. This is typically caused by having only one TempDB data file. Add data files, use one file per CPU core up to eight, and keep the files the same size with the same autogrowth settings so SQL Server uses them evenly.

A is incorrect: Lowering MAXDOP and raising the cost threshold for parallelism addresses a high occurrence of CXPACKET waits, not TempDB PFS contention.

C is incorrect: READ COMMITTED SNAPSHOT is a way to resolve blocking that appears as LCK_M_X waits. It does not address TempDB PFS page contention.

D is incorrect: Index changes address waits such as PAGEIOLATCH_SH or SOS_SCHEDULER_YIELD that are caused by excessive scans. They do not address contention on page 2:1:1.

C is correct.

Explanation: The optimizer is cost-based and uses statistics to estimate row counts. An overestimate such as 10,000,000 rows instead of 100 produces a larger memory grant. A large memory grant can cause RESOURCE_SEMAPHORE waits. If not enough memory is available when the query runs, it spills to tempdb. SQL Server updates statistics automatically, but rapidly changing data can need more frequent updates.

A is incorrect: Row-based processing with cursors increases cost linearly with the number of rows. It does not correct the row estimate that caused the large memory grant.

B is incorrect: An isolation level affects how long locks are held. It does not change row estimates or memory grants.

D is incorrect: Live Query Statistics shows execution progress through the operators. It is a viewing option and does not change the memory grant.

A is correct.

Explanation: Implicit conversions can be costly and negatively affect query plans. They can cause index scans where seeks would have been possible, and they add CPU overhead. Choosing appropriate data types removes the need for conversion. Use explicit conversion with CAST or CONVERT only when a conversion is necessary.

B is incorrect: Data types that are larger than necessary waste space and require more pages to be read. This approach does not remove the conversions and can reduce query performance.

C is incorrect: Implicit conversion is not free. It can negatively affect query plans, cause index scans, and add CPU overhead.

D is incorrect: Some conversions are not possible. For example, a date cannot be converted to a bit. Using CAST in this manner would not resolve the problem.

D is correct.

Explanation: Query Store hints shape query plans without modifying application code. They are useful when the query optimizer doesn't generate an efficient plan and you can't change the query text. Identify the query_id from the Query Store catalog views, reports, or Query Performance Insight for Azure SQL Database. Then run sys.sp_query_store_set_hints with the hint string. Recompiling a query on each execution is a supported scenario.

A is incorrect: Adding OPTION (RECOMPILE) requires changing the query text, which you can't do in this scenario.

B is incorrect: The max degree of parallelism setting controls parallelism. It does not make a query recompile on each execution.

C is incorrect: Adding indexes changes data structures, but it does not force a recompile on each execution. The requirement is a query-level hint.

B and D are correct.

Explanation: For a running scenario, use Query Store reports, catalog views, or Query Performance Insight to find the queries that use the most CPU. Also query sys.dm_exec_requests for RUNNABLE queries with the SOS_SCHEDULER_YIELD wait type to determine whether you have enough CPU capacity.

A is incorrect: sys.server_resource_stats is a catalog view for Azure SQL Managed Instance. For Azure SQL Database, use sys.dm_db_resource_stats.

C is incorrect: sys.dm_os_waiting_tasks is used to analyze waiting scenarios, such as tasks that wait on a resource. This problem is classified as a running scenario.

B and D are correct.

Explanation: Bulk loads of 102,400 rows or more use the optimized load path and go directly into the columnstore. This avoids creating new delta stores. Reorganizing the index with COMPRESS_ALL_ROW_GROUPS forces the existing delta stores to be compressed into the columnstore. Reading rows from open delta stores is less efficient than reading from the columnstore.

A is incorrect: Loads of fewer than 102,400 rows are written to a delta store. Smaller batches increase the number of open delta stores, which reduces query efficiency.

C is incorrect: A clustered b-tree index does not provide the benefits of a columnstore index for large aggregation workloads. In the load comparison, the page-compressed b-tree table took nearly 20 seconds compared with roughly 8 seconds for the columnstore table.

D is correct.

Explanation: The Regressed Queries view identifies queries whose execution metrics regressed because of plan changes over a specified timeframe. Query Store persists multiple plans for each query. You can select a persisted plan in the plan summary pane and force it, or you can use the sp_query_store_force_plan stored procedure.

A is incorrect: The Query Wait Statistics view analyzes wait categories. It does not identify regressions caused by plan changes, and you can only unforce a plan that is already forced.

B is incorrect: The Queries With Forced Plans view lists queries that already have forced plans. It helps you reevaluate forced plans, but the queries here have not been forced.

C is incorrect: The procedure cache stores only the latest execution plan for a query, and plans can be evicted based on memory demands. An earlier plan might not be available.

C is correct.

Explanation: An alert can respond to an error condition by executing another SQL Server Agent job. This fits well-known conditions, such as storage errors 823, 824, and 825, that can be handled without manual intervention. The job can perform a database consistency check.

A is incorrect: A job success notification only informs an operator after the job finishes. It does not respond to storage errors.

B is incorrect: An email notification informs a person. A person must still start the consistency check, so the response is not automatic.

D is incorrect: The Maintenance Cleanup task removes old maintenance plan files, such as text reports and backup files. It does not run a consistency check.

B and D are correct.

Explanation: The runbook signs in with the system identity, so that identity needs appropriate RBAC permissions. Without them, the runbook might fail. If a runbook is interrupted, Azure Automation restarts it at the beginning, so you must write the logic to support a restart.

A is incorrect: An interrupted runbook restarts at the beginning. It does not resume at the last completed statement.

C is incorrect: The Az.SQL module depends on the Az.Accounts module. Import Az.Accounts first, and then import Az.SQL.

C is correct.

Explanation: When the target is a server or an elastic pool, you create a credential in the master database of that server or pool. The job agent uses it to enumerate the databases. Give the credential only the privileges that the job step needs.

A is incorrect: The documented requirement is a credential in the master database of the target server. A credential in the job database alone does not meet that requirement.

B is incorrect: A database credential alone is enough only when the target is a single database. A server target also needs the master database credential.

D is incorrect: Azure SQL Database does not have the msdb database or SQL Server Agent, so you cannot create the credential there.

A is correct.

Explanation: Infrastructure as code means all resources are defined in scripts that are stored in source control. Defining your infrastructure ensures consistent implementation and reduces configuration risks and human errors.

B is incorrect: Creating resources manually in the Azure portal does not ensure consistent implementation and increases the risk of human errors.

C is incorrect: Running imperative commands interactively does not define the resources in scripts stored in source control for consistent, repeatable deployments.

D is incorrect: Exporting configuration to a document for manual application relies on human intervention, which increases configuration risks and errors.

A is correct.

Explanation: The New-AzSqlDatabase cmdlet creates a database on a specific server. You pass the resource group name, server name, and database name as parameters.

B is incorrect: The Get-AzSqlServer cmdlet returns information about one or more logical servers. It does not create a database.

C is incorrect: The New-AzSqlInstance cmdlet creates a SQL Managed Instance. It does not create a database on a logical server.

D is incorrect: The Get-AzSqlInstance cmdlet retrieves information about a managed instance. It does not create a database.

A and B are correct.

Explanation: A Bicep file compiles into an ARM JSON template at deployment time, providing the capabilities of ARM templates with a more concise syntax. Bicep deployments are idempotent, meaning you can deploy repeatedly and your resources end up in a consistent state.

C is incorrect: Bicep provides continuous full support for all resource types and API versions. You can use them in your Bicep file as soon as a resource provider introduces them, without waiting for a tool update.

D is incorrect: Bicep is not intended to be a general-purpose programming language. It is a specialized tool for declaring Azure infrastructure resources and their properties.

A is correct.

Explanation: SQL Server Agent is available on SQL Server and Azure SQL Managed Instance. It is not available on Azure SQL Database. Azure SQL Managed Instance is the option that supports SQL Server Agent jobs, operators, and alerts.

B is incorrect: SQL Server Agent is not available on Azure SQL Database, so you cannot schedule jobs with it there.

C is incorrect: Maintenance plan activities run as SQL Server Agent jobs. Without SQL Server Agent, a maintenance plan cannot schedule them.

D is incorrect: Operators and alerts are SQL Server Agent objects. They are not available when SQL Server Agent is not available.

A is correct.

Explanation: In single-tenant Azure Logic Apps, Standard workflows can use the SQL Server connector as a managed connector or as a built-in connector. The built-in connector supports triggers, such as when a row is inserted, and actions such as Execute query and Get rows. The connector supports Azure SQL Database.

B is incorrect: In multitenant Azure Logic Apps, Consumption workflows can use this connector only as a managed connector. They do not have the built-in connector.

C is incorrect: A runbook runs PowerShell or Python code. It is not a Logic Apps workflow and does not use the built-in SQL Server connector.

D is incorrect: Elastic jobs run T-SQL scripts against a target group, either once or on a schedule. They do not provide a built-in connector trigger for row inserts.

C is correct.

Explanation: The FULL recovery model allows all backup types, including transaction log backups. Transaction log backups let you restore to a more granular point in time to meet a small RPO.

A is incorrect: The SIMPLE recovery model doesn't allow transaction log backups, so you can't schedule them.

B is incorrect: System databases are updated less frequently, and the main concern is backing up user databases. More frequent system database backups don't give point-in-time recovery for the user database.

D is incorrect: If RESTORE runs with recovery, no additional backups can be applied. Use WITH NORECOVERY or WITH STANDBY to apply later backups.

A and C are correct.

Explanation: Auto-failover groups support SQL Managed Instance and provide automatic failover. Clients connect through listener names, so they don't need to update the connection string after failover. GracePeriodWithDataLossHours controls how long Azure waits before it fails over. A higher value makes Azure wait longer, which gives the secondary more time to fully synchronize and can result in less data loss.

B is incorrect: Azure SQL Managed Instance doesn't support active geo-replication. Active geo-replication also doesn't provide automatic failover, and users must update the connection string after failover.

D is incorrect: A lower value shortens the wait before failover. The secondary has less time to synchronize with the primary, and a forced unplanned failover can lose more data.

B is correct.

Explanation: In Azure SQL Database, all connections are dropped when a new node starts, and in-flight transactions are lost. The application must include retry logic. Cloud applications should be designed to handle transient failures.

A is incorrect: With PaaS, you can configure only the options that Azure provides. A failover cluster instance is a SQL Server feature for IaaS deployments.

C is incorrect: ADR is on by default in Azure SQL Database and cannot be turned off. It also contributes to database availability.

D is incorrect: You cannot set a database to the OFFLINE or EMERGENCY state in Azure SQL Database. Azure manages and maintains the service.

A is correct.

Explanation: Each transaction log backup clears the log. A log restore requires an entire unbroken chain of log backups. Using one method keeps the chain in one coordinated place.

B is incorrect: Two uncoordinated log backup methods each clear the log. You might need to piece together backups from both locations to restore a database.

C is incorrect: Restores can't be automated. You must use the restore from URL functionality in SQL Server, and combining two log backup sets doesn't prevent a fragmented chain.

D is incorrect: Ephemeral storage is erased on shutdown or restart, so backups written there can be lost.

A is correct.

Explanation: An FCI provides instance-level protection. The entire instance restarts on another node, so logins and SQL Server Agent jobs are already in place. An FCI is configured when SQL Server is installed. A standalone instance cannot be converted to an FCI. An FCI also requires shared storage, Active Directory Domain Services (AD DS), and Domain Name Services (DNS).

B is incorrect: An AG provides database-level protection. You must manually create SQL Server Agent jobs, instance-level logins, and linked servers on a new primary replica.

C is incorrect: Log shipping provides database-level protection. You must account for objects outside the database yourself.

D is incorrect: Windows authentication can simplify access for an AG. The AG still provides only database-level protection, and SQL Server Agent jobs and linked servers must be created manually.

D is correct.

Explanation: Creating the load balancer is not enough. Each IP address that uses the load balancer requires its own unique probe port. Two listeners require two probe ports. You set each probe port on the cluster IP address resource for the listener by using Set-ClusterParameter. Setting the probe port stops and starts the listener IP address and temporarily brings the AG offline, so configure it before you deploy to production.

A is incorrect: Each IP address that uses the load balancer requires a unique probe port, so two listeners can't share one probe port.

B is incorrect: You can't reserve the listener IP address at the Azure level. Instead, verify that no other resource claims the address to avoid network conflicts.

C is incorrect: A distributed network name is for the WSFC name only. You can't use it with the name of an availability group or an FCI.

D is correct.

Explanation: Availability Zones account for data center-level failure because each zone is a unique physical location in a region. Zones do not protect against in-guest failures such as a SQL Server crash. An availability group (AG) or failover cluster instance (FCI) is also needed to meet recovery objectives. Test the latency between the zones to verify that it meets performance targets.

A is incorrect: Availability sets protect against Azure maintenance and single points of failure in a single data center. They do not protect against a data center failure or an in-guest failure.

B is incorrect: Azure Site Recovery replicates a virtual machine from one Azure region to another. It does not know that SQL Server is running in the virtual machine, so it may not meet the recovery point objective (RPO).

C is incorrect: Availability sets and Availability Zones cannot be combined. You must choose one.

B and D are correct.

Explanation: Without backups, you have neither high availability nor disaster recovery. Features such as availability groups and active geo-replication don't replace a backup and recovery strategy. You must also test backups by restoring them to confirm that they can be used.

A is incorrect: Availability groups and active geo-replication don't replace a proper backup and recovery strategy.

C is incorrect: Backup files that were generated but never restored are only files on a file system. Perform a restore to verify that they work.