Overview

SQL Server supports table and index partitioning which has to be enabled for the Verba database in case of storing large amounts of data. The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally so that groups of rows are mapped into individual partitions. The table or index is treated as a single logical entity when queries or updates are performed on the data. Partitioning large tables or indexes can have the following manageability and performance benefits:

The search may run faster.
Data management policies may run faster.
Maintenance jobs may run faster.

Prerequisites

Partitioning is only available in specific versions and editions of SQL Server:

Standard Edition: version 2016 SP1 or later
Enterprise Edition: all version

When to enable partitioning

We recommend enabling database partitioning when more than 100 million conversations are expected to be stored in the database. It is best to enable partitioning during installation. Partitioning can also be enabled later, retrospectively on existing data. However, this requires careful considerations as partitioning can take a lot of time depending on the size of the database,

Enabling database table partitioning

The following script will install partitioning, and it should be executed in the Verba database: c:\Program Files\Verba\resources\db\manual-partitioning.sql

Use the SQL Server Management Studio to execute the script.

The first partition border will be the first day of the next month by default, so it will not take a long time to execute even on a large database.

DECLARE 
@first_partition VARCHAR(100) = CONVERT(VARCHAR, DATEADD(m, 1, DATEADD(m, DATEDIFF(m, 0, GETUTCDATE()), 0)), 112), 
@second_partition VARCHAR(100) = CONVERT(VARCHAR, DATEADD(m, 2, DATEADD(m, DATEDIFF(m, 0, GETUTCDATE()), 0)), 112) -- or manually: '20190201';

In the following example, the first partition border can be changed manually.

DECLARE
@first_partition VARCHAR(100) = '20190101',
@second_partition VARCHAR(100) = '20190201';

Only monthly partitions are supported, so please set up the @first_partition and @second_partition variables to point to the first day of two consecutive months.

Enabling database table partitioning for existing data

If existing data should be split to partitions too, then it is recommended to do it out of business hours, considering it can take several hours to finish. It is recommended to split one month as the first step and measure how long does it take to execute.

On average, a million record in a month takes ~30 minutes to partition.

The following script can be used to split the existing data in the past: c:\Program Files\Verba\resources\db\manual-extend-partitions-past.sql