Microsoft Administering Microsoft SQL Server 2012/2014 Databases - 070-462 Exam Practice Test
You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3.
You have permissions on both Database1 and Database2.
You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3.
dbo.usp_InsertEvent must execute other stored procedures in the other databases.
You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure.
Which Transact-SQL statement should you use?
You have permissions on both Database1 and Database2.
You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3.
dbo.usp_InsertEvent must execute other stored procedures in the other databases.
You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure.
Which Transact-SQL statement should you use?
Correct Answer: A
Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).
You administer a Microsoft Azure SQL Database database named Orders.
You need to create a copy of Orders named Orders_Reporting.
Which Transact-SQL command should you use?
You need to create a copy of Orders named Orders_Reporting.
Which Transact-SQL command should you use?
Correct Answer: A
You administer a Microsoft SQL Server 2012 instance. After a routine shutdown, the drive that contains tempdb fails.
You need to be able to start the SQL Server.
What should you do?
You need to be able to start the SQL Server.
What should you do?
Correct Answer: C
Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).
You have a two-node SQL Server 2014 cluster that has an AlwaysOn availability group configured in synchronous mode.
You plan to provide a reporting solution by using a third node in the cluster.
You need to add the third node. The solution must prevent any impact on the performance of database writes.
You install another server that has SQL Server installed.
Which three additional actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
You plan to provide a reporting solution by using a third node in the cluster.
You need to add the third node. The solution must prevent any impact on the performance of database writes.
You install another server that has SQL Server installed.
Which three additional actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Correct Answer:
Explanation
Step 1: Add a new availability replica in asynchronous mode.
Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.
Incorrect: Add a new availability replica in synchronous mode.
Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency.
Step 2: Set Readable Secondary to Read-intent only.
For the secondary role, select a new value from the Readable secondary drop list, as follows: Read-intent only Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.
Step 3: Configure read-only routing.
Note: Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica (that is, a replica that is configured to allow read-only workloads when running under the secondary role). To support read-only routing, the availability group must possess an availability group listener. Read-only clients must direct their connection requests to this listener, and the client's connection strings must specify the application intent as "read-only." That is, they must be read-intent connection requests.
References:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-f
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availa
Which of the following is a valid data source which you can import into SQL Server 2012?
Correct Answer: A
Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).
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?
You need to prevent users from disabling server audits in Server01.
What should you create?
Correct Answer: G
You administer several Microsoft SQL Server 2012 servers.
You want to run scheduled checks to confirm that the databases on the servers are not configured to AutoShrink.
You need to create a policy to check the condition.
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.)
You want to run scheduled checks to confirm that the databases on the servers are not configured to AutoShrink.
You need to create a policy to check the condition.
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.)
Correct Answer:
Explanation
The Evaluation Mode defines the combination of 1) when the policy will be evaluated and 2) what the system will do in case of policy violation. The following evaluation modes are supported:
You administer a Microsoft SQL Server database.
You use an OrderDetail table that has the following definition:
You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column.
Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
You use an OrderDetail table that has the following definition:
You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column.
Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
Correct Answer:
Explanation
According to these references, this answer looks correct.
References:
http://msdn.microsoft.com/en-us/library/ms188783.aspx
http://msdn.microsoft.com/en-us/library/ms189280.aspx
You are the senior database administrator for a Microsoft SQL Server environment that uses Windows Azure SQL Database.
You need to grant a junior database administrator only the permissions required to create new logins.
Which Windows Azure SQL Database role should you assign the junior database administrator?
You need to grant a junior database administrator only the permissions required to create new logins.
Which Windows Azure SQL Database role should you assign the junior database administrator?
Correct Answer: D
Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).
You develop an application that uses data from a Microsoft SQL Server database.
A stored procedure named MyProcedure experiences excessive blocking problems.
You need to enable row versioning. However, you do not want to force every connection to have row versioning enabled.
Which code segment or segments should you use to complete the Transact-SQL statements? To answer, drag the appropriate command or commands to the appropriate position or positions in the answer area to complete the Transact-SQL statements. Answer choices may be used once, more than once, or not at all. Answer targets may be used once. Additionally, you may need to drag the split bar between panes or scroll to view content.
A stored procedure named MyProcedure experiences excessive blocking problems.
You need to enable row versioning. However, you do not want to force every connection to have row versioning enabled.
Which code segment or segments should you use to complete the Transact-SQL statements? To answer, drag the appropriate command or commands to the appropriate position or positions in the answer area to complete the Transact-SQL statements. Answer choices may be used once, more than once, or not at all. Answer targets may be used once. Additionally, you may need to drag the split bar between panes or scroll to view content.
Correct Answer:
Explanation
Box 1: SET ALLOW_SNAPSHOT_ISOLATION ON
Database administrators control the database-level settings for row versioning by using the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options in the ALTER DATABASE statement.
The following Transact-SQL statement will enable ALLOW_SNAPSHOT_ISOLATION:
ALTER DATABASE AdventureWorks2008R2
SET ALLOW_SNAPSHOT_ISOLATION ON;
Incorrect: Not READ_COMMITTED_SNAPSHOT
When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database.
Box 2: SET TRANSACTION ISOLATION LEVEL SNAPSHOT
The ALLOW_SNAPSHOT_ISOLATION ON option transactions can specify the SNAPSHOT transaction isolation level.
References:
https://technet.microsoft.com/en-us/library/ms175095(v=sql.105).aspx
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options