Implementing High Availability

Question 1

Which role is valid for Database Mirroring?

• Primary
• Publisher
• Principal
• Monitor

Answer 1

• Principal

EXPLANATION

The principal, which is one of the roles for Database Mirroring, specifies the database that is accepting connections and processing transactions.

Question 2

Which of the following are valid actions for a witness? (Choose all that apply.)

1. Serve the database when the principal and mirror are offline
2. Arbitrate a failover for the High Availability operating mode
3. Service multiple Database Mirroring sessions
4. Arbitrate a failover for the High Safety operating mode

Answer 2

2. Arbitrate a failover for the High Availability operating mode
3. Service multiple Database Mirroring sessions

EXPLANATION

The High Availability operating mode has a witness that is used to arbitrate automatic failover.

A single witness server can service multiple Database Mirroring sessions.

Question 3

Which of the following are endpoint options that are required for transactions to be exchanged between principal and mirror databases? (Choose two. Each choice represents a portion of the correct answer.)

1. COMPRESSSION set to ENABLED
2. Port 6083 specified for communications
3. ROLE set to PARTNER
4. STATE configured with the default option

Answer 3

2. Port 6083 specified for communications
3. ROLE set to PARTNER

EXPLANATION

You must specify a port number for communications.

To exchange transactions between the principal and mirror database, the endpoint created on the instance hosting these databases must be created with a role of either PARTNER or ALL.

Question 4

Which of the following is a valid step for preparing a database to participate in a Database Mirroring session? (Choose all that apply.)

1. Restore the database with RECOVERY.
2. Restore the database with NORECOVERY.
3. Configure distribution.
4. Back up the database.

Answer 4

2. Restore the database with NORECOVERY.
3. Back up the database.

EXPLANATION

A backup of the primary database is restored to the mirror.

The database must be unrecovered to participate in Database Mirroring.

Question 5

Which database setting is valid for Database Mirroring?

• Full recovery model
• 80 compatibility level
• Read-only
• Bulk-logged recovery model

Answer 5

Full recovery model

Question 6

Which of the following are characteristics of the High Availability operating mode? (Choose all that apply.)

1. Manual failover
2. Asynchronous data transfer
3. Automatic failover
4. Synchronous data transfer

Answer 6

3. Automatic failover
4. Synchronous data transfer

EXPLANATION

The High Availability and High Safety operating modes have synchronous transfer.

Automatic failover is available only with the High Availability operating mode and only when the witness server is online.

Question 7

Which of the following are characteristics of the High Performance operating mode? (Choose all that apply. Each answer forms a complete solution.)

1. Manual failover
2. Synchronous data transfer
3. Automatic failover
4. Asynchronous data transfer

Answer 7

1. Manual failover
2. Asynchronous data transfer

EXPLANATION

The High Performance operating mode has asynchronous data transfer.

The High Safety and High Performance operating modes require manual failover

Question 8

Which of the following are characteristics of the High Safety operating mode? (Choose all that apply. Each answer forms a complete solution.)

1. Asynchronous data transfer
2. Synchronous data transfer
3. Automatic failover
4. Manual failover

Answer 8

2. Synchronous data transfer
3. Manual failover

EXPLANATION

The High Availability and High Safety operating modes have synchronous transfer.

The High Safety and High Performance operating modes require manual failover.

Question 9

Coho Vineyards has recently experienced problems with its distribution system. Delays in scheduling trucks and getting shipments out to suppliers were caused by a series of hardware failures. Management has authorized the chief technical officer (CTO) to acquire a hardware solution capable of withstanding the failure of an entire server. Hardware that is compatible with clustering will be acquired. Which operating system should you install to meet these business requirements for the least cost?

• Windows 2000 Advanced Server
• Windows Server 2003 Enterprise Edition
• Windows Server 2003 Standard Edition
• Windows 2000 Server Standard Edition

Answer 9

Windows Server 2003 Standard Edition

EXPLANATION

Windows Server 2003 Standard Edition can be used to build a two-node cluster for minimal cost.

Question 10

The CTO at Coho Vineyards has decided to purchase two servers for clustering that will be used to run the distribution system. Which combination of operating system version and cluster type provides the most fault tolerance for the lowest cost?

• Windows Server 2003 Enterprise Edition with a majority node set cluster
• Windows Server 2003 Standard Edition with a majority node set cluster
• Windows Server 2003 Standard Edition with a standard cluster
• Windows Server 2003 Enterprise Edition with a standard cluster

Answer 10

Windows Server 2003 Standard Edition with a standard cluster

EXPLANATION

Windows Server 2003 Standard Edition supports a two-node standard cluster, which allows one node to fail with the distribution application still operational. This is the most inexpensive option.

Question 11

Which service needs to be running for health checks to be executed within a cluster?

• Server service
• Terminal Services service
• RPC service
• Net Logon service

Answer 11

RPC service

EXPLANATION

Health checks are executed using the RPC service.

Question 12

Consolidated Messenger is experiencing outages because of hardware failures. Because the company’s business is run from SQL Server databases, a solution needs to be implemented to minimize downtime. Management also wants to ensure that the system can recover from failures without requiring the intervention of IT staff. What technology can you use to accomplish these requirements?

• Log shipping
• Replication
• Database snapshots
• Failover clustering

Answer 12

Failover clustering

EXPLANATION

Failover clustering protects from hardware failure and can detect an outage and fail over to another node automatically.

Question 13

Trey Research currently has four instances of SQL Server running a variety of databases in support of the company’s medical research. Instance1 requires 200 GB of disk space for databases and serves more than 500 concurrent users. Instance2 requires about 1 terabyte of storage space for a small group of 25 researchers who are investigating genome therapy. Instance3 and Instance4 contain smaller databases that manage all the company’s infrastructure (for example, HumanResources, Payroll, and Contacts). The Genetrak database on Instance1 routinely consumes more than 60 percent of the processor capacity. Instance2 averages 45 percent processor utilization. Which version and edition of Windows is required to build a SQL Server cluster environment at the minimal cost?

• Windows Server 2003 Standard Edition
• Windows 2000 Datacenter Edition
• Windows Server 2003 Enterprise Edition
• Windows 2000 Advanced Server

Answer 13

Windows Server 2003 Enterprise Edition

EXPLANATION

Because of processing needs, Trey Research requires a minimum of three cluster nodes to ensure that performance is not degraded. Windows Server 2003 Enterprise Edition supports up to four nodes in a cluster.

Question 14

Which of the following is a valid role for log shipping?

• Standby
• Primary
• Principal
• Distributor

Answer 14

Primary

EXPLANATION

Log shipping has databases that can be in the role of either primary or secondary.

Question 15

Which of the following instance-level objects is required on the secondary to be able to access the database when it is recovered?

• Database master key
• Logins
• DDL triggers
• SQL Server Agent jobs

Answer 15

Logins

EXPLANATION

Without the logins, the secondary database cannot be accessed.

Question 16

Which command is issued to recover the secondary database and enable it to start accepting transactions?

• RESTORE DATABASE…WITH STANDBY
• ALTER DATABASE
• EXECUTE ALTER LOGIN
• RESTORE DATABASE…WITH RECOVERY

Answer 16

RESTORE DATABASE…WITH RECOVERY

EXPLANATION

Restoring the database with the RECOVERY option brings the database online.

Question 17

Contoso Limited needs to implement a replication architecture that maintains a reporting server while also ensuring that any changes to the report server do not get sent back to the publisher. Which replication option would be the best solution?

• Snapshot replication
• Transactional replication
• Merge replication
• Peer-to-peer replication

Answer 17

Transactional replication

EXPLANATION

Transactional replication sends changes to the reporting server without allowing changes to be sent back to the publisher.

Question 18

Contoso Limited needs to implement a replication architecture that maintains a reporting server. A small number of changes might be made on the report server when necessary corrections to reports need to be sent back to the publisher. The reporting solution needs to be always available, even if the publisher is offline, while also providing the best performance. Which replication option is the best solution?

• Transactional replication with immediate updating subscribers
• Transactional replication
• Merge replication
• Transactional replication with queued updating subscribers

Answer 18

Transactional replication with queued updating subscribers

EXPLANATION

Transactional replication with queued updating subscribers allows changes to be made at the subscriber even if the publisher is not available while also providing the best performance of all the options.

Question 19

A. Datum Corporation specializes in credit card processing services, and its systems need to maintain more than 99.999% (five 9s) of availability. At the same time, the write volume exceeds the capacity of the current hardware and the projected capacity of new hardware that was proposed. Employees have determined that if the writes could be batched in some manner while read operations are offloaded to another server at the same time, the proposed new hardware would provide enough capacity for the next two or three years. To achieve the necessary write capacity and redundancy, they need to deploy five servers that are geographically separated and two more servers that can be used as both a failover and a primary server for read operations. What is the best choice of technology to meet these business needs?

• Transactional replication in a central publisher configuration
• Database Mirroring
• Transactional replication in a peer-to-peer configuration
• Transactional replication in a queued updating subscriber configuration

Answer 19

Transactional replication in a peer-to-peer configuration

EXPLANATION

Peer-to-peer replication enables all servers to be readable and writable if desired, meets the uptime requirements, and enables more than two servers to participate in sending changes between them.

Question 20

Consolidated Messenger distributes packages between businesses in downtown AbcCity. Orders are received at the central office, and messengers on inline skates receive pickup and delivery instructions through Microsoft Pocket PC-powered devices that use merge replication to synchronize data. When packages are received and delivered, the messenger collects a signature from the customer that triggers an update to the order record. Global positioning system (GPS) devices carried in the messengers’ backpacks continuously transmit location data back to the office so that delivery progress can be tracked. Because changes occur to the same order data at both the central office as well as at the messenger, how should you design the database, replication, and conflict resolution?

• One table for the delivery order and one table for the receipt/delivery information, one publication from the central office to each messenger, and publisher always wins conflict resolution
• One table for the delivery order and one table for the receipt/delivery information, one publication from the central office to each messenger with the data for the delivery order, one publication from the central office to each messenger with the receipt/delivery data, publisher always wins conflict resolution on the delivery order publication, and publisher always wins conflict resolution on the receipt/delivery data
• One table that contains the delivery order and receipt/delivery information, one publication from the central office to messengers, and publisher always wins conflict resolution
• One table that contains the delivery order and receipt/delivery information, one publication from each messenger to the central office, and publisher always wins conflict resolution
• One table that contains the delivery order and receipt/delivery information, one publication from the central office to messengers, and subscriber always wins conflict resolution
• One table for the delivery order and one table for the receipt/delivery information, one publication from the central office to each messenger with the data for the delivery order, one publication from the central office to each messenger with the receipt/delivery data, publisher always wins conflict resolution on the delivery order publication, and subscriber always wins conflict resolution on the receipt/delivery data

Answer 20

One table for the delivery order and one table for the receipt/delivery information, one publication from the central office to each messenger with the data for the delivery order, one publication from the central office to each messenger with the receipt/delivery data, publisher always wins conflict resolution on the delivery order publication, and subscriber always wins conflict resolution on the receipt/delivery data

EXPLANATION

The central office owns the portion of the data related to the delivery order and always should have the master copy of the data. The messengers are delivering the packages, so any information related to receipt/delivery is owned by each messenger. Because there are conflicting requirements for data ownership between the portions of data, you need at least two publications because conflict detection cannot be chosen dynamically for each synchronization cycle. The publications should all be created at the central office for management. Because the central office owns the delivery order information, the central office always should win in a conflict. Because the messenger owns the delivery/receipt information, the messenger always should win in a conflict. The configuration described in this answer meets all these requirements.