How to migrate files between storage

This page describes a procedure to migrate files between different storage instances in S-Filer.

S-Filer Portal allows clients to use different instances of storage for the encrypted files data. It also includes functionalities to determine the default storage instance as well as granular control over which community uses which storage instance. However, the solution does not include a built-in mechanism for migrating existing files in a storage instance to another storage instance. The recommended way is to define the new storage instance, configure it as the default, make sure that no community uses the old storage instance and then let the file retention policies gradually delete the files on the old storage instance.

If the need is to quickly move all files from one storage to another without waiting for the applicable file retention policies then you can use this procedure to do so. To accomplish this task, it uses a functionality in S-Filer Portal called "File re-encryption" which is normally used when a key used to encrypt files expires before the file retention policies have deleted the file.

Warning

This functionality is not yet available in the solution. This procedure involves changing data directly in the database and therefore involves some risks for data corruption. Do not attempt without first consulting OKIOK support and make sure that the steps are executed in a non-production environment before performing them in production.

Prerequisite

1. Create a new storage instance corresponding to the place where you want to move files if it is not already done.
2. Find the ID of the storage instances. Run the following query and find the rows corresponding to the source (note the ID as SOURCE_STORAGE_INSTANCE_ID) and destination (note the ID as DESTINATION_STORAGE_INSTANCE_ID) of the move operation.

SELECT * from StorageInstance

3. Make sure that the source storage is not the default and is not used for any community otherwise files will still be uploaded in that storage.

Migrating files community by community

In this approach, not all files are migrated at once, but rather on a community per community basis.

1. List the communities where files are stored on the source storage like this:

select c.NAME as 'Community Name',
    c.RECIPIENTNODEID, 
    count(fi.fileinfoid) as 'Number of files',
    sum(fi.DOCUMENTSIZE) as 'Total size of files'
from Community c
inner join Certificate cert on cert.RecipientNodeId = c.RECIPIENTNODEID
inner join FileVersion fv on fv.RecipientCertificateId = cert.Id
inner join TransferContext tc on tc.TRANSFERCONTEXTID = fv.TRANSFERCONTEXTID
inner join FileInfo fi on fi.FILEINFOID = tc.FILEINFOID
where fi.STORAGEINSTANCEID = [SOURCE_STORAGE_INSTANCE_ID]
group by c.NAME, c.RECIPIENTNODEID
order by c.name

2. For a given community, change the creation date and expiration date of all certificates for this community:

update Certificate set
    CreationDate = '2001-01-01 00:00:00',
    ExpirationDate = '2001-01-01 00:00:00'
where RECIPIENTNODEID = [SELECTED COMMUNITY RECIPIENTNODEID];

3. Use the scheduler in the configurator to manually run the "Key Renewal" job. This will ensure that there is a new key to be used for re-encryption.
4. Then manually run the "File re-encryption" job. This job will re-encrypt all files and in doing so, will move them to the new storage.
5. This job can take a long time to process all files. To monitor its progress, you can use this query:

SELECT c.id AS certificateId, c.expirationDate AS certificateExpirationDate,
    COUNT(CASE WHEN fi.storageInstanceId = [SOURCE_STORAGE_INSTANCE_ID] THEN 1 ELSE NULL END) AS nbFilesSource,
    COUNT(CASE WHEN fi.storageInstanceId = [DESTINATION_STORAGE_INSTANCE_ID] THEN 1 ELSE NULL END) AS nbFilesDestination
FROM Certificate c
    INNER JOIN RecipientNode rn ON c.recipientNodeId = rn.nodeId
    INNER JOIN FileVersion fv ON fv.recipientCertificateId = c.id
    INNER JOIN TransferContext tc ON tc.transferContextId = fv.transferContextId
    INNER JOIN FileInfo fi ON fi.fileInfoId = tc.fileInfoId
WHERE rn.recipientCommId IS NOT NULL
    AND rn.recipientName = 'COMMUNITY_NAME_PARAM'
GROUP BY c.id, c.expirationDate;

Migrating files for inboxes

This procedure aims to address files that are not covered by the community-by-community file migration. Each user can have an inbox containing files. These files may have been uploaded directly by the user or by another user from a community shared by both users. To address this situation, we need to retrieve the "RECIPIENTNODEID" differently from the "Users" table:

select u.NAME as 'User Name',
    u.RECIPIENTNODEID, 
    count(fi.fileinfoid) as 'Number of files', 
    sum(fi.DOCUMENTSIZE) as 'Total size of files'
from Users u
inner join Certificate cert on cert.RecipientNodeId = u.RECIPIENTNODEID
inner join FileVersion fv on fv.RecipientCertificateId = cert.Id
inner join TransferContext tc on tc.TRANSFERCONTEXTID = fv.TRANSFERCONTEXTID
inner join FileInfo fi on fi.FILEINFOID = tc.FILEINFOID
where fi.STORAGEINSTANCEID = [SOURCE_STORAGE_INSTANCE_ID]
group by u.NAME, u.RECIPIENTNODEID
order by u.name;

The rest of the procedure is similar to the community-by-community file migration. You need to use the "RECIPIENTNODEID" found in the previous query in the certificate update query.

Migrating files for anonymous users

This procedure aims to address files covered by quick sends. For this use case, the "RECIPIENTNODEID" is retrieved from the "AnonymousUser" table. The SQL query to retrieve the "RECIPIENTNODEID" is similar to the other two:

select u.NAME as 'User Name',
    u.RECIPIENTNODEID, 
    count(fi.fileinfoid) as 'Number of files', 
    sum(fi.DOCUMENTSIZE) as 'Total size of files'
from AnonymousUser u
inner join Certificate cert on cert.RecipientNodeId = u.RECIPIENTNODEID
inner join FileVersion fv on fv.RecipientCertificateId = cert.Id
inner join TransferContext tc on tc.TRANSFERCONTEXTID = fv.TRANSFERCONTEXTID
inner join FileInfo fi on fi.FILEINFOID = tc.FILEINFOID
where fi.STORAGEINSTANCEID = [SOURCE_STORAGE_INSTANCE_ID]
group by u.NAME, u.RECIPIENTNODEID
order by u.name;

The rest of the procedure is similar to the community-by-community file migration. You need to use the "RECIPIENTNODEID" found in the previous query in the certificate update query.