Q1. You administer a Windows Azure SQL Database database named Human_Resources. The database contains 2 tables named Employees and SalaryDetails. 

You add two Windows groups as logins for the server: 

CORP\Employees - All company employees CORP\HRAdmins - HR administrators only HR Administrators are also company employees. 

You need to grant users access according to the following requirements: 

CORP\Employees should have SELECT access to the Employees table. 

Only users in CORP\HRAdmins should have SELECT access to the SalaryDetails table. 

Logins are based only on Windows security groups. 

What should you do? 

A. Create a database role called Employees. 

Add CORP\Employees to the db_datareader role. 

Add all company employees except HR administrators to the Employees role. 

Deny SELECT access to the SalaryDetails table to the Employees role. 

B. Create a database role called HRAdmins. 

Add all company employees except HR administrators to the db_datareader role, 

Add all HR administrators to the HRAdmins role. 

Grant SELECT access to the SalaryDetails table to the HRAdmins role. 

Deny SELECT access to the SalaryDetails table to the db_datareader role. 

C. Create two database roles: Employees and HRAdmins. Add all company employees to the Employees role. 

Add HR administrators to the HRAdmins role. 

Grant SELECT access to all tables except SalaryDetails to the Employees role. 

Grant SELECT access to the SalaryDetails table to the HRAdmins role. 

Deny SELECT access to the SalaryDetails table to the Employees role. 

D. Create a database role called Employees. 

Add all HR administrators to the db_datareader role. 

Add all company employees to the Employees role. 

Grant SELECT access to all tables except the SalaryDetails table to the Employees role. 

Deny SELECT access to the SalaryDetails table to the Employees role. 

View Answer

Q2. You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. 

You need to collect data for a long period of time to troubleshoot wait statistics when querying Contoso. You also need to ensure minimum impact to the server. 

What should you create? 

A. An Alert 

B. A Resource Pool 

C. An Extended Event session 

D. A Server Audit Specification 

E. A SQL Profiler Trace 

F. A Database Audit Specification 

G. A Policy 

View Answer

Q3. You administer a Microsoft SQL Server 2012 database that includes a table named dbo.Log. This table contains millions of records about user activity in an application. 

Records in dbo.Log that are more than 90 days old are purged nightly. When records are purged, table locks are causing contention with inserts. 

You need to be able to modify dbo.Log without requiring any changes to the applications that utilize dbo.Log. 

Which type of solution should you use? 

A. Extended events 

B. Columnstore index 

C. Partitioned tables 

D. Read committed snapshot 

View Answer

Q4. You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database. 

The recovery model and backup schedule are configured as shown in the following table: 

At 14:00 hours, you discover that pages 71, 520, and 713 on one of the database files are corrupted on the reporting database. 

You also need to ensure that data loss is minimal. 

What should you do? 

A. Perform a partial restore. 

B. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup. 

C. Restore the latest full backup. 

D. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup. 

E. Perform a page restore. 

F. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup. 

G. Perform a point-in-time restore. 

H. Restore the latest full backup. Then, restore the latest differential backup. 

View Answer

Q5. You administer a Microsoft SQL Server 2012 database named Contoso on a server named 

Server01. 

You need to prevent users from disabling server audits in Server01. 

What should you create? 

A. an Alert 

B. a Resource Pool 

C. an Extended Event session 

D. a Policy 

E. a Database Audit Specification 

F. a SQL Profiler Trace 

G. a Server Audit Specification 

View Answer

Q6. You develop a database for a travel application. 

You need to design tables and other database objects. 

You create the Airline_Schedules table. 

You need to store the departure and arrival dates and times of flights along with time zone information. 

What should you do? 

A. Use the CAST function. 

B. Use the DATE data type. 

C. Use the FORMAT function. 

D. Use an appropriate collation. 

E. Use a user-defined table type. 

F. Use the VARBINARY data type. 

G. Use the DATETIME data type. 

H. Use the DATETIME2 data type. 

I. Use the DATETIMEOFFSET data type. 

J. Use the TODATETIMEOFFSET function. 

View Answer

Q7. You are the administrator of a Microsoft SQL Server 2012 server. 

Some applications consume significant resources. You need to manage the server workload by restricting resource-intensive applications 

You need to dynamically limit resource consumption. 

What should you do? 

A. Set up Service Broker to ensure that applications are not allowed to consume more than the specified amount of resources. 

B. Configure Resource Pools, Workload Groups, and Classifier Function, and then enable the Resource Governor. 

C. Configure Extended Events to monitor and restrict resource limits allowed by each application type. 

D. Create a new Plan Guide with a Scope Type of sql and define the resource limits for each application. 

View Answer

Q8. You administer a Windows Azure SQL Database database named Orders. 

You need to create a copy of Orders named Orders_Reporting. 

Which Transact-SQL command should you use? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q9. You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database. 

The recovery model and backup schedule are configured as shown in the following table: 

The differential backup of the reporting database fails. Then, the reporting database fails at 14:00 hours. 

You need to ensure that the reporting database is restored. You also need to ensure that data loss is minimal. 

What should you do? 

A. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup. 

B. Perform a point-in-time restore. C Restore the latest full backup. 

C. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup. 

D. Restore the latest full backup. Then, restore the latest differential backup. 

E. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup. 

F. Perform a page restore. 

G. Perform a partial restore. 

View Answer

Q10. You administer a Microsoft SQL Server 2012 database. 

Users report that a billing application becomes unresponsive during busy times of the day. 

While investigating, you notice large number of processes taking or waiting for table locks. 

You suspect that SQL Server is assigning stronger locks to queries. 

You start a SQL Profiler trace. 

Which event should you select? 

A. Deadlock graph 

B. Lock: Escalation 

C. Lock: Timeout 

D. Lock: Deadlock 

View Answer