Using CTE with SQL FileTables

SQL FileTables allows you to store files and documents in special tables in the SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications. For some of the use cases, you can use FileTables with CTE.

Considerations

• The CTE Agent must be installed on the same server where the FileTables reside. If the FileTables reside on your SQL server, then you should install the CTE Agent on your SQL server.

• If multiple servers access the SQL FileTables:

  • Install CTE agent on all of the servers.

  • Protect all of the FileTable folders with the same CTE policy.

• When you create a new FileTable, alter, or drop FileTables, this may require applying a new GuardPoint.

• Every FileTable has a separate FileTable Folder so you must apply separate GuardPoints for each FileTable.

• You must apply a unique GuardPoint to each VNN path.

  For example, if you configure two FileTables on an SQL Server, then the remote SQL administrator system must apply one GuardPoint to each configured VNN name.

• Guarding on a VNN name is similar to guarding a network path with CTE.

• If you want to access the FileTables from multiple remote systems, you must install CTE agent on those systems and apply the GuardPoints.

• In the scenario where all remote systems guard the file tables of a Virtual Network Name (VNN) path with CTE LDT over CIFS, and no such GuardPoints exist on the file tables server, then do not make any SQL Server backups of the file tables database during Live Data Transformation. If an SQL Server backup is made during LDT rekey, then it is possible that a file in the File Tables directory could be backed up in an incorrect state.

Advantages

• System administrator cannot see the data locally on the SQL server because no CTE Agent is installed on the SQL server.

• The data transferring between servers is also encrypted.