Microsoft Administering Microsoft SQL Server 2012/2014 Databases - 70-462 FREE EXAM DUMPS QUESTIONS & ANSWERS]

Question 1

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.

Discussion 0

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

Question 2

You create an availability group named HaContoso that has replicas named Server01/HA, Server02/HA, and Server03/HA.
Currently, Server01/HA is the primary replica.
You need to ensure that the following requirements are met:
* Backup operations occur on Server02/HA.
* If Server02/HA is unavailable, backup operations occur on Server03/HA.
* Backup operations do not occur on Server01/HA.
How should you configure HaContoso?

A. Set the backup preference of HaContoso to Secondary only.Set the backup priority of Server02/HA to
20.Set the backup priority of Server03/HA to 10. B. Set the backup preference of HaContoso to Secondary only.Set the backup priority of Server02/HA to
10.Set the backup priority of Server03/HA to 20. C. Set the backup preference of HaContoso to Prefer Secondary.Set the backup priority of Server02/HA to
20.Set the backup priority of Server03/HA to 10. D. Set the exclude replica of Server01/HA to true.Set the backup priority of Server02/HA to 10.Set the backup priority of Server03/HA to 20.

Discussion 0

Correct Answer: A Vote an answer

Question 3

You administer a database that has User A, B and C.
User A must be able to create new tables and stored procedures, User B must be able to select, update, delete and insert data.
User C must be able to provide windows logins to the database
To which role or roles should you add to UserC? Select all that apply.

A. db_datawriter B. db_datareader C. db_ddladmin D. db_owner E. db_accessadmin

Discussion 0

Correct Answer: E Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 4

Question 5

You administer a Microsoft SQL Server database server.
When errors that have severity level of 18 or higher occur, 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. An Extended Events session B. Policies under Policy-Based Management C. Alerts and operators in SQL Server Agent D. SQL Mail

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 6

You administer a SQL Server 2012 database instance.
You need to configure the SQL Server Database Engine service on a failover cluster.
Which user account should you use?

A. A domain user B. A local user with Run as Service permissions C. The SQLBrowser account D. The BUILTIN\SYSTEM account

Discussion 0

Correct Answer: A Vote an answer

Question 7

You administer a Microsoft SQL Server 2012 database named Human_Resources.
You need to ensure that all read activity against any object in the Human_Resources database is audited and written to a text file.
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.)

Discussion 0

Correct Answer:

Explanation
Create a new Audit. For destination, select File.
Create a new Database Audit Specification on Human_Resources. For Audit Action Type, select Select, and for Object Class, select Database.
Enable Audi and Audi Specification.
The general process for creating and using an audit is as follows.
References:
https://msdn.microsoft.com/en-us/library/cc280386%28v=sql.110%29.aspx
https://msdn.microsoft.com/en-us/library/cc280663%28v=sql.110%29.aspx

Question 8

You administer a Microsoft SQL Server database named Contoso that contains a single user-defined database role named BillingUsers.
All objects in Contoso are in the dbo schema.
You need to grant EXECUTE permissions for all stored procedures in Contoso to BillingUsers.
Which Transact-SQL statement should you use?

A. GRANT EXECUTE ON INFORMATION_SCHEMA.ROUTINES TO BillingUsers B. EXEC sp_addrolemember 'db_procexecutor', 'BillingUsers' C. GRANT EXECUTE ON Schema::Contoso TO BillingUsers D. GRANT EXECUTE ON Schema::dbo TO BillingUsers

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 9

You administer a Microsoft SQL Server 2012 database.
You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:

You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location.
A hardware failure occurs and so a new server must be installed and configured.
After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the database.
You need to be able to restore the database.
Which Transact-SQL statement should you use before attempting the restore?

A. Option B B. Option A C. Option C D. Option D

Discussion 0

Correct Answer: C Vote an answer

Question 10

You administer a Microsoft SQL Server 2012.
A process that normally runs in less than 10 seconds has been running for more than an hour.
You examine the application log and discover that the process is using session ID 60.
You need to find out whether the process is being blocked.
Which Transact-SQL statement should you use?

A. SELECT * FROM sys.dm_exec_requests WHERE session_id = 60 B. DBCC OPENTRAN C. DBCC INPUTBUFFER (60) D. SELECT * FROM sys.dm_exec_sessions WHERE session_id = 60

Discussion 0

Correct Answer: A Vote an answer

Question 11

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

Discussion 0

Correct Answer:

Explanation

Question 12

You administer a Microsoft SQL Server database. The database is currently configured to log ship to a secondary server.
You are preparing to cut over to the secondary server by stopping log-shipping and bringing the secondary database online. You want to perform a tail-log backup.
You need to leave the primary database in a read-only state that can also have further transaction logs applied.
Which option of the BACKUP LOG command should you use?

A. FORMAT B. STANDBY C. NORECOVERY D. NO_TRUNCATE

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 13

You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table.

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use?

A. CREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT NULL,CustomerName varchar(255) NOT NULL,CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED(SourceID, CustomerID)); B. CREATE TABLE Customer(SourceID int NOT NULL PRIMARY KEY CLUSTERED,CustomerID int NOT NULL UNIQUE,CustomerName varchar(255) NOT NULL); C. CREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT NULL PRIMARY KEY CLUSTERED,CustomerName varchar(255) NOT NULL); D. CREATE TABLE Customer(SourceID int NOT NULL IDENTITY,CustomerID int NOT NULL IDENTITY,CustomerName varchar(255) NOT NULL);

Discussion 0

Correct Answer: A Vote an answer