You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table.
What should you do?

• A.Reference a user-defined function within the computed column.
• B.Create a BEFORE trigger that maintains the state of the computed column.
• C.Add a default constraint to the computed column that implements hard-coded values.
• D.Add a default constraint to the computed column that implements hard-coded CASE statements.

Comment: *

Name: *

Email: *

Verification: *

You administer a Microsoft SQL Server 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.Create a ConfigurationFile.ini file from Node B, and then run the AddNode command-line tool on Node A.
• B.Use Node A to install SQL Server on Node C.
• C.Run the Add Node to SQL Server Failover Cluster Wizard on Node C.
• D.Use Cluster Administrator to add a new Resource Group to Node B.

Comment: *

Name: *

Email: *

Verification: *

DRAG DROP
You administer a Microsoft SQL Server 2012 database called Human_Resources. The database contains a table named dbo.Salary.
You need to ensure that all read activity against dbo.Salary is audited and written to the Windows Security Log.
What should you do? (To answer, move the three appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Correct Answer:

Explanation:
Box 1:

Box 2:

Box 3:

-- --
Note:
*An audit must exist before creating a server audit specification for it. When a server audit specification is created, it is in a disabled state.
*The general process for creating and using an audit is as follows. Create an audit and define the target. Create either a server audit specification or database audit specification that maps to the audit. Enable the audit specification. Enable the audit. Read the audit events by using the Windows Event Viewer, Log File Viewer, or the fn_get_audit_file function.
*Example (step 2) (we should audit SELECT on the Salary table):
USE AdventureWorks2012 ;
GO
-- Create the database audit specification.
CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables
FOR SERVER AUDIT Payrole_Security_Audit
ADD (SELECT, INSERT
ON HumanResources.EmployeePayHistory BY dbo )
WITH (STATE = ON);
GO

Comment: *

Name: *

Email: *

Verification: *

Which of the following statements will set the maximum amount of system memory that is managed by the SQL Server Memory Manager to 4GB?

• A.sp_configure 'max memory', 4;
• B.ALTER SYSTEM SET 'max server memory'=4096;
• C.sp_configure 'max server memory', 4;
• D.sp_configure 'max server memory', 4096;

Comment: *

Name: *

Email: *

Verification: *

You administer a Microsoft SQL Server database. Service accounts for SQL Agent are configured to use a local user.
A Microsoft SQL Server Integration Services (SSIS) job step has been created within a SQL Server Agent job.
The SSIS package accesses a network share when exporting data from a SQL Server database.
When you execute the SQL Server Agent job, it fails due to a permissions failure on a share on a remote server.
You need to ensure that the SQL Server Agent job can execute the SSIS package.
Which four 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.)

Correct Answer:

Explanation

SQL Server Agent allows creating a proxy account which defines the security context for the job step.
We need to do is to create a credential to be used by the proxy account.
The created proxy need to have access to the SSIS subsystem
References:
https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-accou

Comment: *

Name: *

Email: *

Verification: *