Forum Discussion
According to Microsoft SQL Server configuration best practices, what should you do to configure cach
According to Microsoft SQL Server configuration best practices, what should you do to configure caching on volumes that host log files?
Select only one answer.
Enable read caching only.
Enable write caching only.
Enable read and write caching.
Disable all caching.
You plan to deploy Microsoft SQL Server 2019 to an Azure virtual machine that has eight CPUs and four disks.
You expect that SQL Server will run tempdb heavy-workloads.
You need to optimize the performance of tempdb.
What should you do?
Select only one answer.
Store tempdb in eight files of the same size.
Store tempdb in four files. Configure one of the files to have the size matching the amount of memory allocated to SQL Server.
Enable trace flag 1117.
Enable trace flag 1118.
: You plan to implement SQL Server on an Azure virtual machine that has managed disks.
You need to ensure that the virtual machine disks hosting the database and log files provide predictable latency. The solution must minimize costs.
Which disk type should you use?
Select only one answer.
Ultra disk
Premium SSD
Standard SSD
Standard HDD
To implement multi-factor authentication (MFA) for Azure SQL Database, you can authenticate by using Azure AD.
Select Yes if the statement is true. Otherwise, select No.
Select only one answer.
Yes
No
: You manage a SQL Server 2019 instance that hosts a single user database. The instance runs on a Windows Server 2019 server.
You need to be able to create a user in the user database. The solution must NOT have a corresponding SQL Server login.
What should you do first?
Select only one answer.
Enable the SQL Server Windows Authentication mode.
Disable the SQL Server Windows Authentication mode.
From the user database, enable partial containment.
From the user database, disable partial containment
You can control access to database objects by using schemas.
Select Yes if the statement is true. Otherwise, select No.
Select only one answer.
Yes
No
You can configure authentication to an Azure SQL database on the database level.
Select Yes if the statement is true. Otherwise, select No.
Select only one answer.
Yes
No
: You need to assign a permission that grants the right to view data within an object. The solution must use the principle of least privilege.
Which permission should you assign?
Select only one answer.
CONTROL
REFERENCES
SELECT
VIEW DEFINITION
You need to implement a database-level firewall rule for an Azure SQL database.
What should you use?
Select only one answer.
an extended stored procedure
the Azure portal
Azure Cloud Shell
a stored procedure
You need to implement Transparent Data Encryption (TDE) for an Azure SQL database. The solution must use your company’s internal certificate.
Which additional Azure resource should you implement?
Select only one answer.
a Recovery Services vault
Azure Security Center
Azure key vault
Azure Data Catalog
You need to ensure that network traffic from an Azure virtual network to an Azure SQL database uses the Azure backbone network only.
What should you use?
Select only one answer.
Transparent Data Encryption
Always Encrypted
Secure Enclaves
Private Link
The default masking rule presents the same output for string, number, and date values.
Select Yes if the statement is true. Otherwise, select No.
Select only one answer.
Yes
No
You are creating an Azure Monitor alert rule on an Azure virtual machine that hosts a Microsoft SQL Server instance.
You need to specify the email address to which Azure Monitor should send a notification when the alert is triggered.
What should you configure?
Select only one answer.
an action group
a resource
signal logic
a workbook
You need to identify the Performance Monitor metric that demonstrates CPU pressure.
Which metric should you identify?
Select only one answer.
SQLServer:Buffer Manager\Page life expectancy
System\Processor Queue Length
SQLServer:SQL Statistics\SQL Compilations/sec and SQL Re-Compilations/sec
Paging File(_Total)% Usage
Which three types of information can you identify by using Azure SQL Database Query Performance Insight? Each correct answer presents part of the solution.
Select all answers that apply.
the execution plans of long running queries
the execution plans of highest resource consuming queries
the highest resource consuming queries
the highest long running queries
the query IDs for top resource consuming queries and long running queries
When using Dynamic Thresholds of Azure Monitor alerts, you must provide the value of the threshold for the metric that triggers the alert.
Select Yes if the statement is true. Otherwise, select No.
Select only one answer.
Yes
No
: You need to analyze the read/write latency for the data and transaction log files of a SQL Server that runs on an Azure virtual machine. The solution must use the least amount of administrative effort and provide statistics for database files.
What should you use?
Select only one answer.
Windows Performance Monitor
the Azure portal
a SQL Server extended stored procedure
A SQL Server dynamic management view
By default, Query Store contains every query executed by Azure SQL Database.
Select Yes if the statement is true. Otherwise, select No.
When creating an index, you reduce the likelihood of fragmentation by setting a high fillfactor value.
Select Yes if the statement is true. Otherwise, select No.
Select only one answer.
Yes
No
Fragmentation applies to both clustered and nonclustered indexes.
Select Yes if the statement is true. Otherwise, select No.
Select only one answer.
Yes
No
You need to use Transact-SQL to perform a full database restore.
After the restore, you will restore the transaction logs.
Which option should you include when you run the RESTORE DATABASE statement?
Select only one answer.
WITH RECOVERY
WITH REPLACE
WITH STOPAT
WITH NORECOVERY
A full backup contains the transaction logs.
Select Yes if the statement is true. Otherwise, select No.
Select only one answer.
Yes
No
With the simple recovery model, you can restore transaction logs.
Select Yes if the statement is true. Otherwise, select No.
Select only one answer.
Yes
No
You have an Azure virtual machine that runs Windows Server 2019 and hosts a SQL Server 2019 instance.
You plan to automate the full back up of databases by using the SQL Server Automated Backup v2.
What is the maximum frequency that can be configured for the backups?
Select only one answer.
Once per day
Every 12 hours
Every 8 hours
Every 3 hours
You plan to implement SQL Server on an Azure virtual machine.
Which database can you store on drive D?
Select only one answer.
tempdb
master
model
msdb
You need to grant the most permissive permission to a database view.
Which permission should you grant?
Select only one answer.
EXECUTE
CONTROL
UPDATE
ALTER
You can trigger the execution of an Azure logic app as a response to an Azure Monitor alert.
Select Yes if the statement is true. Otherwise, select No.
Select only one answer.
Yes
No
When you run Microsoft SQL Server on an Azure Linux virtual machine, you must install additional software to capture metrics similar to those provided by Windows Performance Monitor.
Select Yes if the statement is true. Otherwise, select No.
Select only one answer.
Yes
No
A deadlock in Microsoft SQL Server requires an administrative action to be resolved.
Select Yes if the statement is true. Otherwise, select No.
Yes or No
Please help me answer this question
- luistorresBrass Contributor1. According to Microsoft SQL Server configuration best practices, what should you do to configure caching on volumes that host log files?Enable read caching only.2. You plan to deploy Microsoft SQL Server 2019 to an Azure virtual machine that has eight CPUs and four disks.You expect that SQL Server will run tempdb heavy-workloads.You need to optimize the performance of tempdb.What should you do?Store tempdb in eight files of the same size.Enable trace flag 11173. You need to ensure that the virtual machine disks hosting the database and log files provide predictable latency. The solution must minimize costs.Which disk type should you use?Premium SSD4. To implement multi-factor authentication (MFA) for Azure SQL Database, you can authenticate by using Azure AD.Yes5. You manage a SQL Server 2019 instance that hosts a single user database. The instance runs on a Windows Server 2019 server.You need to be able to create a user in the user database. The solution must NOT have a corresponding SQL Server login.From the user database, enable partial containment.6. You can control access to database objects by using schemas.Yes7. You can configure authentication to an Azure SQL database on the database level.Yes8. You need to assign a permission that grants the right to view data within an object. The solution must use the principle of least privilege.SELECT9. You need to implement a database-level firewall rule for an Azure SQL database.the Azure portal10. You need to implement Transparent Data Encryption (TDE) for an Azure SQL database. The solution must use your company’s internal certificate.Azure Security Center (I am not sure, if this is the right one)11. You need to ensure that network traffic from an Azure virtual network to an Azure SQL database uses the Azure backbone network only.Private Link12. The default masking rule presents the same output for string, number, and date values.No13. You are creating an Azure Monitor alert rule on an Azure virtual machine that hosts a Microsoft SQL Server instance.an action group14. You need to identify the Performance Monitor metric that demonstrates CPU pressure.Which metric should you identify?System\Processor Queue Length (No Sure)15. Which three types of information can you identify by using Azure SQL Database Query Performance Insight? Each correct answer presents part of the solution.the highest resource consuming queriesthe highest long running queries16. the query IDs for top resource consuming queries and long running queriesWhen using Dynamic Thresholds of Azure Monitor alerts, you must provide the value of the threshold for the metric that triggers the alert.No17. You need to analyze the read/write latency for the data and transaction log files of a SQL Server that runs on an Azure virtual machine. The solution must use the least amount of administrative effort and provide statistics for database files.the Azure portal18. By default, Query Store contains every query executed by Azure SQL Database.No19. When creating an index, you reduce the likelihood of fragmentation by setting a high fillfactor value.No (No sure)20. Fragmentation applies to both clustered and nonclustered indexes.Yes21. You need to use Transact-SQL to perform a full database restore.After the restore, you will restore the transaction logs.WITH NORECOVERY22. A full backup contains the transaction logs.No23. With the simple recovery model, you can restore transaction logs.No24. You have an Azure virtual machine that runs Windows Server 2019 and hosts a SQL Server 2019 instance.You plan to automate the full back up of databases by using the SQL Server Automated Backup v2.What is the maximum frequency that can be configured for the backups?Every 12 hours I chose the longest in the options but really full back frequency options are Daily or Weekly25. You plan to implement SQL Server on an Azure virtual machine.Which database can you store on drive D?tempdb26. You need to grant the most permissive permission to a database view.Which permission should you grant?ALTER27. You can trigger the execution of an Azure logic app as a response to an Azure Monitor alert.Yes28. When you run Microsoft SQL Server on an Azure Linux virtual machine, you must install additional software to capture metrics similar to those provided by Windows Performance Monitor.No29. A deadlock in Microsoft SQL Server requires an administrative action to be resolved.No
- fmantovaniCopper Contributor
luistorres , to the question:
"You have an Azure virtual machine that runs Windows Server 2019 and hosts a SQL Server 2019 instance.
You plan to automate the full back up of databases by using the SQL Server Automated Backup v2.
What is the maximum frequency that can be configured for the backups?"
I would reply Once per day as it's the longest and mentioned in the documentation.