MS SQL Cluster

Application Version and Upgrade Details

Introduction

Multi-node failover cluster Instance

SQL Server clustering describes a collection of two or more physical servers (nodes), connected via LAN, each of which host a SQL server instance and have the same access to shared storage. Clustering SQL servers provides high availability and protection from disasters when a server hosting the SQL Server instance fails.

Always on failover cluster instances

SQL Server Always On Failover Cluster Instances (FCIs) use Windows Server Failover Clustering (WSFC) to provide high availability at the server instance level. An FCI is a single instance of SQL Server that is installed across WSFC nodes to provide high availability for the entire installation of SQL Server.

Always On availability group

Always On availability groups feature a high availability and disaster recovery solution that provides an alternative to database mirroring, and maximizes the availability of a set of user databases for an enterprise. This supports a failover environment for a discrete set of user databases, known as availability databases.

The availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations. Availability group fails over at the level of an availability replica. Failovers are not caused by database issues such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log.

Prerequisites

• OpsRamp Classic Gateway 14.0.0 and above.
• OpsRamp NextGen Gateway 14.0.0 and above.
  Note: OpsRamp recommends using the latest Gateway version for full coverage of recent bug fixes, enhancements, etc.

• For powershell cmdlets the following are prerequisites:

  • Windows domain User should be able to do powershell remoting
    Enable-PSRemoting -Force
  • Windows domain user should be added to “Remote Management users” group net localgroup “Remote Management Users” /add
  • Windows domain user should be added to “Performance monitor users” group net localgroup “Performance monitor users” /add
  • Add OpsrampGatewayIp to the TrustedHosts list on the target machine to allow the powershell connection from gateway to the target machine.
    • To add TrustedHosts use the following command:
      • To allow any host:
        Set-Item WSMan:\localhost\Client\TrustedHosts -Force -Value *
      • To allow a specific host:
        Set-Item WSMan:\localhost\Client\TrustedHosts -Force -Concatenate -Value
  • Setup and restart the WinRM service for the changes to reflect
    • To set up: Set-Service WinRM -StartMode Automatic
    • Restart using: Restart-Service -Force WinRM

• Granting Remote DCOM Rights

  • To grant the user DCOM rights, log on to each system to be monitored and complete the following procedure:
    • Go to command prompt and enter dcomcnfg
    • Navigate to component services > computers > My computer and then right click and select Properties. Then go to the COM Security tab.
    • Under Access Permissions, go to edit limits and add the domain non-admin user and enable both local and remote access then click OK.
    • In Launch and Activation permissions, go to edit limits, add the domain non-admin user and check all boxes , and click OK.

• Granting Remote WMI Rights

  • To give the user remote WMI rights, log on to each system to be monitored and complete the following procedure:
    • Go to “computer management” and select “WMI Control” under ‘Services and Applications’.
    • Right-click WMI control and go to properties. Then select the Security tab.
    • Select root and click Security.
    • Add the domain non-admin user, and check the boxes for execute methods, enable account, remote enable, and reas security.
    • Click Advanced, select added non-admin domain user and click edit. Then for “Applies” to select “namespace and sub namespaces” in the dropdown.
    • Click OK 3 times .

• For monitoring services like windows cluster service and other mssql services (sql server, sql agent , sql browser etc.), windows domain users have to be a part of “Local Administrators group” as we are using win32_Service class for fetching the details.

  • refer to below link:
    https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.management/get-wmiobject?view=powershell-5.1
  • If you do not want to add the user to local administrator group, you can use “Security descriptors” for monitoring the services.
  • For that we have to do the configuration as below:
    Refer to Windows failover cluster monitoring section of the doc.

• Windows domain user should be granted read-only access to cluster
  Grant-ClusterAccess -User <domain\user> -Readonly

SQL authorization permissions:

• For monitoring some metrics, we are using JDBC. For JDBC connections we are supporting mixed authentication (both NLTM and SQL server authentication)
• The minimum privileges for SQL auth are:
  • server role: public - This server role is available by default
  • securables: We need to enable some securables for connecting to SQL instances,connecting to all databases, and also some read-only permissions like below:

 - CONNECT ANY DATABASE
      GRANT CONNECT ANY DATABASE to < user >
 - CONNECT SQL
      GRANT CONNECT SQL to < user >
 - SELECT ALL USER SECURABLES
      GRANT SELECT ALL USER SECURABLES to < user >
 - VIEW ANY DATABASE
      GRANT VIEW ANY DATABASE to < user >
 - VIEW ANY DEFINITION
      GRANT VIEW ANY DEFINITION to < user >
 - VIEW SERVER STATE
      GRANT VIEW SERVER STATE to < user >

Hierarchy of MSSQL Cluster resources

  • MSSQL Windows Failover Cluster
        - MSSQL Windows Failover Cluster Node
        - MSSQL Windows Failover Cluster Role
        - MSSQL Windows Failover Cluster Storage Disk
        - MSSQL Server Instance

Default Monitoring Configurations

Mssql-cluster has default Global Device Management Policies, Global Templates, Global Monitors and Global metrics in OpsRamp. You can customize these default monitoring configurations as per your business use cases by cloning respective global templates and global Device Management Policies. OpsRamp recommends performing the below activity before installing the application to avoid noise alerts and data.

1. Default Global Device Management Policies

   OpsRamp has a Global Device Management Policy for each Native Type of MS SQL Cluster. You can find those Device Management Policies at Setup > Resources > Device Management Policies, search with suggested names in global scope. Each Device Management Policy follows below naming convention:

   {appName nativeType - version}

   Ex: mssql-cluster MSSQL Windows Failover Cluster - 1 (i.e, appName = mssql-cluster, nativeType =MSSQL Windows Failover Cluster, version = 1)

2. Default Global Templates

   OpsRamp has a Global Template for each Native Type of MSSQL Windows Failover Cluster. You can find those templates at Setup > Monitoring > Templates, search with suggested names in global scope. Each template follows below naming convention:

   {appName nativeType 'Template' - version}

   Ex: mssql-cluster MSSQL Windows Failover Cluster Template - 1 (i.e, appName = mssql-cluster , nativeType = MSSQL Windows Failover Cluster, version = 1)

3. Default Global Monitors

   OpsRamp has a Global Monitors for each Native Type which has monitoring support. You can find those monitors at Setup > Monitoring > Monitors, search with suggested names in global scope. Each Monitors follows below naming convention:

   {monitorKey appName nativeType - version}

   Ex: MSSQL Windows Failover Cluster Monitor mssql-cluster MSSQL Windows Failover Cluster 1 (i.e, monitorKey = MSSQL Windows Failover Cluster Monitor, appName = mssql-cluster , nativeType = MSSQL Windows Failover Cluster , version= 1)

Configure and Install the MS SQL Cluster Integration

1. From All Clients, select a client.
2. Go to Setup > Integrations and Apps.
3. The INSTALLED INTEGRATIONS page, where all the installed applications are displayed.
   Note: If there are no installed applications, it will navigate to the ADD APP page.

4. Click + ADD on the INSTALLED INTEGRATIONS page. The AVAILABLE INTEGRATIONS AND APPS page displays all the available applications along with the newly created application with the version.
   Note: You can even search for the application using the search option available. Also you can use the All Categories option to search.

5. Click ADD in the MS SQL Cluster application.
6. In the Configurations page, click + ADD. The Add Configuration page appears.
7. Enter the below mentioned BASIC INFORMATION:

Notes:

• By default the Is Secure and SQL Server Authentication Mode checkbox is selected.
• IP Address/Host Name and Port should be accessible from Gateway.
• App Failure Notifications: if turned on, you will be notified in case of an application failure that is, Connectivity Exception, Authentication Exception.

8. Select the below mentioned Custom Attribute:

Note: The custom attribute that you add here will be assigned to all the resources that are created by the integration. You can add a maximum of five custom attributes (key and value pair).

9. In the RESOURCE TYPE section, select:
   • ALL: All the existing and future resources will be discovered.
   • SELECT: You can select one or multiple resources to be discovered.
10. In the DISCOVERY SCHEDULE section, select Recurrence Pattern to add one of the following patterns:
    • Minutes
    • Hourly
    • Daily
    • Weekly
    • Monthly
11. Click ADD.

Now the configuration is saved and displayed on the configurations page after you save it.
Note: From the same page, you may Edit and Remove the created configuration.

12. Click Next.
13. Below are the optional steps you can perform on the Installation page.

• Under the ADVANCED SETTINGS, Select the Bypass Resource Reconciliation option, if you wish to bypass resource reconciliation when encountering the same resources discovered by multiple applications.

  Note: If two different applications provide identical discovery attributes, two separate resources will be generated with those respective attributes from the individual discoveries.

• Click +ADD to create a new collector by providing a name or use the pre-populated name.

14. Select an existing registered profile.

15. Click FINISH.

The application is now installed and displayed on the INSTALLED INTEGRATION page. Use the search field to find the installed application.

Modify the Configuration

• See Modify an Installed Integration or Application article.

  Note: Select the MS SQL Cluster application.

View the MS SQL Cluster Details

The MS SQL Cluster integration is displayed in the Infrastructure > Resources > Cluster. You can navigate to the Attributes tab to view the discovery details and Metrics tab to view the metric details for MS SQL Cluster.

View resource metrics

To confirm MS SQL Cluster monitoring, review the following:

• Metric graphs: A graph is plotted for each metric that is enabled in the configuration.
• Alerts: Alerts are generated for metrics that are configured as defined for integration.

Resource Type Filter Keys

MSSQL Cluster application resources are filtered and discovered based on below keys:

Supported Alert Custom Macros

Customize the alert subject and description with below macros then it will generate alerts based on customisation. Supported macros keys:

Supported Metrics

Risks, Limitations & Assumptions

• This application only supports mssql-cluster which is built upon the Windows failover cluster.
• For Cluster object discovery and monitoring implementation, we are considering the object which has Name equals to Cluster Name in Get-ClusterResource response.
• For ClusterGroup monitoring implementation, We are considering the object which has Name as Cluster Group in Get-ClusterGroup response.
• All the failover status metrics possible instance values are;
  • 0 - if there is no change in OwnerNode
  • 1 - If there is a change in OwnerNode
  • 2 - If no OwnerNode
• Application can handle Critical/Recovery failure notifications for below two cases when user enables App Failure Notifications in configuration
  • Connectivity Exception
  • Authentication Exception
• Application will not send any duplicate/repeat failure alert notification until the already existing critical alert is recovered.
• If user enables agent monitoring templates on the Cluster/Node resource, he might see the duplicate metrics with different naming conventions
• In order to determine the actual node IP address from the list of received IPs, we are assuming that the node IP address is a part of the same subnet as the cluster IP address. For example, let’s say the actual node IP is 10.1.1.1 when trying to fetch the details, we will receive two IPs, one associated with the cluster (192.168.0.0) and the other is the actual IP. i.e., if the cluster IP is 10.1.1, the node IPs will be 10.1.X.X.
• We have provided the provision to give Cluster Ip Address OR HostName in configuration, but hostName provision will work only if the host name resolution works.
• Classic gateway requires additional libraries to be packaged which are needed to execute the powershell script to achieve this integration approach.
• This application supports both Classic Gateway and NextGen Gateway.
  Note: Not supported with Cluster Gateway. No support of showing activity log and applied time.
• Optional configuration parameters can be defined.
• Application upgrade is an automated process with version change.