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
Next revisionBoth sides next revision
sa28-00 [2017/05/30 10:44] – [Configuration] Justin Willeysa28-00 [2019/10/18 10:49] – [Moving Blobs Back Into The Database] Justin Willey
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// +  - 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>
  
  
-From this point every blob that is created or edited will be saved externally. The [[sa28-00#blobrelocate_job|BlobRelocate.xml]] 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#blobrelocate_job|BlobRelocate.xml]] job can be used to shift existing blobs out incrementally.
  
 <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> <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>
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====
- 
 <sxh xml> <sxh xml>
 <?xml version="1.0"?> <?xml version="1.0"?>
Line 71: Line 70:
 </Job> </Job>
 </sxh> </sxh>
- 
 ==== Validating the External Storage ==== ==== Validating the External Storage ====
  
Line 83: Line 81:
   DECLARE "@StandardLocation" long varchar;   DECLARE "@StandardLocation" long varchar;
   -- create temp table to hold errors   -- 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;+  DECLARE LOCAL TEMPORARY TABLE BlobStoreCheckIssue("Class" char(1),"ID" char(20), 
 +       "ExternalFilePath" long varchar,"Issue" char(10)) NOT TRANSACTIONAL;
   -- regularise Standard and Alternative locations   -- regularise Standard and Alternative locations
   SET "@StandardLocation" = (SELECT "BlobExternalRootFolder" from params);   SET "@StandardLocation" = (SELECT "BlobExternalRootFolder" from params);
Line 92: Line 91:
   -- loop through external blobs   -- loop through external blobs
     FOR BlobLoop as BlobCursor NO SCROLL CURSOR      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 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           FOR READ ONLY
       DO        DO 
Line 116: Line 116:
         set "@Folder" = left(BExtPath,locate(BExtPath,'\',-1)-1);         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')           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 +           then update BlobStoreCheckIssue set "Issue" = 3  
-           else update BlobStoreCheckIssue set "Issue" = 4 where "Class" = "BClass" and "ID" = BID; // file NOT present       +                  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 if;
     END FOR;     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;+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; END;
 </sxh> </sxh>
Line 139: Line 144:
 BEGIN BEGIN
  DECLARE @StandardLocation long varchar;  DECLARE @StandardLocation long varchar;
- DECLARE LOCAL TEMPORARY TABLE BlobFiles("ID" bigint default autoincrement,"Location" char(1),FilePath long nvarchar ,FileSize unsigned bigint ,Created timestamp with time zone , Modified timestamp with time zone ,Accessed timestamp with time zone, PRIMARY KEY(ID)) NOT TRANSACTIONAL;+ DECLARE LOCAL TEMPORARY TABLE BlobFiles("ID" bigint default autoincrement,"Location" char(1),FilePath long nvarchar ,FileSize unsigned bigint , 
 +            Created timestamp with time zone , Modified timestamp with time zone ,Accessed timestamp with time zone, PRIMARY KEY(ID)) NOT TRANSACTIONAL;
     -- regularise Standard and Alternative locations     -- regularise Standard and Alternative locations
     SET "@StandardLocation" = (SELECT "BlobExternalRootFolder" from params);     SET "@StandardLocation" = (SELECT "BlobExternalRootFolder" from params);
Line 151: Line 157:
     -- 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 164: Line 172:
 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|
Line 171: Line 179:
 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. 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.
      
 +
  • sa28-00.txt
  • Last modified: 2021/11/26 12:45
  • by Michael Scott