You administer

a Microsoft SQL Server 2012 database.

You provide temporary securityadmin access to User1 to the database server.

You need to know if User1 adds logins to securityadmin.

Which server-level audit action group should you use?

A. SERVER_STATE_CHANGE_GROU

P

B. SERVER_PRINCIPAL_IMPERSONATION_GROUP

C. SUCCESSFUL_LOGIN_GROUP

D. SERVER_ROLE_MEMBER_CHANGE_GROUP

Reference:

SERVER_STATE_CHANGE_GROUP

This event is raised when the SQL Server service state is modified. Equivalent to the Audit Server Starts and Stops Event Class.

SERVER_PRI

NCIPAL_IMPERSONATION_GROUP

This event is raised when there is an impersonation within server scope, such as EXECUTE AS .

Equivalent to the Audit Server Principal Impersonation Event Class.

SUCCESSFUL_LOGIN_GROUP Indicates that a principal has

successfully logged in to SQL Server. Events in this class are raised by new connections or by connections that are reused from a connection pool. Equivalent to the Audit Login Event Class.

SERVER_ROLE_MEMBER_CHANGE_GROUP

This event is raised whenever a

login is added or removed from a fixed server role. This event is raised for the sp_addsrvrolemember and sp_dropsrvrolemember stored procedures. Equivalent to the Audit Add Login to Server Role Event Class.

You administer a Microsoft SQL Server 2012 database.

Users report that an applicati

on that accesses the database displays an error, but the error does not provide meaningful information. No entries are found in the SQL Server log or Windows event logs related to the error.

You need to identify the root cause of the issue by retrieving t

he error message.

What should you do?

A. Create an Extended Events session by using the sqlserver.error_reported event.

B. Create a SQL Profiler session to capture all ErrorLog and EventLog events.

C. Flag all stored procedures for recompilation by using

sp_recompile.

D. Execute sp_who.

Reference:

Event sqlserver.error_reported: This event gets fired every time

that an error happens in the server

You administer a Microsoft SQL Server 2012 database.

You need to ensure that the size of the transaction log f

ile does not exceed 2 GB.

What should you do?

A. Execute sp_configure max log size, 2G.

B. use the ALTER DATABASE…SET LOGFILE command along with the maxsize parameter.

C. In SQL Server Management Studio, right-click the instance and select Database

Settings. Set the maximum size of the file for the transaction log.

D. in SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.

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_Certifi

cate 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. ALTER DATABASE Master SET ENCRYPTION OFF;

B. CREATE CERTIFICATE TDE_Certificate FROM FILE = d:\TDE_Certificate.cer

WITH PRIVATE KEY (FILE =

D:\TDE_Certificate.key, DECRYPTION BY PASSWORD =

MyPassword1!);

C. CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = TDE Certificate;

USE Orders;

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificat

e;

D. CREATE CERTIFICATE TDE_Certificate FROM FILE = d:\TDE_Certificate.cer;

You administer a Microsoft SQL Server 2012 database.

You have a SQL Server Agent job instance that runs us

ing the service account. You have a job step within the job that requires elevated permissions.

You need to ensure that the job step can run using a different user account.

What should you use?

A. A notification

B. A schedule

C. A job category

D. A

proxy

You administer a Microsoft SQL Server 2012 database.

Users report that a billing application becomes unresponsive during busy times of the day.

While

investigating, you notice large number of processes taking or waiting for table locks.

You suspect that SQL Server is assigning stronger locks to queries.

You start a SQL Profiler trace.

Which event should you select?

A. Deadlock graph

B. Lock: Escalat

ion

C. Lock: Timeout

D. Lock: Deadlock

You administer a Microsoft SQL Server 2012 database.

You have a SQL Server Agent job instance that runs using the ser

vice 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