Topic 6: Misc. Questions
You have 10 Azure virtual machines that have SQL Server installed.
You need to implement a backup strategy to ensure that you can restore specific
databases to other SQL
Server instances. The solution must provide centralized management of the backups.
What should you include in the backup strategy?
A. Automated Backup in the SQL virtual machine settings
B. Azure Backup
C. Azure Site Recovery
D. SQL Server Agent jobs
Explanation:
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.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/backuprestore#
azbackup
You have an Azure SQL database that contains a table named factSales. FactSales contains the columns shown in the following table.
FactSales has 6 billion rows and is loaded nightly by using a batch process.
Which type of compression provides the greatest space reduction for the database?
A. page compression
B. row compression
C. columnstore compression
D. columnstore archival compression
Explanation:
Columnstore tables and indexes are always stored with columnstore compression. You can
further reduce the size of columnstore data by configuring an additional compression called
archival compression.
Note: Columnstore — The columnstore index is also logically organized as a table with
rows and columns, but the data is physically stored in a column-wise data format.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/datacompression
Note: This question is part of a series of questions that present the same scenario.
Each question in the series contains a unique solution that might meet the stated
goals. Some question sets might have more than one correct solution, while others
might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a
result, these questions will not appear in the review screen.
You have two Azure SQL Database servers named Server1 and Server2. Each server
contains an Azure SQL database named Database1.
You need to restore Database1 from Server1 to Server2. The solution must replace the
existing Database1 on Server2.
Solution: From Microsoft SQL Server Management Studio (SSMS), you rename Database1
on Server2 as Database2. From the Azure portal, you create a new database on Server2
by restoring the backup of Database1 from Server1, and then you delete Database2.
Does this meet the goal?
A. Yes
B. No
You have SQL Server 2019 on an Azure virtual machine that runs Windows Server 2019.
The virtual machine has 4 vCPUs and 28 GB of memory.
You scale up the virtual machine to 16 vCPUSs and 64 GB of memory.
You need to provide the lowest latency for tempdb.
What is the total number of data files that tempdb should contain?
A. 2
B. 4
C. 8
D. 64
Explanation:
The number of files depends on the number of (logical) processors on the machine. As a
general rule, if the
number of logical processors is less than or equal to eight, use the same number of data
files as logical processors. If the number of logical processors is greater than eight, use
eight data files and then if contention
continues, increase the number of data files by multiples of 4 until the contention is reduced
to acceptable levels or make changes to the workload/code.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-datab
You have a Microsoft SQL Server 2019 database named DB1 that uses the following
database-level and instance-level features.
Clustered columnstore indexes
Automatic tuningYou plan to migrate DB1 to an Azure SQL database.
What feature should be removed or replaced before DB1 can be migrated?
A. Clustered columnstore indexes
B. PolyBase
C. Change tracking
D. Automatic tuning
You have an Azure virtual machine based on a custom image named VM1.
VM1 hosts an instance of Microsoft SQL Server 2019 Standard.
You need to automate the maintenance of VM1 to meet the following requirements:
Automate the patching of SQL Server and Windows Server.
Automate full database backups and transaction log backups of the databases on VM1.
Minimize administrative effort.
What should you do first?
A. Enable a system-assigned managed identity for VM1
B. Register VM1 to the Microsoft.Sql resource provider
C. Install an Azure virtual machine Desired State Configuration (DSC) extension on VM1 D18912E1457D5D1DDCBD40AB3BF70D5D
D. Register VM1 to the Microsoft.SqlVirtualMachine resource provider
Explanation:
Automated Patching depends on the SQL Server infrastructure as a service (IaaS) Agent
Extension. The SQL
Server IaaS Agent Extension (SqlIaasExtension) runs on Azure virtual machines to
automate administration tasks. The SQL Server IaaS extension is installed when you
register your SQL Server VM with the SQL Server VM resource provider.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-iaasagent-
extensionautomate-management
You have several Azure SQL databases on the same Azure SQL Database server in a
resource group named ResourceGroup1.You must be alerted when CPU usage exceeds 80 percent for any database. The solution
must apply to any additional databases that are created on the Azure SQL server.
Which resource type should you use to create the alert?
A. Resource Groups
B. SQL Servers
C. SQL Databases
D. SQL Virtual Machines
Explanation:
There are resource types related to application code, compute infrastructure, networking,
storage + databases.
You can deploy up to 800 instances of a resource type in each resource group.
Some resources can exist outside of a resource group. These resources are deployed to
the subscription, management group, or tenant. Only specific resource types are supported
at these scopes.
Reference:
https://docs.microsoft.com/en-us/azure/azure-resource-manager/management/resourceproviders-
and-types
You have an Azure virtual machine named VM1 on a virtual network named VNet1.
Outbound traffic from VM1
to the internet is blocked.
You have an Azure SQL database named SqlDb1 on a logical server named SqlSrv1.
You need to implement connectivity between VM1 and SqlDb1 to meet the following
requirements:Ensure that VM1 cannot connect to any Azure SQL Server other than SqlSrv1.
Restrict network connectivity to SqlSrv1.
What should you create on VNet1?
A. a VPN gateway
B. a service endpoint
C. a private link
D. an ExpressRoute gateway
Explanation:
Azure Private Link enables you to access Azure PaaS Services (for example, Azure
Storage and SQL Database) and Azure hosted customer-owned/partner services over a
private endpoint in your virtual network.
Traffic between your virtual network and the service travels the Microsoft backbone
network. Exposing your service to the public internet is no longer necessary.
Reference:
https://docs.microsoft.com/en-us/azure/private-link/private-link-overview
You have a version-8.0 Azure Database for MySQL database.
You need to identify which database queries consume the most resources.
Which tool should you use?
A. Query Store
B. Metrics
C. Query Performance Insight
D. Alerts
You have an Azure SQL database named sqldb1.
You need to minimize the amount of space by the data and log files of sqldb1.
What should you run?
A. DBCC SHRINKDATABASE
B. sp_clean_db_free_space
C. sp_clean_db_file_free_space
D. DBCC SHRINKFILE
You have SQL Server 2019 on an Azure virtual machine that runs Windows Server 2019.
The virtual machine
has 4 vCPUs and 28 GB of memory.
You scale up the virtual machine to 8 vCPUSs and 64 GB of memory.
You need to provide the lowest latency for tempdb.
What is the total number of data files that tempdb should contain?
A. 2
B. 4
C. 8
D. 64
Explanation:
The number of files depends on the number of (logical) processors on the machine. As a
general rule, if the number of logical processors is less than or equal to eight, use the same
number of data files as logical processors. If the number of logical processors is greater
than eight, use eight data files and then if contention continues, increase the number of
data files by multiples of 4 until the contention is reduced to acceptable levels or make
changes to the workload/code.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database
Note: This question is part of a series of questions that present the same scenario.
Each question in the series contains a unique solution that might meet the stated
goals. Some question sets might have more than one correct solution, while others
might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a
result, these questions will not appear in the review screen
You have an Azure SQL database named Sales.
You need to implement disaster recovery for Sales to meet the following requirements:
During normal operations, provide at least two readable copies of Sales.
Ensure that Sales remains available if a datacenter fails.
Solution: You deploy an Azure SQL database that uses the Business Critical service tier
and Availability Zones.
Does this meet the goal?
A. Yes
B. No
Explanation:
Premium and Business Critical service tiers leverage the Premium availability model, which
integrates compute resources (sqlservr.exe process) and storage (locally attached SSD) on
a single node. High availability is achieved by replicating both compute and storage to
additional nodes creating a three to four-node cluster.
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).
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla
| Page 6 out of 20 Pages |
| Previous |