Enable partitioning for TimeSeries collect

To enable partitioning for the TimeSeries database's collect table

1. Access Microsoft SQL Server Management Studio as described in Microsoft SQL Server Management Studio, and open the following script:

   %DNAInstallRoot%\Historical\sdk\Historical\TimeSeries\Partitioning\PAR_DAT_TimeSeries.sql

2. Modify the following fields in the PAR_DAT_TimeSeries.sql file:

   • createStartDate: Using the format YYYY-MM-DD, enter the date on which to start partitioning.

   • singleDiskLocation: Do one of the following:

     • On the standalone Historical server, enter the full path to the directory where you want to store the partitioned database files.

     • On a Cluster environment, enter the full path to the directory on the disk defined in the Clustered Shared Volume.

       Note: This must be a valid directory with read and write permissions for the DNA Apps group.

   • defaultFileGroupSize: Enter the size in gigabytes (GB) for each monthly database file you want to create.

     The minimum file size is 1GB, and the maximum 16 terabytes (TB).

3. Save the script to the following directory:

   %DNADataRoot%\Historical\sdk\Historical\TimeSeries\Partitioning

4. Start the Microsoft SQL Server Agent, if not already running:

   1. On the Object Explorer in Microsoft SQL Server Management Studio, right-click on SQL Server Agent.

   2. Select Start.

   3. On the taskbar, select Start and enter the following:

      services.msc

      The Windows Services Manager opens.

   4. Navigate to the SQL Server Agent (OASySHDB) entry and set the Startup Type to Automatic, if not already set.

      This ensures that proper partitioning operations occur.

5. Shut down the Historical service.

6. To initiate the conversion of the collect table, proceed as follows:

   5. Connect to the Historical data server where you want to configure partitioning.

   6. Run the scripts below in the following order:

      %DNADataRoot%\Historical\sdk\Historical\TimeSeries\Partitioning\PAR_DAT_TimeSeries.sql

      %DNAInstallRoot%\Historical\sdk\Historical\TimeSeries\Partitioning\PAR_RUN_TimeSeries.sql

      %DNAInstallRoot%\Historical\sdk\Historical\TimeSeries\Partitioning\PAR_JOB_TimeSeries.sql

7. If the Always On availability group is configured, run the following script on each secondary availability node:

   %DNAInstallRoot%\Historical\sdk\Historical\TimeSeries\Partitioning\PAR_JOB_TimeSeries.sql

8. Start the Historical service.

9. Optional: If you want to align the Archive service with the time boundary of partitions, execute the following stored procedure to configure Archive schedules for the collect table:

   execute archive.dbo.modifySchedule <archiveId>, <archiveDevice>, <archiveCutoff>, <delCutOff>, <archiveIsEnabled>, True

   Where the <archId>, <archDevice>, <archiveCutoff>, <delCutOff>, and <archiveIsEnabled> parameter values correspond to the existing configured values of the Archive schedule for the collect table within the TimeSeries database.

Example of a Modified PAR_DAT_TimeSeries.sql file: