Q1. You administer a Microsoft SQL Server 2012 database. 

You provide temporary securityadmin access to User1 to the database server. 

You need to know if User1 adds logins to securityadmin. 

Which server-level audit action group should you use? 

A. SERVER_STATE_CHANGE_GROUP 

B. SERVER_PRINCIPAL_IMPERSONATION_GROUP 

C. SUCCESSFUL_LOGIN_GROUP 

D. SERVER_ROLE_MEMBER_CHANGE_GROUP 

View Answer

Q2. You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database contains two tables that have the following definitions: 

Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders. 

Which Transact-SQL query do you use? 

A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY COUNT(OrderAmount) DESC) AS Rnk FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1 

B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, 

RANK() OVER (PARTITION BY CustomerID ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk FROM Customer c INNER JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1 

C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY OrderAmount DESC) AS Rnk FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1 

D. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, COUNT(OrderAmount) DESC) AS OrderAmount FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID ORDER BY OrderAmount DESC 

View Answer

Q3. You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). 

Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors. 

You need to reduce the occurrence of these timeout errors. 

What should you do? 

A. Set the Merge agent on the problem subscribers to use the slow link agent profile. 

B. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication. 

C. Change the Merge agent on the problem subscribers to run continuously. 

D. Set the Remote Connection Timeout on the Publisher to 0. 

View Answer

Q4. You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN). 

The financial database has the following characteristics: 

A data file of 2 terabytes is located on a dedicated LUN (drive D). 

A transaction log of 10 GB is located on a dedicated LUN (drive E). 

Drive D has 1 terabyte of free disk space. 

Drive E has 5 GB of free disk space. 

The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. 

The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database. 

These data load operations must occur in the minimum amount of time. 

A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. 

You need to ensure that your backup will continue if any invalid checksum is encountered. 

Which backup option should you use? 

A. STANDBY 

B. Differential 

C. FULL 

D. CHECKSUM 

E. BULK_LOGGED 

F. CONTINUE_AFTER_ERROR 

G. SIMPLE 

H. DBO_ONLY 

I. COPY_ONLY 

J. SKIP 

K. RESTART 

L. Transaction log 

M. NO_CHECKSUM 

N. NORECOVERY 

View Answer

Q5. You administer a Microsoft SQL Server 2012 server. One of the databases on the server supports a highly active OLTP application. 

Users report abnormally long wait times when they submit data into the application. 

You need to identify which queries are taking longer than 1 second to run over an extended period of time. 

What should you do? 

A. use SQL Profiler to trace all queries that are processing on the server. Filter queries that have a Duration value of more than 1,000. 

B. Use sp_configure to set a value for blocked process threshold. Create an extended event session. 

C. Use the Job Activity monitor to review all processes that are actively running. Review the Job History to find out the duration of each step. 

D. Run the sp_who command from a query window. 

E. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log. 

View Answer

Q6. You administer a Microsoft SQL Server 2012 database instance. You create a new user named UserA. 

You need to ensure that UserA is able to create SQL Server Agent jobs and execute SQL Server agent jobs owned by UserA 

To which role should you add UserA? 

A. DatabaseMailUserRole 

B. ServerGroupAdministratorGroup 

C. SQLAgentUserRole 

D. Securityadmin 

View Answer

Q7. You install Microsoft SQL Server 2012 on a new server. 

After setup is complete, you attempt to start the SQL Server service. After being in a starting state for a few moments, the service goes back to a stopped state. You need to determine the cause of the failure. 

Which file should you use? 

A. %programfiles%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\Errorlog 

B. %programfiles%\Microsoft SQL Server\110\Setup Bootstrap\Log\Summary.txt 

C. %programfiles%\Microsoft SQL Server\110\Shared\ErrorDumps\SQLDmpr[XXXX].mdmp 

D. %programfiles%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf 

View Answer

Q8. You develop a database for a travel application. 

You need to design tables and other database objects. 

You create a stored procedure. 

You need to supply the stored procedure with multiple event names and their dates as parameters. 

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

Q9. You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. 

There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space. 

You also need to prevent reading queries from blocking writing queries. 

Which isolation level should you use? 

A. SERIALIZABLE 

B. SNAPSHOT 

C. READ COMMITTED SNAPSHOT 

D. REPEATABLE READ 

View Answer

Q10. 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 

View Answer