Azure SQL Database – Read Only Endpoints

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.

diagram

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

milkwayserver

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

sqldb

Next select the target (secondary) location.

readreplica

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.

second

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.

ssms

Let’s confirm with an update query.

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

powerbi

powerbi2

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.

FGs

readendpoiny

ssms1

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

fgssms

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.

1 thought on “Azure SQL Database – Read Only Endpoints

  1. Pingback: Working with Read-Only Endpoints in Azure SQL Database – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s