Support
Søg i ofte stillede spørgsmål
FAQ000283732
Hvordan kan vi hjælpe dig i dag?

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:

faulting framework services host

there was no endpoint listening


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.

Udgivet til: Schneider Electric Danmark
Se mere
Produkt:
PSWSANCZZSPEZZ
Område:
Power Monitoring Expert 9.x
Power Monitoring Expert 8.0
Se mere
Produkt:
PSWSANCZZSPEZZ
Område:
Power Monitoring Expert 9.x
Power Monitoring Expert 8.0
  • Produktdokumentation
  • Download af software
  • Produktvælger
  • Udskiftning og erstatning af produkter
  • Hjælp og kontaktcenter
  • Find forhandler
  • Projektrådgivning
  • Find vores kontorer
  • Schneider Electric Community
  • Karriere
  • Virksomhedsprofil
  • Rapportér en forseelse
  • Tilgængelighed
  • Presserum
  • Investorer
  • EcoStruxure
  • Jobsøgning
  • Blog
  • Privatlivspolitik
  • Cookiemeddelelse
  • Vilkår for anvendelse
  • Rediger dine cookieindstillinger