Splitting out the Transaction Log and Database Files in ION_DATA Database

Consulter notre FAQ

Issue
It's a common practice to split out the mdf and the ldf files associated with the ION_Data database to improve performance and increase reliability

Cause
The database files and the log files have a different I/O patterns because transaction logs are typically sequential writes while data files are mostly random reads and writes. Separating the mdf and the ldf files on different physical drives will reduce I/O contention and hence improve performance. Moreover, placing the files on different locations but on the same physical drive will exhibit no performance gain. It should also be noted that the PME installer only prompts for a single location where both the ldf and mdf files will be deployed initially, so the steps below would be taken post install.

Product Line

Power Monitoring Expert 7.2.x

Power Monitoring Expert 8.x

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 Management Studio(SSMS)

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.*

This article discusses the two methods that can be utilized to move and separate the database files. Both of these methods will achieve the same end result if the following steps are correctly executed:

1) The detach/reattach method:

Stop all the ION services from the services ( Stopping the ION Network Router service first will stop several other services at once)
Launch the Microsoft SQL Server management Studio and connect to the Sql server
Expand the databases tree, right click on the ION_Data database and then detach database as show below

Detach method

Move ION_Data.MDF and the ION_Data.LDF files to their prospective new location. It's advisable to copy and paste the files instead of cutting and pasting. Once the files have been successfully transferred, you can delete the files from the old location.
Attach the MDF file as shown below and then change the current file path of the LDF file to match your new location.

Attach databases

Execute the following SQL script to verify the new location.

SQL cmd 1

Restart the ION services and verify that PME's components are functioning

2) The online/offline method

Stop all the ION services from the services ( Stopping the ION Network Router service first will stop several other services at once)
Launch the Microsoft SQL Server management Studio and connect to the Sql server
Set the ION_DATA database offline by running the following SQL script

SQL cmd 2

Move ION_Data.MDF and the ION_Data.LDF files to their prospective new location
Execute the following script be careful with the NAME as it is case sensitive; ION_Data_Data for the mdf file and ION_Data_Log for the LDF file. The FILENAME should correspond to your new location path.

SQL cmd 3