Free DP-300 Exam Dumps Preview

Box 1: None
Set host caching to none for log file disks.

Box 2: ReadOnly
Set host caching to read-only for data file disks.

Do not enable read/write caching on disks that contain SQL Server data or log files.

References:
https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-storage

Step 1: Attach the SSISDB database

Step 2: Turn on the TRUSTWORTHY property and the CLR property
If you are restoring the SSISDB database to an SQL Server instance where the SSISDB catalog was never created, enable common language runtime (clr)

Step 3: Open the master key for the SSISDB database Restore the master key by this method if you have the original password that was used to create SSISDB.

open master key decryption by password = 'LS1Setup!' --'Password used when creating SSISDB'
Alter Master Key Add encryption by Service Master Key

Step 4: Encrypt a copy of the mater key by using the service master key

References:
https://docs.microsoft.com/en-us/sql/integration-services/backup-restore-and-move-the-ssis-catalog

Automating Azure SQL DB index and statistics maintenance using Azure Automation:
1. Create Azure automation account (Step 1)
2. Import SQLServer module (Step 2)
3. Add Credentials to access SQL DB
This will use secure way to hold login name and password that will be used to access Azure SQL DB 4. Add a runbook to run the maintenance (Step 3)
Steps:
1. Click on "runbooks" at the left panel and then click "add a runbook" 2. Choose "create a new runbook" and then give it a name and choose "Powershell" as the type of the runbook and then click on "create"

5. Schedule task (Step 4) Steps:
1. Click on Schedules 2. Click on "Add a schedule" and follow the instructions to choose existing schedule or create a new schedule.

References:
https://techcommunity.microsoft.com/t5/azure-database-support-blog/automating-azure-sql-db-index-and-statistics-maintenance-using/ba-p/368974

Box 1: Add a secondary replica to AG1.
Migrate an availability group to SQL Server on Azure VM

To ensure the migrated server is synchronized with the source server, stop the SQL Server service on every replica in the availability group, starting with secondary replicas (in SQL Server Configuration Manager > Services) while ensuring the disks hosting SQL data are online.

Incorrect:
* Upgrade to SQL Server 2019
No need to upgrade to SQL Server 2019

* Create a SQL Server 2019 Always ON available group on VM1.

You do that afterwards.
After the migration, enable Always On availability groups for the database.

Box 2: Azure Migrate
You can migrate your SQL Server Always On availability group to SQL Server on Azure VMs using the Azure Migrate: Server Migration tool. Using the migration tool, you will be able to migrate each replica in the availability group to an Azure VM hosting SQL Server, as well as the cluster metadata, availability group metadata and other necessary high availability components.

References:
https://learn.microsoft.com/en-us/azure/azure-sql/migration-guides/virtual-machines/sql-server-availability-group-to-sql-on-azure-vm

References:
https://docs.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview

References:
https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/storage-configuration?tabs=windows2016

Scenario: Business Requirements
Litware identifies business requirements include: meet an SLA of 99.99% availability for all Azure deployments.

Box 1: Cloud witness
If you have a Failover Cluster deployment, where all nodes can reach the internet (by extension of Azure), it is recommended that you configure a Cloud Witness as your quorum witness resource.

Box 2: Azure Basic Load Balancer
Microsoft guarantees that a Load Balanced Endpoint using Azure Standard Load Balancer, serving two or more Healthy Virtual Machine Instances, will be available 99.99% of the time.

Note: There are two main options for setting up your listener: external (public) or internal. The external (public) listener uses an internet facing load balancer and is associated with a public Virtual IP (VIP) that is accessible over the internet. An internal listener uses an internal load balancer and only supports clients within the same Virtual Network.

References:
https://technet.microsoft.com/windows-server-docs/failover-clustering/deploy-cloud-witness
https://azure.microsoft.com/en-us/support/legal/sla/load-balancer/v1_0/

There is no REPLACE option in Azure SQL Database, or even there is no restore option with T-SQL.
https://learn.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups?view=azuresql-db&tabs=azure-portal

As per exhibit, the execution plan 1221065 has lower execution time compared to plan 1220917.

References:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-store-usage-scenarios

Change authentication mode (Transact-SQL)
The following example changes Server Authentication from mixed mode (Windows and SQL) to Windows only.

USE [master]
GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode', REG_DWORD, 1;
GO

References:
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode

o ensure that you can back up db1 to storage1 and meet the requirements, you should generate a shared access signature (SAS) on storage 1. A SAS is a URI that grants restricted access rights to storage objects. By using a SAS URI, you can delegate access to resources in your storage account, such as a container or a blob, without exposing your account key.

Option B is incorrect because creating an access policy does not provide block blob storage or maximize security.
Option C is incorrect because rotating the storage keys does not address the requirements for using block blob storage and maximizing security.
Option D is incorrect because enabling infrastructure encryption does not address the requirement for
using block blob storage.

Box 1: -AssignIdentity
The New-AzSqlServer cmdlet creates an Azure SQL Database server.

Parameter -AssignIdentity
Generate and assign an Azure Active Directory Identity for this server for use with key management
services like Azure KeyVault.

Box 2: KeyId
Parameter -KeyId
The Azure Key Vault URI that is used for encryption.

Example: Create a new Azure SQL Database server with TDE CMK

New-AzSqlServer -ResourceGroupName "ResourceGroup01" -Location "East US" -ServerName
"server01" -ServerVersion "12.0" -SqlAdministratorCredentials (Get-Credential) -AssignIdentity -
IdentityType "UserAssigned" -PrimaryUserAssignedIdentityId "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx- xxxxxxxxxxxx/resourceGroups/resourcegroup01/providers/Microsoft.ManagedIdentity/
userAssignedIdentities/identity01" -UserAssignedIdentityId "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx- xxxxxxxxxxxx/resourceGroups/resourcegroup01/providers/Microsoft.ManagedIdentity/
userAssignedIdentities/identity01" -KeyId
https://contoso.vault.azure.net/keys/contosokey/01234567890123456789012345678901

References:
https://learn.microsoft.com/en-us/powershell/module/az.sql/New-AzSqlServe

Set up disaster recovery for SQL Server
You can protect the SQL Server back end of an application. You do so by using a combination of SQL Server business continuity and disaster recovery (BCDR) technologies and Azure Site Recovery.

SQL Server disaster recovery capabilities include:

Failover clustering
Always On availability groups Database mirroring
Log shipping
Active geo-replication
Auto-failover groups

Note: Azure Recovery Services contributes to your BCDR strategy:

Site Recovery service: Site Recovery helps ensure business continuity by keeping business apps and workloads running during outages. Site Recovery replicates workloads running on physical and virtual machines (VMs) from a primary site to a secondary location. When an outage occurs at your primary site, you fail over to a secondary location, and access apps from there. After the primary location is running again, you can fail back to it.

Backup service: The Azure Backup service keeps your data safe and recoverable.

Site Recovery can manage replication for:

Azure VMs replicating between Azure regions
Replication from Azure Public Multi-Access Edge Compute (MEC) to the region Replication between two Azure Public MECs On-premises VMs, Azure Stack VMs, and physical servers

References:
https://learn.microsoft.com/en-us/azure/site-recovery/site-recovery-sql
https://learn.microsoft.com/en-us/azure/site-recovery/site-recovery-overview

Box 1: CREATE EXTERNAL TABLE
An external table points to data located in Hadoop, Azure Storage blob, or Azure Data Lake Storage.
External tables are used to read data from files or write data to files in Azure Storage. With Synapse SQL, you can use external tables to read external data using dedicated SQL pool or serverless SQL pool.

Syntax:
CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name |
table_name }
( <column_definition> [ ,...n ] )
WITH ( LOCATION = 'folder_or_filepath', DATA_SOURCE = external_data_source_name, FILE_FORMAT = external_file_format_name

Box 2. OPENROWSET
When using serverless SQL pool, CETAS is used to create an external table and export query results to Azure Storage Blob or Azure Data Lake Storage Gen2.

Example:
AS SELECT decennialTime, stateName, SUM(population) AS population
FROM OPENROWSET(BULK '
https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=*/*.parquet',FORMAT='PARQUET')AS[r]
GROUP BY decennialTime, stateName
GO

References:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables

Box 1: TopOne() OVER(PARTITION BY Game ORDER BY Score Desc)
TopOne returns the top-rank record, where rank defines the ranking position of the event in the window according to the specified ordering. Ordering/ranking is based on event columns and can be specified in ORDER BY clause.

Analytic Function Syntax:
TopOne() OVER ([<PARTITION BY clause>] ORDER BY (<column name> [ASC |DESC])+ <LIMIT
DURATION clause> [<WHEN clause>])

Box 2: Tumbling(minute 5)
Tumbling window functions are used to segment a data stream into distinct time segments and perform a function against them, such as the example below. The key differentiators of a Tumbling window are that they repeat, do not overlap, and an event cannot belong to more than one tumbling window.

References:
https://docs.microsoft.com/en-us/stream-analytics-query/topone-azure-stream-analytics
https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/stream-analytics/stream-analytics-window-functions.md

References:
https://azure.microsoft.com/en-gb/blog/announcing-azure-monitor-aiops-alerts-with-dynamic-thresholds/

Scenario: Contoso requirements for the sales transaction dataset include: Implement a surrogate key to account for changes to the retail store addresses.

A surrogate key on a table is a column with a unique identifier for each row. The key is not generated from the table data. Data modelers like to create surrogate keys on their tables when they design data warehouse models. You can use the IDENTITY property to achieve this goal simply and effectively without affecting load performance.

References:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-identity

Transactional replication is a feature of Azure SQL Managed Instance and SQL Server that enables you to replicate data from a table in Azure SQL Managed Instance or a SQL Server instance to tables placed on remote databases. This feature allows you to synchronize multiple tables in different databases.D

You can use transactional replication to push changes made in an Azure SQL Managed Instance to:

1. A SQL Server database - on-premises or on Azure VM 2. A database in Azure SQL Database
3. An instance database in Azure SQL Managed Instance

Incorrect:

Not A: Operates within the same location.

Database mirroring is a solution for increasing the availability of a SQL Server database.

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations.

Not B: Always On availability group on SQL Server on Azure VMs - only within a single region Always On availability groups on Azure Virtual Machines are similar to Always On availability groups on-premises, and rely on the underlying Windows Server Failover Cluster.

VM redundancy
To increase redundancy and high availability, SQL Server VMs should either be in the same availability set, or different availability zones.

Placing a set of VMs in the same availability set protects from outages within a data center caused by equipment failure (VMs within an Availability Set do not share resources) or from updates (VMs within an availability set are not updated at the same time).

Availability Zones protect against the failure of an entire data center, with each Zone representing a set of data centers within a region.

References:
https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-transactional-overview
https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-overview

Enabling Write Accelerator on a specific disk
The next few sections will describe how Write Accelerator can be enabled on Azure Premium Storage VHDs.

Prerequisites
The following prerequisites apply to the usage of Write Accelerator at this point in time:

The disks you want to apply Azure Write Accelerator against need to be Azure managed disks on Premium Storage.
You must be using an M-series VM

References:
https://learn.microsoft.com/en-us/azure/virtual-machines/how-to-enable-write-accelerator¨

References:
https://www.mssqltips.com/sqlservertip/6137/azure-data-factory-control-flow-activities-overview/

Box 1: Public
Public endpoint for a managed instance enables data access to your managed instance from outside the virtual network. You're able to access your managed instance from multi-tenant Azure services like Power BI, Azure App Service, or an on-premises network. By using the public endpoint on a managed instance, you don't need to use a VPN, which can help avoid VPN throughput issues.

Box 2: A network security group Allow public endpoint traffic on the network security group 1. If you have the configuration page of the managed instance still open, navigate to the Overview tab.
Otherwise, go back to your SQL managed instance resource. Select the Virtual network/subnet link, which will take you to the Virtual network configuration page.

2. Select the Subnets tab on the left configuration pane of your Virtual network, and make note of the SECURITY GROUP for your managed instance.

3. Go back to your resource group that contains your managed instance. You should see the Network security group name noted above. Select the name to go into the network security group configuration page.

4. Select the Inbound security rules tab, and Add a rule that has higher priority than the deny_all_inbound rule with the following settings:

* Details omitted *

References:
https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/public-endpoint-configure

You can override the default language by specifying the language magic command %<language> at the beginning of a cell. The supported magic commands are: %python, %r, %scala, and %sql.

References:
https://docs.microsoft.com/en-us/azure/databricks/notebooks/notebooks-use

Step 1: Create a Purview account.
Prerequisites
An Azure account with an active subscription. Create an account for free.
An active Microsoft Purview account.
You'll need to be a Data Source Administrator and Data Reader to register a source and manage it in the Microsoft Purview governance portal.

Step 2: Modify the Access control (IAM) settings for DB1.
A collection is a tool that the Microsoft Purview Data Map uses to group assets, sources, and other artifacts into a hierarchy for discoverability and to manage access control. All accesses to the Microsoft Purview governance portal's resources are managed from collections in the Microsoft Purview Data Map

Step 3: Create a system-assigned managed identity.
Authentication for a scan
To scan your data source, you'll need to configure an authentication method in the Azure SQL Database.

The following options are supported:

* System-assigned managed identity (Recommended) - This is an identity associated directly with your
Microsoft Purview account that allows you to authenticate directly with other Azure resources without needing to manage a go-between user or credential set.

* Etc.

References:
https://docs.microsoft.com/en-us/azure/purview/register-scan-azure-sql-database

References:
https://docs.microsoft.com/en-US/troubleshoot/sql/performance/recommendations-reduce-allocation-contention

A: Change elastic pool storage size
* vCore-based purchasing model
Storage can be provisioned up to the max size limit:

For storage in the Standard or General Purpose service tiers, increase or decrease size in 10-GB increments
For storage in the Premium or Business Critical service tiers, increase or decrease size in 250-GB increments Storage for an elastic pool can be provisioned by increasing or decreasing its max size.

* DTU-based purchasing model
The eDTU price for an elastic pool includes a certain amount of storage at no additional cost. Extra storage beyond the included amount can be provisioned for an additional cost up to the max size limit in increments of 250 GB up to 1 TB, and then in increments of 256 GB beyond 1 TB.

C: You can add or remove databases to/from the pool.

E: Under some circumstances, you may need to shrink a database to reclaim unused space.
Reclaim unused allocated space
Shrink commands impact database performance while running, and if possible should be run during periods of low usage.

References:
https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-scale
https://learn.microsoft.com/en-us/azure/azure-sql/database/file-space-manage
https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-manage

Box 1 = Azure Monitor Agent

Box 2 = An Azure SQL database
https://docs.microsoft.com/en-us/azure/azure-sql/database/sql-database-paas-overview?view=azuresql

References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-plan-stats-transact-sql?view=sql-server-ver15

By default, the cluster of nodes for the premium availability model is created in the same datacenter. With the introduction of Azure Availability Zones, SQL Database can place different replicas of the Business Critical database to different availability zones in the same region. To eliminate a single point of failure, the control ring is also duplicated across multiple zones as three gateway rings (GW). The routing to a specific gateway ring is controlled by Azure Traffic Manager (ATM). Because the zone redundant configuration in the Premium or Business Critical service tiers does not create additional database redundancy, you can enable it at no extra cost. By selecting a zone redundant configuration, you can make your Premium or Business Critical databases resilient to a much larger set of failures, including catastrophic datacenter outages, without any changes to the application logic. You can also convert any existing Premium or Business Critical databases or pools to the zone redundant configuration.

Incorrect Answers:
Note:
There are several versions of this question with different incorrect answer options. Other incorrect answers you may see on the exam include:

Azure SQL Database Hyperscale
Azure SQL Database Basic
Azure SQL Database managed instance General Purpose

References:
https://docs.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla

Box 1: "0.0.0.0"
startIpAddress
The start IP address of the firewall rule. Must be IPv4 format. Use value '0.0.0.0' for all Azure-internal IP addresses.

SQL server firewall rules should not permit start and end IP addresses to be 0.0.0.0. Adding a rule with range 0.0.0.0 to 0.0.0.0 is the same as enabling the "Allow access to Azure services" setting, which allows all connections from Azure, including from other subscriptions. Disabling this setting helps prevent malicious Azure users from connecting to your database and accessing sensitive data.

Box 2: "0.0.0.0"
endIpAddress
The end IP address of the firewall rule. Must be IPv4 format. Must be greater than or equal to startIpAddress. Use value '0.0.0.0' for all Azure-internal IP addresses.

References:
https://docs.fugue.co/FG_R00221.html

To ensure that each customer can only connect to and access their respective database in Azure SQL, you should perform the following two actions:

B. Create users in each database: Creating separate users for each customer in their respective
databases allows you to control access at the database level. Each user will have its own set of permissions and can only connect to and access their assigned database.

C. Configure the database firewall: The database firewall provides an additional layer of security by
controlling the incoming connections to the Azure SQL Database server. By configuring the database firewall, you can define IP address ranges or individual IP addresses that are allowed to connect to each database. This ensures that only authorized connections from specific sources can access the databases.

Therefore, the correct actions to perform are
B. Create users in each database and
C. Configure the database firewall.

In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:
1. The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.
2. The SNAPSHOT isolation level.

If READ_COMMITTED_SNAPSHOT is set to ON (the default on SQL Azure Database), the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

Incorrect Answers:
A: When the PARAMETERIZATION database option is set to SIMPLE, the SQL Server query optimizer may choose to parameterize the queries. This means that any literal values that are contained in a query are substituted with parameters. This process is referred to as simple parameterization. When SIMPLE parameterization is in effect, you cannot control which queries are parameterized and which queries are not.

B: You can specify that all queries in a database be parameterized by setting the PARAMETERIZATION database option to FORCED. This process is referred to as forced parameterization.

C: Delayed transaction durability is accomplished using asynchronous log writes to disk. Transaction log records are kept in a buffer and written to disk when the buffer fills or a buffer flushing event takes place.
Delayed transaction durability reduces both latency and contention within the system.

Some of the cases in which you could benefit from using delayed transaction durability are:
1. You can tolerate some data loss.
2. You are experiencing a bottleneck on transaction log writes.
3. Your workloads have a high contention rate.

References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql

SalesSQLDb1 experiences performance issues that are likely due to out-of-date statistics and frequent blocking queries.
Seeing a Count of All Active SQL Server Wait Types.
Sometimes we're trying to diagnose a problem and we want to know if we're seeing a large number of wait types occurring. We can do this using sys.dm_exec_requests because the current wait type being experienced is presented. Therefore, if we filter out any background or sleeping tasks, we can get a picture of what the waits are for active requests and we can also see if we have a problem. Here's the query: SELECT COALESCE(wait_type, 'None') AS wait_type, COUNT(*) AS Total

FROM sys.dm_exec_requests - WHERE NOT status IN ('Background', 'Sleeping')

GROUP BY wait_type -
ORDER BY Total DESC;
Here is an example of the query output:

We see that we have two LCK_M_S wait types. This is the wait type we get when we have requests waiting on obtaining a shared lock. We can then query along with sys.dm_tran_locks to determine what types of locks these active requests were trying to obtain: SELECT L.request_session_id, L.resource_type, L.resource_subtype, L.request_mode, L.request_type

FROM sys.dm_tran_locks AS L JOIN sys.dm_exec_requests AS DER ON L.request_session_id = DER.session_id WHERE DER.wait_type = 'LCK_M_S'; Incorrect:
Not A: Azure SQL database does not have sys.dm_pdw_nodes_tran_locks.
Not E: Azure SQL database does not have sys.dm_pdw_nodes_os_wait_stats.

References:
https://www.mssqltips.com/sqlservertip/5521/understanding-and-using-sql-server-sysdmexecrequests/

References:
https://docs.microsoft.com/en-US/troubleshoot/sql/performance/recommendations-reduce-allocation-contention

Azure Backup provides an Enterprise class backup capability for SQL Server on Azure VMs. All backups are stored and managed in a Recovery Services vault. There are several advantages that this solution provides, especially for Enterprises.

References:
https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/backup-restore#azbackup

https://www.mssqltips.com/sqlservertip/3685/live-query-statistics-in-sql-server-2016/

References:
https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-managed-instance-online

Migration overview: SQL Server to Azure SQL Managed Instance

We recommend the following migration tools:
* Log Replay Service
This cloud service is enabled for SQL Managed Instance based on SQL Server log-shipping technology.
It's a migration option for customers who can provide full, differential, and log database backups to Azure Storage. Log Replay Service is used to restore backup files from Azure Blob Storage to SQL Managed Instance.

* Etc.

The following table lists alternative migration tools:
* Transactional replication

Replicate data from source SQL Server database tables to SQL Managed Instance by providing a publisher-subscriber type migration option while maintaining transactional consistency.

* Etc.

Compare migration options
Compare migration options to choose the path that's appropriate to your business needs.

The following table compares the recommended migration options:
* Log Replay Service
- Migrate individual line-of-business application databases.
- More control is needed for database migrations.

Considerations:
- The migration entails making full database backups on SQL Server and copying backup files to Azure
Blob Storage. Log Replay Service is used to restore backup files from Azure Blob Storage to SQL Managed Instance.
- Databases being restored during the migration process will be in a restoring mode and can't be used for read or write workloads until the process is complete.

* Etc.

The following table compares the alternative migration options:
* Transactional replication
- Migrate by continuously publishing changes from source database tables to target SQL Managed
Instance database tables.
- Do full or partial database migrations of selected tables (subset of a database).

Considerations:
- Setup is relatively complex compared to other migration options.
- Provides a continuous replication option to migrate data (without taking the databases offline).

* Etc.

References:
https://learn.microsoft.com/en-us/azure/azure-sql/migration-guides/managed-instance/sql-server-to-managed-instance-overview

The COMPRESS (Transact-SQL) function compresses the input expression, using the GZIP algorithm.

The function returns a byte array of type varbinary(max).

Syntax: COMPRESS ( expression )

Arguments
expression
A

binary(n)
char(n)
nchar(n)
nvarchar(max)
nvarchar(n)
varbinary(max)
varbinary(n)
varchar(max)
or

varchar(n)
expression

Incorrect:
The max size for a column of type NVARCHAR(MAX) is 2 GByte of storage. Since NVARCHAR uses 2 bytes per character, that's approx. 1 billion characters.

A table cannot be enabled for compression when the maximum row size plus the compression overhead exceeds the maximum row size of 8060 bytes. For example, a table that has the columns c1 CHAR(8000) and c2 CHAR(53) cannot be compressed because of the additional compression overhead.

Compression has no effect on Nvarchar columns, except for Page compression.

References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/compress-transact-sql

References:
https://docs.microsoft.com/en-us/powershell/module/az.sql/set-azsqldatabasebackupshorttermretentionpolicy?view=azps-7.2.0
https://docs.microsoft.com/en-us/powershell/module/az.sql/set-azsqldatabasebackuplongtermretentionpolicy?view=azps-7.2.0

To configure a SQL Server Agent job on an instance of SQL Server on Azure Virtual Machines (VMs) to rebuild indexes of the databases hosted on the VM, you need to assign the Log on as a batch job user right to the account that will be used by the agent.

Therefore, the correct answer is D. Log on as a batch job.

References:
https://docs.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming-export-configure?tabs=azure-portal#configure-the-streaming-export-of-diagnostic-telemetry

DBCC SHRINKDATABASE shrinks the size of the data and log files in the specified database.

Incorrect Answers:
D: To shrink one data or log file at a time for a specific database, execute the DBCC SHRINKFILE command.

References:
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql