1) You need to migrate the instance to Azure. The solution must support the existing application code. What should you use?
Answer: SQL Server on Azure Virtual Machines
SQL Server on Azure Virtual Machines supports FileTable. It can be used to migrate the instance to Azure and support the existing application code.
Azure SQL Database serverless and Azure SQL Database Hyperscale are tiers of Azure SQL Database that do not support FileTable. Azure SQL Managed Instance does not support FileTable.
2) 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.
Answer: SQL Server on Azure Virtual Machines
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.
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?
Answer: 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.
You have a fact table that contains 500 million rows. The table has a clustered index. Most queries select data from the current week and rarely select data that is more than a month old. Queries against the table are very slow. You need to recommend a solution that will improve the performance of the majority of the queries against the table. What should you include in the recommendation?
Answer: table partitioning
The data of partitioned tables and indexes are divided into units that might be spread across more than one filegroup in a database or stored in a single filegroup. The partition that contains the most current data does not block any queries that are related to old data, so this will benefit the performance.
Row and page compression changes the internal table storage, but it does not help solve the performance issues. The in-memory table is best for transactional workloads, and the fact table is the analytical workload.
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?
Answer: SQL Server on Azure Virtual Machines & Azure SQL Managed Instance
SQL Server on Azure Virtual Machine supports SQL CLR objects. The objects can access files shares, disks, and folders.
Azure SQL Database serverless is a compute tier for Azure SQL Database that does not support SQL CLR objects.
Azure SQL Database Hyperscale is a service tier of Azure SQL Database that does not support SQL CLR objects.
Azure SQL Managed Instance does not support SQL CLR objects that access file shares or folders of the operating system.
You plan to migrate a 40-TB OLTP database to Azure SQL Database.Which service tier should you recommend for the database?
Answer: Hyperscale vCore service tier
x
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.
You have several databases in an Azure SQL Managed Instance that uses the General Purpose vCore service tier.You discover high latency in some of the database files.What should you do to reduce latency?
Answer : Increase the file size.
You should increase the file size to improve performance. If you have a file that is up to 128 GB, then you can have up to 500 IOPS. If you change the file size to 1 TB, then you can have up to 5,000 IOPS.
You should not add more vCores to the instance in the General Purpose tier, as this will not change the available IOPS. Adding more files to the database also does not help, as the number of available IOPS is calculated per file size, not by the number of files. Changing the service tier will change the type of CPU, the maximum file size, and the amount of memory reserved per CPU.
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?
Answer: 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.
You have a resource group named RG1 that contains a Basic tier Azure SQL Database named db1.You need to update the service tier of db1 to Standard.
Which Azure CLI command should you run?
Answer: az sql db update -g RG1 -s SQL1 -n db1 --edition Standard --service-objective S2
az sql db update -g resourcegroup -s sqlserver -n database --edition Standard --service-objective S2 will update the database to the Standard tier.
az sql db update -g RG1 -s SQL1 -n db1 --edition Premium --service-objective P2 will update the database to the Premium tier.
az sql db copy -g RG1 -s SQL1 -n db1 --dest-name db1 --service-objective S2 will copy the database to a new database in the Standard tier.
az sql elastic-pool update -g RG1 -s SQL1 -n db1 --edition Standard will update an elastic pool service tier to Standard.
You are planning a migration from on-premises Microsoft SQL Server to Azure using Azure Database Migration Service. You need to keep databases online during the migration. Which two services can you use? Each correct answer presents a complete solution.
Answer: Azure SQL Managed Instance, SQL Server on Azure Virtual Machines
SQL Server on Azure Virtual Machines and Azure SQL Managed Instance both support online migration.
Azure SQL Database does not support online migration and Azure CosmosDB is not a relational database.
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?
Answer: 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.
You have an Azure SQL Database named db1.You need to provide access to db1 for a Microsoft Entra ID user.What should you do? Select only one answer.
Answer: In db1, run the CREATE USER statement with the FROM EXTERNAL PROVIDER option.
You should use the CREATE USER statement with FROM EXTERNAL PROVIDER option in db1, as this user should have access to this database.
Using the CREATE USER statement with the FROM LOGIN or WITH PASSWORD option creates a login in the master database. By default, the login does not have any access to the databases.
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?
Answer: 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.
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?
Answer: SQL Server event alert
A SQL Server event alert should be used when a specific error occurs, so this is the correct alert type to use in this case.
You should not use a SQL Server performance condition alert because it is used for performance metrics that are generated by Microsoft SQL Server. A WMI event alert occurs in response to a particular WMI event.
You have an Azure SQL Managed Instance. You need to troubleshoot a SQL Server Agent job that failed. Which two methods can you use? Each correct answer presents a complete solution.
Answer: From Microsoft SQL Server Management Studio (SSMS), use the Job Activity Viewer & Review the SQL Server Agent Error Log file.
The SQL Server Agent Error Log file and the Job Activity Viewer will both have the information about a SQL Server Agent job that failed, which allows you to troubleshoot the issue.
The System Event log does not hold SQL Server Agent information, and the sys.messages catalog view contains one row for each system error or warning that can be returned by the SQL Server Database Engine.
You have a Microsoft SQL Server 2019 instance. You need to ensure that an alert is generated when a SQL error of severity 15 occurs. What should you use?
Answer: SQL Server event alert
A SQL Server event alert will fire on errors of severity 15.
A SQL Server performance alert will fire for a performance condition, Azure SQL Auditing tracks database events and records them, and Extended Events is a lightweight performance monitoring system that does not fire on an error of severity 15.
You have an Azure subscription. You plan to deploy 100 Azure SQL Databases. You need to automate the deployment. The solution must be parameterizable, repeatable, and be able to be versioned in source control. Which three components can you use? Each correct answer presents a complete solution.
Answer: Azure PowerShell scripts & Azure Resource Manager (ARM) templates & Bicep templates.
ARM templates, Bicep templates, and PowerShell scripts can all be parameterized and versioned.
Cloud Shell is an interactive, authenticated, browser-accessible shell, and Azure Policy helps to enforce organizational standards and to assess compliance at-scale.
Automate deployment by using Azure Resource Manager templates and Bicep - Training | Microsoft Learn
You have an Azure subscription that contains a resource group named RG1.You need to create an Azure SQL Managed Instance in RG1 by using PowerShell.
Which cmdlet should you run?
Answer: New-AzSqlInstance
By using the New-AzSqlInstance cmdlet, you can create a new SQL managed instance.
New-AzSQLServer creates a new Microsoft SQL server, New-AzSqlDatabase creates a new Azure SQL Database, Set-AzSqlInstance sets the properties for a SQL managed instance, and Set-AzSqlServer sets the properties for a Microsoft SQL server.
You have an Elastic Job agent that will run tasks on an Azure SQL Database elastic pool. You create an elastic job. You need to ensure that the job runs every hour. Which cmdlet should you run?
Answer: Set-AzSqlElasticJob
Using the Set-AzSqlElasticJob cmdlet updates the elastic job properties. You can set the interval, as well as the start and end date of the job.
The Set-AzSqlElasticJobStep cmdlet updates the elastic job steps. You cannot modify the schedule at the step level. The Set-AzSqlElasticPool cmdlet modifies the properties of an Azure SQL Database elastic pool, and the Set-AzSqlDatabase cmdlet sets the properties of a database or moves an existing database into an elastic pool.
You have an Azure subscription. The subscription contains several Azure SQL Databases that support departmental applications. You plan to use elastic jobs to periodically execute T-SQL-based maintenance tasks against all the databases.
What should you do first?
Answer: Create an Azure SQL Database.
You need to create an Azure SQL Database to keep the elastic job configuration.
The Elastic Job agent can be configured once the database and the elastic job configuration is prepared.
You cannot create Elastic Job targets until you have an agent.
You do not need to create an elastic pool server for this task.
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.
You plan to deploy an Azure SQL Database that will include a table named Table1. Table1 will include the following fields:
StockItemTransactionID (Int)
StockItemID (Int)
TransactionTypeID (Int)
CustomerID (Int)
InvoiceID (Int)
SupplierID (Int)
PurchaseOrderID (Int)
TransactionOccurredWhen (Datetime)
Quantity (Int)
LastEditedBy (Int)
LastEditedWhen (Datetime)
You need to recommend a partitioning method for the table. The solution must meet the following requirements:
The data must be kept for 2 years.
All data older than two years must be removed once a month.
Performance of data removal operations must be maximized.
Which three actions should you perform? Each correct answer presents part of the solution.
Create a partition function for each month.
Create a partition schema for the PRIMARY filegroup.
Create the table by using the partition schema on the TransactionOccurredWhen column.
The data must be removed each month so it will be more efficient to create a partition function for each month. You can only use the PRIMARY filegroup in Azure SQL Database, and the data should be partitioned on datetime.
Also, because the data must be removed each month, it will be less efficient to create a partition for each year. You cannot create filegroups in Azure SQL Database.
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?
Answer: a list 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.
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?
Answer: 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.
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?
Create a partition function, create a partition scheme, and then create a table on the partition scheme.
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.
You have an on-premises Microsoft SQL Server 2019 instance. You need to migrate the instance to an Azure SQL Managed Instance. The solution must minimize downtime. What should you do first?
Answer: Create an Azure SQL Managed Instance with premium series hardware. Create full and log backups with WITH CHECKSUM. Store the backups in an SMB network share.
Premium hardware must be used for an online migration. The backups must also be taken with CHECKSUM enabled.
Standard hardware cannot be used for an online migration and storing the backups in Blob storage creates an Azure snapshot of the database files.
You plan to configure Extended Events monitoring to gather information about queries. You need to add information about the session_id to all events.What should you configure for the extended event session?
Answer: action
An action is a programmatic response or series of responses to an event. Actions are bound to an event, and each event may have a unique set of actions. Actions are global fields. Configuring an action allows you to add the required information to all events.
Targets process events, predicates are a set of logical rules used to evaluate events when they are processed, and a package is a container for Microsoft SQL Server Extended Events objects.
You have an Azure SQL Database. Users report poor performance of the database overnight. You need to identify the cause of the performance issue. What should you do?
Answer: Use Query Store to investigate the metrics.
Using Query Store will enable a historical investigation, which will allow you to look at the metrics to identify the cause of the poor performance.
sys.dm_exec_requests returns information about currently running requests, but it does not allow you to look into past performance issues. Auditing for Azure SQL Database tracks database events. A SQL Server Profile trace will gather information about current activity, but it does not allow you to do a historical investigation of the issue.
You have an Azure SQL Database. You need to troubleshoot blocking and deadlocking performance issues. Which two features can you use? Each correct answer presents a complete solution.
Answer: Extended Events and SQL Insights
Extended Events and SQL Insights can both capture blocking and deadlocking metrics that can be used to investigate and troubleshoot the performance issues.
Query Store does not capture blocking and deadlocking metrics, Azure SQL Defender surfaces database vulnerabilities, and an Azure SQL Database ledger incrementally captures the state of the database.
You have an Azure SQL Managed Instance that hosts 100 databases. You need to monitor the instance for database performance degradation and provide root cause analysis of issues. The solution must minimize administrative effort. What should you use?
Answer: Intelligent Insights
Intelligent Insights uses Azure built-in intelligence to provide monitoring and root cause analysis.
Extended Events is a lightweight performance monitoring system, but it does not provide root cause analysis. An Azure SQL Database ledger incrementally captures the state of the database. Auditing for Azure SQL Database tracks database events.
You have an Azure SQL Managed Instance. You discover a high number of blocking queries during peak business hours. You need to investigate the root cause of the blocking queries. Which two dynamic management views should you query? Each correct answer presents a complete solution.
Answer: sys.dm_exec_requests &sys.dm_os_waiting_tasks
You should query the sys.dm_exec_requests view to get the blocking session ID information. This view returns information about each request that is executing in Microsoft SQL Server. You should also use sys.dm_os_waiting_tasks because the information is related to current connections and sessions. There is blocking session ID information that can be used to identify the root of the issue.
The sys.dm_exec_sessions_wait_stats view presents historical information about the waits that occurred on the server, and the sys.dm_exec_query_stats view returns aggregate performance statistics for cached query plans in SQL Server.
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?
Answer: 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.
