Using Data Connector to migrate data or perform a database restore

Products

IT Management Suite Asset Management Solution CMDB Solution

Issue/Introduction

This Q&A article provides information on how Data Connector can be used to perform a data migration, restore or copy of data from one SMP Server (Symantec Management Platform or Notification Server) database to another.

PLEASE NOTE: Broadcom Technical Support is unable to assist the customer with the in-depth implementation or troubleshooting of using Data Connector to migrate, restore or copy data. Broadcom Technical Support can provide the customer with information on how best to achieve their goals and can demonstrate how to set up an import rule, but for extensive configuration, the customer can contact Broadcom Consulting Services for this, who can be reached at https://www.broadcom.com/company/partners/symantec

Environment

ITMS 8.x

Resolution

Question: Can Data Connector be used to perform a data migration?
Answer: The answer depends on the nature of the migration:

One-time migration
Data Connector can be used to migrate data from an old Notification Server version to a new version, such as 6.0 to 7.5. This is very complex, however, because the customer would need to be familiar with what tables and data classes to access, and be familiar with the differences in the database schemas between the two versions. Because of this, is it strongly recommended to instead use the Data Migration Wizard for data migration.

Continuous migration
If the customer needs to have an on-going "migration" of data, using the Data Migration Wizard would not be practical. The best suggestion is to then create a linked server object in SQL Server Management Studio to the old version's database and then make custom reports to view its data on the new Notification Server. This is the officially supported way to use "continuous" old data on a new Notification Server.

Question: Can Data Connector be used to perform a database restore or copy data from one database to another?
Answer: If data is needing to be restored, it is recommended to perform a database restore in Microsoft SQL Server to the last known good backup of the database. With that said, Data Connector can be used to restore or copy data from one database to another. The complexity is similar to using Data Connector to perform a data migration, however, as the customer would need to be familiar with what tables and data classes to access.

Question: Can Data Connector be used to migrate or copy Inventory data?
Answer: While Data Connector can be used to migrate or copy Inventory data, this is not recommended. This can result in problematic or duplicate inventory.

Question: With the above information and complexity in mind, how can Data Connector be used to perform a data migration, restore or copy data from one database to another?
Answer: To begin with, the customer should be familiar with how to create and configure an import rule. Information on this can be found in the following articles:

About Data Connector

Using Data Connector to import data into the Symantec Management Platform database
https://knowledge.broadcom.com/external/article?articleNumber=181160

Next, it should be noted that there is not usually a single table for resources that can be used with the import rule. This results in likely needing numerous import rules to be used to numerous tables for a single resource type. The most efficient way to do this is by using an OLEDB data source that references a custom import SQL query that the customer creates. For example, computers are desired to be migrated/restored/copied. The following, in brief, describe how this can be accomplished:

Create a "primary" import rule that uses an OLEDB data source. For the data source, specify it to use a custom import query. This SQL query should list (1) the primary data classes and one-to-one associations that are to be imported, and (2) limit which records are to be imported. For example, the following SQL script provides a basic example of how these can be imported:

SELECT Name 'Computer', [Serial Number] , [System Number]
FROM vAsset va
LEFT JOIN vItem vi
ON va._ResourceGuid = vi.Guid
WHERE vi.Name = 'Name of computer to include'
OR vi.Name = 'Name of second computer to include (etc.)'
ORDER BY 1
Create any secondary import rules as necessary that also use an OLEDB data source with a custom import query. Their SQL queries will bring in, one type at a time, one-to-many associations and custom data classes/associations. For example, cost items can be one-to-many, which the following SQL script provides an example of how these can be imported. Note: Association references assume that these already exist in the database before they are associated to a resource. If not, these will have to be imported themselves by another import rule first, before they can later be associated using this step to their resource records.

SELECT vi.Name 'Resource Name', vi2.Name 'Type', iad.[Cost Id], ici.Date, ici.Amount, ici.Status, ici.Description, ipoi.[Order Number]
FROM Inv_Cost_Items ici
LEFT JOIN vAsset va
ON ici._ResourceGuid = va._ResourceGuid
LEFT JOIN vItem vi
ON va._ResourceGuid = vi.Guid
LEFT JOIN vItem vi2
ON ici.Type = vi2.Guid
LEFT JOIN Inv_Accounting_Code_Details iad
ON ici.[Accounting Code] = iad._ResourceGuid
LEFT JOIN Inv_Purchase_Order_Information ipoi
ON ici._ResourceGuid = ipoi._ResourceGuid
ORDER BY 1
Once all of the secondary import rules are finished, the imported records should essentially be the same as their originals in the old database. If anything was left over still, create additional secondary import rules to import this data until all data classes and associations are imported.

Notes:
(1) The following article further discusses how to create a Data Connector import rule that accesses a database:

Creating a Data Connector data source that accesses data in a database through OLEDB
https://knowledge.broadcom.com/external/article?articleNumber=181647

(2) An internal-only Symantec Knowledge Base article exists that further demonstrates how to perform these steps. This can be provided upon request by contacting Symantec Technical Support and asking for article HOWTO85114.

Question: Since using Data Connector to migrate data is very complex, is there an easier way that this can be performed?
Answer: Yes, use the Data Migration Wizard. Migration and implementation guides can be found in the Symantec Knowledge Base. Some of these include:

IT Management Suite Data Migration