Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
sa28-00 [2015/12/21 17:11] – Justin Willey | sa28-00 [2021/11/26 12:45] (current) – Michael Scott | ||
---|---|---|---|
Line 1: | Line 1: | ||
====== Configuring External Blob (Document) Storage ====== | ====== Configuring External Blob (Document) Storage ====== | ||
+ | ====Introduction==== | ||
This is the mechanism for storing blobs outside the database. Blob (Binary Large OBject) is a generic term for documents (CVs, letters, photos, videos, spreadsheets etc) stored in the database. | This is the mechanism for storing blobs outside the database. Blob (Binary Large OBject) is a generic term for documents (CVs, letters, photos, videos, spreadsheets etc) stored in the database. | ||
- | By default these are stored inside the database. The advantage of this is that they are backup with the database and no separate arrangements are needed for looking after them. However there can be times when it is more convenient to store them separately. IQX has an alternative storage mechanism which will allow this and which is transparent to the end IQX user. All access to the blobs is still via the database engine - **not directly from IQX client machines** - so no additional folder rights or controls are needed at the client machine level. | + | By default these blobs are stored inside the database. The advantage of this is that they are backup with the database and no separate arrangements are needed for looking after them. However there can be times when it is more convenient to store them separately. IQX has an alternative storage mechanism which will allow this and which is transparent to the end IQX user. All access to the blobs is still via the database engine - **not directly from IQX client machines** - so no additional folder rights or controls are needed at the client machine level. |
<note warning> | <note warning> | ||
+ | ====Configuration==== | ||
Steps to start using External Storage | Steps to start using External Storage | ||
- Create a folder on the database machine under which the blob files will be stored. Ensure the server process has full rights to it including the ability to create subfolders. | - Create a folder on the database machine under which the blob files will be stored. Ensure the server process has full rights to it including the ability to create subfolders. | ||
- | - The most important bit – ensure that it is backed up, preferably by some kind of live sync arrangement. | + | - The most important bit – ensure that the designated folder |
- | - Set params.BlobExternalRootFolder to the path to the above folder - for example:< | + | - It is highly desirable that the designated folder is ONLY accessible by the Windows user account that the database engine is run under and the user account used for the sync / backup process. This will reduce the chance of any inadvertent editing / deleting of the blob files, and can help provide protection against ransomware attacks. |
- | - Create a trigger to delete the external blobs with the blobstore record is deleted:< | + | - Set params.BlobExternalRootFolder to the path to the above folder - for example:< |
- | ORDER 1 ON " | + | |
- | REFERENCING OLD AS old_blob | + | |
- | FOR EACH ROW | + | |
- | WHEN (old_blob.externalfilepath is not null) | + | |
- | BEGIN | + | |
- | call xp_cmdshell(' | + | |
- | END</ | + | |
- Set params.BlobExternalStorage to 1 ie:< | - Set params.BlobExternalStorage to 1 ie:< | ||
- | From this point every blob that is created or edited will be saved externally. The BlobRelocate job can be used to shift existing blobs out incrementally. | + | As users re-enter IQX, every blob that is created or edited will be saved externally. It is important therefore to get all users to exit and re login to IQX once the changes have been made. The [[sa28-00# |
- | <color red>If you ever need to access blobs in reports (e.g. candidate images) use the BlobStoreFetch database function e.g. select BlobStoreFetch(‘J’,person.personid) as mugshot from person. This function | + | <note warning>Blobstore records contain the full path to the file containing the blob. If the value of params.BlobExternalRootFolder is subsequently changed, these paths will need to be updated in the database if documents are to be found.</note> |
+ | ====Accessing blobs in SQL==== | ||
+ | To access blobs in reports or other queries, use the BlobStoreFetch() database function. This function will work for all blobs whether stored internally or externally. | ||
- | <note warning>Blobstore records contain the full path to the file containing the blob. If the value of params.BlobExternalRootFolder is subsequently changed, these paths will need to be updated in the database if documents are to be found.</note> | + | For example: to retrieve candidate images use <code>BlobStoreFetch(‘J’,person.personid)</code> |
- | To reverse the process, change the setting of BlobExternalStorage - new and edited blobs will now be stored internally. Again BlobRelocate will move the existing blobs for you. | + | ====Moving Blobs Back Into The Database==== |
- | BlobRelocate | + | To reverse the process, change the setting of BlobExternalStorage - new and edited blobs will now be stored internally. Again BlobRelocate.xml will move the existing blobs for you. |
+ | ====BlobRelocate Job==== | ||
<sxh xml> | <sxh xml> | ||
<?xml version=" | <?xml version=" | ||
Line 72: | Line 70: | ||
</ | </ | ||
</ | </ | ||
+ | ==== Validating the External Storage ==== | ||
- | === Validating the External Storage === | + | Once the blobs are stored outside the database, the normal database validation cannot check that everything is present and correct. The stored procedure |
- | + | ||
- | Once the blobs are stored outside the database, the normal database validation cannot check that everything is present and correct. The stored procedure | + | |
- | + | ||
- | <sxh SQL> | + | |
- | CREATE PROCEDURE pears.ValidateExternalBlobStoreFilesExist() | + | |
- | RESULT (" | + | |
- | BEGIN | + | |
- | DECLARE " | + | |
- | -- create temp table to hold errors | + | |
- | DECLARE LOCAL TEMPORARY TABLE BlobStoreCheckIssue(" | + | |
- | -- loop through external blobs | + | |
- | FOR BlobLoop as BlobCursor NO SCROLL CURSOR | + | |
- | FOR select " | + | |
- | FOR READ ONLY | + | |
- | DO | + | |
- | if (select byte_substr(xp_read_file(BExtPath, | + | |
- | then insert into BlobStoreCheckIssue(" | + | |
- | END FOR; | + | |
- | -- recheck errors in case files were in use | + | |
- | FOR BlobLoop2 as BlobCursor2 NO SCROLL CURSOR | + | |
- | FOR select " | + | |
- | FOR READ ONLY | + | |
- | DO | + | |
- | if (select byte_substr(xp_read_file(BExtPath, | + | |
- | then update BlobStoreCheckIssue set " | + | |
- | else update BlobStoreCheckIssue set " | + | |
- | end if; | + | |
- | END FOR; | + | |
- | -- check existence of remaining issues | + | |
- | FOR BlobLoop3 as BlobCursor3 NO SCROLL CURSOR | + | |
- | FOR select " | + | |
- | FOR READ ONLY | + | |
- | DO | + | |
- | set " | + | |
- | set " | + | |
- | if exists (select * from sp_list_directory(" | + | |
- | then update BlobStoreCheckIssue set " | + | |
- | else update BlobStoreCheckIssue set " | + | |
- | end if; | + | |
- | END FOR; | + | |
- | select " | + | |
- | END ; | + | |
- | </ | + | |
- | + | ||
- | The validation | + | |
< | < | ||
Line 125: | Line 79: | ||
|O|TI01FQSS161120130006|i: | |O|TI01FQSS161120130006|i: | ||
- | Orphaned Blobs in the file system can be identified with this stored procedure: | + | Orphaned Blobs in the file system can be identified with this stored procedure: |
<sxh SQL> | <sxh SQL> | ||
- | CREATE PROCEDURE | + | CREATE PROCEDURE " |
RESULT ( " | RESULT ( " | ||
" | " | ||
- | BEGIN | + | BEGIN |
- | DECLARE @BlobRoot | + | DECLARE @StandardLocation |
- | DECLARE LOCAL TEMPORARY TABLE BlobFiles(" | + | DECLARE LOCAL TEMPORARY TABLE BlobFiles(" |
- | SET @BlobRoot | + | |
+ | -- regularise Standard and Alternative locations | ||
+ | | ||
+ | IF " | ||
+ | -- @AlternativeLocation now holds the actual location of the files we want to check | ||
+ | IF " | ||
+ | IF " | ||
+ | -- Find the files litsed in the BlobStore | ||
INSERT INTO BlobFiles(" | INSERT INTO BlobFiles(" | ||
- | SELECT ' | + | SELECT ' |
+ | -- Find the files in the file system | ||
INSERT INTO BlobFiles(" | INSERT INTO BlobFiles(" | ||
- | SELECT ' | + | SELECT ' |
+ | from sp_list_directory(@AlternativeLocation,3) where file_type = ' | ||
CREATE INDEX A ON BlobFiles(" | CREATE INDEX A ON BlobFiles(" | ||
+ | -- select those files only listed in the file system | ||
SELECT FilePath , | SELECT FilePath , | ||
- | | + | |
- | END; | + | |
+ | END; | ||
</ | </ | ||
Line 150: | Line 115: | ||
which gives a result like: | which gives a result like: | ||
- | ^FilePath^FileSizeInKB^Created^Modified^Accessed^ | + | ^FilePath^ FileSizeInKB^ Created^ Modified^ Accessed^ |
|i: | |i: | ||
|i: | |i: | ||
+ | |||
+ | Each of these procedures can take an optional parameter of an alternative BlobStore folder, if specified, this will be compared with the contents of the BlobStore instead. This can be useful for checking that the backup of the external BlobStore is being correctly replicated. | ||
| | ||
+ |