Ensurepass

QUESTION 61

You administer a Microsoft SQL Server 2012 instance that has several SQL Server Agent jobs configured. When SQL Server Agent jobs fail, the error messages returned by the job steps do not provide the required detail. The following error message is an example error message:

 

“The job failed. The Job was invoked by User CONTOSOServiceAccount. The last step to run was step 1 (Subplan_1).”

 

You need to ensure that all available details of the job step failures for SQL Server Agent jobs are retained. What should you do?

 

A.      Configure output files.

B.      Expand agent logging to include information from all events.

C.      Disable the Limit size of job history log feature.

D.      Configure event forwarding.

 

Correct Answer: B

 

 

QUESTION 62

You administer a Microsoft SQL Server 2012 instance. You need to configure a new database to support FILETABLES. What should you do? Choose all that apply.

 

A.      Disable FILESTREAM on the Database.

B.      Enable FILESTREAM on the Server Instance.

C.      Configure the Database for Partial Containment.

D.      Create a non-empty FILESTREAM file group.

E.       Enable Contained Databases on the Server Instance.

F.       Set the FILESTREAM directory name on the Database.

 

Correct Answer: BDF

 

 

QUESTION 63

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.        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_Certificate;

 

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

 

Correct Answer: B

 

 

QUESTION 64

You administer two instances of Microsoft SQL Server 2012. You deploy an application that uses a database on the named instance. The application is unable to connect to the database on the named instance. You need to ensure that the application can connect to the named instance. What should you do?

 

A.      Configure the application as data-tiered.

B.      Open port 1433 on the Windows firewall on the server.

C.      Configure the named SQL Server instance to use an account that is a member of the Domain Admins group.

D.      Start the SQL Server Browser Service.

 

Correct Answer: D

 

 

QUESTION 65

You administer a Microsoft SQL Server 2012 database. The database contains a customer table created by using the following definition:

 

clip_image001

 

You need to ensure that the minimum amount of disk space is used to store the data in the customer table. What should you do?

 

A.        Implement row-level compression.

B.        Implement page-level compression.

C.        Convert all indexes to Column Store indexes.

D.        Implement Unicode compression.

 

Correct Answer: B

 

 

QUESTION 66

You administer a single server that contains a Microsoft SQL Server 2012 default instance on which several production databases have been deployed. You plan to install a new ticketing application that requires the deployment of a database on the server. The SQL login for this application requires sysadmin permissions. You need to ensure that the login for the ticketing application cannot access other production databases. What should you do?

 

A.      Use the SQL Server default instance and enable Contained Databases.

B.      Use the SQL Server default instance and configure a user-defined server role. Add the login for the ticketing application to this role.

C.      Install a new named SQL Server instance on the server.

D.      Install a new default SQL Server instance on the server.

 

Correct Answer: C

 

 

QUESTION 67

You need to install the Power View Components on a stand-alone server. Which three actions should you perform (and sorting)?

 

A.      Install SQL 2012 Analysis Services.

B.      Install SQL 2012 Reporting Services.

C.      Install SQL 2012 Database Engine and PowerPivot for SharePoint.

D.      Configure SQL Server Reporting Services and Add-in in SharePoint mode.

E.       Configure SQL Server reporting services in native mode.

 

Correct Answer: BCD

 

 

QUESTION 68

You plan to migrate the db to azure. You verify that all objects are valid for azure sql database. You need to ensure that users and logins are migrated to azure. What should u do?

 

A.      Use the Copy Database wizard.

B.      Use the Database Transfer wizard.

C.      Use the SQL Management Studio to deploy the db to azure.

D.      Back up the databasae from the local server and restore it to azure.

 

Correct Answer: C

 

 

QUESTION 69

You maintain several dbs on a 64 bit ms sql 2005 instance. You need to migrate the dbs to sql 2012 on the same server. You also need to ensure functionality in sql 2012 can be used in the db after migration. What should you do? (Each correct answer presents part of the solution.)

 

A.      Perform an in-place upgrade to 64-bit in sql 2012.

B.      Perform a side by side installation of a 64 bit sql 2012.

C.      Perform a side by side installation of a 32 bit sql 2012.

D.      Detach the old instance and attach it to the new one.

E.       Change the compatibility level of the db.

 

Correct Answer: BDE

 

 

QUESTION 70

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.      Securityadmin

B.      RSExecRole

C.      SQLAgentUserRole

D.      DatabaseMailUserRole

 

Correct Answer: C

 

 

Comments are closed.