Database maintenance

PME uses databases to store information such as system configuration, data logs, and system event log messages. These databases must be maintained to preserve performance, manage disk space use, and guard against data loss in case of database failure.

notice

LOSS of data

● Back up the database at regular intervals.
● Back up the database before upgrading or migrating the system.
● Back up the database before trimming it.
● Back up the database before making manual database edits.
● Verify correct database behavior after making database or system changes.

Failure to follow these instructions can result in permanent loss of data.

 

The following table shows the PME databases and the recommended database maintenance tasks for each:

Database Type of Data Maintenance Tasks*
ApplicationModules Web Applications related configuration data and system event log entries. Backup, Maintenance, Trim
ION_Data Historical power system data such as interval data logs, waveforms and alarms.

Archive, Backup, Maintenance,
Size Notification**
ION_Network Device network and other system configuration data Backup, Maintenance
ION_SystemLog Non-Web Applications related system event log entries. Maintenance, Trim

* See Database maintenance task definitions for basic task definitions.
** Size Notification is only used for systems with SQL Server Express, which has a maximum database size limit of 10 GB.

In Standalone PME systems, the database maintenance tasks are pre-configured and scheduled to run automatically by default. For Distributed Database PME systems, you need to configure the tasks and set up the schedules manually.

NOTE: It is best to automate the maintenance tasks, but you can run them manually on demand using Database Manager and Microsoft SQL Server Management Studio.

The following table shows the default database maintenance task schedules for Standalone PME systems:

Database Task Enabled Trigger Time
ApplicationModules Backup Yes Daily at 01:30
ApplicationModules Maintenance Yes Daily at 03:30
ApplicationModules Trim Yes Daily at 02:30
ION_Data Archive* No** Annually, on Jan 3 at 01:00
ION_Data Backup Yes Weekly, Fridays at 00:00
ION_Data Maintenance Yes Daily at 02:00
ION_Data Size Notification*** Yes Daily at 03:00
ION_Network Backup Yes Daily at 01:00
ION_Network Maintenance Yes Daily at 07:30
ION_SystemLog Maintenance Yes Daily at 07:05
ION_SystemLog Trim Yes Daily at 04:00

* The PME archive task does not trim the database, it only copies data to the archive.
** You need to edit the Windows user account settings before enabling the archive task. See the Note on the ION_Data archive task for more details.
*** Size Notification is only used for systems with SQL Server Express, which has a maximum database size limit of 10 GB.

For more information on the default task settings see Default maintenance task settings.

Managing database maintenance tasks for Standalone PME systems

In Standalone systems, the database maintenance tasks are pre-configured and scheduled to run automatically by default. The scheduling and execution of the tasks is done with Task Scheduler in Windows. The database interaction specific steps of the tasks are defined as Windows PowerShell scripts.

Note on the ION_Data archive task:

NOTE: The archive task for the ION_Data database is disabled by default. The Windows user account that is used to run this task must have a sysadmin server role in the SQL Server database server. The Windows user account that is used by default, IONMaintenance, does not have a sysadmin server role. To enable and run the scheduled archive task successfully, you need to add the sysadmin role to IONMaintenance, or change the user account that is used to run this task to an account with sysadmin role. See Database maintenance account requirements for more information on account requirements.

To edit the task schedule settings (enable or disable tasks, set trigger times):

NOTE: The database maintenance tasks in Task Scheduler are configured to run using the IONMaintenance Windows user account. To save any changes to the task settings in Task Scheduler, you need to enter the password for the IONMaintenance account. See Using IONMaintenance for database maintenance tasks for information on where to find the password.

  1. On the PME application server, open Task Scheduler in Windows.
  2. In the Task Scheduler Library, open the Schneider Electric > Power Monitoring Expert folder to see the configured database maintenance tasks.
  3. Edit the tasks as required:
    1. To enable or disable a task, select it and use the Actions pane in Task Scheduler.
    2. To edit task settings, double-click a task and make the desired changes in the Job Properties dialog box.
  4. (Optional) Select Enable All Tasks History in the Actions pane in Task Scheduler. This turns on event recording for the scheduled tasks, which is useful for auditing and troubleshooting.
  5. Close Task Scheduler.

To edit the task script settings (change backup and archive location, set the data to keep on trim, size notification threshold):

  1. On the PME application server, open the ...\Power Monitoring Expert\config\cfg\DbScheduledTasks\Support\Configuration.ps1 script file in a text editor.
  2. Change the values of the variables in the script file for the settings you want to change. The following settings can be customized:

    NOTE: Follow the instructions in the script file on formatting and syntax.

    SettingVariableDefault ValueComments

    Backup folder location

    $locationForBackupFiles

    ...\Power Monitoring Expert\Database\Backup

    Sets the folder to which the database backups are saved. The backup script will create the following subfolders in this location:

    ...\Data for ION_Data
    ...\Network for ION_Network
    ...\SystemLog for ION_SystemLog
    ...\Applications for ApplicationModules

    NOTE: IONMaintenance needs Read and Write permissions on this folder.

    Archive folder location

    $locationForArchiveDBFiles

    ...\Power Monitoring Expert\Database\Archive

    Sets the folder to which the database archives are saved.

    NOTE: IONMaintenance needs Read and Write permissions on this folder.

    Data to keep when trimming

    $diagnosticsDaysToKeep

    30

    Sets how many days' worth of data is left in the database after trimming.

    Database size (max)

    $maximumDatabaseSizeIn Gigabytes

    9

    Sets the maximum database size. This value is used by the size notification task to assess what percentage of database space has been used.

    The maximum size for a SQL Express database is 10GB. The maximum size in the script is set to 9GB to allow for a 1GB warning buffer before the database stops logging data.

    Database size notification limit

    $databaseSizeNotification ThresholdPercentage

    85

    Sets the threshold for when a database size notification will be issued.

  3. Save the script file changes and close the text editor.

Setting up database maintenance tasks for Distributed PME systems

In Distributed systems, the database maintenance tasks are not pre-configured. You need to set up these tasks manually. The scheduling and execution of the tasks is done with Task Scheduler in Windows. The database interaction specific steps of the tasks are defined as Windows PowerShell scripts. Setting up the database maintenance tasks includes the following high-level steps:

NOTE: The following sections describe how to set up the different database maintenance tasks, except for the archive task for ION_Data. See Setting up the ION_Data archive task for Distributed PME systems for instructions on how to set up this task.

 

Step 1: Creating a Windows user account to run the maintenance tasks. See Database maintenance account requirements for more information on the account requirements.

Step 2: Installing the Microsoft SQL Server Data-Tier Application Framework.

This requires downloading the framework installer from Microsoft or copying it from the PME installation DVD/ISO.

Step 3: Installing and configuring the Windows PowerShell script files.

This requires copying files from the PME application server to the database server.

Step 4: Setting up task schedules in Task Scheduler.

 

Step 1: Creating a Windows user account to run the maintenance tasks:

  1. On the database server, create a new Windows user as a member of the Users group, for example PMEMaintenance.

    NOTE: You will need the password for this account during the initial task setup, and later if you want to edit the tasks in Task Manager in the future.

  2. Open the Local Security Policy tool in Windows.
  3. In the Local Security Policy tool, open the policy settings list in Security Settings > Local Policies > User Rights Assignment.
  4. Add the new Windows user to the following policies: Deny log on locally and Log on as a batch job.
  5. Close the Local Security Policy tool.
  6. Open Microsoft SQL Server Management Studio.
  7. Add the new Windows user as a database Login with the following roles and mappings:

    Server Role: public
    User Mapping:

    DatabaseRole

    ApplicationModules

    db_backupoperator; db_ddladmin; Maintenance; public

    ION_Data

    db_backupoperator; db_ddladmin; Maintenance; public

    ION_Network

    db_backupoperator; db_ddladmin; Maintenance; public

    ION_SystemLog

    db_backupoperator; db_ddladmin; Maintenance; public

  8. Close Microsoft SQL Server Management Studio.

NOTE: You will need the password for this account if you want to edit the tasks in Task Manager in the future, after the initial setup.

Next, install the Microsoft SQL Server Data-Tier Application Framework.

Step 2: Installing the Microsoft SQL Server Data-Tier Application Framework:

  1. Check if the framework is already installed on the database server. To do this, find the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server\.
    If this key includes a Data-Tier Application Framework key, then the framework is installed. Continue with Step 3 - Installing and configuring the Windows PowerShell script files
    If this key does not include a Data-Tier Application Framework key, then the framework is not installed. Continue with the installation of the framework.
  2. On the database server, download the framework installer (DacFramework.msi) from Microsoft. See Resources for the framework installer download link.

    NOTE: You can also find the framework installer (DacFramework.msi) on the PME DVD/ISO under Setup\SetupSupport\database.

  3. On the database server, install DacFramework.msi.

Next, install and configure the Windows PowerShell script files.

Step 3: Installing and configuring the Windows PowerShell script files:

  1. On the database server, create a new folder. You can choose the location and folder name, for example C:\PME_Database_Maintenance.
  2. Copy the DbScheduledTasks folder from ...\Power Monitoring Expert\config\cfg\, on the PME application server, into this new folder.
  3. Open the <New Folder Path>\DbScheduledTasks\Support\DatabaseHelper.ps1 script file in a text editor.
  4. Change the values of the folder path variables in the script file to the new folder path as follows:
    1. Change the value of $customUserDirectory (line 28 in the script) to <New Folder Path>\dbScheduledTasks\Support, for example C:\PME_Database_Maintenance\dbScheduledTasks\Support. The default value is C:\PMEDBs\dbScheduledTasks\Support.
    2. Change the value of $customSystemDirectory (line 29 in the script) to <New Folder Path>, for example C:\PME_Database_Maintenance. The default value is C:\PMEDBs.
    3. (Only if you are using Windows Integrated Authentication) Change the value of $pmeUsingIntegratedAuth (line 32 in the script) to 1. The default value is 0.
  5. Save the script file changes.
  6. Open the <New Folder Path>\DbScheduledTasks\Support\Configuration.ps1 script file in a text editor.
  7. Change the value of the backup and archive folder path variables in the script file to the new folder path as follows:
    1. Change the value of $locationForBackupFiles (line 46 in the script) to <New Folder Path>\Backups\, for example C:\PME_Database_Maintenance\Backups\. The default value is ..\Database\Backup\.
    2. Change the value of $locationForArchiveDBFiles (line 54 in the script) to <New Folder Path>\Archives\, for example C:\PME_Database_Maintenance\Archives\. The default value is ..\Database\Archives\.
  8. Save the script file changes and close the text editor.

Next, set up task schedules in Task Scheduler.

Step 4: Setting up task schedules in Task Scheduler:

  1. On the database server, open Task Scheduler in Windows.
  2. (Optional) In the Task Scheduler Library, create a new folder for the PME database maintenance tasks, for example Task Scheduler Library > Power Monitoring Expert.
  3. Create scheduled database maintenance tasks:

    NOTE: For setting up the archive task for ION_Data, see Setting up the ION_Data archive task for Distributed PME systems

    Use the following information to create the tasks. Replace the variables with the specific settings shown in the task settings table below.

    Name: <task_name>
    Security options: Set the Windows user account created in Step 1 to run the task.
    Security options: Select Run whether user is logged on or not.
    Trigger: <trigger_time>
    Action: Select Start a program.
    Action: Program/script: C:\Windows\syswow64\WindowsPowerShell\v1.0\powershell.exe
    Action: Arguments: -noninteractive -nologo -file "<New Folder Path>\DbScheduledTasks\<script_name>" -DatabaseIdentifier <DB ID>

    NOTE: The "<New Folder Path>\DbScheduledTasks\<script_name>" path must be an absolute path, not a relative path.

    NOTE: Valid settings for the <task_name>, <trigger_time>, <script_name>, and <DB ID> variables are given in the Task Settings table below.

    Example: ApplicationModules backup task

    Name: [ApplicationModules] - Backup - Job
    Security options: Set the Windows user account created in Step 1 to run the task.
    Security options: Select Run whether user is logged on or not.
    Trigger: Daily at 01:30 (1:30 AM)
    Action: Select Start a program
    Action: Program/script: C:\Windows\syswow64\WindowsPowerShell\v1.0\powershell.exe
    Action: Arguments: -noninteractive -nologo -file "<New Folder Path>\DbScheduledTasks\Backup.ps1" -DatabaseIdentifier APPS

    Task Settings table:

    NOTE: The task names and trigger times shown in the table are recommendations. You can choose different names or triggers if necessary.

    TaskSettings

    ApplicationModules backup

    Task Name: [ApplicationModules] - Backup - Job

    Trigger Time: Daily at 01:30 (1:30 AM)

    Action: Arguments: Script Name: Backup.ps1, DB ID: APPS

    ApplicationModules maintenance

    Task Name: [ApplicationModules] - MAINTENANCE - Job

    Trigger Time: Daily at 03:30 (3:30 AM)

    Action: Arguments: Script Name: DatabaseMaintenance.ps1, DB ID: APPS

    ApplicationModules trim

    Task Name: [ApplicationModules] - TRIM - Job

    Trigger Time: Daily at 02:30 (2:30 AM)

    Action: Arguments: Script Name: TrimDiagnostics.ps1, DB ID: APPS

    ION_Data backup

    Task Name: [ION_Data] - BACKUP - Job

    Trigger Time: Weekly at 00:00 (12:00 AM) on Fridays

    Action: Arguments: Script Name: Backup.ps1, DB ID: ION

    ION_Data maintenance

    Task Name: [ION_Data] - MAINTENANCE - Job

    Trigger Time: Daily at 02:00 (2:00 AM)

    Action: Arguments: Script Name: DatabaseMaintenance.ps1, DB ID: ION

    ION_Network backup

    Task Name: [ION_Network] - BACKUP - Job

    Trigger Time: Daily at 01:00 (1:00 AM)

    Action: Arguments: Script Name: Backup.ps1, DB ID: NOM

    ION_Network maintenance

    Task Name: [ION_Network] - MAINTENANCE - Job

    Trigger Time: Daily at 07:30 (7:30 AM)

    Action: Arguments: Script Name: DatabaseMaintenance.ps1, DB ID: NOM

    ION_SystemLog maintenance

    Task Name: [ION_SytemLog] - MAINTENANCE - Job

    Trigger Time: Daily at 07:05 (7:05 AM)

    Action: Arguments: Script Name: DatabaseMaintenance.ps1, DB ID: SYSLOG

    ION_SystemLog trim

    Task Name: [ION_SytemLog] - TRIM - Job

    Trigger Time: Daily at 04:00 (4:00 AM)

    Action: Arguments: Script Name: TrimDiagnostics.ps1, DB ID: SYSLOG

    The completed task list should look like this:

  4. (Optional) Manually run each task to verify its correct operation.
  5. Close Task Scheduler.

To edit the task script settings (for example to change the backup and archive location or to set the amount of data to keep in the database on trim), open the Configuration.ps1 script file, as described in Step 3 and change the values of the variables.

Configurable variables in Configuration.ps1:

Setting Variable Default Value Comments

Backup folder location

$locationForBackupFiles

As defined in the script file; see Step 3-7.

Sets the folder to which the database backups are saved. The backup script will create the following subfolders in this location:

...\Data for ION_Data

...\Network for ION_Network

...\SystemLog for ION_SystemLog

...\Applications for ApplicationModules

NOTE: The Windows user account used to run the backup task needs Read and Write permissions on this folder.

Data to keep when trimming

$diagnosticsDaysToKeep

30

Sets how many days' worth of data is left in the database after trimming.