sa28-00

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision		 Previous revision
sa28-00 [2019/10/18 10:39] – Justin Willeysa28-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:<code>update params set BlobExternalRootFolder = 'C:\\IQXDocs';</code> //note the doubled-up slashes in the file path// Do NOT use spaces in the folder name.   - Set params.BlobExternalRootFolder to the path to the above folder - for example:<code>update params set BlobExternalRootFolder = 'C:\\IQXDocs';</code> //note the doubled-up slashes in the file path// Do NOT use spaces in the folder name.
   - Set params.BlobExternalStorage to 1 ie:<code>update params set BlobExternalStorage = 1;</code>   - Set params.BlobExternalStorage to 1 ie:<code>update params set BlobExternalStorage = 1;</code>
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 below (which will only work with SQL Anywhere v12 and later) checks that each external file referred to in the BlobStore table exists and can be accessed by the database server. It does not identify "orphan blobs" ie files that exist in the file structure but which are not listed in the BlobStore. +Once the blobs are stored outside the database, the normal database validation cannot check that everything is present and correct. The stored procedure ValidateExternalBlobStoreFilesExist checks that each external file referred to in the BlobStore table exists and can be accessed by the database server. It does not identify "orphan blobs" ie files that exist in the file structure but which are not listed in the BlobStore. It is run by SELECTing from the stored procedure:
- +
-<sxh SQL> +
-CREATE PROCEDURE "pears"."ValidateExternalBlobStoreFilesExist"(IN "@AlternativeLocation" long varchar DEFAULT NULL) +
-  RESULT ("Class" char(1),"ID" char(20),"ExternalFilePath" long varchar, "Issue" SmallInt, "Description" char(250)) +
-BEGIN +
-  DECLARE "@Folder" long varchar; +
-  DECLARE "@StandardLocation" long varchar; +
-  -- create temp table to hold errors +
-  DECLARE LOCAL TEMPORARY TABLE BlobStoreCheckIssue("Class" char(1),"ID" char(20),"ExternalFilePath" long varchar,"Issue" char(10)) NOT TRANSACTIONAL; +
-  -- regularise Standard and Alternative locations +
-  SET "@StandardLocation" = (SELECT "BlobExternalRootFolder" from params); +
-  IF "@AlternativeLocation" is NULL THEN SET "@AlternativeLocation" = "@StandardLocation" END IF;  +
-  -- @AlternativeLocation now holds the actual location of the files we want to check +
-  IF right("@StandardLocation",1) != '\' THEN SET "@StandardLocation" = string("@StandardLocation",'\') END IF; +
-  IF right("@AlternativeLocation",1) != '\' THEN SET "@AlternativeLocation" = string("@AlternativeLocation",'\') END IF; +
-  -- loop through external blobs +
-    FOR BlobLoop as BlobCursor NO SCROLL CURSOR  +
-      FOR select "Class" as BClass, "ID" as BID, replace("ExternalFilePath","@StandardLocation","@AlternativeLocation") as BExtPath from BlobStore where "ExternalFilePath" is not null order by "Class", "ID" +
-          FOR READ ONLY +
-      DO  +
-        if (select byte_substr(xp_read_file(BExtPath,1),0,1)) is NULL // ie error reading file +
-            then insert into BlobStoreCheckIssue("Class","ID","ExternalFilePath","Issue") values (BClass, BID, BExtPath, 1) end if; +
-    END FOR; +
-  -- recheck errors in case files were in use +
-    FOR BlobLoop2 as BlobCursor2 NO SCROLL CURSOR  +
-      FOR select "Class" as BClass, "ID" as BID, "ExternalFilePath" as BExtPath from BlobStoreCheckIssue where Issue = 1 order by "Class", "ID" +
-          FOR READ ONLY +
-      DO  +
-        if (select byte_substr(xp_read_file(BExtPath,1),0,1)) is NULL // ie error reading file +
-            then update BlobStoreCheckIssue set "Issue" = 2 where "Class" = "BClass" and "ID" = BID // still a problem +
-            else update BlobStoreCheckIssue set "Issue" = 0 where "Class" = "BClass" and "ID" = BID // now OK +
-        end if; +
-    END FOR; +
-  -- check existence of remaining issues   +
-    FOR BlobLoop3 as BlobCursor3 NO SCROLL CURSOR  +
-      FOR select "Class" as BClass, "ID" as BID, "ExternalFilePath" as BExtPath from BlobStoreCheckIssue where Issue = 2 order by "Class", "ID" +
-          FOR READ ONLY +
-      DO  +
-        set "@Folder" = null; +
-        set "@Folder" = left(BExtPath,locate(BExtPath,'\',-1)-1); +
-        if exists (select * from sp_list_directory("@Folder",1) where file_path=BExtPath and file_type ='F')   +
-           then update BlobStoreCheckIssue set "Issue" = 3 where "Class" = "BClass" and "ID" = BID // file present in folder - must be locked or insufficient rights to read +
-           else update BlobStoreCheckIssue set "Issue" = 4 where "Class" = "BClass" and "ID" = BID; // file NOT present        +
-        end if; +
-    END FOR; +
-select "Class", "ID", "ExternalFilePath", "Issue", case when "Issue" = 3 then 'File present but not accessible' when "Issue" = 4 then 'File missing' end as "Description" from BlobStoreCheckIssue; +
-END; +
-</sxh> +
- +
-The validation is run by SELECTing from the stored procedure:+
 <code>select * from pears.ValidateExternalBlobStoreFilesExist();</code> <code>select * from pears.ValidateExternalBlobStoreFilesExist();</code>
  
Line 129: Line 79:
 |O|TI01FQSS161120130006|i:\iqxdocs\2013\11-16\OTI01FQSS161120130006.dat|4|File missing| |O|TI01FQSS161120130006|i:\iqxdocs\2013\11-16\OTI01FQSS161120130006.dat|4|File missing|
  
-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: <note warning>Note this may need updating for recent versions of SQLanywhere</note>
  
 <sxh SQL> <sxh SQL>
Line 150: Line 100:
     -- Find the files in the file system     -- Find the files in the file system
  INSERT INTO BlobFiles("Location", "FilePath" ,"FileSize" ,"Created","Modified" ,"Accessed")   INSERT INTO BlobFiles("Location", "FilePath" ,"FileSize" ,"Created","Modified" ,"Accessed") 
- SELECT 'D',file_path,file_size,create_date_time,modified_date_time,access_date_time from sp_list_directory(@AlternativeLocation,3) where file_type = 'F';+ SELECT 'D',file_path,file_size,create_date_time,modified_date_time,access_date_time  
 +                   from sp_list_directory(@AlternativeLocation,3) where file_type = 'F';
  CREATE INDEX A ON BlobFiles("Location",FilePath );  CREATE INDEX A ON BlobFiles("Location",FilePath );
     -- select those files only listed in the file system     -- select those files only listed in the file system
  SELECT FilePath ,FileSize/1024 ,"Created", "Modified" ,"Accessed" FROM BlobFiles join (  SELECT FilePath ,FileSize/1024 ,"Created", "Modified" ,"Accessed" FROM BlobFiles join (
-         (select max("ID") as "ID" from BlobFiles  where Location='D' group by "FilePath" having count(*) =1 )) as MissingBlobs on BlobFiles.ID = MissingBlobs.ID order by FilePath; +         (select max("ID") as "ID" from BlobFiles  where Location='D' group by "FilePath" having count(*) =1 )) 
 +            as MissingBlobs on BlobFiles.ID = MissingBlobs.ID order by FilePath; 
 END; END;
 </sxh> </sxh>
Line 163: Line 115:
 which gives a result like: which gives a result like:
  
-^FilePath^FileSizeInKB^Created^Modified^Accessed^+^FilePath^ FileSizeInKB^ Created^ Modified^ Accessed^
 |i:\\iqxdocs\\2003\\06-19\\OTIMS5212151906200300.dat|89|2013-11-15 23:04:47.000+00:00|2013-11-15 23:04:47.000+00:00|2013-11-15 23:04:47.000+00:00| |i:\\iqxdocs\\2003\\06-19\\OTIMS5212151906200300.dat|89|2013-11-15 23:04:47.000+00:00|2013-11-15 23:04:47.000+00:00|2013-11-15 23:04:47.000+00:00|
 |i:\\iqxdocs\\2013\\08-20\\OTI1JAASS200820130002.dat|9|2013-08-20 18:54:10.000+00:00|2013-08-20 18:54:10.000+00:00|2013-08-20 18:54:10.000+00:00| |i:\\iqxdocs\\2013\\08-20\\OTI1JAASS200820130002.dat|9|2013-08-20 18:54:10.000+00:00|2013-08-20 18:54:10.000+00:00|2013-08-20 18:54:10.000+00:00|
  • sa28-00.1571395182.txt.gz
  • Last modified: 2019/10/18 10:39
  • by Justin Willey