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

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 use a contained database named ContosoDb within a domain. 

You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations. 

Which type of user should you create? 

A. SQL user without login 

B. User mapped to an asymmetric key 

C. Domain user 

D. login mapped to a virtual account 

View Answer

Q4. You administer a Microsoft SQL Server 2012 instance. 

You discover that the SQL Agent Error Log file is rapidly growing in size. 

You need to ensure that the SQL Agent Error Log file does not grow rapidly when SQL Server agent jobs execute. 

What should you do? 

A. Execute the sp_cycle_agent_errorlog stored procedure. 

B. Configure event forwarding. 

C. Enable the Auto Shrink option on the master database. 

D. Enable the Auto Shrink option on the msdb database. 

E. Disable the Include execution trace messages feature. 

View Answer

Q5. You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.) 

Which Transact-SQL query should you use? 

A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers-CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW 

B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS 

C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO 

D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS 

E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO 

F. SELECT Name, Country, Crderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS 

G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers') 

H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers') 

View Answer

Q6. You administer a Microsoft SQL Server 2012 database named Contoso that contains a single user-defined database role namedBillingUsers. 

All objects in Contoso are in the dbo schemA. 

You need to grant EXECUTE permission for all stored procedures in Contoso to BillingUsers. 

Which Transact-SQL statement should you use? 

A. GREATE ROLE proc_caller GRANT EXECUTE ON Schema : : dbo TO proc_caller ALTER ROLE proc_caller ADD MEMBER BillingUsers 

B. GRANT EXECUTE ON INFORMATION_SCHEMA.ROUTINES TO BillingUsers 

C. EXEC sp_addrolemember 'executor' , 'BillingUsers' 

D. GREATE ROLE proc_caller GRANT EXECUTE ON ALL PROCEDURES TO proc_caller ALTER MEMBER BillingUsers ADD TO ROLE proc_caller 

View Answer

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

You need to write messages to the Application Log when users are added to or removed from a fixed server role in Server01. 

What should you create? 

A. A Database Audit Specification 

B. A Policy 

C. An Alert 

D. A SQL Profiler Trace 

E. A Resource Pool 

F. An Extended Event session 

G. A Server Audit Specification 

View Answer

Q8. You administer all the deployments of Microsoft SQL Server 2012 in your company. 

A database contains a large product catalog that is updated periodically. 

You need to be able to send the entire product catalog to all branch offices on a monthly basis. 

Which configuration should you use? 

A. . Two servers configured in the same data center 

. A primary server configured to perform log-shipping every 10 minutes 

. A backup server configured as a warm standby 

B. . SQL Server that includes an application database configured to perform transactional replication 

C. . Two servers configured in the same data center 

. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode 

. One server configured as an Active Secondary D 

. Two servers configured in a Windows Failover Cluster in the same data center 

. SQL Server configured as a clustered instance 

D. . SQL Server that includes an application database configured to perform snapshot replication 

E. . Two servers configured in different data centers 

. SQL Server Availability Group configured in Synchronous-Commit Availability Mode 

. One server configured as an Active Secondary 

F. . Two servers configured on the same subnet 

. SQL Server Availability Group configured in Synchronous-Commit Availability Mode 

G. . Two servers configured in different data centers 

. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode 

View Answer

Q9. You administer a Microsoft SQL Server 2012 instance. 

You need to configure a new database to support FILETABLES. 

What should you do? Choose all that apply. 

A. Disable FILESTREAM on the Database. 

B. Enable FILESTREAM on the Server Instance. 

C. Configure the Database for Partial Containment. 

D. Create a non-empty FILESTREAM file group. 

E. Enable Contained Databases on the Server Instance. 

F. Set the FILESTREAM directory name on the Database. 

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