Reply To: SQL AlwaysOn Availability Group – Readable Secondary



Access to the transaction files requires a bit of a trick because Microsoft has very strict permissions on the file. This is why we require an OS user with administrative privileges. Obviously an OS user with administrative privileges can take full control over the database. When we designed. We figured that give this prerequisite any effort to develop a solution that requires less than sysadmin privileges on the database is moot (but perhaps you can convince us otherwise).

As you may know HVR supports not log-based CDC not only using direct reading of the transaction files, but also by getting fragments of the transaction log through SQL calls. In the SQL mode we support a minimal permissions model that does not require sysadmin and not even dbo privileges. However the SQL mode is not available on the standby node of an AlwaysOn cluster (only on the primary node).

Regarding your alternative: I don’t know if the regular logs in a database that is maintained by log replication will contain (more or less) the same information as the primary log would for identical operations. If it does then the SQL mode may work just fine and you could get a user with minimal privileges set up using SQL capture. May be worth a try?


Test drive
Contact us