NEW QUESTION 1
You administer a Microsoft SQL Server 2014 database named Orders. Orders contains a table named OrderShip that is defined as follows:

A NULL value represents a domestic order. Ninety percent of the values in CountryCode are NULL. Customers require a procedure that will return orders for all customers from a specified country. You create a new procedure:

Performance on this procedure is slow.
You need to alter the schema to optimize this query. Objects created must use a minimum amount of resources.
Which Transact-SQL statement should you use?

• A. CREATE NONCLUSTERED INDEX IX_CountryCode ON Ordership (CountryCode) WHERE CountryCode IS NOT NULL
• B. CREATE STATISTICS ST_CountryCode ON OrderShip (CountryCode) WHERE CountryCodeIS NOT NULL
• C. CREATE CLUSTERED INDEX IX_CountryCode ON OrderShip (CountryCode)
• D. CREATE INDEX IX_CountryCode ON OrderShip (CustomerID) WHERE CountryCode IS NOT NULL

Answer: B

Explanation: Here creating statistics is relevant. The CREATE STATISTICS command creates query optimization statistics on one or more columns of a table, an indexed view, or an external table. For most queries, the query optimizer already generates the necessary statistics for a high-quality query plan; in a few cases, you need to create additional statistics with CREATE STATISTICS or modify the query design to improve query performance.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-statistics-transact-sql

NEW QUESTION 2
You administer a SQL 2012 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 remove the Select permission for UserA on the Regions table. You also need to ensure that UserA can still access all the tables in the Customers schema, including the Regions table, through the Sales role permissions.
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: E

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 3
You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.
You need to track all SELECT statements issued in the Contoso database only by users in a role named Sales. 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: F

Explanation: To audit users in a role use a Database Audit Specification.
References:https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-audit-specification-transact-sql

NEW QUESTION 4
A company has an on-premises Microsoft SQL Server 2021 infrastructure. The storage area network (SAN) that supports the SQL infrastructure has reached maximum capacity.
You need to recommend a solution to reduce on-premises storage use without changing the application. What should you do?

• A. Configure an Express Route connection to Microsoft Azure.
• B. Configure a Microsoft Azure Key Vault.
• C. Configure geo-replication on the SAN.
• D. Configure SQL Server Stretch Database in Microsoft Azure.

Answer: D

Explanation: Stretch warm and cold transactional data dynamically from SQL Server to Microsoft Azure with SQL Server Stretch Database. Unlike typical cold data storage, your data is always online and available to query. Benefit from the low cost of Azure rather than scaling expensive, on-premises storage.
References:
https://docs.microsoft.com/en-us/sql/sql-server/stretch-database/stretch-database?view=sql-server-2021

NEW QUESTION 5
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 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
Background
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.
After reviewing the plan cache you have identified that a large number of simple queries are using parallelism, and that execution plans are not being kept in the plan cache for very long.
You review the properties of the instance (Click the Exhibit button). Exhibit:

You need to set the size of the log files for the tempdb database on SRV1.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
Hot Area:

Answer:

Explanation: The ALTER DATABASE with MODIFY FILE command can make a file size bigger (but not smaller). Example:
ALTER DATABASE AdventureWorks2012 MODIFY FILE
(NAME = test1dat3, SIZE = 200MB); Note: MODIFY FILE
Specifies the file that should be modified. Only one <filespec> property can be changed at a time. NAME must always be specified in the <filespec> to identify the file to be modified. If SIZE is specified, the new size must be larger than the current file size.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/move-a-tdeprotected-database-to-a

NEW QUESTION 8
You administer a Microsoft SQL Server 2014 instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.
The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.
These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
You need to ensure that the minimum amount of data is lost. Which recovery model should the database use?

• A. NORECOVERY
• B. FULL
• C. NO_CHECKSUM
• D. CHECKSUM
• E. Differential
• F. BULK_LOGGED
• G. STANDBY
• H. RESTART
• I. SKIP
• J. Transaction log
• K. DBO ONLY
• L. COPY_ONLY
• M. SIMPLE
• N. CONTINUE AFTER ERROR

Answer: B

Explanation: The full recovery model requires log backups. No work is lost due to a lost or damaged data file. Can recover to a specific point in time, assuming that your backups are complete up to that point in time.

NEW QUESTION 9
You administer a Microsoft SQL Server 2014 database.
You have a SQL Server Agent job instance that runs using the service account. You have a job step within the job that requires elevated privileges.
You need to ensure that the job step can run using a different user account. What should you use?

• A. a schedule
• B. an alert
• C. an operator
• D. a proxy

Answer: D

Explanation: A SQL Server Agent proxy defines the security context for a job step. A proxy provides SQL Server Agent with access to the security credentials for a Microsoft Windows user. Each proxy can be associated with one or more subsystems. A job step that uses the proxy can access the specified subsystems by using the security context of the Windows user. Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.
References:https://technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx

NEW QUESTION 10
You are migrating an on-premises Microsoft SQL Server instance to SQL Server on a Microsoft Azure virtual machine. The instance has 30 databased that consume a total of 2 TB of disk space. The instance sustains more than 30,000 transactions per second.
You need to provision storage for the virtual machine. The storage must be able to support the same load as the on-premises deployment.
Solution: You use drive D on the virtual machine to store the database files. Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: The D drive should only be used for temporary data.

NEW QUESTION 11
You administer a Microsoft SQL Server 2014 instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.
The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.
These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
You need to ensure that your backup will continue if any invalid checksum is encountered. Which backup option should you use?

• A. STANDBY
• B. Differential
• C. FULL
• D. CHECKSUM
• E. BULK_LOGGED
• F. CONTINUE_AFTER_ERROR
• G. SIMPLE
• H. DBO_ONLY
• I. COPY_ONLY
• J. SKIP
• K. RESTART
• L. Transaction log
• M. NO_CHECKSUM
• N. NORECOVERY

Answer: F

Explanation: The CONTINUE_AFTER_ERROR option, of the Transact-SQL BACKUP command, instructs BACKUP to continue despite encountering errors such as invalid checksums or torn pages.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

NEW QUESTION 12
You administer a Windows Azure SQL Database database named Human_Resources. The database contains 2 tables named Employees and SalaryDetails. You add two Windows groups as logins for the server:
You need to grant users access according to the following requirements: What should you do?

• A. Create a database role called Employees.Add CORPEmployees to the db_datareader rol
• B. Add all company employees except HR administrators to the Employees rol
• C. Deny SELECT access to the SalaryDetails table to the Employees role.
• D. Create a database role called HRAdmins.Add all company employees except HR administrators to the db_datareader rol
• E. Add all HR administrators to the HRAdmins rol
• F. Grant SELECT access to the SalaryDetails table to the HRAdmins role.Deny SELECT access to the SalaryDetails table to the db_datareader role.
• G. Create two database roles: Employees and HRAdmin
• H. Add all company employees to the Employees role.Add HR administrators to the HRAdmins rol
• I. Grant SELECT access to all tables except SalaryDetails to the Employees rol
• J. Grant SELECT access to the SalaryDetails table to the HRAdmins rol
• K. Deny SELECT access to the SalaryDetails table to the Employees role.
• L. Create a database role called Employees.Add all HR administrators to the db_datareader rol
• M. Add all company employees to the Employees rol
• N. Grant SELECT access to all tables except the SalaryDetails table to the Employees rol
• O. Deny SELECT access to the SalaryDetails table to the Employees role.

Answer: D

Explanation: Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.
References: https://technet.microsoft.com/en-us/library/ms188629(v=sql.90).aspx

NEW QUESTION 13
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 14
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 enable Dynamic Data Masking on the primary replica.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: SQL Database dynamic data masking does not encrypt the data. Transparent Data Encryption (TDE) would provide a solution.
Note: SQL Database dynamic data masking limits sensitive data exposure by masking it to non-privileged users.
Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer.
References:
https://azure.microsoft.com/en-us/blog/how-to-configure-azure-sql-database-geo-dr-with-azure-key-vault/

NEW QUESTION 15
You administer a Microsoft SQL Server 2014 instance named SQL2012 that hosts an OLTP database of 1 terabyte in size.
The database is modified by users only from Monday through Friday from 09:00 hours to 17:00 hours. Users modify more than 30 percent of the data in the database during the week.
Backups are performed as shown in the following schedule:

The Finance department plans to execute a batch process every Saturday at 09:00 hours. This batch process will take a maximum of 8 hours to complete.
The batch process will update three tables that are 10 GB in size. The batch process will update these tables multiple times.
When the batch process completes, the Finance department runs a report to find out whether the batch process has completed correctly.
You need to ensure that if the Finance department disapproves the batch process, the batch operation can be rolled back in the minimum amount of time. What should you do on Saturday?

• A. Perform a differential backup at 08:59 hours.
• B. Record the LSN of the transaction log at 08:59 hour
• C. Perform a transaction log backup at17:01 hours.
• D. Create a database snapshot at 08:59 hours.
• E. Record the LSN of the transaction log at 08:59 hour
• F. Perform a transaction log backup at08:59 hours.
• G. Create a marked transaction in the transaction log at 08:59 hour
• H. Perform a transaction log backup at 17:01 hours.
• I. Create a marked transaction in the transaction log at 08:59 hour
• J. Perform a transaction log backup at 08:59 hours.

Answer: C

Explanation: References:https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server

NEW QUESTION 16
You plan to deploy an on-premises SQL Server 2014 database to Azure SQL Database. You have the following requirements:
Maximum database size of 500 GB
A point-in-time-restore of 35 days
Maximum database transaction units (DTUs) of 500
You need to choose the correct service tier and performance level. Which service tier should you choose?

• A. Standard S3
• B. Premium P4
• C. Standard SO
• D. Basic

Answer: B

Explanation: You should choose Premium P4. The Premium tier is the highest Azure SQL Database tier offered. This tier is used for databases and application that require the highest level of performance and recovery. The P4 level supports a maximum of 500 DTUs, a maximum database size of 500 GB, and a point-in-time-restore to anypoint in the last 35 days.

NEW QUESTION 17
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 rewrite the queries to use aggregates instead of PIVOT statements. Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: Instead you can add more files to the database.
References: https://www.mssqltips.com/sqlservertip/3088/Explanation:-of-sql-server-io-and-latches/

NEW QUESTION 18
Background
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.
After reviewing the plan cache you have identified that a large number of simple queries are using parallelism, and that execution plans are not being kept in the plan cache for very long.
You review the properties of the instance (Click the Exhibit button). Exhibit:

You need to restore the Reporting database to SRV2. What should you do? To answer, drag the appropriate options to the correct locations. Each option may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:

Answer:

Explanation: Step 2: Create: server certificate
Recreate the server certificate by using the original server certificate backup file.
Note: The password must be the same as the password that was used when the backup was created. Step 3: Restore: Reporting database .mdf file.
-- Attach the database that is being moved.
-- The path of the database files must be the location where you have stored the database files. Example:
CREATE DATABASE [CustRecords] ON
( FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATA
CustRecords.mdf' ),
( FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATA
CustRecords_log.LDF' ) FOR ATTACH ;
GO
From scenario: 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.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/move-a-tdeprotected-database-to-a

NEW QUESTION 19
You have an on-premises SQL Server database named DB1 that contains a table named TB1. TB1 is stretched to Microsoft Azure.
A catastrophic hardware failure occurs on the on-premises SQL server.
You deploy a new on-premises server and restore all databases to the new server. You need to resume Stretch Database operations to Azure.
Which statements should you execute?

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

Answer: B

Explanation: Use ALTER TABLE, not CREATE TABLE.
When you enable Stretch for a table by specifying ON, you also have to specify MIGRATION_STATE = OUTBOUND to begin migrating data immediately, or MIGRATION_STATE = PAUSED to postpone data migration.
Syntax:
<stretch_configuration> ::=
{
SET ( REMOTE_DATA_ARCHIVE
{
= ON ( <table_stretch_options> )
| = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED )
| ( <table_stretch_options> [, ...n] )
}
)
}
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2021

NEW QUESTION 20
You have a Microsoft SQL Server that has a database named DB1. DB1 has a data files on drive E and transaction logs on drive L.
Drive L fails and is replaced.
You need to recover DB1. The solution must minimize data loss.
Which three statements should you execute in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

Answer:

Explanation: ALTER DATABASE '<your_database>' SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO
DBCC CHECKDB ('<your_database>', REPAIR_REBUILD) GO
ALTER DATABASE '<your_database>' SET MULTI_USER GO