Using SQL Server to store PersisTables

The basic version of PersisTables uses the file system as its persistence store. Nothing wrong with that for ordinary users who just want to save and share their Excel  data, however for those who worry about security and data segregation it provides limited control. The best that can be offered is for Arenas to be set up in areas where access can be controlled through AD groups and users assigned to appropriate groups for their needs.

With the premium version of PersisTables we now support direct access to SQL Server. PersisTables data can be written to and read back from a SQL Server database with the same level of functionality as the file based system. On top of this it is possible to utilise the security package to allow administrators and Genre owners to assign user permissions to  read and write data by Genre.

A database can be set up as an Arena using the SQLEndPoint driver. There are three Protocol variants available. The SQLReadTable allows data to be read from an existing database without change to the database, the SQLTable allows data to be read and written to an existing database while the SQLPersisTables protocol gives the full PersisTables experience. Note you cannot currently mix and match the protocols on a single database by setting up multiple Arenas as the Arena name must match that of the database.

With the SQLReadtable and SQLTable protocols, each Table is a Genre and subsets of data can be selected by specifying keys to the data as Categories. The SQLTable protocol allows data to be written to an existing database, however this can be a dangerous process as it will replace all data within a Table which matches the Categories specified. Under specifying Categories will result in loss of data and we only recommend using this protocol for tightly controlled applications. Neither of these protocols support Editions and the Persistence Viewer has limited capability.

The SQLPersisTables protocol requires the addition of 2 Tables and a number of stored procedures. A database can be set up dedicated to serving as a PersisTables database or the Tables can be added to an existing database. The security package requires the addition of another Table and some user groups plus supporting procedures.

What does this mean? Any hierarchical data can be stored directly into the SQLPersisTables database without a prior schema definition process. If control is required on data formats, this is implemented through the Data Dictionary which allows dynamic schema validation to be applied at the Genre level.