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.

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 Table 1.

      mssql_alert_purpose

      See Table 1.

      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.