How to use Data Connector to export data from one Altiris database and import it into another (data restoration)

book

Article ID: 181503

calendar_today

Updated On:

Products

Symantec Products

Issue/Introduction

 

Resolution

PLEASE NOTE: Because of the extreme complexity of using a Data Connector import rule for data recovery, the customer is strongly recommended to perform a database restore to recover their data. Symantec Technical Support does not provide data recovery services and is unable to assist the customer with recovering their data, regardless of the cause of the data loss. Because of this, Support can only guide the customer on how the following instructions work but cannot implement or perform the data recovery. Symantec Consulting Services can assist with data recovery and implementation. They can be reached at:

https://www.symantec.com/it-consulting-services


A Data Connector import rule can be used to export data from one Altiris database to another. The following instructions demonstrate how this works. Note: If the customer is needing extensive data copying, such as for data migration or data recovery, Data Connector is not the best tool to use for these processes. These instructions are therefore intended for a limited and/or small amount of data with straightforward data class mappings and little to no one-to-many associations needed to be brought over from one database to another. For information on performing data migration or data recovery, refer to the following article:

Can Data Connector be used to migrate data or perform a database restore?
http://www.symantec.com/business/support/index?page=content&id=HOWTO77183

Exporting and importing data from two databases can be accomplished by one of two Data Connector methods:
 

  • Import using a .CSV file: Using the Symantec Management Platform Console connected to the old database, create a data source and export rule to export selected records and their data classes and associations to a file. Take this file afterwards to the Symantec Management Platform Console connected to the new database. Then, create a data source and import rule to import the old records from the file.
  • Import directly from a database: If the old database is no longer connected to a Symantec Management Platform Console, this will make the process much more complicated because the customer must then know what tables to access as the source. This then gets into the realm of data migration or data recovery, which Data Connector is not really intended to be used for (per KB HOWTO77183). This does put more difficulty into the process because instead of specific records being accessed, now multiple tables must be accessed, which will often require a lot of understanding of how the database works and which tables need to be accessed for specific parts of a record. With that said, this can be the easiest scenario if a SQL script is used for the custom import query.


Instructions for how to export records from an old database to a .CSV file and then import these into a new database.
 

  1. In a Symantec Management Platform Console connected to the old database, click on the Settings button > All Settings.
  2. Click to expand the folders Notification Server > Connector > Data Sources.
  3. Right click on the Data Sources folder and choose New > CSV File Data Source.
  4. Enter a name for the new data source.
  5. Click on the checkbox for "Allow export".
  6. Enter a folder to export the file to in the "Export to folder" field.
  7. Enter a file name prefix in "New file name prefix" field.
  8. Click on the "Save changes" button.
  9. Click to expand the folder Import/Export Rules.
  10. Right click on the folder Import/Export Rules and choose New > Resource Import/Export Rule.
  11. Enter a name for the new import rule.
  12. Select the data source created in steps 1 through 8 in the drop-down "Data source" list.
  13. As these instructions are for a limited number of records and/or data classes, a report of the resources in question must be made beforehand. This is then referenced in the drop-down "Export filter" list by selecting Report and then clicking on "Select a report" and picking the custom report. For example, the custom report may be referencing computers and only ten of them. A SQL script could be used for the custom report to do this, such as:

    SELECT *
    FROM vComputer
    WHERE Name = 'PC1' OR Name = 'PC2' OR Name = 'PC3' OR Name = 'PC4' OR Name = 'PC5'
    OR Name = 'PC6' OR Name = 'PC7' OR Name = 'PC8' OR Name = 'PC9' OR Name = 'PC10'
     
  14. Change the "Resource type" drop-down list to the resource type being exported, such as Computer.
  15. Enter a name for the resource name column in the "Resource name" field, such as Computer Name. This will be the column in the export file where the resource's name value is saved to.
  16. The out of box data classes, associations and reverse associations are added automatically to what will be exported. Add or remove any that are not wanted. Note: If custom data classes are to be exported, these must be manually selected by clicking on the "Select data classes" link, as they will not automatically be added.
  17. Click on the "Save changes" button. Note: In some cases, certain associations and reverse associations may reuse the column name. If so, an error will occur explaining that these have been attempted to be exported multiple times. Either remove one of the associations that the error refers to or change its output column name to be unique.
  18. Click on the "Run now" button.
  19. After the export, open the output file to verify that the records, data classes and associations that were expected were successfully exported.
  20. Take the .CSV file from the old Symantec Management Platform server to the new Symantec Management Platform server.
  21. In a Symantec Management Platform Console connected to the new database, click on the Settings button > All Settings.
  22. Click to expand the folders Notification Server > Connector > Data Sources.
  23. Right click on the Data Sources folder and choose New > CSV File Data Source.
  24. Enter a name for the new data source.
  25. Click on the checkbox for "Allow import".
  26. In the "Import from file" field, click on the browse button and find and select the .CSV file created in steps 1 through 8.
  27. Click on the "Save changes" button.
  28. Click to expand the folder Import/Export Rules.
  29. Right click on the folder Import/Export Rules and choose New > Resource Import/Export Rule.
  30. Enter a name for the new import rule.
  31. Select the data source created in steps 21 through 27 in the drop-down "Data source" list. 
  32. Change the "Resource type" drop-down list to the resource type being exported, such as Computer.
  33. In the "Resource lookup key" drop-down list, select Resource Name.
  34. In the Name field, this should be set automatically to the column name from step 15, if not, change it accordingly.
  35. If updating current records with old data only, uncheck "Create resources if doesn't exist" to prevent the creation of new resources.
  36. Check over all data classes to ensure that their source columns are set to the correct column from the .CSV file. If not, change them accordingly. Note: If custom data classes are to be imported, these must be manually selected by clicking on the "Select data classes" link, as they will not automatically be added.
  37. For all associations and reverse associations,  each one's "Lookup key" and "Key column mappings" must be manually set by the customer. For the "Lookup key", all can usually be set to "Resource Name". The customer will then need to set the "Key column mappings" for the Name to the correct column name from the .CSV file. Notes: (1) This will set this automatically to the first column from the .CSV file, which is incorrect. (2) The customer can refer to step 16, which shows all of the exported association and reverse association column names, as a reference to what to set the "Key column mappings" to.
  38. Once all data classes, associations and reverse associations are configured correctly, click on the "Save changes" button.
  39. Click on the "Test rule" button. Ensure that the expected records, data classes, associations and reverse associations are "imported" correctly. Note: If errors occur such as "Rows skipped", this could indicate issues with the data attempting to be brought in. For example, foreign keys and associations must already exist in the new database before resources can be associated to them. Refer to the following articles on further troubleshooting import issues:

    How to troubleshoot import issues when using a Data Connector import rule
    http://www.symantec.com/business/support/index?page=content&id=TECH195835

    How to troubleshoot Data Connector import rule import log errors
    http://www.symantec.com/business/support/index?page=content&id=TECH207635
     
  40. Once the customer feels comfortable with the test run, click on the "Run now" button to import the data.
  41. In the Console, verify that the expected records were created or updated, with the expected data classes, associations and reverse associations.


Instructions for how to export records from an old database directly into a new database.

  1. In a Symantec Management Platform Console connected to the new database, click on the Settings button > All Settings.
  2. Click to expand the folders Notification Server > Connector > Data Sources.
  3. Right click on the Data Sources folder and choose New > OLEDB Data Source.
  4. Enter a name for the new data source.
  5. In the "DB name" drop-down list, select the name of the old database. This must be mounted on a SQL Server, but is not required to have a Symantec Management Platform connected to it.
  6. In the "Table name" drop-down list, select the table or view to import data from or "Specify custom import query" to add a SQL script. The latter option is recommended if only certain records are desired to be imported, not all of them.
     
    For tables and views

    Important Note: Here is where the significant complexity of these set of instructions come in at. The customer must know what table or view they wish to import data from. In many cases, this can end up being multiple tables and views just to populate data classes for a single resource type, such as a computer. This is because few tables and views will include all expected or desired data classes, and therefore, numerous ones must be selected. However, only one table or view at a time can be selected by a data source. This will then require the customer to either create multiple sets of data sources and import rules to fully import all expected and desired data classes, or, create a SQL view that lists all of these and then this alone used (recommended). For this example, the vComputer view will be used to simplify the import. If tables are needing to be used instead, for computers for example, this could start with the RM_ResourceComputer but would then likely need to then also reference other tables and views afterwards to complete the import.

    For custom import query

    The customer will need to write their own custom SQL script to access the desired tables or views, the specific records and the data classes, associations and reverse associations needed. This may be the easiest scenario to implement if a SQL script is used. For example, the SQL script from the prior instruction's step 13 could be used as an example of how to do this. A more robust SQL script is included as an attachment, "Display standard CMDB computer data classes and associations.txt", that can be used as an elaborate example of how to import computer records.

    Conclusion: In either scenario for this step, the customer will likely need to either write a custom view or SQL script to further accommodate the data source to only see specific records, data classes and their associations to then later match up in the import rule with.
     
  7. Click on the checkbox for "Allow import".
  8. Click on the "Save changes" button.
  9. Click to expand the folder Import/Export Rules.
  10. Right click on the folder Import/Export Rules and choose New > Resource Import/Export Rule.
  11. Enter a name for the new import rule.
  12. Select the data source created in steps 1 through 8 in the drop-down "Data source" list.
  13. Change the "Resource type" drop-down list to the resource type being exported, such as Computer.
  14. In the "Resource lookup key" drop-down list, select Resource Name.
  15. In the Name field, choose the resource's Name field as decided by the data source. This will usually be just "Name".
  16. If updating current records with old data only, uncheck "Create resources if doesn't exist" to prevent the creation of new resources.
  17. For all data classes, associations and reverse associations, these will often need to be manually set up by the customer. Refer to the prior set of instruction's steps 36 and 37 for more information.  Important Note: Here is where another complexity for these set of instructions occur at. There may be significant amount of work that needs to be performed by the customer to correctly set up all data classes, associations and reverse associations as these will often not automatically be set. If a custom view is used for the data source, the customer should try to ensure that the column names of the views match as closely to the data classes, associations and reverse associations names so that they will more readily match up, but otherwise, this is entirely a manually process for this step.
  18. Once all data classes, associations and reverse associations are configured correctly, click on the "Save changes" button.
  19. Click on the "Test rule" button. Ensure that the expected records, data classes, associations and reverse associations are "imported" correctly. Note: If errors occur, refer to the prior instruction's step 39 for troubleshooting information.
  20. Once the customer feels comfortable with the test run, click on the "Run now" button to import the data.
  21. In the Console, verify that the expected records were created or updated, with the expected data classes, associations and reverse associations.
     

Related Article

How to import data using a Data Connector Import Rule
http://www.symantec.com/business/support/index?page=content&id=HOWTO79380

Attachments

Display standard CMDB computer data classes and associations.txt get_app