- January 19, 2017 at 12:05 am #8781
Can you please advise if we could use a SQL 2012 AlwaysOn Availability Group – readable secondary database as a source database for an HVR implementation?January 20, 2017 at 12:07 am #8783MarkKeymaster
Yes, we can use the readable secondary node for real-time capture. To do this you have to use direct read of the transaction logs from the file system (which is the fastest approach anyway). But you do need:
1) An OS user with Administrative privileges to run HVR.
2) A database user with sysadmin privileges.
Hope this clarifies.
Mark.January 20, 2017 at 12:09 am #8785
Thank you for your reply. Can you please advise if there is a document that outlines exactly what SQL privileges are required as opposed to a global sysadmin role? Our intent is to grant read-access to a single database as opposed to granting access to the HVR user the ability to control the entire SQL installation, including modifying the Availability Group replica to force a failover and become primary.
Thank youJanuary 20, 2017 at 12:10 am #8787
Alternatively, if sysadmin role is an absolute requirement, can we use a SQL instance that is NOT part of an Always On Availability Group, a stand-alone instance, with the source database being restored / maintained via SQL transaction log replication with standby files (to make it readable)? We currently execute SQL transaction log dumps every 5 minutes anyway, therefore restoring to this replica with standby files would be very simple. The HVR software could have sysadmin role to the SQL instance without any risk to the “real” production SQL instance.
Thank you.January 20, 2017 at 12:11 am #8789MarkKeymaster
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?
- You must be logged in to reply to this topic.