SQL 2012 Availability Group Questions

[David_Simon]

Our DBA has questions about what Hyland recommends as far as setting up our SQL 2012 with Availability Groups (AG). We read through the Hyland OnBase Database Reference Guide but did not see answers to specific questions. Here are three questions: 

1) What type of AG does the product need?  Does it need a listener for full failover properties, and if so synchronous rather than async.? Does Hyland want bilateral read/write, or will one side be transactional and the other read only?

2) What type of access is needed; once put a db into production DBO privileges can be provided, but SA privileges on shared DB servers  are not given out to vendor products – unless its segregated into a “vendor product only” instance.  This is because anyone with SA privileges can – without knowing – hose the environment for other applications. 

3)  We need assurance that however it is configured, it will not invalidate our warranty.

[Carly_Tatnall · Accepted solution]

Hello and thank you for using Community!

I believe you can find the specific answers to your questions about Availability Groups on MSDN: https://msdn.microsoft.com/en-us/library/ff877884(v=sql.110).aspx
We do not have any rules or recommendations on how to setup the Availability Group. Below is taken from the article above:

1. You would need a primary replica and at least one secondary replica to utilize AG.
2. The listener would be used so that the client connections would not depend on the physical instance of SQL Server.
3. Synchronous vs Asynchronous depends on the business needs. Synchronous provides fully protected committed transactions but has increased transaction latency and can utilize automatic failover. Asynchronous minimizes the transaction latency but data loss is possible during a failover. Asynchronous also requires manual failover.
4. I do not believe AG supports bilateral read/writes for the primary and secondary replicas. Any writes occur on the primary replica. The secondary replicas are read-only.

In terms of access, the sysadmin server role is required to create the availability group. Once it is created the security settings on the primary replica are persisted to the secondary database. OnBase does not require SA privileges so it is up to the customer to manage those privileges as needed.

Using Availability Groups does not void the Hyland maintenance agreement. The software is modifying the primary database and then those modifications are being propagated to the secondary replica which is allowed. Any direct database modification without Hyland approval can result in a voided maintenance agreement.

Let me know if you have any additional questions.

Carly Tatnall
Database Support Specialist