1) You have an Azure SQL Database named db1 and a Microsoft Entra group named db_readers.
You need to ensure that the members of db_readers can read data from db1. The solution must follow the principle of least privilege.
Which two actions should you perform? Each correct answer presents part of the solution.
- Assign the db_accessadmin role to the user in the master database.
- Assign the db_datareader role to the user in db1.
- Assign the db_datareader role to the user in the master database.
- Assign the db_denydatawriter role to the user in db1.
- Create a user in the db1 database for db_readers.
- Create a login and a user in the master database for db_readers.
You need to create a contained database user in the db1 database. The user must be assigned the db_datareader role for db1 to be able to read the data.
You should not create a user in the master database.
You should not assign the db_accessadmin role to the user because this role allows you to manage security assignments for other roles.
You should not assign the db_datareader role to the user for the master database because the user must read data from db1.
You should not assign the db_denydatawriter role to the user for db1 because this role prevents data changes in the database but is unrelated to data selection.
2) You have an app that stores data for multiple tenants using Azure SQL Database elastic pools. Each tenant has a database.
You need to configure an authentication solution for the app. The solution must meet the following requirements:
Ensure that users can only access the database of their respective tenant.
Ensure databases can be moved to a dedicated instance.
Minimize administrative effort.
What should you include in the solution?
a. contained database users
b. database roles
c. server roles
Contained database users enable databases to be moved without recreating logins.
Server roles manage permissions at the server level only, and database roles manage permissions within a database.
Configure database authentication and authorization - Training | Microsoft Learn
Contained user access to contained databases - SQL Server | Microsoft Learn
3) You have an Azure SQL server that hosts 10 Azure SQL Databases.
You create an app named App1 with a system assigned managed identity.
You need to ensure that App1 has read access to each database.
Which three actions should you perform? Each correct answer presents part of the solution.
- Assign the database user the db_reader role for each database.
- Assign the database user the db_reader role for the master database.
- Create a login for App1 from an external provider.
- Create a login for App1 that uses Windows authentication.
- Create a user in each database for the App1 login.
- Create a user in the master database for the App1 login.
A managed identity requires that a login be created from an external provider, each database requires a user for the login, and assigning the database user the db_reader role for each database will allow read access to the user databases.
A managed identity cannot use Windows authentication. The app does not require a user in the master database and assigning the database user the db_reader role for the master database will allow the user read access to the master database, but not read access to the user databases.
Describe Security Principals - Training | Microsoft Learn
4) You have an Azure SQL Database named db1.
You create a user named User1 in the master database.
You need to ensure that User1 can create databases. The solution must follow the principle of least privilege.
Which role should you assign to User1?
a. db_ddladmin
b. dbmanager
c. db_owner
d. loginmanager
You should assign dbmanager, as members of this role can create databases.
Members of the loginmanager role can create and delete users, but they cannot create databases. Members of the db_ddladmin role can run any DDL statements. Members of the db_owner role can perform all activities at the database scope.
Configure database authentication and authorization - Training | Microsoft Learn
Data partitioning strategies - Azure Architecture Center | Microsoft Learn
5) You have a database named db1 in an Azure SQL Managed Instance named Instance1.
You need to encrypt db1 by using Transparent Data Encryption (TDE).
Which three actions should you perform before you can start the encryption? Each correct answer present part of the solution.
- Create a certificate in db1.
- Create a certificate in the master database.
- Create a database encryption key in db1.
- Create a master key in the master database.
- Create a symmetric key in db1.
The procedure of preparing the database to use TDE is first to create a master key and a certificate in the master database, and then to create a database encryption key in the db1.
You should not create a certificate or an asymmetric key in db1, as TDE can only use certificates created in the master database.
Transparent data encryption (TDE) - SQL Server | Microsoft Learn
Protect data in-transit and at rest - Training | Microsoft Learn
6) You have an Azure SQL Database named db1.
You need to ensure that all the data in db1 is encrypted at rest.
What should you use?
a. Always Encrypted
b. Transparent Data Encryption (TDE)
c. SQL Server auditing
d. Microsoft Defender for SQL
TDE is the Azure SQL Database feature that encrypts data at rest. All the data saved to a disk is encrypted.
Auditing is not used for performing data encryption at rest, it is used for gathering diagnostic information.
Defender for SQL is used to mitigate potential database vulnerabilities and detect anomalous activities.
Always Encrypted protects data that is in use or in transit.
Explore Transparent Data Encryption - Training | Microsoft Learn
7) In an Azure SQL Database, you have a table that contains a column named Column1. Column1 stores credit card CVV data.You need to configure Column1 to meet the following requirements:
The data in the column must be protected at rest.
The data in the column must be protected when in use.
Database administrators must be prevented from viewing the contents of Column1.
Which two features can you use? Each correct answer presents a complete solution.
- Always Encrypted Deterministic encryption
- Always Encrypted Randomized encryption
- Column-level SQL Server encryption using symmetric keys
- dynamic data masking
You should use Always Encrypted (Randomized encryption or Deterministic encryption), because this ensures that the administrators are unable to view and decrypt data.
Encryption with symmetric keys will allow the administrators to access the data. Dynamic data masking does not protect data at rest or in use.
8) You have an Azure SQL Database named db1 that contains a table named Sales.
You need to implement change tracking for Sales.
What should you do first?
a. Create a certificate in db1.
b. Enable change tracking for db1.
c. Create a master key in the master database.
d. Enable change tracking for Sales.
You should enable change tracking at the database level first, then you can enable change tracking for each table.
Change tracking must be enabled at the database level before it can be enabled for the Sales table. Creating a master key in the master database and creating a certificate in db1 are not needed for change tracking.
Enable and Disable Change Tracking - SQL Server | Microsoft Learn
Implement compliance controls for sensitive data - Training | Microsoft Learn
9) You have an Azure SQL Database named db1 that contains a table. The table contains a column that stores email addresses.You need to ensure that reporting users can only view email addresses in a format of aXXX@XXX.com. The solution must minimize the impact on other users.
What should you configure?
a. Always Encrypted
b. column encryption
c. dynamic data masking
d. row-level security (RLS)
Dynamic data masking will enable users to only see masked values instead of email addresses.
Always Encrypted protects data in use or in transit. RLS controls access to rows in a table, and column encryption will encrypt the data in the column, which can hinder users’ ability to view the data.
10) You have a data estate that includes multiple Azure SQL Databases.
You need to audit the lifecycle of data across the estate. The solution must minimize administrative effort.
What should you use?
a. Auditing for Azure SQL Database
b. Azure Data Factory
c. Microsoft Defender for SQL
d. Microsoft Purview
Microsoft Purview will provide data lineage.
Auditing for Azure SQL Database tracks database events, Azure SQL Defender surfaces database vulnerabilities, and Azure Data Factory is a data integration service. These options do not provide a full view of the lifecycle of the data while minimizing the administrative effort required to perform the audit.
11) You have 20 on-premises Microsoft SQL Server instances.
You need to migrate the instances to Azure. The solution must support auditing at the server level.
Which two services can you use? Each correct answer presents a complete solution.
- Azure SQL Database elastic pool
- Azure SQL Database Hyperscale
- Azure SQL Database serverless
- Azure SQL Managed Instance
- SQL Server on Azure Virtual Machines
SQL Server on Azure Virtual Machines and Azure SQL Managed Instance support SQL Server Audit and can be used.
Azure SQL Database serverless is a compute tier of Azure SQL Database that does not support SQL Server Audit. Azure SQL Database Elastic pool is a shared database offering of Azure SQL Database that doesn’t support SQL Server Audit. Azure SQL Database Hyperscale is a service tier of Azure SQL Database that does not support SQL Server Audit.
12) You are building a multi-tenant app that will use an Azure SQL Database.
You need to partition the database. The solution must meet the following requirements:
- Improve scalability.
- Reduce contention.
- Optimize performance.
- Isolate each tenant’s data.
What should you use?
a. a combination of list and range shard maps
b. a list shard map only
c. a range shard map only
Using only a list shard map is the correct solution because each tenant’s data will be stored in a separate database.
A range shard map will allow data from multiple tenants to be in stored in the same database.
13) You are deploying a SQL Server on Azure Virtual Machines instance that will be used as a development environment. You will develop two small databases, one of which will have in-memory tables.
You need to choose the most suitable virtual machine type for the deployment.
What should you choose?
a. compute optimized
b. GPU
c. memory optimized
d. general purpose
General purpose virtual machine sizes provide a balanced CPU-to-memory ratio. This is ideal for testing and development, small to medium databases, and low to medium traffic web servers.
Compute optimized virtual machine sizes have a high CPU-to-memory ratio. This is good for medium traffic web servers, network appliances, batch processes, and application servers.
Memory optimized virtual machine sizes offer a high memory-to-CPU ratio that is used for relational database servers, medium to large caches, and in-memory analytics.
GPU optimized virtual machine sizes are specialized virtual machines available with single, multiple, or fractional GPUs. These sizes are designed for compute-intensive, graphics-intensive, and visualization workloads.
Prepare to maintain SQL databases on Azure - Training | Microsoft Learn
Azure VM sizes - General purpose - Azure Virtual Machines | Microsoft Learn
14) You are deploying a SQL Server on Azure Virtual Machines instance for a production environment. One of the databases in this instance has in-memory tables.
You need to choose the most suitable virtual machine type for the deployment.
What should you choose?
a. compute optimized
b. GPU
c. memory optimized
d. general purpose
Memory optimized virtual machine sizes offer a high memory-to-CPU ratio that is good for relational database servers, medium to large caches, and in-memory analytics.
General purpose virtual machines provide a balanced CPU-to-memory ratio. These are best for testing and development, small to medium databases, and low to medium traffic web servers.
Compute optimized virtual machines have a high CPU-to-memory ratio. These are used for medium traffic web servers, network appliances, batch processes, and application servers.
GPU optimized virtual machine sizes are specialized virtual machines available with single, multiple, or fractional GPUs. These sizes are designed for compute-intensive, graphics-intensive, and visualization workloads.
15) You have an on-premises instance of Microsoft SQL Server 2019 that uses a SQL common language runtime (CLR) stored procedure to access a file share.
You need to migrate the instance to Azure.
To which Azure service should you migrate the instance?
16) You plan to migrate a 40-TB OLTP database to Azure SQL Database.
Which service tier should you recommend for the database?
a. Business Critical vCore service tier
b. DTU Premium service tier
c. General Purpose vCore service tier
d. Hyperscale vCore service tier
You should recommend the Hyperscale vCore service tier, as it supports database sizes of up to 100 TB.
The General Purpose and Business Critical vCore service tier and the DTU Premium service tier only support a database size of up to 4 TB.
What is the Hyperscale service tier? - Azure SQL Database | Microsoft Learn
Understand SQL database hyperscale - Training | Microsoft Learn
17) You have an Azure SQL Managed Instance that contains a database named db1.
You need to implement table partitioning for a table in db1.
You create the filegroups.
What should you do next?
a. Create a partition function, create a partition scheme, and then create a table on the partition function.
b. Create a partition function, create a partition scheme, and then create a table on the partition scheme.
c. Create a partition function, create a table on the partition function, and then change the fill factor settings on indexes.
d. Create a partition scheme, create a table on the partition scheme, and then change the fill factor settings on indexes.
You should first create a partition function to define how the rows in a table or index map to a set of partitions based on the values of a partitioning column. Then you should create a partition scheme, which is a database object that maps the partitions of a partition function to one filegroup or to multiple filegroups. Finally, you should create a table specifying the partition scheme.
You should not create a table specifying the partition function, as a partition scheme is needed, nor should you change the fill factor settings for the indexes, as these settings relate to the free space at the index page level.
18) You have an Azure SQL Database that is only used to process data for three hours a day. Currently, the database has the Standard S3 (100 DTU) service tier configured.
You need to recommend a solution that will increase the performance without increasing the cost.
Which database service tier do you recommend?
a. Premium
b. Business Critical vCore
c. Hyperscale vCore
d. vCore serverless
By choosing the vCore serverless service tier, the database will be active only during the daily schedule, and the amount of vCores can be configured dynamically to handle the workload.
The Premium, Hyperscale, and Business Critical service tiers will be a more expensive solution.
Understand SQL database hyperscale - Training | Microsoft Learn
What is the Hyperscale service tier? - Azure SQL Database | Microsoft Learn
19) You have an on-premises SQL Server database that is used as part of a mission critical app.
You plan to migrate the database to SQL Server on Azure Virtual Machines.
You need to configure the disks for the virtual machine. The solution must meet the following requirements:
Maximize throughput.
Minimize latency.
Which type of disk should you use?
a. Premium SSD
b. Standard HDD
c. Standard SSD
d. Ultra disk
Ultra SSD will provide the highest throughput. Standard HDD, Standard SSD, and Premium SSD will not provide the highest throughput.
Explore performance and security - Training | Microsoft Learn
20) You have a database in an Azure SQL Managed Instance.
You need to synchronize data from five tables in the database to an Azure SQL Database. Any subsequent data changes must be visible in the Azure SQL Database as quickly as possible.
Which migration method should you use?
a. Azure Data Factory
b. BCP
c. change data capture
d. transactional replication
Transactional replication allows you to select tables and apply filters so only the required data will be sent to the target database. The replication process occurs nearly in real time, and it does not require any additional coding.
Change data capture and BCP require an additional orchestration component, such as Data Factory, to send changes from the source server to the target server. If you use Data Factory, you will need to detect changes when the pipeline is running. This process takes more time.
Transactional replication - Azure SQL Managed Instance | Microsoft Learn
21) You have an on-premises database that uses Transparent Data Encryption (TDE).
You plan to migrate the database to an Azure SQL Managed Instance named Instance1.
You export the TDE certificate to a file.
You need to ensure that you can restore the database to Instance1.
Where should you upload the certificate?
a. a Recovery Services vault
b. an Azure key vault
c. Instance1
d. Azure Blob Storage
The certificate should be uploaded to the managed instance, as the certificate should not be stored in either a Recovery Services vault or a key vault for the managed instance to have access.
Migrate SQL workloads to Azure Managed Instances - Training | Microsoft Learn
Migrate TDE certificate - managed instance - Azure SQL Managed Instance | Microsoft Learn
22) You have a SQL Server on an Azure Virtual Machines instance.
You need to configure an alert when a log truncation occurs in a database.
Which type of alert should you configure?
a. SQL Server event alert
b. WMI event alert
c. SQL Server performance condition alert
Configuring a SQL Server performance condition alert allows you to use performance metrics that are generated by Microsoft SQL Server. You can specify an alert to occur in response to a particular performance condition. In this case, you specify the performance counter to monitor, a threshold for the alert, and the behavior that the counter must show if the alert is to occur.
A SQL Server event alert should be used when a specific error occurs and the WMI event alert occurs in response to a specific WMI event.
Alerts - SQL Server Agent | Microsoft Learn
Create and manage SQL Agent jobs - Training | Microsoft Learn
23) You have a SQL Server on Azure Virtual Machines instance.
You need to configure an alert when an error of severity 21 is detected.
Which type of alert should you configure?
a. SQL Server event alert
b. WMI event alert
c. SQL Server performance condition alert
A SQL Server event alert should be used when a specific error occurs and the WMI event alert occurs in response to a specific WMI event.
Alerts - SQL Server Agent | Microsoft Learn
Create and manage SQL Agent jobs - Training | Microsoft Learn
24) You have a SQL Server on Azure Virtual Machines instance.
You need to ensure that an automated email is sent if a job fails. The solution must minimize administrative effort.
Which three actions should you perform? Each correct answer presents part of the solution.
- Create a Database Mail profile.
- Create an alert.
- Enable the mail profile on SQL Server Agent.
- Restart the SQL Server Agent service
- Restart the SQL Server service.
To ensure that an automated email is sent if a job fails, you need to create and configure a Database Mail profile, then configure the SQL Server Agent properties. You also need to enable the mail profile because it is disabled by default for security reasons, and a restart of the SQL Server Agent service is required.
Restarting the SQL Server service is unnecessary, because email notifications are configured on the SQL Server Agent level. You need to restart the SQL Server Agent service instead. You should not create an alert, as notifications about the job failure can be achieved directly on each job step.
Create and manage SQL Agent jobs - Training | Microsoft Learn
SQL Server Agent Properties (Alert System Page) - SQL Server Agent | Microsoft Learn
25) You deploy a SQL Server on Azure Virtual Machines instance.
You need to ensure that automated patching is enabled.
Which two cmdlets should you run? Each correct answer presents part of the solution.
b. New-AzVMSqlServerAutoPatchingConfig
c. Set-AzVMSqlServerExtension
d. Set-AzVMCustomScriptExtension
The New-AzVMSqlServerAutoPatchingConfig cmdlet allows you to create a configuration object for automatic patching on a virtual machine, and the Set-AzVMSqlServerExtension cmdlet establishes a maintenance window for a SQL Server on Azure Virtual Machines instance. Automated updates can only be installed during this maintenance window.
Set-AzVMCustomScriptExtensionUpdate Adds a custom script extension to a virtual machine, and the New-AzVMConfig cmdlet creates a configurable local virtual machine object for Azure.
Set-AzVMSqlServerExtension (Az.Compute) | Microsoft Learn
Automate deployment of database resources - Training | Microsoft Learn
26) You have a bacpac file from an existing Microsoft SQL Server database. You copy the file to an Azure Storage account.
You need to apply the bacpac file to an existing Azure SQL Database.
Which command should you run?
a. az sql db create
b. az sql db restore
c. az sql db import
d. az sql db update
You can use the az sql db import command to import the database from the bacpac file. The database must be created before the bacpac file is imported.
The az sql db restore command creates a new database from backup, and the az sql db update command updates the existing database.
27) You create an Azure Logic App to insert data into a table named Sales in an Azure SQL Database named db1. The logic app uses the Insert row action.
Each execution of the logic app throws an error because there is trigger in the Sales table.
You need to fix the logic app without affecting the Sales table.
Which three actions should you perform? Each correct answer presents part of the solution.
- Add a When item is created trigger to the logic app.
- Create a stored procedure in db1 that inserts a row into the Sales table.
- Create an Execute stored procedure action in the logic app.
- Remove the Insert row action from the logic app.
- Remove the trigger from the Sales table.
You should create a stored procedure in db1 that inserts a row into the Sales table. In this case, the information about the trigger execution is captured in the stored procedure. You should also remove the Insert row action from the logic app, create an Execute stored procedure action in the logic app, and then add a reference to the stored procedure you created in db1.
Removing the existing trigger from the Sales table is incorrect, as the table cannot be affected. Adding a When item is created trigger to the logic app is useful if the logic app needs to take action when data is added to the Sales table.
28) You have an Azure SQL Database named db1 that contains a table named Sales.
You create an Azure Logic App.
You need to configure the logic app to capture information when a new row is added to Sales.
What should you configure in the logic app?
a. a Get rows action
b. a When item is created trigger
c. an Execute SQL statement action
d. an Insert row action
By configuring a When item is created trigger, the trigger will be executed when a new row is inserted into the table.
The Execute SQL query action is used when you provide a query that will be executed by the logic app on the target database, the Get rows action selects data from a table, and the Insert row action inserts a new row into the table.
SQL Server - Connectors | Microsoft Learn
Manage Azure PaaS tasks using automation - Training | Microsoft Learn
29) You have an Azure subscription that includes 20 Azure SQL logical servers.
You need to automate management tasks using an Azure Automation runbook.
What should you do first?
a. Create an Azure Automation credential.
b. Create an Azure Automation account.
c. Create an Azure Automation runbook schedule.
d. Deploy an Azure Automation hybrid runbook runner.
To automate management tasks by using an Azure Automation runbook, you first need to create an Azure Automation account.
You cannot create a credential or a schedule without first creating an Azure Automation account, and you do not need a hybrid runbook runner.
30) You need to set up a baseline for performance monitoring of an Azure SQL Database.
Which dynamic management view should you query to get the baseline performance data?
a. sys.dm_db_wait_stats.
b. sys.dm_exec_query_stats
c. sys.dm_exec_sessions_wait_stats
d. sys.dm_os_waiting_tasks
sys.dm_db_wait_stats returns information about all the waits encountered in the database. This allows you to see the baseline performance data.
sys.dm_os_waiting_tasks is related to current connections and sessions. This view does not store any historical waits, so it should not be used to create the performance baseline. sys.dm_exec_sessions_wait_stats is used to find the wait statistics for a particular session. sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans in Microsoft SQL Server.
sys.dm_db_wait_stats (Azure SQL Database) - SQL Server | Microsoft Learn
Establish baseline metrics - Training | Microsoft Learn
31) You have an Azure SQL Server 2019 instance.
You need to enable Extended Events to monitor deadlocks.
Which T-SQL Statement should you run?
a. CREATE EVENT SESSION deadlocks ON SERVER ADD EVENT sqlserver.lock_deadlock(ACTION(sqlserver.sql_text)), ADD EVENT sqlserver.lock_deadlock_chain(ACTION(sqlserver.sql_text)) ADD TARGET package0.event_file ( SET filename=C:\traces\deadlocks.xel' ) WITH (MAX_MEMORY=4MB, MAX_DISPATCH_LATENCY = 3 SECONDS); GO.
b. CREATE EVENT SESSION deadlocks ON SERVER ADD EVENT sqlserver.lock_deadlock(ACTION(sqlserver.sql_text)), ADD EVENT sqlserver.lock_deadlock_chain(ACTION(sqlserver.sql_text)) ADD TARGET package0.event_counter WITH (MAX_MEMORY=4MB, MAX_DISPATCH_LATENCY = 3 SECONDS); GO
c. CREATE EVENT SESSION deadlocks ON SERVER ADD EVENT sqlserver.lock_deadlock(ACTION(sqlserver.sql_text)), ADD EVENT sqlserver.lock_deadlock_chain(ACTION(sqlserver.sql_text)) ADD TARGET package0.histogram ( SET filtering_event_name=N'sqlserver.lock_deadlock’, source=N'sqlserver.sql_text’ ) WITH (MAX_MEMORY=4MB, MAX_DISPATCH_LATENCY = 3 SECONDS); GO
The event_file target will enable historical troubleshooting.
The event counter target merely counts how many times each specified event occurs and the histogram target counts occurrences of different types of items.
Targets for Extended Events in SQL Server - SQL Server | Microsoft Learn
Explore extended events - Training | Microsoft Learn
32) You plan to use Query Store in an Azure SQL Managed Instance.
You need to ensure that you can manually force execution plans for regressed queries. The solution must follow the principle of least privilege.
To which database role should you be assigned?
a. db_owner
b. db_accessadmin
c. db_ddladmin
d. db_securityadmin
As a member of the db_owner fixed database role, you can perform all configuration and maintenance activities on the database, while following the principle of least privilege.
Members of the db_ddladmin role can run any DDL commands for a database. Members of this role can potentially elevate their privileges by manipulating code that may be executed under high privileges, and their actions should be monitored. A member of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and Microsoft SQL Server logins. Members of the db_securityadmin role can modify role membership for custom roles only and manage permissions.
Database-Level Roles - SQL Server | Microsoft Learn
Configure database authentication and authorization - Training | Microsoft Learn
33)You discover that a stored procedure running on an Azure SQL Database is slow due to a parameter sniffing issue. The procedure contains one SELECT statement.
You need to resolve the issue by using query hints. You do not want to remove query plans after each procedure execution.
Which hint should you use?
a. FORCESEEK
b. INDEX
c. OPTIMIZE FOR
Using the OPTIMIZE FOR hint in the SELECT statement instructs Microsoft SQL Server to use a particular value for a parameter when the query is optimized.
The INDEX hint forces the query optimizer to use only the specified indexes to access the data in the referenced table or view, and the FORCESEEK hint forces the optimizer to use only an index seek operation to access the data in the referenced table or view.
Attach Query Hints to a Plan Guide - SQL Server | Microsoft Learn
Explore query performance optimization - Training | Microsoft Learn
34) You have an Azure SQL Database that uses Query Store.
You notice poor performance for a report caused by the execution plan and the report parameters.
You need to improve the performance of the report. The solution must minimize administrative effort.
What should you do?
a. Disable parameter sniffing.
b. Enable snapshot isolation.
c. Execute the DBCC FREEPROCCACHE command.
d. Force the most efficient execution plan.
Forcing the plan that is the most efficient for most the executions will improve report performance.
Disabling parameter sniffing will ignore any parameters and not choose the most efficient plan. DBCC FREEPROCCACHE will remove all plans from the cache.
Snapshot isolation is used to enable concurrent execution of transactions.
Evaluate performance improvements - Training | Microsoft Learn
Monitor performance by using the Query Store - SQL Server | Microsoft Learn
35) You have a database in an Azure SQL Managed Instance.
You need to control the MAXDOP configuration at the workload level. The solution must minimize administrative and development effort.
What should you do?
a. At the database query level, set the MAXDOP hint.
b. Configure the MAXDOP option in the database by using ALTER DATABASE SCOPED CONFIGURATION.
c. Execute the sp_configure stored procedure with the max degree of parallelism parameter.
d. In Resource Governor, set the MAX_DOP workload group configuration.
36) You need to find all indexes that have high fragmentation.
Which two dynamic management views should you query? Each answer presents a part of the solution.
- sys.dm_db_column_store_row_group_physical_stats
- sys.dm_db_index_operational_stats
- sys.dm_db_index_physical_stats
- sys.dm_db_index_usage_stats
You should query the sys.dm_db_index_physical_stats view, as it returns size and fragmentation information for the data and indexes of the specified table or view in Microsoft SQL Server. For an index, one row is returned for each level of the B-tree in each partition. For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. You should also query the sys.dm_db_column_store_row_group_physical_stats view because it provides current rowgroup-level information about all the columnstore indexes in the current database. Based on the information, you can calculate the index fragmentation.
The sys.dm_db_index_usage_stats view returns counts of different types of index operations and the time each type of operation was last performed, and the sys.dm_db_index_operational_stats view returns current lower-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.
sys.dm_db_index_physical_stats (Transact-SQL) - SQL Server | Microsoft Learn
Configure databases for optimal performance - Training | Microsoft Learn
37) You have an Azure SQL Managed Instance that hosts two databases named db1 and db2.
You need to limit usage of db1 sessions to a maximum of 30 percent of the available CPU.
Which three actions should you perform? Each correct answer presents part of the solution.
- Configure the Max Degree of Parallelism.
- Create a resource pool.
- Create a workload group.
- Create an Extended Events session.
- Enable Automatic Database Tuning.
- Enable Resource Governor.
Resource Governor needs to be enabled, a resource pool needs to be created, and a workload group needs to be created to limit db1 to a maximum of 30 percent.
Automatic Database Tuning is used to notify you whenever a potential performance issue is detected and lets you apply corrective actions. Extended Events is a lightweight performance monitoring system. Max Degree of Parallelism limits the number of processors to use in a parallel plan execution.
38) You have an Azure SQL Managed Instance that contains a database named DB1. The database compatibility level for DB1 is 150.
Queries that use table variables are performing poorly. You identify that the issue is caused by incorrect cardinality estimations.
You need to configure an option to ensure that the database engine defines the cardinality estimate when the query is executed, and the actual row count is known.
Which option should you configure?
a. DEFERRED_COMPILATION_TV
b. ELEVATE_ONLINE
c. INTERLEAVED_EXECUTION_TVF
d. TSQL_SCALAR_UDF_INLINING
You should use the DEFERRED_COMPILATION_TV option because it enables or disables table variable compilation.
The INTERLEAVED_EXECUTION_TVF option enables or disables interleaved execution for multi-statement table-valued functions.
The TSQL_SCALAR_UDF_INLINING option enables or disables the T-SQL Scalar UDF inlining.
The ELEVATE_ONLINE option causes the engine to automatically elevate supported operations online.
ALTER DATABASE SCOPED CONFIGURATION - SQL Server (Transact-SQL) | Microsoft Learn
39) You plan to deploy a SQL Server on an Azure Virtual Machines instance running on a Windows Server operating system.
You need to configure storage for the virtual machine. The solution must ensure the highest performance for the data files.
Which two actions should you perform? Each correct answer presents part of the solution.
- Set host caching to none.
- Set host caching to read-only.
- Set host caching to read/write.
- Stripe multiple disks by using Storage Spaces.
- Use Write Accelerator disks.
You should stripe multiple disks, as it will increase IOPS, as well as set host caching to read-only, as data files perform best by using this type of host caching.
Setting host caching to none means that reads will not be cached, setting host caching to read/write will degrade performance and lead to data loss. Write Accelerator Disks should be used for transaction log disks.
Storage: Performance best practices & guidelines - SQL Server on Azure VMs | Microsoft Learn
Optimize database storage - Training | Microsoft Learn
40) You have an Azure SQL Database that uses the General Purpose service tier.
You need to ensure that there are at least two readable copies of the database, and the database is available if a datacenter becomes unavailable.
What should you include in the recommendation?
a. active geo-replication
b. automated backups
c. elastic pools
d. failover groups
Active geo-replication is a feature that lets you create a continuously synchronized, readable secondary database for a primary database. The readable secondary database can be in the same Azure region as the primary database, or, more commonly, in a different region.
The failover groups allow you to manage the replication and failover of some or all databases on a logical server to another region, but do not allow you to create multiple Azure SQL Database geo-secondary replicas. Automated backups do not let you have two readable copies of the database available, and elastic pools are used for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single server and share a set number of resources.
Active geo-replication - Azure SQL Database | Microsoft Learn
Explore IaaS and PaaS solutions for high availability and disaster recovery - Training | Microsoft Learn
41) You have 100 on-premises Microsoft SQL servers.
You plan to migrate the servers to SQL Server on Azure Virtual Machines.
You need to recommend a high-availability solution that will maintain availability if a datacenter fails.
What should you include in the recommendation?
a. availability zones
b. availability sets
c. Azure Backup
d. Azure Site Recovery
You should recommend availability zones, as they account for datacenter-level failure in Azure.
Availability sets provide uptime against Azure-related maintenance and single points of failure in a single datacenter, Azure Site Recovery replicates workloads running on physical and virtual machines from a primary site to a secondary location, and Azure Backup is used to provide independent and isolated backups to guard against the unintended destruction of the data on your virtual machines.
Describe Azure high availability and disaster recovery features for Azure Virtual Machines - Training | Microsoft Learn
42) You have an on-premises Microsoft SQL Server 2019 server that hosts 10 databases that is up to date with patching. You are required to replicate all of the databases to the cloud.
Which two options could you recommend? Each correct answer presents a complete solution.
- a SQL Server on Azure Virtual Machines instance
- an Azure SQL Database
- an Azure SQL Database elastic pool
- An Azure SQL Managed Instance link
- Configure a backup preference.
- Configure a read write routing URL.
- Configure Connection Timeout=60 for the connection string of App1.
- Configure MultiSubnetFailover=True for the connection string of App1.
- Create an availability group listener.
The connection string needs MultiSubnetFailover=True to be able to connect to IPS in a different subnet, and the listener needs to be created to ensure continuity in the event of a failover.
The connection timeout does not need to be changed, and the backup preference and the read write routing URL do not need to be configured to meet the goal.
High availability, disaster recovery, business continuity - SQL Server on Azure VMs | Microsoft Learn
Plan and implement an high availability and disaster recovery environment - Training | Microsoft Learn
44) You need to perform a manual backup of a database running on an Azure SQL Managed Instance.
Which option should you include in the BACKUP DATABASE statement to perform the backup operation?
a. WITH COPY_ONLY
b. WITH FORMAT
c. WITH INIT
d. WITH NO_TRUNCATE
The WITH COPY_ONLY option must be included in the BACKUP DATABASE statement. The option is mandatory because Azure SQL Managed Instance has built-in automated backups.
WITH NO_TRUNCATE specifies that the transaction log should not be truncated and causes the SQL Server Database Engine to attempt the backup regardless of the state of the database. Consequently, a backup taken with the NO_TRUNCATE option might have incomplete metadata. WITH INIT specifies that all backup sets should be overwritten, but it preserves the media header. If INIT is specified, any existing backup set on that device is overwritten, if conditions permit. WITH FORMAT specifies that a new media set will be created.
45) You have an Azure SQL Managed Instance that contains a database named db1. You have a valid backup of db1.
You need to restore db1 to the point in time of the backupdb1 over the existing database.
Which two actions should you perform? Each correct answer presents part of the solution.
a. Drop db1.
b. Restore db1 from a file.
c. Restore db1 from a URL.
d. Set db1 to single-user mode.
You should drop the existing database first because the existing database cannot be overwritten on the SQL managed instance. You should also restore the database db1 from a URL.
Setting db1 to single-user mode is not allowed on the SQL managed instance, and while the SQL managed instance allows you to perform a manual database restore from a URL, it does not allow you to restore the database from a tape or disk.
Restore a database from a backup - Azure SQL Managed Instance | Microsoft Learn
Back up and restore databases - Training | Microsoft Learn
46) You have a SQL Server on Azure Virtual Machines instance.
During a weekly maintenance task, you discover that a database named db1 has consistency errors.
You plan to restore the database from backups.
Which command should you run before you run the RESTORE command?
a. ALTER DATABASE db1 SET EMERGENCY
b. ALTER DATABASE db1 SET OFFLINE
c. ALTER DATABASE db1 SET SINGLE_USER
d. ALTER DATABASE db1 SET RESTRICTED_USER
You should run the ALTER DATABASE db1 SET SINGLE_USER command, as only one user at a time can access the database. If you specify SINGLE_USER, and another user connects to the database, the ALTER DATABASE command is blocked until all users disconnect from the specified database. This is the command that should be run before the RESTORE command is run.
Running the ALTER DATABASE db1 SET EMERGENCY command would not work because the database is marked as READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role.
By running ALTER DATABASE db1 SET OFFLINE, the database will shut down, and you cannot perform the restore operation. You cannot modify a database while it is offline.
Running the ALTER DATABASE db1 SET RESTRICTED_USER command allows for only members of the db_owner fixed database role dbcreator, and fixed server roles to connect to the database.
ALTER DATABASE SET Options (Transact-SQL) - SQL Server | Microsoft Learn
Back up and restore databases - Training | Microsoft Learn
47) You have a Microsoft SQL Server 2019 instance.
You are configuring a backup maintenance solution.
You need to ensure that the databases can be restored to any point in time.
Which two types of backups should you include? Each correct answer presents part of the solution.
- backup copy-only
- file
- filegroup
- full database
- transaction log
To configure a backup maintenance solution and ensure that the databases can be restored to any point in time, a full database backup and a transaction log backup are required.
A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. A file backup contains all the data in one or more files, and a filegroup backup contains all the data in one or more filegroups.
48) You have two SQL Server on Azure Virtual Machines instances.
You need to configure log shipping for a database named Sales.
You enable the Sales database as a primary server in the log shipping configuration.
What should you do next?
a. Add a secondary server to the log shipping configuration.
b. Configure a monitoring server instance.
c. Configure the Backup Settings for Sales.
d. Create the Sales database on a secondary server.
You should first configure the Backup Settings for the Sales database. You need to specify the network location.
You can only add a secondary server to the log shipping configuration after you have configured the Backup Settings on the primary server. Configuring a monitoring server instance is an optional step, and you can do it only after you have configured the Backup Settings on the primary server. You can decide if the database will be created or restored during the secondary server configuration.
Configure Log Shipping (SQL Server) - SQL Server | Microsoft Learn
Back up and restore databases - Training | Microsoft Learn
49) You have an Always On availability group that contains three Microsoft SQL replicas.
You have a business process that runs on each replica. The process runs every hour and must be started only on the primary replica.
Which view should you query to obtain the necessary information about the state of each replica?
a. sys.availability_groups
b. sys.availabilty_replicas
c. sys.dm_hadr_availability_replica_states
d. sys.dm_hadr_database_replica_cluster_states
The sys.dm_hadr_availability_replica_states view returns a row for each local replica and a row for each remote replica in the same Always On availability group as a local replica. Each row contains information about the state of a given replica. The columns role and role_desc contain information about the description of the replica role.
sys.availability_groups returns a row for each availability group for which the local instance of SQL Server hosts an availability replica. sys.dm_hadr_database_replica_cluster_states returns a row that contains information intended to provide you with insights into the health of the availability databases in the Always On availability groups in each Always On availability group on a Windows Server Failover Cluster (WSFC) cluster. sys.availabilty_replicas returns a row for each availability replica that belongs to any Always On availability group in the WSFC failover cluster.
Monitor availability groups using Transact-SQL (T-SQL) - SQL Server Always On | Microsoft Learn
sys.dm_hadr_availability_replica_states (Transact-SQL) - SQL Server | Microsoft Learn
