Azure Searching On File Attachments

OZDBA 1 Reputation point
2020-11-12T22:59:38.31+00:00

I need to convert some Oracle databases to MSSQL AZURE.

We seem to have most things working except the ability to search on file attachments

Oracle lets us index a column in a table that uses a filepath link.

I got excited in MSSQL when a column in a table can be added using:

[File] VARBINARY(MAX) FILESTREAM
and then an index can be setup so files can then be searched.

I managed to get this working with the Oracle table we had used with this special column.

Unfortunately my optimism was broken when FILESTREAM was not supported on AZURE.

I'm trying to use the same oracle table with this extra column & data & I am hoping if there was a way i could still achieve this on AZURE.

Any ideas if we could still achieve this by searching on the same table?

Azure Database Migration service
Azure SQL Database

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Cris Zhan-MSFT 6,611 Reputation points
    2020-11-13T07:25:25.993+00:00

    Hi @OZDBA ,

    Yes..Azure SQL DB and Azure SQL Managed Instance currently doesn’t support FileStream and FileTables.
    For workloads dependent on these features, you can opt for SQL Servers running on Azure VMs as your Azure target.

    Please refer to the following similar post.

    1. varbinary(max) (Usually varbinary(max) is not available for files/docs larger than 1MB)
    2. Store content of a file in Azure Blob storage and keep paths/urls in table
      https://stackoverflow.com/questions/36688593/enable-and-configure-filestream-in-azure-sql-db

    If the answer is helpful, please click "Accept Answer" and upvote it.
    What can I do if my transaction log is full?--- Hot issues November
    How to convert Profiler trace into a SQL Server table -- Hot issues November

  2. OZDBA 1 Reputation point
    2020-11-13T07:53:22.127+00:00

    Hi,

    unfortunately we cannot use Azure VMs. We have got Azure SQL Db