SQL AlwaysOn Availability Group – Readable Secondary

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • #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?

    #8783
    Mark
    Keymaster

    Hi There,

    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.

    #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 you

    #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.

    #8789
    Mark
    Keymaster

    Kris,

    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?

    http://info.hvr-software.com/free-trial

    Mark.

Viewing 5 posts - 1 through 5 (of 5 total)
  • You must be logged in to reply to this topic.

© 2020 HVR

Test drive Contact us