TN - 1294 Backfill Tier 2 Historian Data Using Existing Tier 1 Historian Data (Replication TagName Differs From Source Tag Name)

Description

NOTE: This is not an officially supported method.  AVEVA has released an official replication backfill utility in the System Platform 2023.

Occasionally, a tier 2 Historian will be set up long after a Tier one.  In the case that applications will be layered on top of the tier 2 Historian, it may be desirable to have the older history from the Tier one transferred to that one as well.  We cannot simply copy History Blocks over from the Tier 1 circular folder due to replicated tags having a different TagID in the database than the tags they are replicating from.  Additional steps must be taken to sync these up.  These steps will look slightly different depending on the naming schema chosen for replication to tier 2:

• If you are using a suffix or prefix, i.e. <server_name>.<source_tag_name>, we can take advantage of the built in tag rename utility provided by historian.  This can be found at C:\Program FIles(x86)\Wonderware\Historian\X64\aahTagRename.exe.
• If you are replicating to tier 2 and keeping the same tag name, i.e. <source_tag_name>, we will have to do a few extra steps to get those tags into the TagHistory table.  This process will detailed in a later Knowledge Article (Timestamp: 10/3/2022)

The details:  This process takes advantage of the Historian TagHistory table that stores deleted and modified tags.  This table is what allows a user to make changes to the tag yet maintain the connection to history collected for the tag.  It also allows you connect to the history of a previous deleted tag if you were to ever create that tag again.  This is what we will be doing with the second scenario detailed above.

• Author: Devin Hepburn
• Published: 10/01/2022
• Applies to: Historian 2017 and Newer

Details

If you are replicating to tier 2 and keeping the same tag name, i.e. <source_tag_name>, we will have to do a few extra steps to get those tags into the TagHistory table.  This process will detailed in a later Knowledge Article.

Both scenarios require that a full restore of the original T1 Historian Runtime database be done on the Tier 2 historian.  This can be done using the Historian Configuration Import/Export Tool.  Export on the T1 and Import on the T2.

1. Ensure that the historian is not running and that you are on a fresh (unused) runtime database.
2. Migrate all History Blocks from the Circular folder on Tier 1 to the Circular Folder on Tier 2.
3. Restore the historian configuration on the T2 historian like mentioned above.  It is important to note that you will have additional items at first that we can remove later: IO Servers, IDAS configuration, replication configurations, etc.  These are from the T1 historian and will not be used.
4. ON THE TIER 2 HISTORIAN WITH FRESH INSTALL - Open SSMS (Sql Server Management Studio) - It is important to ensure that you were on a fresh runtime database prior to import to eliminate risk of losing data connections here:
   • Use the following SQL Script to clean up the restored runtime DB on the Tier 2 Historian and move all tag configurations into the Manual Tag configuration.  These tags will be moved into TagHistory after the TagRename Utility is ran.
   • Use RunTime
     Go
     --Move all current tags into Manual Tag
     Update _Tag SET IOServerKey = 2, TopicKey = 2 WHERE IOServerKey > 2
     GO
     --Delete all extra topics
     DELETE _Topic WHERE TopicKey > 2
     GO
     --Delete all extra IOServers (underneath IDAS)
     DELETE _IOServer WHERE IOServerKey > 2
     GO
     --Delete all extra IDAS
     DELETE _IODriver WHERE IODriverKey > 2
     GO
     --Delete replication settings
     DELETE FROM ReplicationTagEntity
     DELETE FROM ReplicationServer WHERE ReplicationServerKey > 1
     GO
5. Export the list of tags now held in the manual tag section of the configuration.  The default is "Tab Delimited Text"; select CSV instead.  This will help us generate a CSV file needed for the next step.
   • 
   • • Open the tag list export in excel.  It is comma delimited; excel should determine this automatically.  I had to move this file onto a system that had excel installed and then move it back.  Feel free to do the same.  Excel makes this step much easier due to formula copying.
       • Delete Row 1 that has the column headers.  This row is not needed.
       • For the data in the second column we will use the following formula:
       • =CONCAT("<Prefix>.",A1)
       • Be sure to replace the prefix with strings that match your system.  These are determined during replication setup on the Tier 1 (shown below).  It will look something like TestServer.Tagname  when complete.
         • 
       • Save the file and open it in notepad to verify that the format matches what is expected.  You should have a full list of old tag names followed by the new tag name that will be replicated into the system.  Store this file in a location that is easy to access. 
6. At this point you should have a rather clean Historian configuration with only a local IDAS and tags configured in the manual tag configuration.
   • 
   •  
   • Run the AVEVA Configurator for Historian Server to ensure it agrees with all DB changes.  We should now be good to start up the Historian on Tier 2 and begin allowing replication to the system.  As tags are replicated into the T2, they will be created in the system under the manual tags area.  They will show the TagName, SourceServer and SourceTagName.  We will use the TagRename utility to marry these new tags to the old ones.  This will sync up their data in the history blocks.
7. Allow replication to push all of the new tags into the system.  Once we have a complete list, stop the historian again.
8. Run the tag rename utility located at C:\Program FIles(x86)\Wonderware\Historian\X64\aahTagRename.exe.  You can do a tag by tag assignment or drop a csv file into the utility. 
9. Load the CSV into the system and click "Rename Tags".  For more information on the RenameUtility specifically, please refer to Tech Note Hist 248 
   • https://knowledge.insourcess.com/Video_Articles/Historian/TN_Hist248_How_to_use_the_Wonderware_Historian_Tag_Rename_Utility
   • The editor should show success for all tags.
10. Verify in the SMC that all of the older tags have been removed from the manual tags configuration section.  Their data link has been migrated over to the new tags.  Start the Historian Back Up.  Store and Forward from the Tier 1 should backfill any data lost during this setup process.
11. Verify tag history in the Query or Trend client tool.