Home > Sample chapters > Programming > SQL Server

Administering Microsoft SQL Server 2012 Databases: Clustering and AlwaysOn

Answers

This section contains the answers to the lesson review questions and solutions to the case scenarios in this chapter.

Lesson 1

  1. Correct Answer: C

    1. Incorrect: Windows 7 Enterprise edition cannot be used as the host operating system for a multi-site failover cluster instance.

    2. Incorrect: Windows Server 2008 Enterprise edition does support failover clustering but does not support multi-site failover clusters.

    3. Correct: Windows Server 2008 R2 Enterprise edition supports multi-site failover clustering.

    4. Incorrect: Windows Vista Ultimate edition cannot be used as the host operating system for a multi-site failover cluster instance.

  2. Correct Answer: A

    1. Correct: The Move-ClusterGroup cmdlet enables you to move a clustered service or application from one node to another in a failover cluster. You can use this cmdlet to perform manual failover of a SQL Server clustered instance from one node to another.

    2. Incorrect: The Move-ClusterResource cmdlet enables you to move a clustered resource from one clustered application to another but not to move a clustered service or application from one node to another.

    3. Incorrect: The Move-ClusteredSharedVolume cmdlet enables you to move the ownership of a clustered shared volume from one node to another. You cannot use this cmdlet to perform failover on a SQL Server clustered instance.

    4. Incorrect: The Move-ClusterVirtualMachineRole cmdlet enables you to move a clustered virtual machine to a different cluster node. You cannot use this cmdlet to perform failover on a SQL Server clustered instance.

  3. Correct Answer: A

    1. Correct: You should evict the failed node. After this is done, you can repair the server, join it back to the cluster, and then reinstall SQL Server.

    2. Incorrect: You should not evict the new primary node; you should instead evict the failed node from the cluster.

    3. Incorrect: You should not reinstall SQL Server on the failed node until you have evicted the node, repaired the failure, and joined the node back to the cluster.

    4. Incorrect: You should not join the failed node back to the cluster until you have evicted and repaired the node.

  4. Correct Answers: A, B, and D

    1. Correct: You must run advanced cluster preparation on all nodes that will participate in the failover cluster instance.

    2. Correct: You must run advanced cluster preparation on all nodes that will participate in the failover cluster instance.

    3. Incorrect: You run advanced cluster completion only on the node that has control of the shared disk resource. Because SQL-B has control of this resource, you should not run this process on SYD-A.

    4. Correct: You run advanced cluster completion only on the node that has control of the shared disk resource.

Lesson 2

  1. Correct Answer: C

    1. Incorrect: You can enable AlwaysOn Availability Groups by using either SQL Server Configuration Manager or PowerShell. You cannot perform this task by using SQL Server Management Studio.

    2. Incorrect: You can enable AlwaysOn Availability Groups by using either SQL Server Configuration Manager or PowerShell. You cannot perform this task by using SQL Server Installation Center.

    3. Correct: You can enable AlwaysOn Availability Groups by using either SQL Server Configuration Manager or PowerShell.

    4. Incorrect: You can enable AlwaysOn Availability Groups by using either SQL Server Configuration Manager or PowerShell. You cannot perform this task by using SQL Server Data Tools.

  2. Correct Answer: A

    1. Correct: You use the Switch-SqlAvailabilityGroup cmdlet to perform manual availability group failover.

    2. Incorrect: You use the New-SqlHadrEndpoint cmdlet to create a mirroring endpoint for AlwaysOn Availability Groups. You use the Switch-SqlAvailabilityGroup cmdlet to perform manual availability group failover.

    3. Incorrect: You use the New-SqlAvailabilityGroupListener cmdlet to create a new availability group listener. You use the Switch-SqlAvailabilityGroup cmdlet to perform manual availability group failover.

    4. Incorrect: You use the Enable-SqlAlwaysOn cmdlet to enable AlwaysOn on an instance. You use the Switch-SqlAvailabilityGroup cmdlet to perform manual availability group failover.

  3. Correct Answer: D

    1. Incorrect: You use the New-SqlAvailabilityGroupListener cmdlet to create a new availability group listener.

    2. Incorrect: You use the Switch-SqlAvailabilityGroup cmdlet to perform manual availability group failover.

    3. Incorrect: You use the Enable-SqlAlwaysOn cmdlet to enable AlwaysOn on an instance.

    4. Correct: You use the New-SqlHadrEndpoint cmdlet to create a mirroring endpoint for AlwaysOn Availability Groups.

  4. Correct Answers: B and D

    1. Incorrect: To support automatic failover to any available secondary replica, all replicas must use synchronous-commit mode. Automatic failover cannot occur if the primary replica uses asynchronous-commit mode.

    2. Correct: To support automatic failover to any available secondary replica, all replicas must use synchronous-commit mode. Automatic failover cannot occur if the primary replica uses asynchronous-commit mode.

    3. Incorrect: To support automatic failover to any available secondary replica, all replicas must use synchronous-commit mode. Automatic failover cannot occur if the primary replica uses asynchronous-commit mode.

    4. Correct: To support automatic failover to any available secondary replica, all replicas must use synchronous-commit mode. Automatic failover cannot occur if the primary replica uses asynchronous-commit mode.

Case Scenario 1

  1. You must deploy SQL Server 2012 Enterprise edition to support the proposed cluster configuration because this is the only edition that supports four nodes.

  2. You must support multi-subnet failover clustering, which requires either Windows Server 2008 R2 Enterprise or Datacenter editions. Windows Server 2008 does not support multi-subnet failover clustering.

  3. You can use either the Failover Cluster manager or the Move-ClusterGroup PowerShell cmdlet to perform manual failover.

Case Scenario 2

  1. You can use the Enterprise or Datacenter editions of Windows Server 2008 R2 to support the proposed configuration.

  2. Because the SQL Server service uses local accounts, you must use certificate-based authentication for the endpoints.

  3. You must configure the synchronous-commit availability mode, given the requirement for automatic failover.