Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
sa28-00 [2019/10/18 10:41] – Justin Willey | sa28-00 [2021/11/26 12:45] (current) – Michael Scott | ||
---|---|---|---|
Line 13: | Line 13: | ||
- 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 the designated folder is backed up, preferably by some kind of live sync arrangement. | - The most important bit – ensure that the designated folder is backed up, preferably by some kind of live sync arrangement. | ||
- | - 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. | + | - 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. |
- Set params.BlobExternalRootFolder to the path to the above folder - for example:< | - Set params.BlobExternalRootFolder to the path to the above folder - for example:< | ||
- Set params.BlobExternalStorage to 1 ie:< | - Set params.BlobExternalStorage to 1 ie:< | ||
Line 30: | Line 30: | ||
====Moving Blobs Back Into The Database==== | ====Moving Blobs Back Into The Database==== | ||
- | 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. | + | 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==== | ====BlobRelocate Job==== | ||
Line 72: | Line 72: | ||
==== 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 " | + | |
- | RESULT (" | + | |
- | BEGIN | + | |
- | DECLARE " | + | |
- | DECLARE " | + | |
- | -- create temp table to hold errors | + | |
- | DECLARE LOCAL TEMPORARY TABLE BlobStoreCheckIssue(" | + | |
- | " | + | |
- | -- regularise Standard and Alternative locations | + | |
- | SET " | + | |
- | IF " | + | |
- | -- @AlternativeLocation now holds the actual location of the files we want to check | + | |
- | IF right(" | + | |
- | IF right(" | + | |
- | -- loop through external blobs | + | |
- | FOR BlobLoop as BlobCursor NO SCROLL CURSOR | + | |
- | FOR select " | + | |
- | from BlobStore where " | + | |
- | 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 " | + | |
- | where " | + | |
- | else update BlobStoreCheckIssue set " | + | |
- | where " | + | |
- | end if; | + | |
- | END FOR; | + | |
- | select " | + | |
- | when " | + | |
- | when " | + | |
- | as " | + | |
- | END; | + | |
- | </ | + | |
- | + | ||
- | The validation | + | |
< | < | ||
Line 136: | 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> | ||
Line 172: | 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: |