One of the main benefits of configuring active geo-replication for Azure SQL Database is leveraging the read-only endpoint, a good technique to split away read only activity from OLTP based workloads. This means that there is no reason why you cannot point users to these databases via tools such as Power BI as highlighted below.

To do this, obviously you need to setup the secondary database as shown below. For this example, I am working with CompanyDB.

Then within the Azure Portal go to settings > geo-replication. (For the database in question)

Next select the target (secondary) location.

Here I select UK South, as you can see, the secondary type is readable (below). Typically, you will want to use the paired data centre in real world scenarios.

This will take couple of minutes, once done let’s connect to the secondary readable database. Soon as you login via SSMS (SQL Server Management Studio) there are clues that we are in read-only mode, as you can see the query store is read only.

Let’s confirm with an update query.

If you try and change the status, well think again.

ALTER DATABASE [companydb] SET  READ_WRITE WITH ROLLBACK IMMEDIATE

Msg 42008, Level 16, State 5, Line 1

ODBC error: State: 42000: Error: 1468 Message:'[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The operation cannot be performed on database “companydb” because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.’.

Msg 5069, Level 16, State 3, Line 1 ALTER DATABASE statement failed.

As you can see below, I connect using Power BI just as I would any other data source.

What about Failover Groups? Failover groups are an abstraction layer sitting above active geo-replication, required for those situations where you need to manage many replicated databases at scale.

If you see the image below, I have a failover group with 2 databases within it (just as an example). I then drill into the group where I will see the read-only endpoint.

Again, I connect via SSMS to see both the databases defined within my group.

So, with failover groups you can access read only endpoints too. However, if you want multiple copies in different locations then you will need to stick with active geo-replication. Also, it is worth remembering that it is very easy to add and remove databases to the failover group, literally with a couple of clicks of your mouse.

Note – This design / idea is slightly different to the concept of read-scale out that is available within the premium / business critical tier.