Free Microsoft 70-462 Exam Dumps Questions & Answers

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.

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

You administer a Microsoft SQL Server 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 Policy
• C.A Resource Pool
• D.A Server Audit Specification
• E.An Extended Event session
• F.A SQL Profiler Trace
• G.A Database Audit Specification

You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person. The tables have the following definitions:

You create a view named VwEmployee as shown in the following Transact-SQL statement.

Users are able to use single INSERT statements or INSERT...SELECT statements into this view. You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view.
Which Transact-SQL statement should you use?

• A.CREATE TRIGGER TrgVwEmployeeON VwEmployeeINSTEAD OF INSERTASBEGININSERT INTO Person(Id, FirstName, LastName)SELECT Id, FirstName, LastName, FROM insertedINSERT INTO Employee(PersonId, EmployeeNumber)SELECT Id, EmployeeNumber FROM insertedEND
• B.CREATE TRIGGER TrgVwEmployeeON VwEmployeeFOR INSERTASBEGININSERT INTO Person(Id, FirstName, LastName)SELECT Id, FirstName, LastName, FROM insertedINSERT INTO Employee(PersonId, EmployeeNumber)SELECT Id, EmployeeNumber FROM insertedEND
• C.CREATE TRIGGER TrgVwEmployeeON VwEmployeeINSTEAD OF INSERTASBEGINDECLARE
  @ID INT, @FirstName NVARCHAR(25),@LastName NVARCHAR(25), @PersonID
  INT,@EmployeeNumber NVARCHAR(15)SELECT @ID = ID, @FirstName = FirstName,@LastName
  = LastName, @EmployeeNumber = EmployeeNumberFROM insertedINSERT INTO Person(Id, FirstName, LastName)VALUES(@ID, @FirstName, @LastName)INSERT INTO Employee(PersonID, EmployeeNumber)VALUES(@PersonID, @EmployeeNumberEND
• D.CREATE TRIGGER TrgVwEmployeeON VwEmployeeINSTEAD OF INSERTASBEGININSERT INTO Person(Id, FirstName, LastName)SELECT Id, FirstName, LastName FROM VwEmployeeINSERT INTO Employee(PersonID, EmployeeNumber)SELECT Id, EmployeeNumber FROM VwEmployeeEND

You administer two Microsoft SQL Server 2012 databases named Contoso and ContosoWarehouse.
You plan to replicate tables from the Contoso database to the ContosoWarehouse database.
Indexes will be added to the tables in ContosoWarehouse to improve the performance of reports.
You need to ensure that the following requirements are met:
* Indexes are not modified when the subscriptions are reinitialized.
* Only the clustered index from Contoso will be replicated.
* Minimal transaction log activity during the reinitialization.
What should you do? Choose the correct option(s).

Correct Answer:

Explanation

You administer a Microsoft SQL Server 2012 environment that contains a production SQL Server 2005 instance named SQL2005 and a development SQL Server 2012 instance named SQL2012.
The development team develops a new application that uses the SQL Server 2012 functionality. You are planning to migrate a database from SQL2005 to SQL2012 so that the development team can test their new application.
You need to migrate the database without affecting the production environment.
Which three 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

References: