What copies of my database, if any, do readable secondaries make bigger?
2
votes
1
answer
72
views
Suppose that I have an Enterprise Edition database that has completely default settings and nothing but normal rowstore tables. No RCSI, no snapshot isolation and no triggers. Assume nothing that would require [row versioning](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver16#space-used-in-data-rows) .
Clearly, these assumptions mean that I will not be paying the extra 14-bytes-per-row cost of row versioning. However, readable AG secondaries run under SNAPSHOT isolation and SNAPSHOT isolation requires row versioning. So if I put my database in an Availability Group and add a readable secondary, what copies of my database (if any) will start paying the 14-bytes-per-row cost of row versioning? Does this change if I have a non-readable secondary in addition to the readable secondary? What if I fail over and fail back?
The relevant documentation, linked earlier, makes no comment on this. I plan to run experiments myself, but AGs are notoriously easy to make mistakes with and I don't want to draw incorrect conclusions.
Asked by J. Mini
(1225 rep)
Mar 17, 2025, 08:59 PM
Last activity: Mar 30, 2025, 03:17 PM
Last activity: Mar 30, 2025, 03:17 PM