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.
-
Caution
Accessing the FileTable without CTE may corrupt the data.
-
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.
Caution
LDT is not supported with SQL FileTables. Only use offline Data Transformation to transform the SQL data.
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.
