NEW QUESTION 1
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 2
You use a Microsoft SQL Server 2014 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit.
(Click the Exhibit button.)

You write the following Transact-SQL query:

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail.
You need to improve the performance of the query. What should you do?

• A. Use a FORCESCAN hint in the query.
• B. Add a clustered index on SalesOrderId in SalesOrderHeader.
• C. Use a FORCESEEK hint in the query.
• D. Update statistics on SalesOrderId on both tables.

Answer: D

Explanation: New statistics would be useful.
The UPDATE STATISTICS command updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.
References:
http://msdn.microsoft.com/en-us/library/ms187348.aspx

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 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 5
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 6
You have a Microsoft SQL Server 2014 named SRV2014 that has a single tempdb database file. The tempdb database file is eight gigabytes (GB) in size.
You install a SQL Server 2021 instance named SQL Server 2021 by using default settings. The new instance has eight logical processor cores.
You plan to migrate the databases from SRV2014 to SRV2021.
You need to configure the tempdb database on SRV2021. The solution must minimize the number of future tempdb autogrowth events.
What should you do?

• A. Increase the size of the tempdb datafile to 8 G
• B. In the tempdb database, set the value of the MAXDOP property to8.
• C. Increase the size of the tempdb data files to1 GB.
• D. Add seven additional tempdb data file
• E. In the tempdb database, set the value of the MAXDOP property to8.
• F. Setthe value for the autogrowth setting for the tempdb data file to128megabytes (MB). Add seven additional tempdb data files and set the autogrowth value to128 MB.

Answer: B

Explanation: In an effort to simplify the tempdb configuration experience, SQL Server 2021 setup has been extended to configure various properties for tempdb for multi-processor environments.
1. A new tab dedicated to tempdb has been added to the Database Engine Configuration step of setup workflow.
2. Configuration options: Data Files
* Number offiles – this will default to the lower value of 8 or number of logical cores as detected by setup.
* Initial size – is specified in MB and applies to each tempdb data file. This makes it easier to configure all files of same size. Total initial size is the cumulative tempdb data file size (Number of files * Initial Size) that will be created.
* Autogrowth – is specified in MB (fixed growth is preferred as opposed to a non-linear percentage based growth) and applies to each file. The default value of 64MBwas chosen to cover one PFS interval.
Figure:

References:https://blogs.msdn.microsoft.com/psssql/2021/03/17/sql-2021-it-just-runs-faster-automatic-tempdb-configuration/

NEW QUESTION 7
You use Microsoft Azure Resource Manager to deploy two new Microsoft SQL Server instances in an Azure virtual machine (VM). VM has 28 gigabytes (GB) of memory. The instances are named Instance1 and Instance2, respectively.
The various databases on the instances have the following characteristics:

You run the following Transact-SQL statements:

You need to configure each SQL Server instance to correctly allocate memory. What should you do?

• A. On Instance1, run the following Transact-SQL code: On Instance2, run the following Transact-SQL code:
• B. On Instance1, run the following Transact-SQL code: On Instance2, run the following Transact-SQL code:
• C. On Instance1, run the following Transact-SQL code: On Instance2, run the following Transact-SQL code:
• D. On Instance1, run the following Transact-SQL code: On Instance2, run the following Transact-SQL code:

Answer: D

NEW QUESTION 8
Settings Value VM size D3
Storage Location Drive E Storage type Standard Tempdb location Drive C
The workload on this instance has of the tembdb load.
You need to maximize the performance of the tempdb database.
Solution: You use an AB compute-intensive instance and store the tempdb database in Standard storage. Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: For D-series, Dv2-series, and G-series VMs, the temporary drive on these VMs is SSD-based. If your workload makes heavy use of TempDB (such as temporary objects or complex joins), storing TempDB on the D drive could result in higher TempDB throughput and lower TempDB latency.
References:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performan

NEW QUESTION 9
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 10
You need to create an Elastic Database job to rebuild indexes across 10 Microsoft Azure SQL databases. Which powershell cmdlet should you run?

• A. New-AzureSqlJob
• B. New-AzureWebsiteJob
• C. New-AzureBatchJob
• D. New-ScheduledJobOption
• E. New-JobTrigger

Answer: A

Explanation: The New-AzureSqlJob cmdlet, in the ElasticDatabaseJobs module, creates a job definition to be used for subsequent job runs.
References:
https://docs.microsoft.com/en-us/powershell/module/elasticdatabasejobs/new-azuresqljob?view=azureelasticdbj

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 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 Basic database pool that has 1,600 eDTUs. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: We need the use of a Standard database pool.

NEW QUESTION 13
A company has an on-premises Microsoft SQL Server 2021 environment. All futures databases must meet the following requirements:
The recovery model must be set to simple.
The compatibility level must be set to SQL server 2014 (120).
Your need to configure the SQL server 2021 environment.
In the table below, identify the database you must modify for each requirement.

Answer:

Explanation: Model: Change from full to simple Recovery Model
Newly created user databases use the same recovery model as the model database.
The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. The entire contents of the model database, including database options, are copied to the new database.
Model: Set compatibility level to 120
For all installations of SQL Server, the default compatibility level is set to the version of the Database Engine. Databases are set to this level unless the model database has a lower compatibility level.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/model-database?view=sql-server-2021 https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-se

NEW QUESTION 14
You have been hired as a Database Consultant by ABC.com to design a SQL Server 2014 database solution. You are tasked with designing a scale-out and high-availability SQL Server 2014 Online Transaction
Processing (OLTP) database solution that will maintain copies of data across two server instances.
Your solution must provide scale-out of read operations by distributing the reads from clients across two SQL Server 2014 nodes. The data in both SQL Server nodes needs to be indexed.
What should you include in your solution?

• A. You should include a primary database with scheduled log shipping to the secondary database configured.
• B. You should include two servers configured in an Active-Passive SQL Server 2014 Cluster.
• C. You should include a primary SQL Server 2014 database that uses transactional replication to replicate data to the secondary database.
• D. You should include two servers in an Asynchronous-Commit Availability Mode Availability Group.
• E. You should include two servers in a Synchronous-Commit Availability Mode Availability Group.

Answer: C

Explanation: Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes.
Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.
References:https://msdn.microsoft.com/en-us/library/ms151196(v=sql.110)

NEW QUESTION 15
You administer a Microsoft SQL Server 2014 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

Which Transact-SQL query should you use?

• A. SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers-CustomerIdWHERE Customers.CustomerId = 1FOR XML RAW
• B. SELECT OrderId, OrderDate, Amount, Name, CountryFROM Orders INNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML RAW, ELEMENTS
• C. SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML AUTO
• D. SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId= 1FOR XML AUTO, ELEMENTS
• E. SELECT Name, Country, OrderId, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= FOR XML AUTO
• F. SELECT Name, Country, Crderld, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= FOR XML AUTO, ELEMENTS
• G. SELECT Name AS `@Name', Country AS `@Country', OrderId, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML PATH (`Customers')
• H. SELECT Name AS `Customers/Name', CountryAS `Customers/Country', OrderId, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= 1FOR XML PATH (`Customers'

Answer: G

NEW QUESTION 16
You manage an on-premises, multi-tier application that has the following configuration:
Two SQL Server 2012 databases named SQL1 and SQL2
Two application servers named AppServer1 and AppServer2 that run IIS You plan to move your application to Azure.
You need to ensure that during an Azure update cycle or a hardware failure, the application remains available.
Which two deployment configurations should you implement? Each correct answer presents part of the solution.

• A. Deploy AppServer1 and AppServer2 in a single availability set.
• B. Deploy all servers in a single availability set.
• C. Deploy SQL1 and AppServer1 in a single availability set.
• D. Deploy SQL2 and AppServer2 in a single availability set.
• E. Deploy SQL1 and SQL2 in a single availability set.

Answer: AE

Explanation: You should deploy AppServerl and AppServer2 in a single availability set. You should deploy SQL1 and SQL2 in a single availability set.
Note: Using availability sets allows you to build in redundancy for your Azure services. By grouping related virtual machines and services (tiers) into an availability set (in this case, deploying both of your databases into an availability set), you ensure that if there is a planned or unplanned outage, your services will remain available. At the most basic level, virtual machines in an availability set are put into a different fault domain and update domain. An update domain allows virtual machines to have updates installed and then the virtual machines are rebooted together.
If you have two virtual machines in an availability set, each in its own update domain, a rebooting of one server does not bring down all of the servers in a given tier. A fault domain operates in the same manner, so if there is a physical problem with a server, rack, network, or other service, both machines are separated, and services will continue.

NEW QUESTION 17
You administer a Microsoft SQL Server 2014 database.
You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements: CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword1!'
CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate'; BACKUP CERTIFICATE TDE_Certificate TO FILE = ''d:TDE_Certificate.cer' WITH PRIVATE KEY (FILE = 'D:TDE_Certificate.key',
ENCRYPTION BY PASSWORD = 'MyPassword1!'); CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
ALTER DATABASE Orders SET ENCRYPTION ON;
You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location.
A hardware failure occurs and so a new server must be installed and configured.
After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the database.
You need to be able to restore the database.
Which Transact-SQL statement should you use before attempting the restore?

• A. ALTER DATABASE Master SET ENCRYPTION OFF;
• B. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:TDE_Certificate.cer'WITH PRIVATE KEY (FILE = 'D:TDE_Certificate.key',DECRYPTION BY PASSWORD = 'MyPassword1!');
• C. CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate'; USE Orders;CREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_256ENCRYPTIONBY SERVER CERTIFICATE TDE_Certificate;
• D. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:TDE_Certificate.cer';

Answer: B

Explanation: The CREATE CERTIFICATE command adds a certificate to a database in SQL Server. Creating a certificate from a file
The following example creates a certificate in the database, loading the key pair from files. Code
Copy
USE AdventureWorks2012; CREATE CERTIFICATE Shipping11
FROM FILE = 'c:ShippingCertsShipping11.cer'
WITH PRIVATE KEY (FILE = 'c:ShippingCertsShipping11.pvk', DECRYPTION BY PASSWORD = 'sldkflk34et6gs%53#v00');
GO
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql

NEW QUESTION 18
You are the administrator of a Microsoft SQL Server 2014 server.
Some applications consume significant resources. You need to manage the server workload by restricting resource-intensive applications
You need to dynamically limit resource consumption. What should you do?

• A. Configure Resource Pools, Workload Groups, and Classifier Function, and then enable the Resource Governor
• B. Set up Service Broker to ensure that application are not allowed to consume more than the specified amount of resource
• C. Create a new rule for each application that sets the resource limit allowed
• D. Create a new plan Guide with a Scope Type of sql and define the resource limits for each application

Answer: A

Explanation: In the SQL Server Resource Governor, a resource pool represents a subset of the physical resources of an instance of the Database Engine. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use within the resource pool. Each resource pool can contain one or more workload groups. When a session is started, the Resource Governor classifier assigns the session to a specific workload group, and the session must run using the resources assigned to the workload group.
References:https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor-resou

NEW QUESTION 19
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-