Download New Updated (June) Microsoft 70-469 Practice Test Topic 12, Mix Questions Set 1 part01

Ensurepass

 

Topic 12, Mix Questions Set 1

 

 

QUESTION 120  (Topic 12)

 

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.

Encrypting File System (EFS)

C.

Transparent Data Encryption (TDE)

D.

Reversible encryption

 

Answer: C

Explanation: * 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.

 

* SQL Server provides the following mechanisms for encryption:

Transact-SQL functions

Asymmetric keys

Symmetric keys

Certificates

Transparent Data Encryption

Transparent Data Encryption (TDE)

 

 

QUESTION 121  (Topic 12)

 

You have a SQL Server instance on a server named Server1.

 

You need to recommend a solution to perform the following tasks every week:

 

Rebuild the indexes by using a new fill factor.

Run a custom T-SQL command.

Back up the databases.

 

What should you recommend?

 

More than one answer choice may achieve the goal. Select the BEST answer.

 

A.

A trigger

B.

An alert

C.

A maintenance plan

D.

Windows PowerShell

E.

A system policy

 

Answer: C

Explanation: Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies.

Maintenance Plans

 

 

QUESTION 122  (Topic 12)

 

You have a SQL Server 2014 environment That contains 20 servers.

 

The corporate security policy states that all SQL Server 2014 instances must meet specific security standards.

 

You need to recommend a management strategy for the SQL Server 2014 servers.

 

What should you include in the recommendation?

 

More than one answer choice may achieve the goal. Select the BEST answer.

 

A.

Multi server jobs

B.

Policy-Based Management

C.

Common criteria compliance

D.

Maintenance plans

 

Answer: B

Explanation: Policy-Based Management is a system for managing one or more instances of SQL Server. When SQL Server policy administrators use Policy-Based Management, they use SQL Server Management Studio to create policies to manage entities on the server, such as the instance of SQL Server, databases, or other SQL Server objects.

 

Policy-Based Management How-to Topics

 

 

QUESTION 123  (Topic 12)

 

 

You deploy a database by using SQL Server 2014. The database contains a table named Table1.

 

You need to recommend a solution to track all of the deletions executed on Table1. The solution must minimize the amount of custom code required.

 

What should you recommend?

 

A.

Change data capture

B.

Statistics

C.

A trigger

D.

Master Data Services

 

Answer: A

Explanation: Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.

About Change Data Capture (SQL Server)

 

 

QUESTION 124  (Topic 12)

 

You are designing a database named DB1.

 

Changes will be deployed to DB1 every Wednesday night.

 

You need to recommend a strategy to deploy the changes to DB1. The strategy must meet the following requirements:

 

The strategy must not disrupt backup operations.

DB1 must be online while the changes are deployed. You must be able to undo quickly any changes made to objects.

 

What should you recommend?

 

More than one answer choice may achieve the goal. Select the BEST answer.

 

A.

Perform a copy-only database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.

B.

Create a database snapshot. If the deployment fails, recover the objects from the database snapshot.

C.

Create a database snapshot. If the deployment fails, revert the database to the database snapshot.

D.

Perform a full database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.

 

Answer: C

Explanation: Database Snapshots (SQL Server)

 

 

QUESTION 125  (Topic 12)

 

You deploy a SQL Server instance named SQLProd that uses SQL Server 2014.

 

You need to recommend a solution to monitor the transactions that are running currently against SQLProd. The solution must minimize the amount of custom code required.

 

What should you recommend?

 

A.

Statistics

B.

A dynamic management view

C.

A trigger

D.

User-defined views

 

Answer: B

Explanation: Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

Transactions can be monitored.

Dynamic Management Views and Functions (Transact-SQL)

 

 

QUESTION 126

DRAG DROP  (Topic 12)

 

You are designing a database for a university.

 

The database will contain two tables named Classes and Enrollment that have the following specifications:

 

Classes will store brochures in the XPS format. The brochures must be structured in folders and must be accessible by using UNC paths. Enrollment will store information about students and their classes. Performance must be enhanced for queries of the current enrollments.

 

You need to identify which SQL Server technology meets the specifications of each table.

 

Which technologies should you identify?

 

To answer, drag the appropriate technology to the correct table in the answer area.

 

clip_image002

 

A.

 

B.

 

C.

 

D.

 

 

Answer:

 

 

QUESTION 127

DRAG DROP  (Topic 12)

 

You have two servers named SQL1 and SQL2 that have SQL Server 2012 installed. SQL1 contains a database that is mirrored asynchronously to SQL2. The database contents are updated once a month.

 

You need to upgrade the database to SQL Server 2014. The solution must minimize downtime.

 

Which upgrade steps should you recommend?

 

To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

 

clip_image004

 

A.

 

B.

 

C.

 

D.

 

 

Answer:

 

 

QUESTION 128

HOTSPOT  (Topic 12)

 

You use SQL Server 2014. You create a table within a database by using the following DDL:

 

clip_image006

 

The following table illustrates a representative sample of data:

 

 

 

clip_image008

 

The system is expected to handle 50 million orders a month over the next five years.

 

You have been instructed by your Team Lead to follow best practices for storage and performance in the utilization of SPARSE columns.

 

Which columns should you designate as SPARSE? To answer, mark each column as SPARSE or NOT SPARSE in the answer area.

 

clip_image010

 

A.

 

B.

 

C.

 

D.

 

 

Answer:

 

 

QUESTION 129  (Topic 12)

 

You have a SQL Server 2014 instance named SQL1.

 

SQL1 creates error events in the Windows Application event log.

 

You need to recommend a solution that will run an application when SQL1 logs a specific error in the Application log.

 

Which SQL elements should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.)

 

A.

A policy

B.

A maintenance plan

C.

An alert

D.

A job

E.

A trigger

 

Answer: DE

Explanation: Use a trigger that starts a job which executes the application.

 

 

 

http://technet.microsoft.com/en-us/library/hh849759.aspx

 

 

QUESTION 130  (Topic 12)

 

Your network contains an Active Directory domain that has two groups named Group1 and Group2.

 

The domain contains two SQL Server instances named SQLDev
and SQLProd. Each SQL Server instance has access to various storage media.

 

The SQL Server instances have a database that contains a table named Table1.

 

Table1 contains a column named Column1. The value for Column1 can be either Value1 or Value2.

 

You need to recommend a solution to ensure that users in Group1 can retrieve only rows from Column1 that contain the value of Value1.

 

What should you recommend?

 

A.

A dynamic management view

B.

Filegroups

C.

Snapshot isolation

D.

User-defined views

 

Answer: D

Explanation: A view is a virtual table whose contents are defined by a query. Like a table, a view consists of a set of named columns and rows of data. Unless indexed, a view does not exist as a stored set of data values in a database. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.

A view acts as a filter on the underlying tables referenced in the view. http://msdn.microsoft.com/en-us/library/ms190174.aspx

 

Incorrect:

Not A: Dynamic management views and functions return server state information that can

 

 

 

be used to monitor the health of a server instance, diagnose problems, and tune performance.

http://technet.microsoft.com/en-us/library/ms188754(v=sql.120).aspx

Free VCE & PDF File for Microsoft 70-469 Real Exam

Instant Access to Free VCE Files: MCSE|MCSA|MCITP…
Instant Access to Free PDF Files: MCSE|MCSA|MCITP…