NEW QUESTION 1
You have an on-premises Microsoft SQL server that has a database named DB1. DB1 contains several tables that are stretched to Microsoft Azure.
From SQL Server Management Studio (SSMS), a junior database administrator accidentally deletes several rows from the Azure SQL database and breaks the connection to Azure.
You need to resume Stretch Database operations.
Which two stored procedures should you use? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

• A. sys.sp_rda_reconcile_batch
• B. sys.sp_rda_reconcile_indexes
• C. sys.sp_rda_reauthorize_db
• D. sys.sp_rda_reconcile_columns
• E. sys.sp_rda_set_rpo_duration

Answer: CD

Explanation: sys.sp_rda_reauthorize_db restores the authenticated connection between a local database enabled for Stretch and the remote database.
If you have accidentally deleted columns from the remote table, run sp_rda_reconcile_columns to add columns to the remote table that exist in the Stretch-enabled SQL Server table but not in the remote table.

NEW QUESTION 2
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 manage a Microsoft SQL Server environment with several databases.
You need to ensure that queries use statistical data and do not initialize values for local variables.
Solution: You enable the LEGACY_CARDINALITY_ESTIMATION option for the databases. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }
Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. This is equivalent to Trace Flag 9481.
References:https://msdn.microsoft.com/en-us/library/mt629158.aspx

NEW QUESTION 3
You have a database named DB1 that contains a table named Table1. Table1 has 1 billion rows.
You import 10 million rows of data into Table1.After the import, users report that queries take longer than
usual to execute.
You need to identify whether an out-of-date execution plan is causing the performance issue. Which dynamic management view should you use?

• A. sys.dm_xtp_transaction_stats
• B. sys.dm_exec_input_buffer
• C. sys.dm_db_index_operational_stats
• D. sys.dm_db_stats_properties

Answer: C

Explanation: sys.dm_db_index_operational_stats dynamic management function provides us the current low-level I/O, locking, latching, and access method for each partition of the table. This information is really useful to troubleshoot SQL Server performance issues.
Reference:
https://basitaalishan.com/2013/03/19/using-sys-dm_db_index_operational_stats-to-analyse-howindexes-are-utili

NEW QUESTION 4
HOTSPOT
You need to open the firewall ports for use with SQL Server environment. In table below, identify the firewall port that you must use for each service.
NOTE: Make only one selection in each column.

Answer:

Explanation: Report Server: 80
By default, the report server listens for HTTP requests on port 80.

NEW QUESTION 5
You administer a Microsoft SQL Server 2014 database.
You need to ensure that the size of the transaction log file does not exceed 2 GB. What should you do?

• A. Execute sp_configure 'max log size', 2G.
• B. use the ALTER DATABASE...SET LOGFILE command along with the maxsize parameter.
• C. In SQL Server Management Studio, right-click the instance and select Database Setting
• D. Set the maximum size of the file for the transaction log.
• E. in SQL Server Management Studio, right-click the database, select Properties, and then click Files.Open the Transaction log Autogrowth window and set the maximum size of the file.

Answer: B

Explanation: You can use the ALTER DATABASE (Transact-SQL) statement to manage the growth of a transaction log file
To control the maximum the size of a log file in KB, MB, GB, and TB units or to set growth to UNLIMITED, use the MAXSIZE option. However, there is no SET LOGFILE subcommand.
References: https://technet.microsoft.com/en-us/library/ms365418(v=sql.110).aspx#ControlGrowth

NEW QUESTION 6
User report that a query takes a long time to execute. The query has the following wait statistics.

Which resource causes the issue?

• A. processor
• B. disk
• C. blocking
• D. network

Answer: B

Explanation: PAGEIOLATCH Wait time and WaitCount are both high.
One of the most common wait type seen on SQL Server and definitely one that causes a lot of troubles to less experienced database administrators is the PAGEIOLATCH_SH wait type. This is one of those wait types that clearly indicates one thing, but which background and potential causes are much subtler and may lead to erroneous conclusions and worse, incorrect solutions
The Microsoft definition of this wait type is:
Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
References: https://www.sqlshack.com/handling-excessive-sql-server-pageiolatch_sh-wait-types/

NEW QUESTION 7
You deploy a new Microsoft Azure SQL database instance to support a variety of mobile application and public websites. You configure geo-replication with regions in Brazil and Japan.
You need to implement real-time encryption of the database and all backups.
Solution: You use the always Encrypted wizard to encrypt all possible for the tables in the primary instance. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: Always Encrypted does not support geo replication. Transparent Data Encryption (TDE) would provide a solution.
Note: Use the Always Encrypted Wizard to help protect sensitive data stored in a SQL Server database. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to SQL Server.
References:
https://azure.microsoft.com/en-us/blog/how-to-configure-azure-sql-database-geo-dr-with-azure-key-vault/
http://blog.pragmaticworks.com/sql-server-2021-data-masking-and-always-encrypted

NEW QUESTION 8
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 following requirements are met: 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: J

Explanation: Use REVOKE to remove the grant or deny of a permission.
References:https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-

NEW QUESTION 9
You have Microsoft SQL server on a Microsoft Azure virtual machine. The virtual machine has 200 GB of data.
User report a slow response time when querying the database.
You need to identify whether the storage subsystem causes the performance issue. Which performance monitor counter should you view?

• A. Data sec/Write
• B. Avg.disk Read Queue Length
• C. % Disk Read Time
• D. Disk sec/Read

Answer: B

NEW QUESTION 10
You create a new Microsoft Azure subscription.
You need to create a group of Azure SQL databases that share resources. Which cmdlet should you run first?

• A. New-AzureRmAvailabilitySet
• B. New-AzureRmLoadBalancer
• C. New-AzureRmSqlDatabaseSecondary
• D. New-AzureRmSqlElasticPool
• E. New-AzureRmVM
• F. New-AzureRmSqlServer
• G. New-AzureRmSqlDatabaseCopy
• H. New-AzureRmSqlServerCommunicationLink

Answer: D

Explanation: SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single Azure SQL Database server and share a set number of resources (elastic Database Transaction Units (eDTUs)) at a set price. Elastic pools in Azure SQL Database enable SaaS developers to optimize the price performance for a group of databases within a prescribed budget while delivering performance elasticity for each database.
References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool

NEW QUESTION 11
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 12
You have a server named Serverl that is hosted in an Azure virtual machine. Server1 contains the following:
One instance of SQL Server 2021 Enterprise
10 databases
500 stored procedures
You have a database named Database1 that is hosted on Server1.
Database1 contains 100 queries that are executed dynamically from web applications. You plan to remove data from the procedure cache on Database1.
You have the following requirements:
Changes to Database1 must not affect other databases that are hosted on Server1
Changes to Database1 must not affect the performance of queries that are stored in other databases.
The solution must minimize administrative effort.
You need to remove the data from the procedure cache as quickly as possible. What should you do?

• A. Run DBCC FREEPROCCACHE.
• B. Run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE in thecontext of Database 1.
• C. Run DBCC DROPCLEANBUFFERS.
• D. Write a script that iterates through each stored procedure definition and add WITH RECOMPILE to the definition.

Answer: B

Explanation: You should run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE in the
context of Database! This statement lets you change the settings of a database without affecting other databases that are installed on the instance of SQL Server 2021.

NEW QUESTION 13
You administer a Microsoft SQL Server 2014 database. The database is currently configured to log ship to a secondary server.
You are preparing to cut over to the secondary server by stopping log-shipping and bringing the secondary database online. You want to perform a tail-log backup.
You need to leave the primary database in a restoring state.
Which option of the BACKUP LOG command should you use?

• A. NO_TRUNCATE
• B. NORECOVERY
• C. STANDBY
• D. FORMAT

Answer: B

Explanation: It is recommended that you take a tail-log backup in the following scenarios:
* If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the … WITH NORECOVERY option of the BACKUP Transact-SQL statement.
Note: A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server

NEW QUESTION 14
You plan to migrate on-premises Microsoft SQL Server to SQL Server on a Microsoft Azure virtual machine. You need to ensure that the Azure virtual machine can handle the workload.
Which tool should you use for each environment? To answer, drag the appropriate tools to the correct options. Each tool may be used once. More than once, or not at all.

Answer:

Explanation:

NEW QUESTION 15
You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.
You need to collect data for a long period of time to troubleshoot wait statistics when querying Contoso. You also need to ensure minimum impact to the server.
What should you create?

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

Answer: C

Explanation: SQL Server Extended Events has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem.
Extended Events is a light weight performance monitoring system that uses very few performance resources. A SQL Server Extended Events session is created in the SQL Server process hosting the Extended Events
engine.
References:https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events

NEW QUESTION 16
You have Microsoft SQL Server on a Microsoft Azure virtual machine. You create a SQL Server Agent job by using the following statement.

You need to send an email message if the job fails. Which stored procedure should you use?

• A. msd
• B. db
• C. sp_updace_alerc
• D. msdb.dbo.sp_add_jobstep
• E. msdb.dbo.sp_add_notification
• F. msdb.dbo.sp_help_alert

Answer: C

Explanation: To notify an operator of job status through Transact-SQL.
In Object Explorer, connect to an instance of Database Engine. On the Standard bar, click New Query.
-- adds an e-mail notification for the specified alert (Test Alert).
-- This example assumes that Test Alert already exists
-- and that François Ajenstat is a valid operator name. USE msdb ;
GO
EXEC dbo.sp_add_notification
@alert_name = N'Test Alert',
@operator_name = N'François Ajenstat',
@notification_method = 1 ; GO
References:
https://docs.microsoft.com/en-us/sql/ssms/agent/notify-an-operator-of-job-status?view=sql-server-2021

NEW QUESTION 17
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 18
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 are tuning the performance of a virtual machines that hosts a Microsoft SQL Server instance. The virtual machine originally had four CPU cores and now has 32 CPU cores.
The SQL Server instance uses the default settings and has an OLTP database named db1. The largest table in db1 is a key value store table named table1.
Several reports use the PIVOT statement and access more than 100 million rows in table1.
You discover that when the reports run, there are PAGELATCH_IO waits on PFS pages 2:1:1, 2:2:1, 2:3:1, and 2:4:1 within the tempdb database.
You need to prevent the PAGELATCH_IO waits from occurring. Solution: You add more tempdb databases.
Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: From SQL Server’s perspective, you can measure the I/O latency from sys.dm_os_wait_stats. If you consistently see high waiting for PAGELATCH_IO, you can benefit from a faster I/O subsystem for SQL Server.A cause can be poor design of your database - you may wish to split out data located on 'hot pages', which are accessed frequently and which you might identify as the causes of your latch contention. For example, if you have a currency table with a data page containing 100 rows, of which 1 is updated per transaction and you have a transaction rate of 200/sec, you could see page latch queues of 100 or more. If each page latch wait costs just 5ms before clearing, this represents a full half-second delay for each update. In this case, splitting out the currency rows into different tables might prove more performant (if less normalized and logically structured).
References: https://www.mssqltips.com/sqlservertip/3088/Explanation:-of-sql-server-io-and-latches/

NEW QUESTION 19
You have a Microsoft Azure SQL Database server named server1-contoso.database.windows.net in a resource group named RG1.
You need to create an elastic pool.
How should you complete the script? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Answer:

Explanation: Box 1: New-AzureRmSqlServer
Create an Azure SQL Database logical server using the New-AzureRmSqlServer command. A logical server contains a group of databases managed as a group.
Example:
New-AzureRmSqlServer -ResourceGroupName $resourcegroupname `
-ServerName $servername `
-Location $location `etc.
Box 2: New-AzureRmSqlElasticPool
The New-AzureRmSqlElasticPool cmdlet creates an elastic database pool for an Azure SQL Database. Example:
New-AzureRmSqlElasticPool -ResourceGroupName "ResourceGroup01" -ServerName "Server01"
-ElasticPoolName "ElasticPool01" -Edition "Standard"
References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-get-started-powershell https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/new-azurermsqlelasticpool?view=azurermps-6