This is the third post in the series about system-versioned ledger tables, a new feature introduced in Azure SQL Database. You can read Part 1 and Part 2 if you haven’t already.
Every choice we make is a trade-off. New features have limitations, and ledger tables are no exception.
Some of these limitations are perfectly sensible. For example, the whole point of ledger tables is to ensure that we can provide tamper evidence. This necessarily means you can’t turn it off once it’s enabled, unless you drop the database entirely — this is just one scenario where a full defence-in-depth strategy is required.
Other limitations would fall under Michael J. Swart’s 10 Percent Rule and are common-sense.
There are also limitations which make less sense to me, but it’s version 1, and I presume there are technical reasons which might be overcome in later versions as more customers use this feature.
(This list is not exhaustive, and it is based on information from Microsoft Docs that was current at the time of this writing.)
- You get all the limitations of temporal tables on updatable ledger tables, like requiring a primary key, the history table cannot have any constraints, and so on.
- You can’t disable the ledger feature on a database once it’s enabled. You would have to drop the database and recreate it.
- All new tables in a ledger-enabled database are updatable by default. If you want to create an append-only table, you must specify that in your
CREATE TABLEsyntax. - The extra system-generated columns count towards your 1,024-column limit. (Why are you making tables that wide?)
- Columns cannot use
XML,SQL_VARIANT, user-defined types, orFILESTREAM. - Ledger tables cannot be a
FILETABLE. - Ledger tables cannot be renamed (or moved to a different schema).
- Full-text indexes are not supported on ledger tables.
- In-memory tables are not supported.
- Sparse column sets are not supported.
- Truncating or modifying ledger history tables is not permitted. This does not affect temporal data if you have temporal tables along with ledger tables.
- You cannot use
SWITCH IN/OUTon ledger tables. - Change tracking is not supported on ledger tables.
- It probably goes without saying, but you can’t turn off system-versioning on ledger tables.
- Your transaction cannot update more than 200 ledger tables at a time. (Why are you updating so many tables?)
- Ledger tables cannot have a non-clustered rowstore index when they have a clustered columnstore index.
- You can’t remove columns from a ledger table (a feature that is sorely missing for temporal tables).
Things that you can do (with workarounds):
- While you cannot convert existing tables to ledger tables, you can create a new table that is ledger-enabled and move the data from an existing table.
- If adding new columns to a ledger table, they must be nullable. You also can’t use
WITH VALUES, however you can change the column toNOT NULLonce it contains values. - You can use computed columns as long as they’re deterministic.
- You can change collation as long as the code page remains the same.
- You can change the length of variable length columns.
Keeping in mind that as this is an Azure SQL Database feature, there are some additional Azure-specific limitations you need to keep in mind:
- You cannot store your database digest on an Azure Storage account with LRS (locally-redundant storage). You need more redundancy.
- Long-term backups (LTR) are not supported for ledger-enabled databases.
Some of these limitations might be a deal breaker for you, so please do your research. That said, I think this is an exciting new way to ensure that your system of record database is forensically sound.
Share your thoughts in the comments.