Online Access Free 70-764 Exam Questions

You have the following Microsoft SQL Server instances. The instances are members of a cluster:

Server1 has 42 gigabytes (GB) of memory and server 2 has 4g of memory. The operating system and other processes on each.......... server required 4 GB memory.
Instance does not failover successfully to Server1.
You need to resolve the issue.
What should you do?

• A.Minimum server memory server memory option for Instances 1 to 16384
• B.Set the value of the Maximum server memory option for Instance2 to 32760.
• C.Set the value of the Maximum server memory option for Instance to 24576.
• D.Set the value of the Minimum server memory option for instance 2 to 0.

You are planning the deployment of two new Always On Failover Cluster Instances (FCIs) of Microsoft SQL Server to a single Windows Server Cluster with three nodes. The planned configuration for the cluster is shown in the Server Layout exhibit. (Click the Exhibit button.)

The SAN team has configured storage for the cluster and sent the configuration to you in the email shown in the SAN Team Email exhibit. (Click the Exhibit button.)

Each node of the cluster has identical local storage available as shown in the Local Storage exhibit. (Click the Exhibit button.)

All local storage is on SSD.
You need to plan specific configurations for the new cluster.
For each of the following statement, select Yes if the statement is true. Otherwise, select No.

Correct Answer:

Explanation

Box 1: Yes
tempdb on local storage. FCIs now support placement of tempdb on local non-shared storage, such as a local solid-state-drive, potentially offloading a significant amount of I/O from a shared SAN.
Prior to SQL Server 2012, FCIs required tempdb to be located on a symmetrical shared storage volume that failed over with other system databases.
Box 2: No
The VNN is set on the group level, not on the instance level.
Database client applications can connect directly to a SQL Server instance network name, or they may connect to a virtual network name (VNN) that is bound to an availability group listener. The VNN abstracts the WSFC cluster and availability group topology, logically redirecting connection requests to the appropriate SQL Server instance and database replica.
The logical topology of a representative AlwaysOn solution is illustrated in this diagram:

Box 3: No
You don't configure the SAN from a SQL Server, instead you can use a Microsoft Server server.
References:
http://download.microsoft.com/download/d/2/0/d20e1c5f-72ea-4505-9f26-fef9550efd44/microsoft%20sql%20se

You have an SQL Server 2014 server named SQL1. You are designing a performance monitoring solution.
You need to monitor the following events on SQL1:
* A deadlock graph
* Missing column statistics
* CPU performance statistics
* A batch of completed Transact-SQL statements
Which two tools should you use? Each correct answer presents a complete solution.

• A.SQL Server Profiler
• B.Database Engine Tuning Advisor
• C.dynamic management views
• D.Activity Monitor
• E.Data Profile Viewer

You use SQL Azure to store data used by an e-commerce application.
You develop a stored procedure named sp1.
Sp1 is used to read and change the price of all the products sold on the e-commerce site.
You need to ensure that other transactions are blocked from updating product data while sp1 is executing.
Which transaction isolation level should you use in sp1?

• A.serializable
• B.repeatable read
• C.snapshot
• D.read committed

You manage a database named DB1 that uses the following filegroups:

The database is configured to use full recovery model. Transaction logs are backed up to a backup set named TLogBackup.
The PRIMARY and FG2 for DB1
You need to design a piecemeal restore plan that meets all the above requirements. You need to bring critical filegroups online as soon as possible while minimizing restoration time. All damaged filegroups must be online after the restore operation completes.
Which five actions should you perform in sequence? To ansjver, 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: Perform a differential database backup...
Step 2: Perform a tail-log backup...
A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.
Step 3:
The PRIMARY and FG2 for DB1 are damaged. FG1 and FG3 are intact.
Step 4:
Transaction logs are backed up to a backup set named TLogBackup.
Step 5:
The PRIMARY and FG2 for DB1 are damaged.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-files-and-filegroups-sql-server?v
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server?view=sql-se