Microsoft 70-457 valid study dumps : Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1

You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)


Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you a create a unique constraint?

• A. MiddleName
• B. LastName
• C. EmployeeNum
• D. FirstName
• E. DateHired
• F. JobTitle
• G. EmployeelD
• H. ReportsToID
• I. DepartmentID

You administer a Microsoft SQL Server 2012 instance. You need to stop a blocking process that has an SPID of 64 without stopping other processes. What should you do?

• A. Execute the following Transact-SQL statement:
  ALTER SESSION KILL '64'
• B. Restart the SQL Server service.
• C. Execute the following Transact-SQL statement:
  EXECUTE sp_KillSPID 64
• D. Execute the following Transact-SQL statement:
  KILL 64

You administer a Microsoft SQL Server 2012 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. CREATE CERTIFICATE TDE_Certificate FROM EXECUTABLE FILE = 'd:\TDE_Certificate.cer'
• B. CREATE ASSEMBLY TDE_Assembly FROM 'd:\TDE_Certificate.cer' WITH PERMISSION_SET = SAFE;
  GO
  CREATE CERTIFICATE TDE_Certificate
  FROM ASSEMBLY TDE_Assembly;
• C. DECLARE @startdate date SET @startdate = GETDATE() CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer' WITH START_DATE = @startdate;
• D. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer' WITH PRIVATE KEY (FILE = 'd:\TDE_Certificate.key', DECRYPTION BY PASSWORD 'MyPassword1!');

You administer a Microsoft SQL Server 2012 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. Use Node B to install SQL Server on Node
• C. Use Node A to install SQL Server on Node C.
• D. Run the Add Node to SQL Server Failover Cluster Wizard on Node C.

You administer a Microsoft SQL Server 2012 server that has multiple databases. You need to ensure that users are unable to create stored procedures that begin with sp_. 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.)
Build List and Reorder: