TechDocs

By default, DataLog stored the attachments of posted sales within the database.

Posted attachments can also be split out and stored on the server in a file folder.

Overview

The ease of which attachments can be added to a sale has resulted in several UAAR DataLog clients taking advantage of this feature. Some of these clients are using Microsoft’s SQL Server  Express and SQL Server Management Studio Express. Unfortunately there is a 10 GB maximum database size with this version of SQL Server. Many of the attachments being added to sales are pictures/.jpg files that the appraiser has taken of the property. These files vary in size depending on the camera settings, but it is very common for each picture to be .5 MB or more. With many of the clients having thousands of sales in their database it is easy to see how the 10 GB maximum database size is being approached by some. In an effort to remedy the quickly growing database size AgWare has developed a solution.

Solution

The problem can be remedied by storing these sale attachment files in a client specified location on disk instead of storing them within the database. This will free up large amounts of space within the database while changing nothing with regard to the normal usage of the UAAR DataLog software. To the user the change in where the attachments are stored will be completely transparent. During installation the database administrator will set the parent directory on disk where the attachments will be stored.

*Note The conversion may take a few hours. It is best to do this outside normal business hours.

Enabling FILESTREAM

To enable and change FILESTREAM settings

On the Start menu go to Microsoft SQL Server 20xx, and then click SQL Server Configuration Manager.

In the SQL Server Configuration Manager snap-in, Click on SQL Server Services and locate the instance of SQL Server on which you want to enable FILESTREAM.

Right-click the instance, and then click Properties.

In the SQL Server Properties dialog box, click the FILESTREAM tab.

Select the Enable FILESTREAM for Transact-SQL access check box.

If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.

Click Apply.

In SQL Server Management Studio, click New Query to display the Query Editor.

In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level, 2  

RECONFIGURE

Click Execute.

If the query is run in MSSMS, the confirmation will display

Restart the SQL Server service.

Setting the path for attachments

Run the following query on the database. Be sure to change the database in both ALTER DATABASE statements. For the FILENAME, choose an existing folder but do not create the Filestream folder as it is automatically created when the query is run.

*Warning The FILESTREAM container is a folder managed by SQL Server. Do not add or remove files in the FILESTREAM folder manually or through other applications. If you do, this will result in backup and inconsistency errors.

ALTER DATABASE [YourDataBase]

ADD FILEGROUP AgWareFSGroup CONTAINS FILESTREAM;

GO

-- 'C:\YourAttachmentFolder\' must exist. 'Filestream' must not.

ALTER DATABASE [YourDatabase]

ADD FILE

(

    NAME = 'fsAgWareFileStream',

    FILENAME = 'C:\YourFolderForAttachments\Filestream'

)

TO FILEGROUP AgWareFSGroup;

*Note If you are on SQL Server 2022 and get the error below, please see SQL Server 2022 Issue at the end of this document.

Convert Attachments to Filestream

Use our Data Converter found at:

(Windows)https://www.agwarefiles.com/maven/tools/fileupdater-windows.zip

(Console) https://www.agwarefiles.com/maven/tools/fileupdater-console.zip

Extract the zip folder and open the DataConverter executable and enter you connection string as:

Data Source=YourServer\YourInstance;Initial Catalog=YourDatabase;Integrated Security=True;Encrypt=false;

The Convert to Filestream radio should already be selected.

You can choose to skip appraisal files, appraisal files revision, or sale attachments. 

Click Convert

There is confirmation once it has completed

SQL Server 2022 Issue.

SQL Server 2022 has a known issue. Microsoft has said that this will be fixed in CU1 for SQL 2022. The fix is also available in the RTM version of SQL 2022, but it is hidden under a traceflag, 

-T12324.

Add it as a startup parameter in SSCM and restart SQL Server:

After the conversion, open DataLog, go to Sever Setup and click on System Configuration and make sure the Sale Attachment Root Path is empty.
Once the field is empty, click File→Refresh Shared Data.

Check If Filestream is Currently Being Used

To make sure that Filestream is being used, right click on the table dbo.SaleAttachment table and choose Script Table As and select CREATE To→NewQuery Editor Window.
The output will show if filestream is being used.