NEW QUESTION 1
You have a Microsoft SQL Server instance that has a database named DB1. DB1 has data files on drive E and transaction logs on drive L.
You perform full backups of DB1 daily and transaction log backups hourly. Drive E fails and is replaced.
You need to recover DB1 and prevent any data loss.
Which four 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.
Select and Place:

Answer:

Explanation: Section: Deploy and migrate applications Step 1: Perform a tail-log backup.
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.
Step 2: Restore a full backup.
Backups must be restored in the order in which they were created. Before you can restore a particular transaction log backup, you must first restore the following previous backups without rolling back uncommitted transactions, that is WITH NORECOVERY:
The full database backup and the last differential backup, if any, taken before the particular transaction log backup.
Step 3: Restore the log backups.
Log backups must be applied in the sequence in which they were created, without any gaps in the log chain. Step 4: Restore the tail-log backups.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-transaction-log-backup-sqlser https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server

NEW QUESTION 2
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 3
Your company has many Microsoft SQL Server instances hosted in a data center. You also manage five Microsoft Azure SQL Database instances that are hosted on a single server in Azure.
You need to minimize costs associated with Azure resources while maintaining the current performance levels of each Azure SQL Database instance.
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: 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 at a set price.
References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool

NEW QUESTION 4
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 5
You are deploying a Microsoft SQL Server database that will support a mixed OLTP and OLAP workload. The target virtual machine has four CPUs.
You need to ensure that reports do not use all available system resources. What should you do?

• A. Enable Auto Close.
• B. Increase the value for the Minimum System Memory setting.
• C. Set MAXDOP to half the number of CPUs available.
• D. Increase the value for the Minimum Memory per query setting.

Answer: C

Explanation: When an instance of SQL Server runs on a computer that has more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution.

NEW QUESTION 6
You administer a Microsoft SQL Server 2021 instance.
You need to configure a new database to support FILETABLES. What should you do? Choose all that apply.

• A. Disable FILESTREAM on the Database.
• B. Enable FILESTREAM on the Server Instance.
• C. Configure the Database for Partial Containment.
• D. Create a non-empty FILESTREAM file group.
• E. Enable Contained Databases on the Server Instance.
• F. Set the FILESTREAM directory name on the Database.

Answer: BDF

Explanation: References:
https://docs.microsoft.com/en-us/sql/relational-databases/blob/enable-the-prerequisites-for-filetable

NEW QUESTION 7
You administer a Microsoft SQL Server 2014 failover cluster that contains two nodes named Node A and Node B. A single instance of SQL Server is installed on the cluster.
An additional node named Node C has been added to the existing cluster.
You need to ensure that the SQL Server instance can use all nodes of the cluster. What should you do?

• A. Run the New SQL Server stand-alone installation Wizard on Node C.
• B. Run the Add Node to SQL Server Failover Cluster Wizard on Node C.
• C. Use Node B to install SQL Server on Node C.
• D. Use Node A to install SQL Server on Node C.

Answer: B

Explanation: To add a node to an existing SQL Server failover cluster, you must run SQL Server Setup on the node that is to be added to the SQL Server failover cluster instance. Do not run Setup on the active node.
The Installation Wizard will launch the SQL Server Installation Center. To add a node to an existing failover cluster instance, click Installation in the left-hand pane. Then, select Add node to a SQL Server failover cluster.
References:
http://technet.microsoft.com/en-us/library/ms191545.aspx

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

NEW QUESTION 9
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 a GS- Series VM and store the tempdb database on attached Premium storage. Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: For VMs that support Premium Storage (DS-series, DSv2-series, and GS-series), we recommend storing TempDB on a disk that supports Premium Storage with read caching enabled. There is one exception to this recommendation; if your TempDB usage is write-intensive, you can achieve higher performance by storing TempDB on the local D drive, which is also SSD-based on these machine sizes.
References:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performan

NEW QUESTION 10
You plan to deploy a Microsoft SQL Server database that will use FILESTREAM. The database will store 4 TB of FILESTREAM data on a single Windows partition.
You need to configure the hard disk that will support the FILESTREAM data. The solution must provide the fastest read and write access to the data.
How should you configure the disk? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Answer:

Explanation: File System: NTFS
8.3 filename support: Disabled Indexing: Disabled
NTFS is required.
Disable generation of 8.3 names on all NTFS volumes used for FILESTREAM data storage.
Check that search indexing is not enabled on FILESTREAM volumes, under the Volume Properties window, unchecking the “Allow files on this drive to have contents indexed in addition to file properties” box.
References:
https://blogs.msdn.microsoft.com/blogdoezequiel/2011/02/11/best-practices-on-filestreamimplementations/

NEW QUESTION 11
You administer a Microsoft SQL Server 2014 failover cluster.
You need to ensure that a failover occurs when the server diagnostics returns query_processing error. Which server configuration property should you set?

• A. SqlOumperDumpFlags
• B. FailureConditionLevel
• C. HealthCheckTimeout
• D. SqlDumperDumpPath

Answer: B

Explanation: Use the FailureConditionLevel property to set the conditions for the Always On Failover Cluster Instance (FCI) to fail over or restart.
The failure conditions are set on an increasing scale. For levels 1-5, each level includes all the conditions from the previous levels in addition to its own conditions.
Note: The system stored procedure sp_server_diagnostics periodically collects component diagnostics on the SQL instance. The diagnostic information that is collected is surfaced as a row for each of the following components and passed to the calling thread.
The system, resource, and query process components are used for failure detection. The io_subsytem and events components are used for diagnostic purposes only.
References:https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/configure-failurecondition

NEW QUESTION 12
You administer all the deployments of Microsoft SQL Server 2014 in your company. You have two servers in the same data center that hosts your production database.
You need to ensure that the database remains available if a catastrophic server failure or a disk failure occurs. You also need to maintain transactional consistency of the data across both servers.
You need to achieve these goals without manual intervention. Which configuration should you use?

• A. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
• B. SQL Server that includes an application database configured to perform transactional replication
• C. Two servers configured in the same data centerA primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby
• D. Two servers configured in different data centersSQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
• E. Two servers configured in the same data centerSQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary
• F. Two servers configured in different data centersSQL Server Availability Group configured in Asynchronous-Commit Availability Mode
• G. SQL Server that includes an application database configured to perform snapshot replication
• H. Two servers configured on the same subnetSQL Server Availability Group configured in Synchronous-Commit Availability Mode

Answer: H

Explanation: Always On availability groups supports two availability modes—asynchronous-commit mode and synchronous-commit mode
Synchronous-commit mode emphasizes high availability over performance, at the cost of increased transaction latency.
References:https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-mode

NEW QUESTION 13
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 set the value of the MAXDOP parameter to 2.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: When an instance of SQL Server runs on a computer that has more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism (MAXDOP) option to limit the number of processors to use in parallel plan execution.
References:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-paralleli

NEW QUESTION 14
You administer a Microsoft SQL Server 2014 instance. After a routine shutdown, the drive that contains tempdb fails.
You need to be able to start the SQL Server. What should you do?

• A. Modify tempdb location in startup parameters.
• B. Start SQL Server in minimal configuration mode.
• C. Start SQL Server in single-user mode.
• D. Configure SQL Server to bypass Windows application logging.

Answer: B

Explanation: If you have configuration problems that prevent the server from starting, you can start an instance of Microsoft SQL Server by using the minimal configuration startup option.
When you start an instance of SQL Server in minimal configuration mode, note the following: Only a single user can connect, and the CHECKPOINT process is not executed.
Remote access and read-ahead are disabled. Startup stored procedures do not run.
tempdb is configured at the smallest possible size.
References:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/start-sql-server-with-minimal-configur

NEW QUESTION 15
You administer a Microsoft SQL Server 2014 Enterprise Edition server that uses 64 cores.
You discover performance issues when large amounts of data are written to tables under heavy system load. You need to limit the number of cores that handle I/O.
What should you configure?

• A. Processor affinity
• B. Lightweight pooling
• C. Max worker threads
• D. I/O affinity

Answer: D

Explanation: The affinity Input-Output (I/O) mask Server Configuration Option.
To carry out multitasking, Microsoft Windows 2000 and Windows Server 2003 sometimes move process threads among different processors. Although efficient from an operating system point of view, this activity can reduce Microsoft SQL Server performance under heavy system loads, as each processor cache is repeatedly reloaded with data. Assigning processors to specific threads can improve performance under these conditions by eliminating processor reloads; such an association between a thread and a processor is called processor affinity.
References:
http://msdn.microsoft.com/en-us/library/ms189629.aspx

NEW QUESTION 16
You administer a single server that contains a Microsoft SQL Server 2014 default instance. You plan to install a new application that requires the deployment of a database on the server. The application login requires sysadmin permissions.
You need to ensure that the application login is unable to access other production databases. What should you do?

• A. Use the SQL Server default instance and configure an affinity mask.
• B. Install a new named SQL Server instance on the server.
• C. Use the SQL Server default instance and enable Contained Databases.
• D. Install a new default SQL Server instance on the server.

Answer: B

Explanation: References:
https://docs.microsoft.com/en-us/sql/sql-server/install/work-with-multiple-versions-and-instances-of-sql-server

NEW QUESTION 17
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 18
You manage a Microsoft SQL Server environment in a Microsoft Azure virtual machine.
You must enable Always Encrypted for columns in a database. You need to configure the key store provider.
What should you do?

• A. Use the Randomized encryption type
• B. Modify the connection string for applications.
• C. Auto-generate a column master key.
• D. Use the Azure Key Vault.

Answer: D

Explanation: There are two high-level categories of key stores to consider - Local Key Stores, and Centralized Key Stores.
Centralized Key Stores - serve applications on multiple computers. An example of a centralized key store is Azure Key Vault.
Local Key Stores References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-and-storecolumn-master-ke

NEW QUESTION 19
You plan to migrate a Microsoft SQL server instance between physical servers.
You must migrate the metadata associated with the database instance.
You need to ensure that the new instance retains the existing jobs and alerts. Solutions: You restore the msdb database.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/msdb-database?view=sql-server-2021

NEW QUESTION 20
You have Microsoft SQL Server on a Microsoft Azure virtual machine. The virtual machine has a database named DB1. DB1 contains a table named Table1 that has 4 billion rows.
Users report that a query using Table1 takes longer than expected to execute.
You review the execution plan for the query and discover that the expected number of returned rows is one, while the actual number of returned rows is 1 million.
You need to reduce the amount of time it takes for the query to execute. The solution must prevent additional performance issues from being introduced.
Hot Area:

Answer:

Explanation: When you set the AUTO_CREATE_STATISTICS option on, the Query Optimizer creates statistics on individual columns used in a predicate, if these statistics are not already available. These statistics are necessary to generate the query plan.
References:
https://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statisticsoptions/