000013731 - How to manage multiple SQL instances reside on the same server

Document created by RSA Customer Support Employee on Jun 16, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000013731
Applies Toenvision 3.5.x, 3.7.x, 4.0
IssueYou wish to know how to set up envision so that it can manage multiple SQL instances which reside on the same server
You have 2 or more instances set up on the same SQL server. While configuring envision to manage both of them, you realise there are errors in the NIC system about connecting to these database. In specific it's not able to access the trace file 
CauseThe trace file is used to log audit information of the database. With SQL 2005, most of the logs are stored using the trace file. If there are multiple ODBC sources trying to access the trace file, it will result in lock up, thus you see error in envision message viewer
Resolution

You will need to create additional ODBC type to cater for the additional instances you wish to audit.

In order to create the additional ODBC type, all the settings will remain the same as the original MSSQL odbc type, except:

  1. The name of the ODBC need to be different (e.g: MSSQL_2)
  2. The location of the trace file need to be different (e.g: C:\trace\TimTrace2)

To create additional ODBC type

1. Go to overview -> Systme configuration -> services -> Universal Device Collection -> Manage ODBC Type

2. Click Add. Then fill in the following details

Name: MSSQL_2 (or any name differ from MSSQL)

Description: <optional>

Device Name: Microsoft SQL Server

Device desrciption: <optional>

Tag: MSSQL

Default Interval: 10 Seconds

Output field delimiter: ||

Data query: exec nic_aud_swap_trace 3, 'c:\Trace\Trace2', 1, 'WHERE StartTime > %TRACKING%'

Address column: <optional>

Level column: <optional>

EventID Column: EventClass

Max tracking query: Select getDate()

Tracking column:StartTime

Note: For this ODBC type, the trace file will be stored in C:\Trace folder of the SQL server. Please ensure the folder C:\Trace existed before you create this type.

3. Click Apply

4. Go to manage ODBC service and create a ODBC service for the 2nd instance. In the type, select the one you have just created. (Match it with the Name)

5. Repeat the same with other instances you have set up on the SQL server

6. Restart NIC ODBC service

Now you are able to collect trace file for all the instances in your SQL server

Legacy Article IDa42564

Attachments

    Outcomes