Cluster Server 8.0 Implementation Guide for Microsoft SQL Server - Windows

Last Published:
Product(s): InfoScale & Storage Foundation (8.0)
Platform: Windows
  1. Section I. Introducing Veritas InfoScale solutions for application high availability
    1. Understanding the InfoScale solutions for application high availability
      1.  
        About the Veritas InfoScale solutions for monitoring SQL Server
      2. About the VCS agents for SQL Server
        1.  
          About the VCS agent for SQL Server Database Engine
        2.  
          About the VCS agent for SQL Server FILESTREAM
        3.  
          About the VCS GenericService agent for SQL Server Agent service and Analysis service
        4.  
          About the agent for MSDTC service
        5.  
          About the monitoring options
      3. How VCS monitors storage components
        1.  
          Shared storage - if you use NetApp filers
        2.  
          Shared storage - if you use SFW to manage cluster dynamic disk groups
        3.  
          Shared storage - if you use Windows LDM to manage shared disks
        4.  
          Non-shared storage - if you use SFW to manage dynamic disk groups
        5.  
          Non-shared storage - if you use Windows LDM to manage local disks
        6.  
          Non-shared storage - if you use VMware storage
      4. How application availability is achieved in a physical environment
        1.  
          Typical SQL Server cluster configuration using shared storage
        2.  
          Typical SQL Server disaster recovery cluster configuration
        3.  
          SQL Server sample dependency graph
        4.  
          MSDTC sample dependency graph
      5. How is application availability achieved in a VMware virtual environment
        1.  
          How the VMwareDisks agent communicates with the vCenter Server instead of the ESX/ESXi host
        2.  
          Typical VCS cluster configuration in a virtual environment
    2. Managing storage and installing the VCS agents
      1. Managing storage using NetApp filer
        1.  
          Connecting virtual disks to the cluster node
        2.  
          Disconnecting virtual disks from the cluster nodes
      2. Managing storage using Windows Logical Disk Manager
        1.  
          Reserving disks (if you use Windows LDM)
        2.  
          Creating volumes (if you use Windows LDM)
        3.  
          Mounting volumes (if you use Windows LDM)
        4.  
          Unassigning a drive letter
        5.  
          Releasing disks (if you use Windows LDM)
      3.  
        Managing storage using VMware virtual disks
      4.  
        About installing the VCS agents
    3. Installing SQL Server
      1.  
        About installing SQL Server for a high availability (HA) configuration
      2.  
        Configuring Microsoft iSCSI initiator
      3.  
        About installing SQL Server on the first system
      4.  
        About installing SQL Server on additional systems
      5.  
        Assigning ports for multiple SQL Server instances
      6.  
        Enabling IPv6 support for the SQL Server Analysis Service
  2. Section II. Configuring SQL Server in a physical environment
    1. Overview
      1.  
        About configuring SQL Server in physical environment
    2. Configuring the VCS cluster
      1.  
        Configuring the cluster using the Cluster Configuration Wizard
      2.  
        Configuring notification
      3.  
        Configuring Wide-Area Connector process for global clusters
    3. Configuring the SQL Server service group
      1.  
        About configuring the SQL Server service group
      2.  
        Before configuring the SQL Server service group
      3. Configuring a SQL Server service group using the wizard
        1.  
          Configuring detail monitoring for a SQL Server instance
        2.  
          Assigning privileges to the existing SQL Server databases and logs
      4.  
        Configuring the service group in a non-shared storage environment
      5.  
        Running SnapManager for SQL Server
      6.  
        About the modifications required for tagged VLAN or teamed network
      7. Making SQL Server user-defined databases highly available
        1.  
          Create volumes or LUNs for SQL Server user-defined databases
        2.  
          Creating SQL Server databases
        3.  
          Adding storage agent resources to the SQL service group
      8. Verifying the service group configuration
        1.  
          Bringing the service group online
        2.  
          Taking the service group offline
        3.  
          Switching the service group
      9. Administering a SQL Server service group
        1.  
          Modifying a SQL service group configuration
        2.  
          Deleting a SQL service group
    4. Configuring an MSDTC service group
      1.  
        About configuring the MSDTC service group
      2.  
        Typical MSDTC service group configuration using shared storage
      3.  
        Before configuring the MSDTC service group
      4.  
        Creating an MSDTC service group
      5.  
        About configuring an MSDTC client
      6.  
        Configuring an MSDTC client
      7.  
        Verifying the installation
    5. Configuring the standalone SQL Server
      1. Typical high availability configuration for a standalone SQL Server setup
        1.  
          Sample configuration
      2. Configuring a standalone SQL Server for high availablility
        1.  
          Moving the existing SQL Server data files and user databases
    6. Configuring an Active/Active cluster
      1. About running SQL Server in an active-active clustered environment
        1.  
          Sample configuration
      2.  
        Setting up the Active/Active cluster
    7. Configuring a disaster recovery setup
      1. Setting up the disaster recovery cluster
        1.  
          Why implement a disaster recovery solution
        2.  
          Understanding replication
        3.  
          What needs to be protected in a SQL Server environment
      2. Configuring a disaster recovery set up for SQL Server
        1.  
          Configuring replication using NetApp SnapMirror
        2.  
          Configuring SnapMirror resources at the primary site
      3. Configuring the Global Cluster Option for wide-area failover
        1.  
          Prerequisites
        2.  
          Linking clusters: Adding a remote cluster to a local cluster
        3.  
          Converting a local service group to a global service group
        4.  
          Bringing a global service group online
      4. Administering global service groups
        1.  
          Taking a remote global service group offline
        2.  
          Switching a remote service group
        3.  
          Deleting a remote cluster
  3. Section III. Configuring SQL Server in a VMware environment
    1. Configuring application monitoring using the Veritas High Availability solution
      1.  
        Deploying the Veritas High Availability solution for configuring application monitoring
      2. Notes and recommendations
        1. Assigning privileges for non-administrator ESX/ESXi user account
          1.  
            Creating a role
          2.  
            Integrating with Active Directory or local authentication
          3.  
            Creating a new user
          4.  
            Adding a user to the role
      3. Configuring application monitoring
        1.  
          Configuring the VCS cluster
        2.  
          Configuring the application
      4.  
        Modifying the ESXDetails attribute
    2. Administering application monitoring
      1.  
        About the various interfaces available for performing application monitoring tasks
      2. Administering application monitoring using the Veritas High Availability tab
        1.  
          Understanding the Veritas High Availability tab work area
        2.  
          To view the status of configured applications
        3.  
          To configure or unconfigure application monitoring
        4.  
          To start or stop applications
        5.  
          To suspend or resume application monitoring
        6.  
          To switch an application to another system
        7.  
          To add or remove a failover system
        8.  
          To clear Fault state
        9.  
          To resolve a held-up operation
        10.  
          To determine application state
        11.  
          To remove all monitoring configurations
        12.  
          To remove VCS cluster configurations
      3.  
        Administering application monitoring settings
      4. Administering application availability using Veritas High Availability dashboard
        1. Understanding the dashboard work area
          1.  
            Aggregate status bar
          2.  
            ESX cluster/host table
          3.  
            Taskbar
          4.  
            Filters menu
          5.  
            Application table
        2.  
          Monitoring applications across a data center
        3.  
          Monitoring applications across an ESX cluster
        4.  
          Searching for application instances by using filters
        5.  
          Selecting multiple applications for batch operations
        6.  
          Starting an application using the dashboard
        7.  
          Stopping an application by using the dashboard
        8.  
          Entering an application into maintenance mode
        9.  
          Bringing an application out of maintenance mode
        10.  
          Switching an application
  4. Section IV. Appendixes
    1. Appendix A. Troubleshooting
      1.  
        VCS logging
      2.  
        VCS Cluster Configuration Wizard (VCW) logs
      3.  
        VCWsilent logs
      4.  
        NetApp agents error messages
      5. Error and warning messages from VCS agent for SQL Server
        1.  
          Messages from the VCS agent for SQL Server Database Engine
        2.  
          Messages from the VCS agent for SQL Server FILESTREAM
        3.  
          Messages from the VCS agent for SQL Server Agent service and Analysis service
        4.  
          SQL Server Analysis service (MSOLAP) service fails to come online with "invalid context of address" error
        5.  
          Messages from the VCS agent for MSDTC
      6. Troubleshooting application monitoring configuration issues
        1.  
          Running the 'hastop - all' command detaches virtual disks
        2.  
          Validation may fail when you add a failover system
        3.  
          Adding a failover system may fail if you configure a cluster with communication links over UDP
      7. Troubleshooting Veritas High Availability view issues
        1.  
          Veritas High Availability tab not visible from a cluster node
        2.  
          Veritas High Availability tab does not display the application monitoring status
        3.  
          Veritas High Availabilitytab may freeze due to special characters in application display name
        4.  
          Veritas High Availability view may fail to load or refresh
        5.  
          Operating system commands to unmount resource may fail
    2. Appendix B. Using the virtual MMC viewer
      1.  
        About using the virtual MMC viewer
      2.  
        Viewing DTC transaction information

Configuring the application

The Veritas High Availability Wizard fails to configure a service group due to changes related to encryption using the standard AES-256 algorithm. To avoid this issue, run the following commands on any node in the cluster before you run the Veritas High Availability Wizard.

  1. haconf -makerw

  2. vcsencrypt -gensecinfo

  3. haconf -dump -makero

Perform the following steps to configure monitoring for SQL Server using the Veritas High Availability Configuration Wizard.

Note:

Veritas High Availability does not support application monitoring for two different versions of SQL Server on the same system simultaneously.

To configure the application

  1. Launch the vSphere Client and connect to the vCenter Server that manages the virtual machine. On the Inventory view of the vCenter Server, in the left pane, select the virtual machine where you want to configure application monitoring. Then, in the right pane, select the Veritas High Availability tab.
  2. On the Veritas High Availability tab, click Configure Application for High Availability. This option is available only after a cluster has been configured. Unless you configure a cluster, you cannot configure an application for monitoring.
  3. On the Welcome panel of the Veritas High Availability Configuration wizard, review the pre-requisites and then click Next.
  4. On the Application Selection panel, select the application from the Supported Applications list and then click Next.

    Alternatively, you can use the Search box to find the application and then click Next.

    If you want to download any of the High Availability Agents, click the Download Application Agents (SORT) link to download the agents from the Veritas Operations Readiness Tools (SORT) site.

    https://sort.veritas.com/agents

  5. On the Application Inputs panel, 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.
  6. On the SQL Instance Selection panel, choose the SQL Server instances and any of the following associated components that you want to monitor, and then click Next.

    SQL Agent Service

    Select this option to configure monitoring for SQL Server Agent service for the selected instance.

    You must select this for each selected SQL Server instance separately.

    Analysis Service

    Select this option to configure monitoring for SQL Server Analysis service for the selected instance.

    You must select this for each selected SQL Server instance separately.

    FILESTREAM

    Select this option to configure monitoring for FILESTREAM.

    You can select this option, if FILESTREAM is enabled on the selected instance.

  7. To enable detail monitoring for the selected instances and associated services, select Configure detail monitoring and provide the following required details:
    • Enter a non-zero value in the Monitor after every ... cycles box. This value indicates the number of online monitor cycles that the agent must wait before performing detail monitoring.

      The numeric value specifies how often the monitoring check must run. 1 means, run a detail check every single monitor interval, 2 means run the detail check every second monitor interval. This interpretation may be extended to other values.

      Veritas recommends that you set this value between 1 and 12. The default value is 5.

    • Select one of the following modes for detail monitoring:

      • Database monitoring

        Select this mode to enable detail monitoring by connecting to the database and choosing the desired databases from the Databases list.

      • Script-based monitoring

        Select this mode to enable detail monitoring by using a user-defined SQL script.

        If you choose script-based monitoring, you must enter the following details:

        Script Path

        Enter the script location on the virtual machine

        User Name

        Enter the valid user name

        Note:

        The user account must have rights to run the script and execute the SQL commands specified in the script.

        Password

        Enter the password for the user account specified in the User Name text box

    • If the detail monitoring fails, the SQL Server agent can fail over the service group to another node. If you want the agent to initiate the failover in such a scenario, make sure that the Restart the SQL instance if detail monitoring fails check box is checked.

  8. On the Registry Replication Details panel, select a location from the Registry replication directory drop-down list to save the registry replication data.

    Veritas recommends that you store the registry replication data and the SQL Server application data at different locations.

  9. On the Configuration Inputs panel, select the VCS cluster systems on which you want to configure the application for high availability and move them to the Application failover targets list. The local system is selected by default.

    Using the up-down arrow keys, you can define the priority order for the failover systems.

    For each system that you assign as a failover target, you must specify the domain user account details in the appropriate fields on the Edit System dialog box. The VCS agents use these details to perform domain operations (such as Active Directory updates).

  10. On the Virtual Network Details panel, specify the virtual IP and the network details for the application to be configured and then click Next.

    To specify the virtual IP and network details, select the IP version and then specify the following details for each failover system:

    Note:

    You must select the same IP version as the one that was selected during the VCS cluster configuration.

    Virtual IP address

    Specify a unique virtual IP address.

    Subnet mask

    Specify the subnet mask to which the IP address belongs.

    Virtual name

    Specify a virtual name.

    Network Adapter column

    Select the network adapter that will host the virtual IP.

  11. On the Storage HA Inputs panel, specify the ESX/ESXi hosts and the administrative user account details for each host, and then click Next.

    Note:

    This panel appears only if you have specified a registry replication directory, or multiple application failover targets, or both for the selected SQL Server application.

    To specify the ESX/ESXi hosts, click Add ESX/ESXi Host and on the Add ESX/ESXi Host dialogue box, specify the following details:

    ESX/ESXi hostname or IP address

    Specify the target ESX hostname or IP address.

    The virtual machines will fail over on this ESX host during vMotion.

    The mount points configured on the ESX host where the application is currently running must be available on the target ESX host.

    User name

    Specify a user account for the ESX host.

    The user account must have administrator privileges on the specified ESX host.

    Password

    Specify the password for the user account provided in the User name text box.

    Note:

    By default, the wizard sets up a communication link with the ESX/ESXi server. You can modify the configuration to set up the communication link with vCenter Server instead. To set up a link with a vCenter Server, you must modify the ESXDetails attribute after this application monitoring configuration workflow is complete.

    See Modifying the ESXDetails attribute.

  12. On the Configuration Summary panel, review the application configuration details.

    The wizard assigns a unique name to the application service group. Click Rename to rename the service group.

    Click Next to initiate the application monitoring configuration.

  13. On the Implementation panel, the wizard performs the application configuration tasks.

    The wizard displays the status of each task. After all the tasks are complete, click Next.

    If the configuration tasks fail, click Diagnostic information to check the details of the failure. Rectify the cause of the failure, and run the wizard again to configure application monitoring.

  14. On the Finish panel, click Finish to complete the wizard workflow. This configures the application for high availability on the selected VCS cluster systems.

    The Veritas High Availability view now displays the application status and its component dependency.

    If the application status shows as not running, click Start to start the configured components on the system.

    Note:

    Ensure that you start the application (bring the service group online) on the first system at least once. This is required to store the database related information and the SQL Server instances registry setting details in the VCS cluster configuration. When the application fails over to any other VCS cluster system, this information is applied to that system and the application is brought online on that system.