Forum Discussion

ngbeken1monday's avatar
ngbeken1monday
Copper Contributor
Dec 08, 2022

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

  • luistorres's avatar
    luistorres
    Brass Contributor

    ngbeken1monday 

    1. 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 1117

    3. 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 SSD

    4. To implement multi-factor authentication (MFA) for Azure SQL Database, you can authenticate by using Azure AD.
    Yes

    5.  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.
    Yes

    7. You can configure authentication to an Azure SQL database on the database level.
    Yes

    8. 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.
    SELECT

    9. You need to implement a database-level firewall rule for an Azure SQL database.
    the Azure portal

    10. 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 Link

    12. The default masking rule presents the same output for string, number, and date values.
    No

    13. You are creating an Azure Monitor alert rule on an Azure virtual machine that hosts a Microsoft SQL Server instance.
    an action group

    14. 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 queries
    the highest long running queries

    16. 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.
    No

    17. 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 portal

    18. By default, Query Store contains every query executed by Azure SQL Database.
    No

    19. 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.
    Yes

    21. You need to use Transact-SQL to perform a full database restore.
    After the restore, you will restore the transaction logs.
    WITH NORECOVERY

    22. A full backup contains the transaction logs.
    No

    23. With the simple recovery model, you can restore transaction logs.
    No

    24. 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 Weekly

    25. You plan to implement SQL Server on an Azure virtual machine.
    Which database can you store on drive D?
    tempdb

    26. You need to grant the most permissive permission to a database view.
    Which permission should you grant?
    ALTER

    27. You can trigger the execution of an Azure logic app as a response to an Azure Monitor alert.
    Yes

    28. 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.
    No

    29. A deadlock in Microsoft SQL Server requires an administrative action to be resolved.
    No
    • fmantovani's avatar
      fmantovani
      Copper 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.

Resources