Use the inbuilt problem and alert signatures in vRealize Log Insight for the Microsoft SQL Server for vRealize Automation. For each alert, you create one instance for each region so that alerts are still available if the Microsoft SQL Server instance is failed over to Region B.

About this task

For monitoring the health of the Microsoft SQL Server installation in the Software-Defined Data Center, you can use the following alerts in vRealize Log Insight:

Table 1. Microsoft SQL Server Alerts in vRealize Log Insight

Alert Name

Purpose

Severity

MS-SQL: Failed login attempt

Error codes in this group will have severity levels 14 or 16.

Login errors with severity level 14 would indicate security-related errors, such as permission denied.

Login errors with severity 16 would indicate login error that can be rectified by user. The exact error message text appears in the line just after the error code and severity level. Detailed error description can be found on:

http://technet.microsoft.com/en-us/library/cc645603%28v=sql.105%29.aspx

Critical

MS-SQL: Out of Memory (Resources)

Error codes in this group will have severity levels 17 or 16.

Severity level 17 Indicates that the statement caused SQL Server to run out of resources (such as memory, locks, or disk space for the database) or to exceed some limit set by the system administrator.

Severity level 16 indicates issues that can be addressed by the user.

For further information on error codes falling in the category refer:

http://technet.microsoft.com/en-us/library/cc645603%28v=sql.105%29.aspx

Critical

MS-SQL : Transaction Deadlocked

Transaction deadlock errors are usually characterized by severity level 13.

This implies transaction was deadlocked on resources with another process and has been chosen as the deadlock victim. This signifies that you will have to re run the transaction.

For further information on error codes falling in the category refer:

http://technet.microsoft.com/en-us/library/cc645603%28v=sql.105%29.aspx

Critical

MS-SQL : Database Corruption

Database corruption is defined as a problem associated with the improper storage of the actual zeroes and ones needed to store you database data at the disk or IO sub-system level.

Error codes in this group will have severity levels 20, 21, 22 or 23 or 16.

You might need to check the logical and physical integrity of all the objects in the specified database.

You can run DBCC CHECKDB to check for any database corruption.

For more details on various error messages visit:

http://technet.microsoft.com/en-us/library/cc645603%28v=sql.105%29.aspx

For detailed information on MS SQL severity:

http://msdn.microsoft.com/en-us/library/ms164086(v=sql.100).aspx

Critical

Procedure

  1. Open the vRealize Log Insight user interface.
    1. Open a Web browser and go to the following URL.

      Region

      vRealize Log Insight URL

      Region A

      https://sfo01vrli01.sfo01.rainpole.local

      Region B

      https://lax01vrli01.lax01.rainpole.local

    2. Log in using the following credentials.

      Setting

      Value

      User name

      admin

      Password

      vrli_admin_password

  2. In the vRealize Log Insight user interface, click Interactive Analytics.
  3. Click the icon and select Manage Alerts.
  4. Select an alert that is related to Microsoft SQL for vRealize Automation.
    1. In the search box of the Alerts dialog box, enter the following alert name as a search phrase.
      MS-SQL: Failed login attempt
    2. Select the alert from the search result and click the Edit icon next to the alert name.
  5. Create an alert for the Microsoft SQL Server for vRealize Automation in Region A using the name of the virtual machine in the query filter.
    1. In the New Alert dialog box, click Run Query.

      A query editor page opens.

    2. Click Add Filter and use the drop-down menus to define the following filter.
      Table 2. Filters for vRealize Log Insight

      Filter

      Value for Microsoft SQL for vRealize Automation Alerts in Region A

      Value for Microsoft SQL for vRealize Automation Alerts in Region B

      Object type

      vc_vm_name

      vc_vm_name

      Operation

      contains

      contains

      Object

      vra01mssql01

      vra01mssql01

    3. Click on the Search icon.
    4. Click the icon and select Create Alert from Query.
    5. In the New Alert dialog box, configure the following alert settings and click Save.
      Table 3. Alerts for vRealize Log Insight

      Setting

      Value for Microsoft SQL for vRealize Automation Alert in Region A

      Value for Microsoft SQL for vRealize Automation Alert in Region B

      Name

      MS-SQL: Failed login attempt (vra01mssql01))

      MS-SQL: Failed login attempt (vra01mssql01))

      Description (Recommendation)

      mssql_alert_purpose

      See Microsoft SQL Server Alerts in vRealize Log Insight.

      mssql_alert_purpose

      See Microsoft SQL Server Alerts in vRealize Log Insight.

      Email

      Email address to send alerts to

      Email address to send alerts to

      Send to vRealize Operations Manager

      Selected

      Selected

      Fallback Object (VMs)

      vra01mssql01

      vra01mssql01

      Criticality

      critical

      critical

      Raise an alert

      On any match

      On any match

  6. Repeat Step 3 to Step 5 for the rest of the alerts in the region.
  7. Repeat the procedure in vRealize Log Insight to create the alerts for the Microsoft SQL Server for vRealize Automation virtual machine in the other region.