Ensurepass

QUESTION 21

You have a SQL Server 2012 database named DB1. You have a backup device named Device1. You

discover that the log file for the database is full. You need to ensure that DB1 can complete

transactions. The solution must not affect the chain of log sequence numbers (LSNs). Which code

segment should you execute?

 

  1. BACKUP LOG DB1 TO Device1 WITH TRUNCATE_ONLY

  2. BACKUP LOG DB1 TO Device1 WITH COPY_ONLY

  3. BACKUP LOG DB1 TO Device1 WITH NORECOVERY

  4. BACKUP LOG DB1 TO Device1

 

Correct Answer: D

 

 

QUESTION 22

You are creating a table to support an application that will cache data outside of SQL Server. The

application will detect whether cached values were changed before it updates the values. You

need to create the table, and then verify that you can insert a row into the table. Which code

segment should you use?

 

  1. CREATE TABLE Table1 (

ID int IDENTITY(1,1),

Name varchar(100),

Version uniqueidentifier DEFAULT NEWID())

INSERT INTO Table1 (Name, Version)

VALUES (‘Smith, Ben’)

 

  1. CREATE TABLE Table1 (

ID int IDENTITY(1,1),

Name varchar(100),

Version rowversion)

INSERT INTO Table1 (Name, Version)

VALUES (‘Smith, Ben’, NEWID())

 

  1. CREATE TABLE Table1 (

ID int IDENTITY(1,1),

Name varchar(100),

Version uniqueidentifier DEFAULT NEWID())

INSERT INTO Table1 (Name, Version)

VALUES (‘Smith, Ben’, NEWID())

 

  1. CREATE TABLE Table1 (

ID int IDENTITY(1,1),

Name varchar(100),

Version rowversion)

INSERT INTO Table1 (Name)

VALUES (‘Smith, Ben’)

 

Correct Answer: D

 

 

 

 

 

 

QUESTION 23

You use SQL Server 2012 to maintain the data used by the applications at your company. You plan

to create a table named Table1 by using the following statement.

(Line numbers are included for reference only.)

 

70-459-demo-9

 

You need to ensure that Table1 contains a column named UserName. The UserName column will:

 

  • Store string values in any language.

  • Accept a maximum of 200 characters.

  • Be case-sensitive and accent-sensitive.

 

Which code segment should you add at line 03?

 

  1. UserName nvarchar(200) COLLATE Latin1_General_CI_AI NOT NULL,

  2. UserName varchar(200) COLLATE Latin1_GeneraI_CI_AI NOT NULL,

  3. UserName nvarchar(200) COLLATE Latin1_General_CS_AS NOT NULL,

  4. UserName varchar(200) COLLATE Latin1_General_CS_AS NOT NULL,

  5. UserName nvarchar(200) COLLATE Latin1_General_CI_AS NOT NULL,

  6. UserName varchar(200) COLLATE Latin1_General_CI_AS NOT NULL,

 

Correct Answer: C

 

 

QUESTION 24

You are creating a database that will store usernames and credit card numbers for an application.

You need to recommend a solution to store the credit card numbers in the database. What

should you recommend? More than one answer choice may achieve the goal. Select the BEST

answer.

 

  1. One-way encryption

  2. Reversible encryption

  3. Encrypting File System (EFS)

  4. Transparent Data Encryption (TDE)

Correct Answer: B

 

 

QUESTION 25

You have two SQL Server instances named SQLDev and SQLProd that have access to various

storage media. You plan to synchronize SQLDev and SQLProd. You need to recommend a solution

that meets the following requirements:

 

  • The database schemas must be synchronized from SQLDev to SQLProd.

  • The database on SQLDev must be deployed to SQLProd by using a package.

  • The package must support being deployed to SQL Azure.

 

What should you recommend? More than one answer choice may achieve the goal. Select the

BEST answer.

 

  1. a database snapshot

  2. change data capture

  3. a data-tier application

  4. SQL Server Integration Services (SSIS)

  5. SQL Data Sync

 

Correct Answer: D

 

 

QUESTION 26

You have a database named DB1. You plan to create a stored procedure that will insert rows into

three different tables. Each insert must use the same identifying value for each table, but the

value must increase from one invocation of the stored procedure to the next. Occasionally, the

identifying value must be reset to its initial value. You need to design a mechanism to hold the

identifying values for the stored procedure to use. What should you do? More than one answer

choice may achieve the goal. Select the BEST answer.

 

  1. Create a sequence object that holds the next value in the sequence. Retrieve the next value

by using the stored procedure. Reset the value by using an ALTER SEQUENCE statement as

needed.

  1. Create a fourth table that holds the next value in the sequence. At the end each transaction,

update the value by using the stored procedure. Reset the value as needed by using an

UPDATE statement.

  1. Create a sequence object that holds the next value in the sequence. Retrieve the next value

by using the stored procedure. Increment the sequence object to the next value by using an

ALTER SEQUENCE statement. Reset the value as needed by using a different ALTER

SEQUENCE statement.

  1. Create an identity column in each of the three tables. Use the same seed and the same

increment for each table. Insert new rows into the tables by using the stored procedure. Use

the DBCC CHECKIDENT command to reset the columns as needed.

 

Correct Answer: A

 

 

QUESTION 27

Drag Drop

You have a SQL Server 2012 database named Database1. Database1 has a data file named

database1_data.mdf and a transaction log file named database1_Log.ldf. Database1_Data.mdf is

1.5 GB. Database1_Log.ldf is 1.5 terabytes. A full backup of Database1 is performed every day.

You need to reduce the size of the log file. The solution must ensure that you can perform

transaction log backups in the future. Which code segment should you execute?

 

To answer, move the appropriate code segments from the list of code segments to the answer

area and arrange them in the correct order.

 

Build List and Reorder:

70-459-demo-10

 

Correct Answer:

70-459-demo-11

 

 

 

QUESTION 28

DRAG DROP

You plan to install two SQL Server 2012 environments named Environment1 and Environment2.

Your company identifies the following availability requirements for each environment:

 

  • Environment1 must have Mirroring with automatic failover implemented.

  • Environment2 must have AlwaysOn with automatic failover implemented.

 

You need to identify the minimum number of SQL Server 2012 servers that must be deployed to

each environment to ensure that all data remains available if a physical server fails. How many

servers should you identify?

 

To answer, drag the appropriate number to the correct environment in the answer area.

 

Select and Place:

70-459-demo-12

 

Correct Answer:

70-459-demo-13

 

 

 

 

 

 

 

 

 

QUESTION 29

DRAG DROP

You plan to deploy SQL Server 2012. You identify the following security requirements for the

deployment:

 

  • Users must be prevented from intercepting and reading the T-SQL statements sent from the

clients to the database engine.

  • All database files and log files must be encrypted if the files are moved to another disk on

another server.

 

You need to identify which feature meets each security requiremen
t. The solution must minimize

processor overhead. Which features should you identify?

 

To answer, drag the appropriate feature to the correct requirement in the answer area.

 

Select and Place:

70-459-demo-14

 

Correct Answer:

70-459-demo-15

 

 

QUESTION 30

DRAG DROP

You plan to deploy SQL Server 2012. Your company identifies the following monitoring

requirements:

 

  • Tempdb must be monitored for insufficient free space.

  • Deadlocks must be analyzed by using Deadlock graphs.

 

You need to identify which feature meets each monitoring requirement. Which features should

you identify?

 

To answer, drag the appropriate feature to the correct monitoring requirement in the answer

area.

 

Select and Place:

70-459-demo-16

 

Correct Answer:

70-459-demo-17

 

 

Instant Access to Download Latest Complete Collection of Microsoft MCSE 70-459 Real Exam

Try Microsoft MCSE 70-459 Free Demo

Comments are closed.