Administering Microsoft SQL Server 2012 Databases: Clustering and AlwaysOn

  • 7/15/2012
Contents

Lesson 1: Clustering SQL Server 2012

A SQL Server 2012 failover cluster instance is a special deployment of SQL Server 2012 that stores database files on a shared storage device. If the server that hosts one Database Engine instance fails, another Database Engine instance in the failover cluster takes control of the database files and seamlessly continues to service client requests.

After this lesson, you will be able to:

  • Prepare the host operating system for the installation of a failover cluster instance of SQL Server 2012.

  • Deploy a failover cluster instance.

  • Manage multiple instances on a cluster.

  • Deploy multi-subnet failover clusters.

  • Troubleshoot failover clusters.

Estimated lesson time: 60 minutes

Fulfilling Edition Prerequisites

You can deploy failover cluster instances only on specific editions of the host operating system and SQL Server. When planning the deployment of a failover cluster instance, remember that:

  • SQL Server 2012 Enterprise edition supports up to 16 cluster nodes. This edition of SQL Server is the only one that you can deploy in a production environment that supports multi-subnet failover clustering.

  • SQL Server 2012 Business Intelligence edition supports a two-node maximum for failover clusters.

  • SQL Server 2012 Standard edition supports a two-node maximum.

  • Windows Server 2008 R2 Enterprise and Datacenter editions support failover clustering. These editions also support multi-subnet failover clustering.

  • Windows Server 2008 Enterprise and Datacenter editions support failover clustering but do not support multi-subnet failover clustering.

You must have a functional Windows Server failover cluster prior to deploying SQL Server as a failover cluster instance. Only then can you install SQL Server as a failover cluster instance.

NOTE

MORE INFO WINDOWS SERVER FAILOVER CLUSTERING WITH SQL SERVER 2012

You can learn more about Windows Server failover clustering with SQL Server 2012 at http://msdn.microsoft.com/en-us/library/hh270278.aspx.

Windows Server 2008 R2 as Shared Storage

Except in the case of multi-subnet failover clusters, SQL Server failover cluster instances require shared storage to host the database and log files. In production situations, you use a dedicated storage area network (SAN) device for this task. If you are using a fiber channel SAN, you use vendor software to make the connection between Microsoft Windows Server 2008 or Windows Server 2008 R2 and the SAN. If you are using iSCSI, you can use the vendor-supplied software or the iSCSI initiator that is included with the server operating system. Connecting by using the iSCSI initiator is covered in the next section, Connecting to the SAN by Using iSCSI Initiator.

If you don’t have access to a SAN, you can use Windows Storage Server 2008 R2 as an iSCSI target by installing the iSCSI Software Target, which you can download from the Microsoft website. You can use this software to simulate an iSCSI storage device on a SAN when running virtual machines within a Hyper-V environment without actually having to connect to a traditionally deployed SAN.

NOTE

MORE INFO DOWNLOAD iSCSI SOFTWARE TARGET

You can download the iSCSI Software Target at http://www.microsoft.com/download/en/details.aspx?id=19867.

After you have installed the iSCSI Software Target on a computer running Windows Server 2008 R2, you must perform several steps to configure the server so other computers can connect and use a specially configured virtual hard disk file as a SAN storage device.

NOTE

iSCSI TARGET

Configuring the iSCSI target is unlikely to be covered directly on the 70-462 exam, but this is a necessary step to complete the clustering-related practice exercises in this chapter.

The first step you must take to configure the iSCSI Software Target is to configure Windows Firewall with Advanced Security rules on the computer on which you’ve installed the iSCSI Software Target. You can do this by running the following commands from an elevated command prompt:

netsh advfirewall firewall add rule name="Microsoft iSCSI Software Target Service-
TCP-3260" dir=in action=allow protocol=TCP localport=3260

netsh advfirewall firewall add rule name="Microsoft iSCSI Software Target Service-
TCP-135" dir=in action=allow protocol=TCP localport=135

netsh advfirewall firewall add rule name="Microsoft iSCSI Software Target Service-
UDP-138" dir=in action=allow protocol=UDP localport=138

netsh advfirewall firewall add rule name="Microsoft iSCSI Software Target Service"
dir=in action=allow program="%SystemRoot%\System32\WinTarget.exe" enable=yes

netsh advfirewall firewall add rule name="Microsoft iSCSI Software Target Service Status
Proxy" dir=in action=allow program="%SystemRoot%\System32\WTStatusProxy.exe" enable=yes

After you have configured the computer that will function as the iSCSI target with the appropriate firewall rules, you can configure the iSCSI Software Target application by performing the following steps:

  1. Open the iSCSI Software Target application from the Administrative Tools menu.

  2. Right-click the iSCSI Targets node, shown in Figure 8-1, and choose Create iSCSI Target. Click Next.

    Figure 8-1

    Figure 8-1. iSCSI Target console

  3. On the iSCSI Target Identification page, enter a name for the target.

  4. On the iSCSI Initiators Identifiers page, click Advanced. On the Advanced Identifiers page, click Add.

  5. On the Add/Edit Identifiers page, enter the IP address or fully qualified domain name (FQDN) of the hosts that will be accessing the iSCSI target from the network. The wizard presents a warning when adding multiple initiators. Figure 8-2 shows sql-c.contoso.com and sql-d.contoso.com configured as identifiers. Return to the iSCSI Initiators Identifiers page by clicking OK; click Next and then click Finish.

Figure 8-2

Figure 8-2. Advanced identifiers

To create an iSCSI logical unit number (LUN), you create a virtual hard disk (VHD) and make it available. Remember that you must provide a LUN to store quorum information and a LUN to store database files. To provide a LUN by using the iSCSI Software Target, perform the following steps:

  1. In the iSCSI Software Target console, right-click Device and choose Create Virtual Disk.

  2. On the File page of the Create Virtual Disk Wizard, specify the path to a VHD file that will serve as the storage device for SAN client, for example, d:\SAN\disk-one.vhd.

  3. On the Access page, shown in Figure 8-3, click Add to add the iSCSI targets to allow connection to the virtual disk over the network. Click Next and then click Finish.

Figure 8-3

Figure 8-3. Access iSCSI target

NOTE

iSCSI SOFTWARE TARGET

The iSCSI Software Target is included in Windows Server 2012 and does not require a separate download.

Connecting to the SAN by Using iSCSI Initiator

iSCSI Initiator is a component built into the Windows Server 2008 R2 and Windows 7 operating systems that you can use to connect to an iSCSI LUN by using an iSCSI target. When preparing two servers that will function as cluster nodes in a SQL Server 2012 failover cluster, you can configure each server to connect to the same iSCSI LUN for the purposes of shared storage. To connect to an iSCSI LUN by using an iSCSI initiator, perform the following steps:

  1. Open iSCSI Initiator from the Administrative Tools menu. If prompted to configure the iSCSI service to start automatically, click Yes.

  2. On the Targets tab of the iSCSI Initiator properties, enter the IP address or FQDN of the iSCSI target and click Quick Connect. Verify that the discovered target is correct, as shown in Figure 8-4, and click Done.

    Figure 8-4

    Figure 8-4. Discovered target

  3. On the Volumes And Devices tab, click Auto Configure and then click OK.

  4. Verify that the volumes are available to be brought online and formatted in the Disk Management node of the Server Manager console.

NOTE

MORE INFO BEFORE DEPLOYING A FAILOVER CLUSTER INSTANCE

You can learn more about the steps you must take before deploying a SQL Server failover cluster instance at http://msdn.microsoft.com/en-us/library/ms189910(SQL.110).aspx.

Creating a Windows Server 2008 R2 Failover Cluster

The first step in creating a Windows Server 2008 R2 failover cluster to host a SQL Server 2012 failover cluster is to install the Failover Clustering feature. You can do this through the Server Manager console, as shown in Figure 8-5, or by using the following Windows PowerShell command when the ServerManager module is loaded:

Add-WindowsFeature Failover-Clustering
Figure 8-5

Figure 8-5. Installing the Failover Clustering feature

To configure a failover cluster, perform the following steps:

  1. When you have connected each potential node to the shared storage device and installed the Failover Clustering feature, open the Failover Cluster Manager from the Administrative Tools menu.

  2. In the Failover Cluster Manager console, click Create A Cluster in the Actions menu.

  3. On the Select Servers page, shown in Figure 8-6, enter the names of the nodes that will participate in the cluster.

    Figure 8-6

    Figure 8-6. Selecting cluster nodes

  4. Choose whether to perform validation tests.

    NOTE

    MORE INFO VALIDATION TESTS

    Although validation tests are necessary only if you want Microsoft to support the cluster configuration, you should use the tests to identify any potential deviation from best practice.

  5. On the Access Point For Administering The Cluster page, enter a name and IP address of the cluster, as shown in Figure 8-7.

    Figure 8-7

    Figure 8-7. Cluster administration point

  6. On the Confirmation page, verify the settings and click Next to have the wizard create the cluster.

Quick Check

  • Which editions of SQL Server 2012 support more than two-node failover clusters?

Quick Check Answer

  • Only SQL Server 2012 Enterprise edition supports more than two-node failover clusters.

Installing a SQL Server Failover Cluster

Installing a SQL Server failover cluster involves performing two installation steps from SQL Server Installation Center. You must first run the Advanced Cluster Preparation Wizard. When this first wizard is complete, you must then run the Advanced Cluster Completion Wizard. You might need to restart the host server between running the first and the second wizards.

If you want to support protocol encryption on a failover cluster instance, you must install a certificate that uses the instance name as a fully qualified domain name on each of the nodes that will host the failover cluster instance prior to running the Advanced Cluster Preparation Wizard.

To prepare a SQL Server failover cluster, perform the following steps:

  1. Ensure that the Microsoft .NET Framework 3.5.1 feature is installed.

  2. On the first node in the cluster, run setup.exe from the installation media.

  3. In the Advanced area of SQL Server Installation Center, shown in Figure 8-8, click Advanced Cluster Preparation.

    Figure 8-8

    Figure 8-8. Advanced cluster preparation

  4. On the Product Key page, enter the product key or specify that you use the Evaluation edition. On the License Terms page, select I Accept The License Terms, install any necessary updates, and review the Setup Support Rules warnings.

  5. On the Feature Selection page, choose which SQL Server features you want to install on the failover cluster.

  6. On the Instance Configuration page, choose the properties of the instance.

  7. Review the Disk Space Requirements.

  8. On the Server Configuration page, specify a specially configured domain account to be used for the Service Accounts.

  9. Review the Error Reporting page.

  10. On the Ready To Install page, shown in Figure 8-9, click Install.

    Figure 8-9

    Figure 8-9. Preparing failover cluster installation

  11. When the process completes, you might need to restart the server.

  12. Repeat this process on each node that will participate in the cluster.

Complete the Installation

When you have completed the advanced cluster preparation process on each node that will participate in the failover cluster, return to the cluster node that has ownership of the shared disk and perform the following steps:

  1. On the Advanced page of SQL Server Installation Center, click Advanced Cluster Completion. After the setup support rules have run, click OK and then click Next.

  2. On the Cluster Node Configuration page, shown in Figure 8-10, specify the SQL Server Instance Name and the SQL Server Network Name that will identify the failover cluster on the network. This network name must be different from any preexisting cluster resource name. Click Next.

    Figure 8-10

    Figure 8-10 Cluster node configuration

  3. On the Cluster Resource Group page, enter a new name for a new Cluster Resource Group.

  4. On the Cluster Disk Selection page, shown in Figure 8-11, specify the disk that will be used as the default drive for databases.

    Figure 8-11

    Figure 8-11. Cluster disk selection

  5. On the Cluster Network Configuration page, specify an IP address for the cluster resource.

  6. On the Server Configuration page, verify that the correct collation is set and then click Next.

  7. On the Database Engine Configuration page, specify Authentication Mode and the SQL Server administrator.

  8. On the Ready To Install page, shown in Figure 8-12, verify the configuration settings and then click Install.

Figure 8-12

Figure 8-12. Final cluster configuration

You must run the Advanced Cluster Completion Wizard only once because this configures all the nodes that you prepared by using the Advanced Cluster Preparation Wizard.

NOTE

MORE INFO SQL SERVER FAILOVER CLUSTER INSTALLATION

You can learn more about the specifics of SQL Server failover cluster instance deployment at http://msdn.microsoft.com/en-us/library/hh231721(SQL.110).aspx.

Multi-Subnet Failover Clustering

Multi-subnet failover clustering is a special configuration where each node in the failover cluster is located on a different TCP/IP subnet. A multi-subnet failover cluster does not use shared storage. When configuring a multi-subnet failover cluster, you must use another solution to replicate data between the instances on separate subnets.

NOTE

STRETCH CLUSTERS

Stretch cluster is a term for a geographically dispersed cluster.

Multi-subnet failover clustering is supported only in production environments in SQL Server 2012 Enterprise edition and Windows Server 2008 R2 Enterprise or Datacenter editions. You cannot deploy multi-subnet failover clustering if the host operating system is running Windows Server 2008. When running the Create A New SQL Server Failover Cluster (Setup) Wizard while configuring a multi-subnet failover cluster, you must ensure that the IP address resource dependency is set to OR.

You can deploy stand-alone instances on servers that also host multi-subnet failover cluster instances. One of the challenges of this configuration is ensuring that communication occurs seamlessly and that no conflicts occur between the multi-subnet failover cluster instance and any stand-alone instances installed on the same host. You can minimize the chance of a conflict occurring by configuring stand-alone instances to use non-default fixed ports and leaving the multi-subnet failover cluster instance to use port 1433.

NOTE

MORE INFO MULTI-SUBNET FAILOVER CLUSTERING

You can learn more about multi-subnet failover clustering at http://msdn.microsoft.com/en-us/library/ff878716.aspx.

Performing Manual Failover

You can use the Failover Cluster Manager to perform failover of the cluster resource from one node to another. For example, to perform failover of the SQLCRG resource from SQL-C to SQL-D, perform the following steps:

  1. Open Failover Cluster Manager from the Administrative Tools menu.

  2. Click the SQLCRG node. On the Actions pane, click Move This Service Or Application To Another Node and then click Move To Node SQL-D.

  3. In the Please Confirm Action dialog box, shown in Figure 8-13, click Move SQLCRG To SQL-D.

    Figure 8-13

    Figure 8-13. Moving the cluster resource

  4. Verify that SQLCRG comes online on the other node.

TIP

EXAM TIP

You can move resources from one node to another node by using the Move-ClusterGroup PowerShell cmdlet when the FailoverClusters module is installed. For example, to move the SQLCRG resource from node SQL-C to node SQL-D, issue the following command:

Move-ClusterGroup SQLCRG SQL-D

Troubleshooting Failover Clusters

If failover occurs because the primary node suffers irreparable hardware failure, you should perform the following steps:

  1. Evict the failed node from the failover cluster instance. You can do this from the Failover Cluster Manager by right-clicking the failed node, choosing Move Actions, and then selecting Evict Node.

  2. Verify that the failed node has been successfully evicted from the failover cluster.

  3. Replace the hardware that has failed and then use the Failover Cluster Manager console to add the failed node back to the original cluster.

  4. After the node has been added to the original cluster, run SQL Server setup to readmit the failed node to the failover cluster instance.

NOTE

MORE INFO TROUBLESHOOTING FAILOVER CLUSTERS

You can learn more about troubleshooting failover clusters at http://msdn.microsoft.com/en-us/library/ms189117(SQL.110).aspx.

httpatomoreillycomsourcemspimages1685956.jpg Quorum failure is a more complicated situation that is generally caused by persistent communications failure or by the problematic configuration of cluster nodes. Quorum failure must be resolved manually by performing the following steps:

  1. Start the Windows Server Failover Cluster by using forced quorum on a single node. You do this by choosing Force Cluster Start from the Actions pane of the Failover Cluster Manager.

  2. Start the Windows Server Failover Cluster service on additional nodes that can communicate with the node you started by using forced quorum.

  3. Configure a new quorum mode and node vote configuration that reflect the realities of the quorum topology. For example, if two nodes are frequently unavailable due to persistent communication failure, reconfigure the quorum mode and vote assignments to remediate this problem.

NOTE

MORE INFO RESOLVING QUORUM FAILURE

You can learn more about resolving quorum failure at http://msdn.microsoft.com/en-us/library/hh270277.aspx.

Practice Building a SQL Server 2012 Failover Cluster

Practice Building a SQL Server 2012 Failover Cluster

In this practice, you create a Windows Server failover cluster and then deploy a SQL Server failover cluster instance. After you have deployed the failover cluster instance, you perform failover.

EXERCISE 1 Configure iSCSI Volumes for Failover Clustering

In this exercise, you configure iSCSI volumes so they can be used as shared storage in a Windows Server failover cluster. You also configure firewall rules and a service account. To complete this exercise, perform the following steps:

  1. Log on to server DC with the Kim_Akers user account.

  2. Ensure that the SQL-C and SQL-D computer accounts are included in the SQL Server organizational unit (OU).

  3. Download and install the iSCSI Software Target.

  4. Create a folder named C:\SAN.

  5. On DC, configure inbound rules for TCP ports 135 and 3260 and for the inbound rule for the following executables:

    • %systemroot%\System32\WinTarget.exe

    • %systemroot%\System32\WTStatusProxy.exe

  6. On DC, create an iSCSI target named DC-TARGET and configure it to be accessible to sql-c.contoso.com and sql-d.contoso.com.

  7. Create a virtual disk named c:\SAN\disk-one.vhd. Set the size to 2 GB. Allow access to the DC-TARGET iSCSI target.

  8. Create a virtual disk named c:\SAN\disk-two.vhd. Set the size to 10 GB. Allow access to the DC-TARGET iSCSI target.

  9. Use the iSCSI initiator to connect to the domain controller as a target on both SQL-C and SQL-D.

  10. Using the Disk Management node of the Server Manager console on SQL-C to bring each of the two volumes online, initialize them and create new simple volumes formatted with the NTFS file system.

  11. Use the DNS console to create a DNS A record for the address sql-cluster.contoso.com mapped to IP address 10.10.10.111.

  12. Use Active Directory Users And Computers to create a user account named SQL-Cluster with the password Pa$$w0rd.

  13. Edit the Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment\Log On As A Service policy in the SQL-POLICY Group Policy Object (GPO) and grant the SQL-Cluster user account the Log On As A Service right.

EXERCISE 2 Configure a Windows Server 2008 R2 Failover Cluster

In this exercise, you configure a Windows Server 2008 R2 failover cluster by using the shared storage device configured in the previous exercise. To complete this exercise, perform the following steps:

  1. When logged on with the Kim_Akers user account, install the Failover Clustering and .NET Framework 3.5.1 features on SQL-C and SQL-D.

  2. Run the Create Cluster Wizard from the Failover Cluster Manager console. Configure the failover cluster with the following properties:

    • Cluster Servers: SQL-C and SQL-D

    • Cluster Name: SQL-Cluster

    • Cluster IP Address: 10.10.10.111

EXERCISE 3 SQL Server Failover Cluster Advanced Cluster Preparation

In this exercise, you run the advanced cluster preparation process on the nodes that will participate in the failover cluster instance. To complete this exercise, perform the following steps:

  1. Log on to servers SQL-C and SQL-D with the Kim_Akers user account.

  2. From SQL Server Installation Center, run Advanced Cluster Preparation on SQL-C and SQL-D with the following options:

    • Install the Database Engine Services, SQL Server Replication, Management Tools - Basic, and Management Tools - Complete features.

    • Use the Default instance with the default settings.

    • Use the CONTOSO\SQL-Cluster account for the SQL Agent and Database Engine service accounts.

  3. You might need to restart SQL-C and SQL-D to complete the advanced cluster preparation.

EXERCISE 4 SQL Server Failover Cluster Advanced Cluster Completion

In this exercise, you complete the failover cluster instance installation process. To complete this exercise, perform the following steps:

  1. Log on to server SQL-C with the Kim_Akers user account.

  2. Verify that SQL-C has control of the two SAN disks that will be used with the cluster.

  3. On the Advanced page of SQL Server Installation Center, run Advanced Cluster Completion and provide the following settings:

    • SQL Server network name: SQL2012Cluster

    • Cluster resource group name: SQLCRG

    • Cluster Network Configuration IP address: 10.0.0.120 with subnet mask 255.255.255.0

    • Use Windows Authentication mode; set CONTOSO\Kim_Akers as SQL Server administrator

  4. After installation is complete, open the Failover Cluster Manager and verify that the SQLCRG service is online, as shown in Figure 8-14.

Figure 8-14

Figure 8-14. Verifying cluster configuration

EXERCISE 5 Perform Cluster Failover

In this exercise, you perform failover of the failover cluster instance. To complete this exercise, perform the following steps:

  1. Log on to server SQL-C with the Kim_Akers user account.

  2. Use Failover Cluster Manager to move the SQLCRG resource from SQL-C to SQL-D.

  3. Verify that SQLCRG comes online on the other node and that the current owner is set to SQL-D.

  4. Use the appropriate PowerShell cmdlet to move the SQLCRG resource back to SQL-C from SQL-D.

Lesson Summary

  • A Windows Server Failover Cluster must be created prior to installing a failover cluster instance.

  • Windows Server 2008 Enterprise and Datacenter editions and Windows Server 2008 R2 Enterprise and Datacenter editions can function as host operating systems for failover cluster instances.

  • To install a failover cluster instance, first run advanced cluster preparation on all nodes and then run advanced cluster completion on the node that has control of the shared storage device.

  • Multi-subnet failover clusters have nodes on separate TCP/IP subnets.

  • Use the Failover Cluster Manager console or the Move-ClusterGroup PowerShell cmdlet to perform manual failover.

  • In the event of hardware failure, evict the failed node from the cluster and then join it after it is repaired before reinstalling SQL Server.

Lesson Review

Answer the following questions to test your knowledge of the information in this lesson. You can find the answers to these questions and explanations of why each answer choice is correct or incorrect in the Answers section at the end of this chapter.

  1. Which of the following operating systems can you use as the host operating system for a SQL Server 2012 multi-subnet failover cluster instance?

    1. Windows 7 Enterprise edition

    2. Windows Server 2008 Enterprise edition

    3. Windows Server 2008 R2 Enterprise edition

    4. Windows Vista Ultimate edition

  2. Which of the following Windows PowerShell commands can you use to perform failover of a SQL Server failover cluster instance from one node to another?

    1. Move-ClusterGroup

    2. Move-ClusterResource

    3. Move-ClusteredSharedVolume

    4. Move-ClusterVirtualMachineRole

  3. The primary node of a four-node SQL Server failover cluster instance fails due to a hardware failure. Replacement hardware will not arrive for 48 hours. Which of the following steps should you take first to remedy this situation?

    1. Evict the failed node.

    2. Evict the new primary node.

    3. Reinstall SQL Server on the failed node.

    4. Join the failed node to the cluster.

  4. You have configured servers SYD-A and SYD-B to be members of a Windows Server failover cluster. Server SYD-B has control of the shared disk resources. You will deploy SQL Server 2012 as a failover cluster instance on these servers. Which of the following steps must you take to accomplish this goal? (Each correct answer forms part of a complete solution. Choose all that apply.)

    1. Run Advanced Cluster Preparation on SYD-A.

    2. Run Advanced Cluster Preparation on SYD-B.

    3. Run Advanced Cluster Completion on SYD-A.

    4. Run Advanced Cluster Completion on SYD-B.

Save to your account

This chapter is from the book