In the case of a side-by-side migration with EPM, preferred servers are not migrated automatically and must be done manually.
If you have many preferred servers, I have created scripts to handle this via the database (scheduling remains to be done).
The scripts will display the SQL queries to execute for the new EPM database, no direct modifications will be made.
- The first script exports the preferred servers.
#-------------------------- SQL connector ----------------------------------
#Old LDMS
$dataSource = "OldSQL"
$user = "SQLUser"
$PassSQL = 'SQLPassword'
$database = "LDMS17"
$connectionString = "Server=$dataSource;uid=$user; pwd=$PassSQL;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
#------------------------ Query -----------------------------------------------------
$query = "SELECT * FROM [$database].[dbo].[PreferredServer]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tablePS = new-object System.Data.DataTable
$tablePS.Load($result)
foreach ($elementPS in $tablePS) {
$PreferredServer_Idn_PS = $elementPS.PreferredServer_Idn
$ServerName_PS = $elementPS.ServerName
$Description_PS = $elementPS.Description
$Username_PS = $elementPS.Username
$Password_PS = $elementPS.Password
$WriteUsername_PS = $elementPS.WriteUsername
$WritePassword_PS = $elementPS.WritePassword
$ServerName_PS = ''''+$ServerName_PS+''''
$Description_PS = ''''+$Description_PS+''''
$Username_PS = ''''+$Username_PS+''''
$Password_PS = ''''+$Password_PS+''''
$WriteUsername_PS = ''''+$WriteUsername_PS+''''
$WritePassword_PS = ''''+$WritePassword_PS+''''
Write-host "INSERT INTO dbo.PreferredServer (ServerName,Description,Username,Password,WriteUsername,WritePassword) VALUES ($ServerName_PS,$Description_PS,$Username_PS,$Password_PS,$WriteUsername_PS,$WritePassword_PS);"
}
- The second script adds the replicators.
#-------------------------- SQL connector ----------------------------------
#Old LDMS
$dataSource = "OldSQL"
$user = "SQLUser"
$PassSQL = 'SQLPassword'
$database = "LDMS17"
$connectionString = "Server=$dataSource;uid=$user; pwd=$PassSQL;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
#--------------------- New LDMS ---------------------
$dataSource = "NewSQL"
$user = "SQLUser"
$PassSQL = 'SQLPassword'
$database = "LDMS21"
$connectionString = "Server=$dataSource;uid=$user; pwd=$PassSQL;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
#------------------------ Query -----------------------------------------------------
$query = "SELECT * FROM [$database].[dbo].[PreferredServer]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tablePS = new-object System.Data.DataTable
$tablePS.Load($result)
$query = "SELECT * FROM [$database].[dbo].[Computer]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tableORDI2 = new-object System.Data.DataTable
$tableORDI2.Load($result)
foreach ($elementPS in $tablePS) {
$PreferredServer_Idn_PS = $elementPS.PreferredServer_Idn
$ServerName_PS = $elementPS.ServerName
$Description_PS = $elementPS.Description
$Username_PS = $elementPS.Username
$Password_PS = $elementPS.Password
$WriteUsername_PS = $elementPS.WriteUsername
$WritePassword_PS = $elementPS.WritePassword
if ($ServerName_PS -like "*.*") {
$temp = $ServerName_PS.split(".")
$ServerName_PS = $Temp[0]
}
foreach ($elementORDI2 in $tableORDI2) {
$DeviceNameOrdi2 = $elementORDI2.DeviceName
$Computer_IdnOrdi2 = $elementORDI2.Computer_Idn
if ($DeviceNameOrdi2 -eq $ServerName_PS) {
$Computer_IdnOrdi2 = ''''+$Computer_IdnOrdi2+''''
write-host "UPDATE dbo.PreferredServer SET Replicator_Idn = $Computer_IdnOrdi2 WHERE PreferredServer_Idn = $PreferredServer_Idn_PS;"
}
}
}
- The third script adds the IP limits.
#--------------------- Old LDMS ---------------------
$dataSource = "OldSQL"
$user = "SQLUser"
$PassSQL = 'SQLPassword'
$database = "LDMS17"
$connectionString = "Server=$dataSource;uid=$user; pwd=$PassSQL;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection. Open()
$query = "SELECT * FROM [$database].[dbo].[PreferredServerIPLimit]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tableIP = new-object System.Data.DataTable
$tableIP.Load($result)
$query = "SELECT * FROM [$database].[dbo].[PreferredServer]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tablePS = new-object System.Data.DataTable
$tablePS.Load($result)
#--------------------- New LDMS ---------------------
$dataSource = "NewSQL"
$user = "SQLUser"
$PassSQL = 'SQLPassword'
$database = "LDMS21"
$connectionString = "Server=$dataSource;uid=$user; pwd=$PassSQL;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$query = "SELECT * FROM [$database].[dbo].[PreferredServer]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tablePS2 = new-object System.Data.DataTable
$tablePS2.Load($result)
$query = "SELECT * FROM [$database].[dbo].[PreferredServerIPLimit]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tableIP2 = new-object System.Data.DataTable
$tableIP2.Load($result)
foreach ($elementIP in $tableIP) {
$PreferredServer_Idn_IP = $elementIP.PreferredServer_Idn
$StartingIPAddress = $elementIP.StartingIPAddress
$EndingIPAddress = $elementIP.EndingIPAddress
foreach ($elementPS in $tablePS) {
$PreferredServer_Idn_PS = $elementPS.PreferredServer_Idn
$ServerName = $elementPS.ServerName
If ($PreferredServer_Idn_PS -eq $PreferredServer_Idn_IP) {
foreach ($elementPS2 in $tablePS2) {
$PreferredServer_Idn_PS2 = $elementPS2.PreferredServer_Idn
$ServerName2 = $elementPS2.ServerName
if($ServerName -eq $ServerName2) {
$dejaexistant = 0
foreach ($elementIP2 in $tableIP2) {
$PreferredServer_Idn_IP2 = $elementIP2.PreferredServer_Idn
$StartingIPAddress2 = $elementIP2.StartingIPAddress
$EndingIPAddress2 = $elementIP2.EndingIPAddress
if (($PreferredServer_Idn_IP2 -eq $PreferredServer_Idn_PS2) -and ($StartingIPAddress2 -eq $StartingIPAddress) -and ($EndingIPAddress2 -eq $EndingIPAddress)) { $dejaexistant = 1 }
}
if ($dejaexistant -eq 0) {
$PreferredServer_Idn_PS2 = ''''+$PreferredServer_Idn_PS2+''''
$StartingIPAddress = ''''+$StartingIPAddress+''''
$EndingIPAddress = ''''+$EndingIPAddress+''''
write-host "INSERT INTO dbo.PreferredServerIPLimit (PreferredServer_Idn,StartingIPAddress,EndingIPAddress) VALUES ($PreferredServer_Idn_PS2,$StartingIPAddress,$EndingIPAddress);"
}
}
}
}
}
}
- The fourth script adds the sources.
#--------------------- Old LDMS ---------------------
$dataSource = "OldSQL"
$user = "SQLUser"
$PassSQL = 'SQLPassword'
$database = "LDMS17"
$connectionString = "Server=$dataSource;uid=$user; pwd=$PassSQL;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$query = "SELECT * FROM [$database].[dbo].[FileReplSourcePath]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tableSRC = new-object System.Data.DataTable
$tableSRC.Load($result)
$query = "SELECT * FROM [$database].[dbo].[PreferredServer]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tablePS = new-object System.Data.DataTable
$tablePS.Load($result)
$query = "SELECT * FROM [$database].[dbo].[FileReplJob]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tableJob = new-object System.Data.DataTable
$tableJob.Load($result)
#--------------------- New LDMS ---------------------
$dataSource = "NewSQL"
$user = "SQLUser"
$PassSQL = 'SQLPassword'
$database = "LDMS21"
$connectionString = "Server=$dataSource;uid=$user; pwd=$PassSQL;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$query = "SELECT * FROM [$database].[dbo].[FileReplSourcePath]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tableSRC2 = new-object System.Data.DataTable
$tableSRC2.Load($result)
$query = "SELECT * FROM [$database].[dbo].[PreferredServer]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tablePS2 = new-object System.Data.DataTable
$tablePS2.Load($result)
foreach ($elementJob in $tableJob) {
$FileReplSourcePath_Idn_Job = $elementJob.FileReplSourcePath_Idn
$PreferredServer_Idn_Job = $elementJob.PreferredServer_Idn
$Nomsource = ""
foreach ($elementSRC in $tableSRC) {
$FileReplSourcePath_Idn_SRC = $elementSRC.FileReplSourcePath_Idn
$Name_SRC = $elementSRC.Name
If ($FileReplSourcePath_Idn_Job -eq $FileReplSourcePath_Idn_SRC) { $Nomsource = $Name_SRC }
}
$NomPF = ""
foreach ($elementPS in $tablePS) {
$PreferredServer_Idn_PS = $elementPS.PreferredServer_Idn
$ServerName_PS = $elementPS.ServerName
If ($PreferredServer_Idn_Job -eq $PreferredServer_Idn_PS) { $NomPF = $ServerName_PS }
}
$PFID2 = ""
foreach ($elementPS2 in $tablePS2) {
$PreferredServer_Idn_PS2 = $elementPS2.PreferredServer_Idn
$ServerName_PS2 = $elementPS2.ServerName
If ($NomPF -eq $ServerName_PS2) { $PFID2 = $PreferredServer_Idn_PS2 }
}
$SRCID2 = ""
foreach ($elementSRC2 in $tableSRC2) {
$FileReplSourcePath_Idn_SRC2 = $elementSRC2.FileReplSourcePath_Idn
$Name_SRC2 = $elementSRC2.Name
If ($Name_SRC2 -eq $Nomsource) { $SRCID2 = $FileReplSourcePath_Idn_SRC2 }
}
$SRCID2 = ''''+$SRCID2+''''
$PFID2 = ''''+$PFID2+''''
write-host "INSERT INTO dbo.FileReplJob (FileReplSourcePath_Idn,PreferredServer_Idn) VALUES ($SRCID2,$PFID2);"
}
