NEW QUESTION 1
You administer a SQL Server 2014 server that contains a database named SalesDB. SalesDb contains a
schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to ensure that the Sales role, including UserA, is disallowed to select from any of the tables in the Customers schema.
Which Transact-SQL statement should you use?

• A. REVOKE SELECT ON Schema::Customers FROM UserA
• B. DENY SELECT ON Object::Regions FROM UserA
• C. EXEC sp_addrolemember 'Sales', 'UserA'
• D. DENY SELECT ON Object::Regions FROM Sales
• E. REVOKE SELECT ON Object::Regions FROM UserA
• F. DENY SELECT ON Schema::Customers FROM Sales
• G. DENY SELECT ON Schema::Customers FROM UserA
• H. EXEC sp_droprolemember 'Sales', 'UserA'
• I. REVOKE SELECT ON Object::Regions FROM Sales
• J. REVOKE SELECT ON Schema::Customers FROM Sales

Answer: F

Explanation: Use SQL Data Warehouse or Parallel Data WarehouseGRANT and DENY statements to grant or deny a permission (such as UPDATE) on a securable (such as a database, table, view, etc.) to a security principal (a login, a database user, or a database role).
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-warehouse-

NEW QUESTION 2
You use a Microsoft Azure SQL database as a data warehouse. The database is in the Standard service tier and has 400 elastic database throughput units (eDTUs).
You load data to the database by using Azure Data Factory. You need to reduce the amount of time it takes to load the data.
Solution: You move the database to a Premium database pool that has 125 eDTUs. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: We need at least 400 eDTUs.

NEW QUESTION 3
You have Microsoft SQL Server on a Microsoft azure virtual machine that has 12 databases. All database files are in the same Azure Blob storage account.
You need to receive an email notification if I/O operations to the database files exceed 800 MB/s for more than five minutes.
Solution: You run the Add-AzureRmMetricAlertRule cmdlet and specify the –MetricName ‘Network Out’ parameter.
Does this meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 4
You administer two instances of Microsoft SQL Server 2014. You deploy an application that uses a database on the named instance.
The application is unable to connect to the database on the named instance. You need to ensure that the application can connect to the named instance. What should you do?

• A. Configure the application as data-tiered.
• B. Open port 1433 on the Windows firewall on the server.
• C. Configure the named SQL Server instance to use an account that is a member of the Domain Admins group.
• D. Start the SQL Server Browser Service.

Answer: D

Explanation: The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:
References: https://technet.microsoft.com/en-us/library/ms181087(v=sql.105).aspx

NEW QUESTION 5
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have Microsoft SQL Server on a Microsoft Azure virtual machine that has a database named DB1. You discover that DB1 experiences WRITE_LOG waits that are longer than 50 ms.
You need to reduce the WRITE_LOG wait time.Solution: Add additional log files to tempdb. Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: In SQL Server, if we have a transactional based system and find a high WRITELOG wait type this is a performance bottleneck and can cause the transaction log file to grow rapidly and frequently.
It is being recommended to SQL server users that they must archive the log files on a separate disk for getting better performance.
References: https://atdhebuja.wordpress.com/2021/06/20/resolving-sql-server-transaction-log-waits/

NEW QUESTION 6
DRAG DROP
A new Azure Active Directory security principal named ReportUser@contoso.onmicrosoft.com should have access to select all current and future objects in the Reporting database. You should not grant the principal any other
permissions. You should use your Active Directory Domain Services (AD DS) account to authenticate to the Azure SQL database.
You need to create the new security principal.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Answer:

Explanation: Step 1:
To provision an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database (here the Reporting database) with an Azure AD identity (not with a SQL Server account) that has access to the database.
Step 2: CREATE USER ... FROM EXTERNAL PROVIDER
To create an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity, as a user with at least the ALTER ANY USER permission. Then use the following Transact-SQL syntax:
CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;
Step 3:
Grant the proper reading permissions.
References:https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad- authentication

NEW QUESTION 7
You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01. You need to prevent users from disabling server audits in Server01.
What should you create?

• A. A Database Audit Specification
• B. A Policy
• C. An Alert
• D. A SQL Profiler Trace
• E. A Resource Pool
• F. An Extended Event session
• G. A Server Audit Specification

Answer: B

Explanation: Writing to the Windows Security log requires the SQL Server service account to be added to the Generate security audits policy. By default, the Local System, Local Service, and NetworkService are part of this policy. This setting can be configured by using the security policy snap-in (secpol.msc). Additionally, the Audit object access security policy must be enabled for both Success and Failure.
References:https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-

NEW QUESTION 8
A company uses several Microsoft Azure elastic pools with Azure SQL Database instances.
You have two pools named Pool1 and Pool2. Pool2 is near maximum capacity and cannot accommodate the database move.
You need to move the database from Pool1 to Pool2.
Which PowerShell cmdlets should you run? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Answer:

Explanation: References:
https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqlelasticpool?view=azurermps-6.1 https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqldatabase?view=azurermps-6.12.

NEW QUESTION 9
HOTSPOT
You need to resolve the identified issues.
Use the drop-down menus to select the answer choice that answers each question based on the information presented in the graphic.

Answer:

Explanation: From exhibit we see:
Cost Threshold of Parallelism: 5 Optimize for Ad Hoc Workloads: false
Max Degree of Parallelism: 0 (This is the default setting, which enables the server to determine the maximum degree of parallelism. It is fine.)
Locks: 0
Query Wait: -1
Box 1: Optimize for Ad Hoc Workload
Change the Optimize for Ad Hoc Workload setting from false to 1/True.
The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

NEW QUESTION 10
You create an availability group that has replicas named HA/Server01 and HA/Server02. Currently, HA/Server01 is the primary replica.
You have multiple queries that read data and produce reports from the database.
You need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica. What should you do?

• A. Set the Availability Mode property of HA/Server02 to Asynchronous commit.
• B. Set the Readable Secondary property of HA/Server02 to Read-intent only.
• C. Set the Connections in Primary Role property of HA/Server01 to Allow read/write connections.
• D. Set the Availability Mode property of HA/Server01 to Asynchronous commit.

Answer: B

Explanation: To set up a readable secondary replica, you first create an availability group. Then you add replicas. You can choose either Yes or Read-intent only options.

References: http://msdn.microsoft.com/en-us/library/jj542414.aspx

NEW QUESTION 11
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets stated goals.
Your company plans to use Microsoft Azure Resource Manager templates for all future deployments of SQL Server on Azure virtual machines.
You need to create the templates.
Solution: You use Visual Studio to create a JSON template that defines the deployment and configuration settings for the SQL Server environment.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: Azure Resource Manager template consists of JSON, not XAML, and expressions that you can use to construct values for your deployment.
A good JSON editor can simplify the task of creating templates.
Note: In its simplest structure, an Azure Resource Manager template contains the following elements:
{
"$schema": "http://schema.management.azure.com/schemas/2015-01- 01/deploymentTemplate.json#",
"contentVersion": "", "parameters": { },
"variables": { },
"resources": [ ],
"outputs": { }
}
References:https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-group-authoring-templates

NEW QUESTION 12
You have Microsoft SQL Server on a Microsoft Azure virtual machine.
You suspect that the current SQL Server indexes cause queries to execute slowly.
You need to identify which indexes must be created to reduce the query execution time.
Which three dynamic management views should you use? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

• A. sys.dm_db_index_physical_stats
• B. sys.dm_db_missing_index_group_stats
• C. sys.indexes
• D. sys.dm_db_index_usage_stats
• E. sys.dm_db_missing_index_groups
• F. sys.dm_db_index_operational_stats
• G. sys.dm_db_missing_index_details
• H. sys.sysindexkeys

Answer: BEG

Explanation: The missing indexes feature consists of the following components:
A set of dynamic management objects that can be queried to return information about missing indexes.
The Missing Indexes element in XML Showplans, which correlate indexes that the query optimizer considers missing with the queries for which they are missing.
Dynamic Management Objects
After running a typical workload on SQL Server, you can retrieve information about missing indexes by querying the dynamic management objects listed in the following table. These dynamic management objects are stored in the master database.
sys.dm_db_missing_index_group_stats
Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes.
sys.dm_db_missing_index_groups
Returns information about a specific group of missing indexes, such as the group identifier and the identifiers ofall missing indexes that are contained in that group.
sys.dm_db_missing_index_details
Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.
sys.dm_db_missing_index_columns
Returns information about the database table columns that are missing an index. References: https://technet.microsoft.com/en-us/library/ms345524(v=sql.105).aspx

NEW QUESTION 13
A company runs Microsoft SQL Server 2021 in an on-premises environment. The databases are memory-optimized.
An integrity check of a database has failed.
You need to ensure that the data is healthy and passes an integrity check. What should you do?

• A. Run the checktable Transact-SQL statement.
• B. Clear the buffer of the database.
• C. Restore from a verified backup.
• D. Run the cleantable Transact-SQL statement.

Answer: C

Explanation: To verify the integrity of the on-disk checkpoint files, perform a backup of the MEMORY_OPTIMIZED_DATA filegroup.

NEW QUESTION 14
You have an on-premises database.
You plan to migrate the database to Microsoft SQL Server on a Microsoft Azure virtual machine.
You move the database files to Azure.
You need to attach the database files to the SQL Server instance on the virtual machine. The solution must ensure that you can run file snapshot backups.
How should you complete the statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Answer:

Explanation: References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-sql-server-transact-sql

NEW QUESTION 15
You are designing a Windows Azure SQL Database for an order fulfillment system. You create a table named Sales.Orders with the following script.

Each order is tracked by using one of the following statuses:
Fulfilled
Shipped
Ordered
Received
You need to design the database to ensure that that you can retrieve the following information:
The current status of an order
The previous status of an order.
The date when the status changed.
The solution must minimize storage.
More than one answer choice may achieve the goal. Select the BEST answer.

• A. To the Sales.Orders table, add three columns named Status, PreviousStatus and ChangeDat
• B. Update rows as the order status changes.
• C. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Statu
• D. Insert new rows into the table as the order status changes.
• E. Implement change data capture on the Sales.Orders table.
• F. To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate.Update the value of each column from null to the appropriate date as the order status changes.

Answer: A

Explanation: This stores only the minimal information required.

NEW QUESTION 16
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets stated goals.
You have a mission-critical application that stores data in a Microsoft SQL Server instance. The application runs several financial reports. The reports use a SQL Server-authenticated login named Reporting_User. All queries that write data to the database use Windows authentication.
Users report that the queries used to provide data for the financial reports take a long time to complete. The queries consume the majority of CPU and memory resources on the database server. As a result, read-write queries for the application also take a long time to complete.
You need to improve performance of the application while still allowing the report queries to finish.
Solution: You create a snapshot of the database. You configure all report queries to use the database snapshot.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: Use a Resource Governor instead.
References:https://msdn.microsoft.com/en-us/library/bb933866.aspx

Topic 4, automobile parts Case Study 1Background
You manage the Microsoft SQL Server environment for a company that manufactures and sells automobile parts.
The environment includes the following servers: SRV1 and SRV2. SRV1 has 16 logical cores and hosts a SQL Server instance that supports a mission-critical application. The application has approximately 30,000 concurrent users and relies heavily on the use of temporary tables.
The environment also includes the following databases: DB1, DB2, and Reporting. The Reporting database is protected with Transparent Data Encryption (TDE). You plan to migrate this database to a new server. You detach the database and copy it to the new server.
You are performing tuning on a SQL Server database instance. The application which uses the database was written using an object relationship mapping (ORM) tool which maps tables as objects within the application code. There are 30 stored procedures that are regularly used by the application.
Exhibit

NEW QUESTION 17
You manage an on-premises Microsoft SQL server that has a database named DB1. An application named App1 retrieves customer information for DB1.
Users report that App1 takes an unacceptably long time to retrieve customer records. You need to find queries that take longer than 400 ms to run.
Which statement should you execute?
A)

B)

C)

D)

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: B

Explanation: Total_worker_time: Total amount of CPU time, reported in microseconds (but only accurate to milliseconds), that was consumed by executions of this plan since it was compiled.

NEW QUESTION 18
You plan to install Microsoft SQL Server 2014 for a web hosting company.
The company plans to host multiple web sites, each supported by a SQL Server database.
You need to select an edition of SQL Server that features backup compression of databases, basic data integration features, and low total cost of ownership.
Which edition should you choose?

• A. Express Edition with Tools
• B. Standard Edition
• C. Web Edition
• D. Express Edition with Advanced Services

Answer: B

NEW QUESTION 19
You administer a Microsoft SQL Server 2014 server. One of the databases on the server supports a highly active OLTP application.
Users report abnormally long wait times when they submit data into the application.
You need to identify which queries are taking longer than 1 second to run over an extended period of time. What should you do?

• A. use SQL Profiler to trace all queries that are processing on the serve
• B. Filter queries that have a Duration value of more than 1,000.
• C. Use sp_configure to set a value for blocked process threshol
• D. Create an extended event session.
• E. Use the Job Activity monitor to review all processes that are actively runnin
• F. Review the Job History to find out the duration of each step.
• G. Run the sp_who command from a query window.
• H. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.

Answer: A

NEW QUESTION 20
HOTSPOT
You need to create the contosodb1 database.
How should you complete the Azure PowerShell command? To answer, select the appropriate Azure PowerShell segments in the answer area.

Answer:

Explanation: Box 1: New-AzureRmSqlDatabase
New-AzureRmSqlDatabase creates a database or an elastic database.
New-AzureRmSqlDatabase is a command with the Azure Resource Manager (AzureRM) module.Azure Resource Manager enables you to work with the resources in your solution as a group.