How to enable a Virtual Data Class to access different database by using a SQL view

book

Article ID: 181396

calendar_today

Updated On:

Products

CMDB Solution

Issue/Introduction

 

Resolution

A Virtual Data Class (VDC) can access a different database other than the default Symantec_CMDB database. In some situations, however, a direct database connection may not appear in its server drop-down list, even if in SQL a linked server object is present and connects successfully to the other database. This is entirely controlled by a linked server object on the SQL Server, which is not entirely functioning as expected. Information on how to configure this can be found here:

How to create a Microsoft SQL Server Linked Server Object for use with CMDB Solution's Virtual Data Class
http://www.symantec.com/business/support/index?page=content&id=HOWTO83783

If a linked server cannot be successfully made, a method of working around this is to instead create a custom view that leverages the linked server. This assumes that the user has created a working linked server. The following instructions demonstrate how this can be accomplished:
 

  1. On the SQL server that hosts the Symantec_CMDB database, run Microsoft SQL Server Management Studio.
  2. Create a new query.
  3. Write a SQL script that will provide the data classes that are to be used later by the VDC. For example:

    USE Symantec_CMDB -- Change this to the name of the correct Symantec database, if it is not the default name.
    SELECT *
    FROM [linked-server-name].database-name-on-the-other-server.dbo.table-name-on-the-other-server

    Note: If the user's version of this fails to work, they are either not setting the database, table or column names correctly,  or the linked server object is not working as expected. Ensure that the script can run directly on the other database with the same basic syntax, if not, that will need to be resolved first.

    Specifically, if a connection is to be made to a ServiceDesk ProcessManager database, this could look like the following then:

    SELECT *
    FROM [SDESK75].ProcessManager.dbo.Task

    This lists all the columns from the Task table in the ProcessManager (ServiceDesk database) on the SDESK75 SQL Server.
     
  4. Once a SQL script is ready, use this to save as a new view:

    USE Symantec_CMDB -- Ensure that the database name to use is correct otherwise the view will go to where ever the current database is set to, such as to "master" (default). Also, change this to the name of the correct Symantec database, if it is not the default name.

    And then run the following but not at the same time:

    CREATE VIEW dbo.view-name AS
    FROM [linked-server-name].database-name-on-the-other-server.dbo.table-name-on-the-other-server
     
  5. In the Symantec Management Platform Console, create or edit a VDC. If a console is already open to one, the user will need to refresh the page or go to another area, then come back to the VDC, as its drop-down list will not automatically be refreshed.
  6. In the VDC, select the following:

    Database server: Pick the server name of the server that hosts the Symantec_CMDB database. This may be the only choice present.
    Database: Symantec_CMDB, or otherwise pick the name of the Symantec database if it is something else.
    Table/View name: Pick the new view created in step 4.
     
  7. The user can then continue configuring the VDC as they desire, which will display its selected columns in the attributes configuration section.
  8. When finished, click on the "Save changes" button.