How to create a Microsoft SQL Server Linked Server Object for use with CMDB Solution's Virtual Data Class

book

Article ID: 181397

calendar_today

Updated On:

Products

CMDB Solution

Issue/Introduction

 

Resolution

CMDB can create Virtual Data Classes (VDC) that accesses a different database other than the default Symantec_CMDB database. This is controlled by creating a linked server object on the Microsoft SQL Server that hosts the Symantec_CMDB database. Note: If a linked server cannot be created or fails to show up as a database server in a VDC, this prevents CMDB from being able to access the database directly. This is regardless of if the linked server enables scripts to run to it. A correctly configured linked server is required on the Microsoft SQL Server to function. CMDB has no control over whether the linked server works or not. Please contact your DBA or Microsoft for assistance with configuring linked servers. Additional information about how to set up linked servers can be found on Microsoft's web site:

http://msdn.microsoft.com/en-us/library/ms188279

If a linked server fails to enable a VDC to see it, a workaround is to set up a view to the linked server's database, tables and data classes. This can then be used by the VDC, if the linked server is not otherwise correctly configured to enable direct access. This workaround's instructions can be found here:

How to enable a Virtual Data Class to access different database by using a SQL view
http://www.symantec.com/business/support/index?page=content&id=HOWTO83778

Instructions for how to create a linked server that accesses a different Microsoft SQL Server are included below:
 

  1. On the SQL server that hosts the Symantec_CMDB database, run Microsoft SQL Server Management Studio.
  2. In the Object Explorer, click to open the folder Server Objects > Linked Servers.
  3. Right click on the Linked Server folder and choose New Linked Server.
  4. In the "Linked server" name field, enter the network name of the other Microsoft SQL Server. For example, "SDESK75".
  5. In the "Server type" field, click on SQL Server, if it is not already selected.
  6. Click on the Security page.
  7. Credentials of some form will likely need to be set up here. For example, click to choose "Be made using the login's current security context". Note: You may need to discuss this with your DBA if you are unsure which to use or what account to use.
  8. Click on the OK button. If errors occur, double check and change as necessary the name of the server and the credentials as configured in steps 4 and 7 before continuing.
  9. Right click on the new linked server and choose Test Connection. If errors occur, again double check the name of the server and the credentials before continuing.
  10. Once the test of the connection succeeds, also try a basic SQL query to the other database. For example:

    SELECT *
    FROM [linked-server-name].database-name-on-the-other-server.dbo.table-name-on-the-other-server

    Specifically,

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

    Note: As mentioned above, just because the connection test succeeds and a SQL script can work with the other database through the linked server does not necessarily guarantee that it will still work with a VDC. However, using the above steps, assuming that the other database server is on the same domain, should work in a normal environment with a VDC.
     
  11. Additional information on creating a VDC can be found in the Symantec Management Platform 7.1 SP2's User Guide starting on page 771:

    Symantec Management Platform 7.1 SP2 User Guide
    http://www.symantec.com/business/support/index?page=content&id=DOC4730
     
  12. 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.
  13. The new linked server should now appear in the "Database server" drop-down list. If not, the workaround from HOWTO83778 may need to be used if the user is unable to correctly configure the linked server.
  14. The user can then continue configuring the VDC as they desire.
  15. When finished, click on the "Save changes" button.