QUESTION 116
Drag and Drop Question
You are designing a database for a university.
The database will contain two tables named Classes and StudentGrades that have the following specifications:
- Classes will store brochures in the XPS format. The brochures must be structured in folders and must be accessible by using UNC paths.
- StudentGrades must be backed up on a separate schedule than the rest of the database.
You need to identify which SQL Server technology meets the specifications of each table.
Which technologies should you identify? To answer, drag the appropriate technology to the correct table in the answer area.

Answer:

QUESTION 117
You use SQL Server 2014 to maintain the data used by applications at your company.
You want to execute two statements.
You need to guarantee that either both statements succeed, or both statements fail together as a batch.
Which code should you use?

A.    Option A
B.    Option B
C.    Option C
D.    Option D
E.    Option E

Answer: D
Explanation:
Structure should be:
BEGIN TRY
BEGIN TRANSACTION
..
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH.

QUESTION 118
Your company has a SQL Azure subscription.
You implement a database named Database1.
Database1 has two tables named Table1 and Table2.
You create a stored procedure named sp1.
Sp1 reads data from Table1 and inserts data into Table2.
A user named User1 informs you that he is unable to run sp1.
You verify that User1 has the SELECT permission on Table1 and Table2.
You need to ensure that User1 can run sp1.
The solution must minimize the number of permissions assigned to User1.
What should you do?

A.    Grant User1 the INSERT permission on Table2.
B.    Add User1 to the db_datawriter role.
C.    Change sp1 to run as the sa user.
D.    Grant User1 the EXECUTE permission on sp1.

Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/ms191291.aspx

QUESTION 119
You deploy a SQL Server instance named SQLProd that uses SQL Server 2014.
You need to recommend a solution to monitor the transactions that are running currently against SQLProd.
The solution must minimize the amount of custom code required.
What should you recommend?

A.    Statistics
B.    A dynamic management view
C.    A trigger
D.    User-defined views

Answer: B
Explanation:
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
Transactions can be monitored.

QUESTION 120
Your network contains an Active Directory domain that has two groups named Group1 and Group2.
The domain contains two SQL Server instances named SQLDev and SQLProd.
Each SQL Server instance has access to various storage media.
The SQL Server instances have a database that contains a table named Table1.
Table1 contains a column named Column1.
The value for Column1 can be either Value1 or Value2.
You need to recommend a solution to ensure that users in Group1 can retrieve only rows from Column1 that contain the value of Value1.
What should you recommend?

A.    A dynamic management view
B.    Filegroups
C.    Snapshot isolation
D.    User-defined views

Answer: D
Explanation:
A view is a virtual table whose contents are defined by a query.
Like a table, a view consists of a set of named columns and rows of data.
Unless indexed, a view does not exist as a stored set of data values in a database.
The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.
A view acts as a filter on the underlying tables referenced in the view.
Incorrect:
Not A: Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

QUESTION 121
You are the new database administrator for a SQL Server 2014 instance.
You conduct an assessment on the instance and determine that the auto create statistics setting on the database named DB1 has been turned off.
You see no evidence that any maintenance has been occurring.
You want to set up monitoring to see if query performance is being affected.
You need to set up a monitoring process that will capture any cases where statistics could have been useful if they existed.
What should you do?

A.    Create a SQL Server Agent job to execute DBCC SHOWSTATISTICS on each of the
primary key columns in the database.
B.    Use the missing_column_statistics extended event.
C.    Query the sys.statistics system view to see all cases where the statistics were last needed.
D.    Write a query using the sys.dm_db_missing_index_group_stats DMV Joining to sys.indexes,
filtering on is_hypothetical.

Answer: B
Explanation:
The Missing Column Statistics event class indicates that column statistics that could have been useful for the optimizer are not available.
By monitoring the Missing Column Statistics event class, you can determine if there are statistics missing for a column used by a query.
This can cause the optimizer to choose a less efficient query plan than expected.

QUESTION 122
You have two databases named DB1 and DB2 that are located on the same server.
You plan to create a stored procedure named SProc1 in DB1.
SProc1 will query a table named Table2 in DB2.
You need to recommend a solution to ensure that SProc1 can access Table2 without granting users direct access to Table2.
What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

A.    Contained databases
B.    Application roles
C.    Cross-database ownership chaining
D.    Digital certificates

Answer: B
Explanation:
An application role is a database principal that enables an application to run with its own, user-like permissions.
You can use application roles to enable access to specific data to only those users who connect through a particular application.
Unlike database roles, application roles contain no members and are inactive by default.

QUESTION 123
You have a server named Server1 that has 16 processors.
You plan to deploy multiple instances of SQL Server 2014 to Server1.
You need to recommend a method to allocate processors to each instance.
What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

A.    Processor affinity
B.    Windows System Resource Manager (WSRM)
C.    Max Degree of Parallelism
D.    Resource Governor

Answer: A
Explanation:
CPU affinity management through Windows System Resource Manager is not recommended for SQL Server multi-instance management. Instead, use the processor affinity settings in SQL Server.

QUESTION 124
You have two SQL Server 2014 instances named SQLDev and SQLProd.
You plan to create a new database on SQLProd that will use SQL Server Authentication.
You need to ensure that when the new database is copied from SQLProd to SQLDev, users can connect to the database on SQLDev even if they do not have a login on the SQLDev instance.
What should you use? More than one answer choice may achieve the goal. Select the BEST answer.

A.    Triggers
B.    Contained database
C.    SQL Server Analysis Services (SSAS) scripts
D.    Extended Events
E.    SQL Server Integration Services (SSIS) scripts

Answer: B
Explanation:
A fully contained database includes all the settings and metadata required to define the database and has no configuration dependencies on the instance of the SQL Server Database Engine where the database is installed.

QUESTION 125
You have a query that is used by a reporting dashboard.
Users report that the query sometimes takes a long time to run.
You need to recommend a solution to identify what is causing the issue.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

A.    Set the blocked process threshold, and then run SQL Server Profiler.
B.    Set the blocked process threshold, and then create an alert.
C.    Enable trace flag 1204, and then create an alert.
D.    Create a job that queries the sys.dm_os_waiting_tasks dynamic management view.

Answer: B
Explanation:
Step 1: Turn on the blocked process report.
This will look for any blocking taking 20 seconds or longer.
--Make sure you don't have any pending changes
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO
exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO
exec sp_configure 'blocked process threshold (s)', 20;
GO
RECONFIGURE
GO
Step 2: Set up a trace to capture the blocked process report. Run it as a server side trace.