Q61. You administer all the deployments of Microsoft SQL Server 2012 in your company.
You need to ensure that an OLTP database that includes up-to-the-minute reporting requirements can be off-loaded from the primary database to another server. You also need to be able to add indexes to the secondary database.
Which configuration should you use?
A. . Two servers configured in different data centers
. SQL Server Availability Group configured in Synchronous-Commit Availability Mode
. One server configured as an Active Secondary
B. . 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
C. . 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
D. . Two servers configured in different data centers
. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
E. . Two servers configured on the same subnet
. SQL Server Availability Group configured in Synchronous-Commit Availability Mode
F. . SQL Server that includes an application database configured to perform transactional replication
G. . SQL Server that includes an application database configured to perform snapshot replication
H. . Two servers configured in a Windows Failover Cluster in the same data center
. SQL Server configured as a clustered instance
Answer: F
Q62. 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
Answer: C
Q63. 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?
A. You should include two servers configured in an Active-Active SQL Server 2012 Cluster
B. You should include a primary SQL Server 2012 database that uses transactional replication to replicate data to a secondary database.
C. You should include two servers configured in an Active-Passive SQL Server 2012 Cluster.
D. You should include two servers in an Asynchronous-Commit Availability Mode Availability Group.
E. You should include two servers in a Synchronous-Commit Availability Mode Availability Group
Answer: B
Q64. You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views.
You grant User1 access to execute the stored procedure.
You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required.
What should you do? (Each correct answer presents a complete solution. Choose all that apply.)
A. Create a SQL Server login that has VIEW SERVER STATE permissions. Create an application role and a secured password for the role.
B. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
C. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement.
D. Grant the db_owner role on the database to User1.
E. Grant the sysadmin role on the database to User1.
Answer: BC
Q65. You are a database administrator for a Microsoft SQL Server 2012 environment.
You want to deploy a new application that will scale out the workload to at least five different SQL Server instances.
You need to ensure that for each copy of the database, users are able to read and write data that will then be synchronized between all of the database instances.
Which feature should you use?
A. Database Mirroring
B. Peer-to-Peer Replication
C. Log Shipping
D. Availability Groups
Answer: B
Q66. You administer a Microsoft SQL Server 2012 instance.
You need to stop a blocking process that has an SPID of 64 without stopping other processes
What should you do?
A. Execute the following Transact-SQL statement: EXECUTE sp_KillSPID 64
B. Restart the SQL Server service.
C. Execute the following Transact-SQL statement: KILL 64
D. Execute the following Transact-SQL statement: ALTER SESSION KILL '64'
Answer: C
Q67. 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
Answer: D
Q68. 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. The Sales role is granted the Select permission on the Customers schema.
You need to ensure that UserA is disallowed to select from any of the tables in the Customers schema.
Which Transact-SQL statement should you use?
A. DENY SELECT ON Object::Regions FROM UserA
B. DENY SELECT ON Object::Regions FROM Sales
C. REVOKE SELECT ON Schema::Customers FROM Sales
D. REVOKE SELECT ON Schema::Customers FROM UserA
E. REVOKE SELECT ON Object::Regions FROM Sales
F. REVOKE SELECT ON Object::Regions FROM UserA
G. DENY SELECT ON Schema::Customers FROM Sales
H. DENY SELECT ON Schema::Customers FROM UserA
I. EXEC sp_addrolemember 'Sales', 'UserA'
J. EXEC sp droprolemember 'Sales', 'UserA'
Answer: H
Q69. Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. The business team wants a report that displays the total number of orders placed on the current day.
You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?
A. SELECT COUNT(*) FROM SalesOrders
WHERE OrderTime = CONVERT(DATE, GETDATE())
B. SELECT COUNT(*) FROM SalesOrders
WHERE OrderTime = GETDATE()
C. SELECT COUNT(*) FROM SalesOrders
WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I, 112))
D. SELECT COUNT(*) FROM SalesOrders
WHERE OrderTime >= CONVERT(DATE, GETDATE())
AND OrderTime < DATEADD(DAY, CONVERT(DATE, GETDATE()))
Answer: D
Q70. You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2012 Standard Edition. The server hosts a database named Orders.
Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmenteD.
You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation.
Which Transact-SQL command should you use?
A. ALTER INDEX IX_OrderDateON CustomerOrderDISABLE
B. ALTER INDEX IX_OrderDateON CustomerOrderENABLE
C. ALTER INDEX IX_OrderDateON CustomerOrderREORGANIZE
D. ALTER INDEX IX OrderDateON CustomerOrderREBUILD
Answer: C