InfoScale™ 9.0 Storage Foundation and High Availability Solutions HA and DR Solutions Guide for Microsoft SQL Server - Windows

To create a SQL Server service group on the cluster

1. Ensure that you run the wizard from the first cluster node where you installed SQL Server.
2. Ensure that you have stopped the SQL Server service for the instances that you wish to configure.

   Note:

   If the SQL Server service is running when you launch the wizard in the create mode, the wizard fails to reconfigure the service to start under Lanman context.

3. Start the SQL Server Configuration Wizard from the Apps menu on the Start screen.
4. Review the prerequisites on the Welcome panel and then click Next.
5. On the Wizard Options panel, click Create service group and then click Next.
6. On the Service Group Configuration panel, specify the service group name and system list, as follows:

   • In the Service Group Name field, specify a name for the SQL Server service group, for example, INST1_SG. If there are multiple instances, ensure that the name is unique within the cluster.

   • In the Available Cluster Systems box, select the systems on which to configure the service group and click the right-arrow to move the systems to the Systems in Priority Order list.

   • To change the priority of a system in the Systems in Priority Order list, select the system and click the up and down arrow icons. Arrange the systems in priority order as failover targets for the group. The server that needs to come online first must be at the top of the list.

     For an active/active configuration, ensure that the active and failover systems are set differently for each instance. For example, if the system priority for the first instance is SYSTEM1, then SYSTEM2, the system priority for the second instance should be SYSTEM2, then SYSTEM1.

   • To enable the service group to automatically come online on one of the systems, select the Include selected systems in the service group's AutoStartList attribute checkbox.

     For information about the AutoStartList attribute, see the Cluster Server Administrator's Guide.

   • Click Next.

7. On the SQL Server Instance Selection panel, complete the following steps and then click Next.

   • From the SQL Server version drop-down list, select the SQL Server version for which you wish to configure the service group.

     You can configure a service group for only one SQL Server version in a single wizard workflow. To configure another SQL Server version, you must run the wizard again.

     The wizard displays instances of the selected SQL Server version that satisfy the following criteria:

     • Instances installed identically on all the systems

     • Instances not configured in other SQL Server service groups

   • Select the SQL Server instance(s) that you wish to configure in the service group.

   • If required, select the other services that you wish to make highly available. These options are available for selection only if the corresponding services are installed.

     Note that you can choose only one instance of the Analysis service per service group. If you have selected an instance of Analysis service, you must uncheck it before you can select another instance of the Analysis service.

     Note that services that are already configured and online in the cluster appear in bold and are not available for selection. You have to offline the service group and run the wizard in the modify mode to edit the service resources.

   • Select SQLFILESTREAM if you wish to configure high availability for FILESTREAM enabled database objects. The wizard configures a resource only if FILESTREAM is enabled for the instance on the current node.

     Note that FILESTREAM option will not appear for selection if it is not enabled on the node.

8. Click Yes on the dialog box that prompts you whether you wish to allow the wizard to reconfigure the database paths for the selected instances using the current cluster node as a reference.
9. On the User Databases List panel, view the summary of the databases for the selected instance and then click Next.

   In case of multiple instances, select the required instance from the SQL Server instance dropdown list. The panel displays the databases and the respective files for which the wizard configures resources. Click a database name to view its database files.

   Databases that appear with a red cross indicate that the wizard does not configure the storage agent resources for those items. These databases either do not reside on shared storage or the wizard is unable to locate them. If you wish to configure resources for these databases, ensure that the database are located on shared storage and then run the wizard again.

10. On the SQL Server Cluster Account Configuration panel, specify the SQL Server cluster account details and then click Next.

    The SQL Server cluster account must be configured if the SQL Server service and the SQL Server Agent service accounts do not have local administrator privileges on all the SQL Server nodes in the service group.

    Complete the following steps for each SQL Server instance that you wish to configure in the service group:

    • Select a SQL Server instance in the Instance Name box.

    • Check the Configure SQL Server Cluster Account check box.

    • Click Use service SIDs to set the SQL Server service name as the SQL Server cluster account.

    • Click Use Domain Group Account and then click the adjacent ellipsis button to launch the Windows Select Users, Computers, or Groups dialog box. Then specify a domain group and click OK to set the domain group as the SQL Server cluster account.

      If you specify a domain group as the SQL Server cluster account, ensure that the SQL Server service and SQL Server Agent service accounts are part of the specified domain group.

    The SQL Server agent assigns the specified account with Full Control privileges to the SQL Server databases and log files. This ensures that they are accessible upon failover.

11. On the Detail Monitoring Configuration panel, configure detail monitoring for the SQL Server instances. This step is optional. If you do not want to configure detail monitoring, click Next and proceed to the next step.
    

    Perform the following steps only if you wish to configure detail monitoring for an instance:

    • Check the check box for a SQL Server instance, and then click the button from the Detail Monitoring Properties column to specify the detail monitoring settings.

      Clear the check box to disable detail monitoring for the instance.

    • On the Detail Monitor configuration dialog box, specify the monitoring interval in the Detail monitoring interval field.

      This sets the value for the DetailMonitoringInterval attribute of the SQL Server agent. It indicates the number of online monitor cycles that the agent must wait before performing detail monitoring. The default value is 5. Arctera recommends that you set the monitoring interval between 1 and 12.

    • Select DBList Detail Monitoring and then choose the databases from the list of databases available for the instance. The selected databases populate the DBList attribute of the SQL Server agent. In this mode of detail monitoring the agent monitors the health of the databases by connecting to those databases. The agent monitors only the databases specified in the DBList attribute.

    • Select SQLFile Detail Monitoring if you wish to use a script to monitor SQL Server databases. In this mode of detail monitoring, the agent executes the script that you specify for detail monitoring.

    • Specify the fully qualified user name and the password for connecting to the SQL Server database. Make sure that the user has SQL Server logon permissions.

    • Select Global or Per System depending on whether the monitoring script location is the same for all the nodes or is unique for each cluster node, and then specify the path of the script appropriately.

    • Check Fail over service group if detail monitoring fails check box, if not already checked. This allows the SQL Server agent to fail over the service group to another node if the detail monitoring fails.

    • On the Detail Monitor configuration dialog box, specify the monitoring frequency in the Detail monitoring frequency field.

      This sets the value for the LevelTwoMonitorFreq attribute of the SQL Server agent. It indicates the number of online monitor cycles that the agent must wait before performing detail monitoring. The default value is 5. Arctera recommends that you set the monitoring interval between 1 and 12.

    • Select DBList Detail Monitoring and then choose the databases from the list of databases available for the instance. The selected databases populate the DBList attribute of the SQL Server agent. In this mode of detail monitoring the agent monitors the health of the databases by connecting to those databases. The agent monitors only the databases specified in the DBList attribute.

    • Select SQL-Script based detail monitoring if you wish to use a script to monitor SQL Server databases. In this mode of detail monitoring, the agent executes the script that you specify for detail monitoring.

    • Specify the fully qualified user name and the password for connecting to the SQL Server database. Make sure that the user has SQL Server logon permissions.

      Note:

      These credentials are required for both, DBList as well as SQL script-based detail monitoring.

    • Select Global or Per System depending on whether the monitoring script location is the same for all the nodes or is unique for each cluster node, and then specify the path of the script appropriately.

    • Check Fail over service group if detail monitoring fails check box, if not already checked. This allows the VCS agent for SQL Server to fail over the service group to another node if the detail monitoring fails.

    • Click Apply.

    • Repeat these steps for each SQL Server instance that you wish to configure detail monitoring for, and then click Next.

12. On the Registry Replication Path panel, specify the mount path to the registry replication volume (INST1_REGREP_VOL) and click Next.

    Arctera recommends that RegRep resources and SQL Server data be in separate volumes.

13. On the Virtual Server Configuration panel, specify the virtual server and network information and then click Next.
    

    Complete the following steps:

    • Select IPv4 to configure an IPv4 address for the virtual server.

      • In the Virtual IP Address field, type a unique virtual IPv4 address for the virtual server.

      • In the Subnet Mask field, type the subnet to which the virtual IPv4 address belongs.

    • Select IPv6 to configure an IPv6 address for the virtual server. The IPv6 option is disabled if the network does not support IPv6.

      Select the network from the drop-down list. The wizard uses the network prefix and automatically generates an IPv6 address that is valid and unique on the network.

    • In the Virtual Server Name field enter the virtual name for the server, for example INST1-VS. Ensure that the virtual server name you enter is unique in the cluster.

    • For each system in the cluster, select the public network adapter name. Select the Adapter Display Name field to view the adapters associated with a system.

      The wizard displays all TCP/IP enabled adapters on a system, including the private network adapters, if they are TCP/IP enabled. Make sure that you select the adapters to be assigned to the public network, and not those assigned to the private network.

    • If you require a computer object to be created in the Active Directory (AD), click Advanced Settings, check the Active Directory Update Required check box, specify the desired Organizational Unit in the domain and then click OK. The user account configured for VCS Helper service must have adequate privileges on the specified container to create and update computer accounts.

      This sets the Lanman resource attributes ADUpdateRequired and ADCriticalForOnline to true. This allows the Lanman agent to update Active Directory with the virtual SQL Server name.

      You can type the OU details in the format:

      CN=Computers,DC=domainname,DC=com.

      To search for the OU, click the ellipsis button and specify the search criteria in the Windows Find Organizational Units dialog box.

      By default, the Lanman resource adds the virtual server name to the default container "Computers."

      Note:

      If you have a tagged VLAN network configuration having multiple logical network interfaces or a teamed network interface that have the same MAC address, then you must edit the "MACAddress" attribute of the NIC agent and the IP agent, after you configure the application service group.

      See About the modifications required for tagged VLAN or teamed network.

    • In the Specify ManagedIdentityClientID, enter the client ID of the user-assigned managed identity that you want to use for authenticating the Azure subscription.

      You can get the managed identity details from the Azure portal.

      Note:

      This is applicable only if you are configuring the service group in an Azure cloud environment.

14. In the Service Group Summary panel, review the service group configuration.

    • The Resources box lists the configured resources. The wizard assigns unique names to resources based on their respective name rules. Click a resource to view its attributes and their configured values in the Attributes box.

      Optionally, if desired, change the names of the resources as follows:

      • To edit a resource name, click the resource name or press the F2 key. Press the Enter key after editing each resource name.

      • To cancel editing a resource name, press the Esc key.

      • To enable all the VMDg resources in the service group for fast failover, select the Enable FastFailOver attribute for all the VMDg resources in the service group checkbox.

        For information about the FastFailOver attribute, see the Cluster Server Administrator's Guide.

      • Click Next.

15. Click Yes when prompted that the wizard will modify the configuration. The wizard begins to create the service group. Various messages indicate the status of the commands.
16. Check the Bring the service group online check box and then click Finish. This brings the service group online on the current node.

    You must bring the SQL Server service group online on the node from where you ran the configuration wizard. This is the first cluster node where you installed SQL Server. This allows the wizard to configure the resources required for SQL Server services.