NEW QUESTION NO: 70
DRAG DROP
You use SQL Server 2014 Enterprise Edition.
Your database contains a partitioned table named AuditData. AuditData is partitioned by year. Partition 1 contains data from the year 2010 and prior.
Management has decided to archive all AUDITDATA records from 2010 and prior.
Management wants the records to be removed from the database entirely and provided to the backup team as a zipped text file. The data must no longer reside in the database.
There is very little tolerance for performance degradation in your environment. You need to remove all
2010 and prior data from the AuditData table by using the least amount of system resources possible.
Develop the solution by selecting and arranging the required SQL actions in the correct order.
You may not need all of the actions.
Select and Place:

Answer: 

Explanation/Reference:
Explanation:
Note:
- Create a new partitioned table with the partition function you want, and then insert the data from the old table into the new table by using an INSERT INTO...SELECT FROM statement.
- SPLIT RANGE ( boundary_value )
Adds one partition to the partition function. boundary_value determines the range of the new partition, and must differ from the existing boundary ranges of the partition function. Based on boundary_value, the Database Engine splits one of the existing ranges into two.
Of these two, the one where the new boundary_value resides is considered the new partition.
- BCP can be used top produce the zipped text file.
- Example: plitting a partition of a partitioned table or index into two partitions The following example creates a partition function to partition a table or index into four partitions.
ALTER PARTITION FUNCTION splits one of the partitions into two to create a total of five partitions.
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );
GO
-Split the partition between boundary_values 100 and 1000
-to create two partitions between boundary_values 100 and 500
--and between boundary_values 500 and 1000.
ALTER PARTITION FUNCTION myRangePF1 ()
SPLIT RANGE (500);

NEW QUESTION NO: 71
Your company has offices in Seattle and Montreal.
The network contains two servers named Server1 and Server2 that have SQL Server 2012 installed.
Server1 is located in the Seattle office. Server2 is located in the Montreal office. The latency of the WAN link between the Montreal office and the Seattle office is more than 200 ms.
You plan to implement an AlwaysOn availability group on both servers.
You need to recommend a failover type for the availability group. What should you recommend?
A. Synchronous manual failover
B. Synchronous automatic failover
C. Asynchronous automatic failover
D. Asynchronous manual failover
Answer: D

NEW QUESTION NO: 72
You administer all the deployments of Microsoft SQL Server 2016 in your company.
You need to ensure that data changes are sent to a non-SQL Server database server in near real time.
You also need to ensure that data on the primary server is unaffected.
Which configuration should you use?
A. SQL Server that includes an application database configured to perform transactional replication
B. Two servers configured in different data centers SQL Server Availability Group configured in AsynchronousCommit Availability Mode
C. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
D. SQL Server that includes an application database configured to perform snapshot replication
E. Two servers configured in the same data center SQL Server Availability Group configured in AsynchronousCommit Availability Mode One server configured as an Active Secondary
F. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous- Commit Availability Mode
G. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
H. Two servers configured in the same data center A primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby
Answer: A
Explanation/Reference:
SQL Server supports the following heterogeneous scenarios for transactional and snapshot replication:
Publishing data from SQL Server to non- SQL Server Subscribers.

Publishing data to and from Oracle has some restrictions.

References: https://docs.microsoft.com/en-us/sql/relational-databases/replication/non-sql/heterogeneous- database-replication

NEW QUESTION NO: 73
HOTSPOT
You are planning the deployment of two new Always On Failover Cluster Instances (FCIs) of Microsoft SQL Server to a single Windows Server Cluster with three nodes. The planned configuration for the cluster is shown in the Server Layout exhibit. (Click the Exhibit button.)

The SAN team has configured storage for the cluster and sent the configuration to you in the email shown in the SAN Team Email exhibit. (Click the Exhibit button.)

Each node of the cluster has identical local storage available as shown in the Local Storage exhibit. (Click the Exhibit button.)

All local storage is on SSD.
You need to plan specific configurations for the new cluster.
For each of the following statement, select Yes if the statement is true. Otherwise, select No.
Hot Area:

Answer: 


NEW QUESTION NO: 74
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 section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.
One customer reports that their database is not responding as quickly as the service level agreements dictate. You observe that the database is fragmented.
You need to optimize query performance.
Solution: You run the DBCC CHECKDB command.
Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation/Reference:
Explanation:
DBCC CHECKDB only checks the logical and physical integrity of all the objects in the specified database.
It does not update any indexes, and does not improve query performance.
References: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb- transact-sql

NEW QUESTION NO: 75
You are creating a database that will store usernames and passwords for an application.
You need to recommend a solution to store the passwords in the database.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
A. One-way encryption
B. Transparent Data Encryption (TDE)
C. Encrypting File System (EFS)
D. Reversible encryption
Answer: C
Explanation/Reference:
Explanation:
1. Transparent Data Encryption (TDE) is a special case of encryption using a symmetric key. TDE encrypts an entire database using that symmetric key called the database encryption key. The database encryption key is protected by other keys or certificates which are protected either by the database master key or by an asymmetric key stored in an EKM module.
2. SQL Server provides the following mechanisms for encryption:
Transact-SQL functions

Asymmetric keys

Symmetric keys

Certificates

Transparent Data Encryption


NEW QUESTION NO: 76
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You need to examine information about logins, CPU times, and Disk I/O on a particular database in Microsoft Azure.
What should you use?
A. Activity Monitor
B. Sp_who3
C. SQL Server Management Studio (SSMS) Object Explorer
D. SQL Server Data Collector
E. SQL Server Data Tools (SSDT)
F. SQL Server Configuration Manager
Answer: A
Explanation/Reference:
Explanation:
Activity Monitor displays information about SQL Server processes and how these processes affect the current instance of SQL Server.
Activity Monitor is a tabbed document window with the following expandable and collapsible panes:
Overview, Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries.
The Activity User Tasks Pane shows information for active user connections to the instance, including the following column:
* Login: The SQL Server login name under which the session is currently executing.
The Recent Expensive Queries Pane shows information about the most expensive queries that have been run on the instance over the last 30 seconds, including the following column:
* CPU (ms/sec): The rate of CPU use by the query
References: https://technet.microsoft.com/en-us/library/cc879320(v=sql.105).aspx

NEW QUESTION NO: 77
DRAG DROP
Overview
Application Overview
Contoso, Ltd., is the developer of an enterprise resource planning (ERP) application.
Contoso is designing a new version of the ERP application. The previous version of the ERP application used SQL Server 2008 R2.
The new version will use SQL Server 2014.
The ERP application relies on an import process to load supplier data. The import process updates thousands of rows simultaneously, requires exclusive access to the database, and runs daily.
You receive several support calls reporting unexpected behavior in the ERP application. After analyzing the calls, you conclude that users made changes directly to the tables in the database.
Tables
The current database schema contains a table named OrderDetails.
The OrderDetails table contains information about the items sold for each purchase order. OrderDetails stores the product ID, quantities, and discounts applied to each product in a purchase order.
The product price is stored in a table named Products. The Products table was defined by using the SQL_Latin1_General_CP1_CI_AS collation.
A column named ProductName was created by using the varchar data type. The database contains a table named Orders.
Orders contains all of the purchase orders from the last 12 months. Purchase orders that are older than
12 months are stored in a table named OrdersOld.
The previous version of the ERP application relied on table-level security.
Stored Procedures
The current version of the database contains stored procedures that change two tables. The following shows the relevant portions of the two stored procedures:

Customer Problems
Installation Issues
The current version of the ERP application requires that several SQL Server logins be set up to function correctly. Most customers set up the ERP application in multiple locations and must create logins multiple times.
Index Fragmentation Issues
Customers discover that clustered indexes often are fragmented. To resolve this issue, the customers defragment the indexes more frequently. All of the tables affected by fragmentation have the following columns that are used as the clustered index key:

Backup Issues
Customers who have large amounts of historical purchase order data report that backup time is unacceptable.
Search Issues
Users report that when they search product names, the search results exclude product names that contain accents, unless the search string includes the accent.
Missing Data Issues
Customers report that when they make a price change in the Products table, they cannot retrieve the price that the item was sold for in previous orders.
Query Performance Issues
Customers report that query performance degrades very quickly. Additionally, the customers report that users cannot run queries when SQL Server runs maintenance tasks. Import Issues During the monthly import process, database administrators receive many supports call from users who report that they cannot access the supplier data. The database administrators want to reduce the amount of time required to import the data.
Design Requirements
File Storage Requirements
The ERP database stores scanned documents that are larger than 2 MB. These files must only be accessed through the ERP application. File access must have the best possible read and write performance.
Data Recovery Requirements
If the import process fails, the database must be returned to its prior state immediately.
Security Requirements
You must provide users with the ability to execute functions within the ERP application, without having direct access to the underlying tables.
Concurrency Requirements
You must reduce the likelihood of deadlocks occurring when Sales.Prod and Sales.Proc2 execute.
You need to recommend which statement should be used to update SalesOrder.
How should you recommend completing the statement? To answer, drag the appropriate elements to the correct locations. Each element 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: 


NEW QUESTION NO: 78
You administer a Microsoft SQL Server 2016 database instance.
You create a new user named UserA. You need to ensure that UserA is able to create SQL Server Agent jobs and to execute SQL Server Agent jobs.
To which role should you add UserA?
A. SQLAgentUserRole
B. DatabaseMailUserRole
C. Securityadmin
D. RSExecRole
Answer: A

NEW QUESTION NO: 79
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You are a database administrator for a company that has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases, and each customer uses a dedicated instance. The environments that you manage are shown in the following table.

You need to monitor WingDB and gather information for troubleshooting issues.
What should you use?
A. sp_updatestats
B. sp_lock
C. sys.dm_os_waiting_tasks
D. sys.dm_tran_active_snapshot_database_transactions
E. Activity Monitor
Answer: B
Explanation/Reference:
Explanation:
The sp_lock system stored procedure is packaged with SQL Server and will give you insight into the locks that are happening on your system. This procedure returns much of its information from the syslock info in the master database, which is a system table that contains information on all granted, converting, and waiting lock requests.
Note: sp_lock will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. To obtain information about locks in the SQL Server Database Engine, use the sys.dm_tran_locks dynamic management view.
sys.dm_tran_locks returns information about currently active lock manager resources in SQL Server
2008and later. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock- transact-sql

NEW QUESTION NO: 80
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are the database administrator for a company that hosts Microsoft SQL Server. You manage both on- premises and Microsoft Azure SQL Database environments.
One instance hosts a user database named HRDB. The database contains sensitive human resources data.
You need to grant an auditor permission to view the SQL Server audit logs while following the principle of least privilege.
Which permission should you grant?
A. DDLAdmin
B. db_datawriter
C. dbcreator
D. dbo
E. View Database State
F. View Server State
G. View Definition
H. sysadmin
Answer: F
Explanation/Reference:
Explanation:
Unless otherwise specified, viewing catalog views requires a principal to have one of the following:
Membership in the sysadmin fixed server role.

The CONTROL SERVER permission.

The VIEW SERVER STATE permission.

The ALTER ANY AUDIT permission.

The VIEW AUDIT STATE permission (gives only the principal access to the sys.server_audits catalog

view).
References: https://technet.microsoft.com/en-us/library/cc280386(v=sql.110).aspx

https://www.newpassleader.com/Microsoft/70-764-exam-preparation-materials.html