Administering Microsoft SQL Server 2012 Databases
Question No: 81 DRAG DROP – (Topic 2)
You administer a Microsoft SQL Server 2012 database named Human_Resources.
You need to ensure that all read activity against any object in the Human_Resources database is audited and written to a text file.
What should you do? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Create a new Audit. For destination, select File.
Create a new Database Audit Specification on Human_Resources. For Audit Action Type, select Select, and for Object Class, select Database.
Enable Audi and Audi Specification.
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.
Question No: 82 – (Topic 2)
What is the maximum number of SQL Server 2012 Enterprise edition Database Engine instances that you can deploy on a non-clustered server running Windows Server 2008 R2 Enterprise edition?
Question No: 83 – (Topic 2)
If you find that session ID 42 is blocking several other sessions, which of the following will kill that session?
KILL SESSION 42;
Question No: 84 – (Topic 2)
You are a database administrator for a Microsoft SQL Server 2012 instance.
You need to ensure that data can be migrated from a production server to two reporting servers with minimal data to ensure that data on the reporting server is always accessible which solution should you use?
Change Data Capture
Question No: 85 – (Topic 2)
You have recently removed a SQL 2012 Database Engine instance from a computer running the Windows Server 2008 R2 operating system. Prior to the removal of the instance, you had configured affinity so that the default instance used CPU 0 and 1 and the second instance used CPU 2 and 3.
You want to ensure that the default instance can use all processors available to the host. Which of the following commands would you use to accomplish this goal?
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 2,3
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,1
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,4
Question No: 86 – (Topic 2)
You have been hired as a Database Consultant by ABC.com to design a SQL Server 2012 database solution.
You are tasked with designing a scale-out and high-availability SQL Server 2012 Online
Transaction Processing (OLTP) database solution that will maintain copies of data across two server instances.
Your solution must provide scale-out of read operations by distributing the reads from clients across two SQL Server 2012 nodes. The data in both SQL Server nodes needs to be indexed.
What should you include in your solution?
You should include a primary database with scheduled log shipping to the secondary database configured.
You should include two servers configured in an Active-Passive SQL Server 2012 Cluster.
You should include a primary SQL Server 2012 database that uses transactional replication to replicate data to the secondary database.
You should include two servers in an Asynchronous-Commit Availability Mode Availability Group.
You should include two servers in a Synchronous-Commit Availability Mode Availability Group.
Question No: 87 – (Topic 2)
You administer a SQL Server 2012 server that contains a database named SalesDB. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table and the Sales role is granted the Select permission on the Customers schema.
You need to ensure that the Sales role, including UserA, is disallowed to select from the Regions table.
Which Transact-SQL statement should you use?
REVOKE SELECT ON Schema::Customers FROM UserA
REVOKE SELECT ON Object::Regions FROM UserA
EXEC sp_addrolemember #39;Sales#39;, #39;UserA#39;
DENY SELECT ON Schema::Customers FROM Sales
EXEC sp_droprolemember #39;Sales#39;, #39;UserA#39;
REVOKE SELECT ON Schema::Customers FROM Sales
DENY SELECT ON Object::Regions FROM UserA
REVOKE SELECT ON Object::Regions FROM Sales
DENY SELECT ON Schema::Customers FROM UserA
DENY SELECT ON Object::Regions FROM Sales
Answer: J Explanation: References:
Question No: 88 – (Topic 2)
You administer a Microsoft SQL Server 2012 database.
Users report that an application 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 the error message. What should you do?
Update all stored procedure to use a TRY…CATCH block.
Create a SQL Profiler session to capture all ErrorLog and EventLog events.
Flag all stored procedures for recompilation by using sp_recompile.
Question No: 89 – (Topic 2)
Which of the following is required in order for a differential backup to be useful for recovery purposes?
A full backup taken previous to the differential backup.
Hard drive space that is 2 times the size of the database you want to restore.
Less than 1000 database transactions an hour.
A full backup taken after the differential backup.
Question No: 90 HOTSPOT – (Topic 2)
You administer a Microsoft SQL Server 2012 instance.
You need to configure an existing SQL Authenticated Login that meets the following requirements:
->Enforces password policy
->No password expiration enforcement
->Resets the default database to AdventureWorks database
Which option or options should you choose in the Login Properties of the login for User1? (To answer, configure the appropriate option or options in the dialog box in the answer areA. )
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|