Ensurepass

QUESTION 11

You create an availability group named HaContoso that has replicas named Server01/HA, Server02/HA, and Server03/HA. Currently, Server01l/HA is the primary replica.

 

You need to ensure that the following requirements are met:

 

Ÿ   Backup operations occur on Server02/HA.

Ÿ   If Server02/HA is unavailable, backup operations occur on Server03/HA.

Ÿ   Backup operations do not occur on Server01/HA.

 

How should you configure HaContoso?

 

A.        Set the backup preference of HaContoso to Prefer Secondary.

Set the backup priority of Server02/HA to 20.

Set the backup priority of Server03/HA to 10.

 

B.        Set the backup preference of HaContoso to Secondary only.

Set the backup priority of Server02/HA to 20.

Set the backup priority of Server03/HA to 10.

 

C.        Set the backup preference of HaContoso to Secondary only.

Set the backup priority of Server02/HA to 10.

Set the backup priority of Server03/HA to 20.

 

D.        Set the exclude replica of Server01/HA to true.

Set the backup priority of Server02/HA to 10.

Set the backup priority of Server03/HA to 20.

 

Correct Answer: B

 

 

QUESTION 12

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 are truncated. The following error message is an example of the truncated error message:

 

“Executed as user CONTOSOServiceAccount. …0.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2011. All rights reserved. Started 63513 PM Error 2012-06-23 183536.87 Code 0XC001000E Source UserImport Description Code 0x00000000 Source Log Import Activity Descript… The package execution fa… The step failed.”

 

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

 

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

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

C.      Configure event forwarding.

D.      Configure output files.

 

Correct Answer: D

 

 

QUESTION 13

You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed. You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables. You need to configure the SQL Server environment to support these packages. What should you do?

 

A.      Create SSIS configuration files for the packages.

B.      Create an Integration Services catalog.

C.      Install Data Quality Services.

D.      Install Master Data services.

 

Correct Answer: B

 

 

QUESTION 14

You administer a Microsoft SQL Server 2012 server. When transaction logs grow, SQL Server must send an email message to the database administrators. You need to configure SQL Server to send the email messages. What should you configure?

 

A.      SQL Mail

B.      An Extended Events session

C.      Alerts and operators in SQL Server Agent

D.      Policies under Policy-Based Management

 

Correct Answer: C

 

 

QUESTION 15

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.      Use the Data Quality Client to configure the application.

B.      Start the SQL Server Browser Service.

C.      Start the SQL Server Integration Services Service.

D.      Use the Master Data Services Configuration Manager to configure the application.

 

Correct Answer: B

 

 

QUESTION 16

You administer a Microsoft SQL Server 2012 database. The database has a table named Customers owned by UserA and another table named Orders owned by UserB. You also have a stored procedure named GetCustomerOrderInfo owned by UserB. GetCustomerOrderInfo selects data from both tables. You create a new user named UserC. You need to ensure that UserC can call the GetCustomerOrderInfo stored procedure. You also need to assign only the minimum required permissions to UserC. Which permission or permissions should you assign to UserC? Choose all that apply.

 

A.      The Select permission on Customers

B.      The Execute permission on GetCustomerOrderInfo

C.      The Take Ownership permission on Customers

D.      The Control permission on GetCustomerOrderInfo

E.       The Take Ownership permission on Orders

F.       The Select permission on Orders

 

Correct Answer: AB

 

 

QUESTION 17

You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact-SQL statement should you use?

 

A.        GRANT DELETE ON Purchases.Suppliers TO ContosoUser

B.        CREATE PROCEDURE Purchases.PurgeInactiveSuppliers

WITH EXECUTE AS USER = ‘dbo’

AS

DELETE FROM Purchases.Suppliers WHERE IsActive = 0

GO

GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser

C.        GRANT SELECT ON Purchases.Suppliers TO ContosoUser

D.        CREATE PROCEDURE Purchases.PurgeInactiveSuppliers

AS

DELETE FROM Purchases.Suppliers WHERE IsActive = 0

GO

GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser

 

Correct Answer: B

 

 

QUESTION 18

You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations. Which type of user should you create?

 

A.      User mapped to a certificate

B.      SQL user without login

C.      Domain user

D.      SQL user with login

 

Correct Answer: C

 

 

QUESTION 19

You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema. Some users must be prevented from deleting records in any of the tables in the Sales schema. You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort. What should you do?

 

A.      Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.

B.      Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.

C.      Deny Delete permissions on each table in the Sales schema for each user.

D.      Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role.

 

Correct Answer: A

 

 

QUESTION 20

You are the lead database administrator (DBA) of a Microsoft SQL Server 2012 environment. All DBAs are members of the DOMAINJrDBAs Active Directory group. You grant DOMAINJrDBAs access to the SQL Server. You need to create a server role named SpecialDBARole that can perform the following functions:

 

Ÿ   View all databases.

Ÿ   View the server state.

Ÿ   Assign GRANT, DENY, and REVOKE permissions on logins.

 

You need to add DOMAINJrDBAs to the server role. You also need to provide the least level of privileges necessary. Which SQL statement or statements should you use? Choose all that apply.

A.      CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin;

B.      ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAINJrDBAs];

C.      CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;

D.      GRANT VIEW DEFINITION TO [SpecialDBARole];

E.       CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin;

F.       GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];

 

Correct Answer: BCF

 

 

Comments are closed.