PME How to remove server name from ION databases
Issue
When upgrading, migrating, or installing PME the ION Databases make many references to the server name.
If during an upgrade, migration or installation 2 names get added to the database then many varying issues will results.
Examples can be seen below:
Product Line
ION EEM
ION Enterprise 6.0. x
Struxureware Power Monitoring 7.0.x
Power Monitoring Expert 7.2.2
Power Monitoring Expert 8.2
Power Monitoring Expert 9.0
Power Monitoring Expert 2020
Power Monitoring Expert 2021
Power Monitoring Expert 2022
Power Monitoring Expert 2023
Power Monitoring Expert 2024
Environment
SQL Server 2005
SQL Server 2008 R 2
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2018
SQL Server 2019
Cause
The most common cause of this issue is the CM Tool migration scripts not updating the server name in all of the ION databases.
The CM Tool migration script do not get executed by the tool when in a distributed PME architecture: System architecture
Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*
In order to resolve, we first need to determine the names of the application server that have been configured into the databases by the PME installer or the PME services.
To determine name of the application servers, log into SQL Server Management Studio and run the attached SQL script (select_server_name_in_live_databases.sql).
Alternatively, execute the query(s) below :
Script to find all server names in the ION databases:
SELECT * FROM [ION_Data].[dbo].[Source]
SELECT * FROM [ION_Network].[dbo].[Machine]
SELECT * FROM [ION_Network].[dbo].[Gate]
SELECT * FROM [ION_Network].[dbo].[IRM_DeviceInfo]
SELECT * FROM [ION_Network].[dbo].[SoftwareNode]
SELECT * FROM [ION_Network].[dbo].[SRC_Source]
SELECT * FROM [ION_Network].[dbo].[IAS_MeasurementAddress]
SELECT * FROM [ApplicationModules].[Inventory].[HostedServiceDetails]
Next, run the attached SQL script to delete the server name from the database. (delete_server_name_in_live_databases.sql)
!! It is usually BEST to delete the new server name that was added during the upgrade, migration, or installation !!
Script to delete a server name from the ION databases:
DECLARE @NewServerName nvarchar (100) = UPPER('WIN-9RUTOK23LU3')
-- update Source table
DELETE FROM [ION_Data].[dbo].IncidentAlarm WHERE AlarmID=(SELECT ID FROM [ION_Data].[dbo].Alarm WHERE AlarmDefinitionID=(SELECT ID FROM [ION_Data].[dbo].AlarmDefinition WHERE SourceID=(SELECT ID from [ION_Data].[dbo].Source WHERE Name ='LOGINSERTER.' + @NewServerName)))
DELETE FROM [ION_Data].[dbo].AlarmStates WHERE AlarmID=(SELECT ID FROM [ION_Data].[dbo].Alarm WHERE AlarmDefinitionID=(SELECT ID FROM [ION_Data].[dbo].AlarmDefinition WHERE SourceID=(SELECT ID from [ION_Data].[dbo].Source WHERE Name ='LOGINSERTER.' + @NewServerName)))
DELETE FROM [ION_Data].[dbo].AlarmEvent WHERE AlarmID=(SELECT ID FROM [ION_Data].[dbo].Alarm WHERE AlarmDefinitionID=(SELECT ID FROM [ION_Data].[dbo].AlarmDefinition WHERE SourceID=(SELECT ID from [ION_Data].[dbo].Source WHERE Name ='LOGINSERTER.' + @NewServerName)))
DELETE FROM [ION_Data].[dbo].Alarm WHERE AlarmDefinitionID=(SELECT ID FROM [ION_Data].[dbo].AlarmDefinition WHERE SourceID=(SELECT ID from [ION_Data].[dbo].Source WHERE Name ='LOGINSERTER.' + @NewServerName))
DELETE FROM [ION_Data].[dbo].AlarmDefinition WHERE SourceID=(SELECT ID FROM [ION_Data].[dbo].Source WHERE Name ='LOGINSERTER.' + @NewServerName)
DELETE FROM [ION_Data].[dbo].Device WHERE ID=(SELECT ID from [ION_Data].[dbo].Source WHERE Name ='LOGINSERTER.' + @NewServerName)
DELETE FROM [ION_Data].[dbo].EventLog2 WHERE SourceID=(select ID from [ION_Data].[dbo].Source WHERE Name ='LOGINSERTER.' + @NewServerName)
DELETE FROM [ION_Data].[dbo].RecorderState WHERE RecorderID=(SELECT ID FROM [ION_Data].[dbo].Recorder WHERE SourceID=(select ID from [ION_Data].[dbo].Source WHERE Name ='LOGINSERTER.' + @NewServerName))
DELETE FROM [ION_Data].[dbo].Recorder WHERE SourceID=(select ID from [ION_Data].[dbo].Source WHERE Name ='LOGINSERTER.' + @NewServerName)
DELETE FROM [ION_Data].[dbo].SourceState WHERE SourceID=(select ID from [ION_Data].[dbo].Source WHERE Name ='LOGINSERTER.' + @NewServerName)
DELETE FROM [ION_Data].[dbo].[Source] WHERE Name = 'LOGINSERTER.' + @NewServerName
DELETE FROM [ION_Data].[dbo].IncidentAlarm WHERE AlarmID=(SELECT ID FROM [ION_Data].[dbo].Alarm WHERE AlarmDefinitionID=(SELECT ID FROM [ION_Data].[dbo].AlarmDefinition WHERE SourceID=(SELECT ID from [ION_Data].[dbo].Source WHERE Name ='QUERYSERVER.' + @NewServerName)))
DELETE FROM [ION_Data].[dbo].AlarmStates WHERE AlarmID=(SELECT ID FROM [ION_Data].[dbo].Alarm WHERE AlarmDefinitionID=(SELECT ID FROM [ION_Data].[dbo].AlarmDefinition WHERE SourceID=(SELECT ID from [ION_Data].[dbo].Source WHERE Name ='QUERYSERVER.' + @NewServerName)))
DELETE FROM [ION_Data].[dbo].AlarmEvent WHERE AlarmID=(SELECT ID FROM [ION_Data].[dbo].Alarm WHERE AlarmDefinitionID=(SELECT ID FROM [ION_Data].[dbo].AlarmDefinition WHERE SourceID=(SELECT ID from [ION_Data].[dbo].Source WHERE Name ='QUERYSERVER.' + @NewServerName)))
DELETE FROM [ION_Data].[dbo].Alarm WHERE AlarmDefinitionID= (SELECT ID FROM [ION_Data].[dbo].AlarmDefinition WHERE SourceID=(SELECT ID from [ION_Data].[dbo].Source WHERE Name ='QUERYSERVER.' + @NewServerName))
DELETE FROM [ION_Data].[dbo].AlarmDefinition WHERE SourceID=(SELECT ID FROM [ION_Data].[dbo].Source WHERE Name ='QUERYSERVER.' + @NewServerName)
DELETE FROM [ION_Data].[dbo].[Source]WHERE Name = 'QUERYSERVER.' + @NewServerName
DECLARE @Command_ION_Data nvarchar (500)
IF (CAST((SELECT [Value] FROM [ION_Data].[dbo].[Registry] WHERE Name = 'Revision') AS DECIMAL) > 5.1)
BEGIN
SET @Command_ION_Data =
'DELETE FROM [ION_Data].[dbo].[Source] WHERE DisplayName = ''LOGINSERTER.' + @NewServerName + '''' + ';
DELETE FROM [ION_Data].[dbo].[Source] WHERE DisplayName = ''QUERYSERVER.' + @NewServerName + ''';'
EXEC (@Command_ION_Data)
END
-- update Machine and Gate tables
DELETE FROM [ION_Network].[dbo].PortState where ChannelID= (Select ID FROM [ION_Network].[dbo].Channel WHERE ID=(SELECT DataServerID FROM [ION_Network].[dbo].DataServer WHERE ID=(SELECT ID FROM [ION_Network].[dbo].[Machine] WHERE name = @NewServerName)))
DELETE FROM [ION_Network].[dbo].Channel WHERE DataServerID=(SELECT DataServerID FROM [ION_Network].[dbo].DataServer WHERE ID=(SELECT ID FROM [ION_Network].[dbo].[Machine] WHERE name = @NewServerName))
DELETE FROM [ION_Network].[dbo].DataServer WHERE ID=(SELECT ID FROM [ION_Network].[dbo].[Machine] WHERE name = @NewServerName)
DELETE FROM [ION_Network].[dbo].Scheduler WHERE MachineID=(SELECT ID FROM [ION_Network].[dbo].[Machine] WHERE name = @NewServerName)
DELETE FROM [ION_Network].[dbo].[Machine] WHERE name = @NewServerName
DELETE FROM [ION_Network].[dbo].[Gate] WHERE name = @NewServerName
-- update IRM_DeviceInfo table
DELETE FROM [ION_Network].[dbo].[IRM_DeviceInfo] WHERE Name = 'LOGINSERTER.' + @NewServerName
DELETE FROM [ION_Network].[dbo].[IRM_DeviceInfo] WHERE Name = 'QUERYSERVER.' + @NewServerName
-- delete Softwarenode table !!Network router service will repopulate when it starts!!
DELETE FROM [ION_Network].[dbo].[SoftwareNode]
-- update SRC_Source
DELETE FROM [ION_Network].[dbo].[SRC_Source] WHERE Name = 'LOGINSERTER.' + @NewServerName
DELETE FROM [ION_Network].[dbo].[SRC_Source] WHERE Name = 'QUERYSERVER.' + @NewServerName
DECLARE @Command_ION_Network nvarchar (500)
IF (CAST((SELECT [Value] FROM [ION_Network].[dbo].[Registry] WHERE Name = 'Revision') AS DECIMAL) > 13.1)
BEGIN
SET @Command_ION_Network =
'DELETE FROM [ION_Network].[dbo].[SRC_Source] WHERE DisplayName = ''LOGINSERTER.' + @NewServerName + '''' + ';
DELETE FROM [ION_Network].[dbo].[SRC_Source] WHERE DisplayName = ''QUERYSERVER.' + @NewServerName + ''';'
EXEC (@Command_ION_Network)
END
-- IAS_MeasurementAddress
DELETE FROM [ION_Network].[dbo].[IAS_MeasurementAddress] WHERE Segment2 like 'VIP%' AND ISManual = 0
-- App Mods Services
DELETE FROM [ApplicationModules].[Inventory].[HostedService] WHERE HostID IN (SELECT HostID FROM [ApplicationModules].[Inventory].[Host] WHERE ServerID > 1)
DELETE FROM [ApplicationModules].[Inventory].[Host] WHERE ServerID > 1
DELETE FROM [ApplicationModules].[Inventory].[ApplicationServer]
WHERE Name = @NewServerName
-- Event Watcher tables
DELETE FROM [ION_Network].[dbo].[EVT_SourceGroupMap] WHERE SourceName = 'LOGINSERTER.' + @NewServerName
Lastly, in cases where you'd like to fix the upgrade, migration, or installation that was causing issues, run the attached SQL script to update the server name from the database. (update_server_name_in_live_databases.sql)
Script to update the current server name in the ION databases:
-- Uncomment the variable definitions below and set values for them properly if run the script manually
-- Replace the variables <OLD_PME_SERVERNAME> and <NEW_PME_SERVERNAME> below with the actual names of the old and new PME server.
-- Use all upper-case letters for the names. Do not remove the single quotes " ' ". Example: SET @oldName = 'PMESERVER'.
--DECLARE @oldName nvarchar (100) = UPPER('<OLD_PME_SERVERNAME>')
--DECLARE @newName nvarchar (100) = UPPER('<NEW_PME_SERVERNAME>')
-- update Source table
UPDATE [ION_Data].[dbo].[Source]
SET Name = 'LOGINSERTER.' + @newName
WHERE Name = 'LOGINSERTER.' + @oldName
UPDATE [ION_Data].[dbo].[Source]
SET Name = 'QUERYSERVER.' + @newName
WHERE Name = 'QUERYSERVER.' + @oldName
DECLARE @Command_ION_Data nvarchar (500)
IF (CAST((SELECT [Value] FROM [ION_Data].[dbo].[Registry] WHERE Name = 'Revision') AS DECIMAL) > 5.1)
BEGIN
SET @Command_ION_Data = 'UPDATE [ION_Data].[dbo].[Source] SET DisplayName = ''LOGINSERTER.' + @newName + ''''
+ ' WHERE DisplayName = ''LOGINSERTER.' + @oldName + '''' + ';
UPDATE [ION_Data].[dbo].[Source] SET DisplayName = ''QUERYSERVER.' + @newName + ''''
+ ' WHERE DisplayName = ''QUERYSERVER.' + @oldName + ''';'
EXEC (@Command_ION_Data)
END
-- update Machine and Gate tables
Update [ION_Network].[dbo].[Machine]
SET name = @newName
where name = @oldName
Update [ION_Network].[dbo].[Gate]
SET name = @newName
where type ='Computer'
-- update CFG_ITemValue table
UPDATE [ION_Network].[dbo].[CFG_ItemValue]
SET Value = 'http://' + @newName + '/ionreportdataservice/ReportDataService.asmx'
WHERE Value = 'http://' + @oldName + '/ionreportdataservice/ReportDataService.asmx'
-- update IRM_DeviceInfo table
UPDATE [ION_Network].[dbo].[IRM_DeviceInfo]
SET Name = 'LOGINSERTER.' + @newName
WHERE Name = 'LOGINSERTER.' + @oldName
UPDATE [ION_Network].[dbo].[IRM_DeviceInfo]
SET Name = 'QUERYSERVER.' + @newName
WHERE Name = 'QUERYSERVER.' + @oldName
-- delete Softwarenode table
-- Network router service will repopulate when it starts.
DELETE FROM [ION_Network].[dbo].[SoftwareNode]
-- update SRC_Source
UPDATE [ION_Network].[dbo].[SRC_Source]
SET Name = 'LOGINSERTER.' + @newName
WHERE Name = 'LOGINSERTER.' + @oldName
UPDATE [ION_Network].[dbo].[SRC_Source]
SET Name = 'QUERYSERVER.' + @newName
WHERE Name = 'QUERYSERVER.' + @oldName
DECLARE @Command_ION_Network nvarchar (500)
IF (CAST((SELECT [Value] FROM [ION_Network].[dbo].[Registry] WHERE Name = 'Revision') AS DECIMAL) > 13.1)
BEGIN
SET @Command_ION_Network = 'UPDATE [ION_Network].[dbo].[SRC_Source] SET DisplayName = ''LOGINSERTER.' + @newName + ''''
+ ' WHERE DisplayName = ''LOGINSERTER.' + @oldName + '''' + ';
UPDATE [ION_Network].[dbo].[SRC_Source] SET DisplayName = ''QUERYSERVER.' + @newName + ''''
+ ' WHERE DisplayName = ''QUERYSERVER.' + @oldName + ''';'
EXEC (@Command_ION_Network)
END
-- IAS_MeasurementAddress
DELETE FROM [ION_Network].[dbo].[IAS_MeasurementAddress] WHERE Segment2 like 'VIP%'
AND ISManual = 0
UPDATE [ApplicationModules].[Inventory].[ApplicationServer]
SET Name = @newName
WHERE Name = @oldName
-- Event Watcher tables
UPDATE [ION_Network].[dbo].[EVT_SourceGroupMap]
SET SourceName = 'LOGINSERTER.' + @newName
WHERE SourceName = 'LOGINSERTER.' + @oldName
If you are attempting to upgrade, migrate, or install, and you successfully updated the server name in the ION databases so that you now only have one. Then you must run a reconfigure before attempting to continue commissioning the system.