Questions about failover groups in SQL Azure Database

Question

In Azure SQL, we can configure replica database for primary database, and then on the Server of Primary Database, we can configure Primary Database and Replica Database as a Failover Group. Then use the Read-Write Endpoint and the Read-Only Endpoint to host read-write and read-only operations, respectively.

My problem is How do I apply for this ACL if I want to access the primary database and replica database? Only need access to the endpoint, or have to apply ACL for both the Primary+Replica database?

Background:

In the previous architecture, the database db1 on ServerA was the primary database, and db1 on ServerB was the replica database, and we usually used the Read-Write endpoint in the connection string. However, when someone fails over the database in the failover group and we continue to use the old database connection string(using failover group ), we report the Verify Hostname and port number error message.

Our temporary solution at that time was to change the server name in the connection string to database db1 on ServerA, and then it worked.

Answer 1

WisonHii Thank you for reaching out.

Permissions from login side are on each individual server.

Endpoint is just for ease of redirection not having to change connection string.

Permissions for a failover group are managed via Azure role-based access control (Azure RBAC).

Azure RBAC write access is necessary to create and manage failover groups. The SQL Server Contributor role has all the necessary permissions to manage failover groups.

For specific permission scopes, review how to configure auto-failover groups in Azure SQL Database.

• Failover group read-write listener.

A DNS CNAME record those points to the current primary. It is created automatically when the failover group is created and allows the read-write workload to transparently reconnect to the primary when the primary changes after failover. When the failover group is created on a server, the DNS CNAME record for the listener URL is formed as <fog-name>.database.windows.net.

• Failover group read-only listener

A DNS CNAME record that points to the current secondary. It is created automatically when the failover group is created and allows the read-only SQL workload to transparently connect to the secondary when the secondary changes after failover. When the failover group is created on a server, the DNS CNAME record for the listener URL is formed as <fog-name>.secondary.database.windows.net.

Auto-failover groups overview & best practices (Azure SQL Database)

Regards,

Oury