dbi Blog

Last blog of this year 2018 with SQL databases and Cloud Azure and this is not trivial choice. <Speculation mode> I would say year 2019 could be important for all stakeholders. Cloud transformation has begun in many forms at customer shops and we heard about some big actors who are coming in Switzerland including Azure or Google. Data Platforms services will not be an exception in some degree of magnitude … <end of speculation mode>. I briefly introduced SQL Managed instances on a previous write-up and this service will likely not be available immediately when Azure datacenters will start next year in Switzerland. But I keep an eye on it because I believe it may address a lot of customer requests. So, I remembered to attend a session about SQL Server Managed Instances (SQL MI) a couple of months ago at SQL Saturday Madrid (October 2018) and a question raised about high-availability capabilities in such infrastructure especially if a SQL MI could be part of SQL Server availability groups. At this moment the answer was it is not supported because there is no need to use Always-On to achieve HA within the Azure region. Every instance is inherently highly available as that is one of the core PaaS capabilities in SQL Database including redundancy with Azure storage accounts as well as active and passive compute nodes. I admit I partially agreed because it means we restrict HA scenarios on fully PaaS architectures and I had in mind hybrid scenarios where we might include a SQL MI in AG as standby server for DR for instance. But anyway, at the moment of the session, 2 MI options were available including General Purpose and Business Critical in Preview only and on December 4^th 2018 the latter was finally available in GA.

Let’s say Business Critical tier differs from General Purpose tiers in terms of HA because it provides built-in availability groups behind the scene and auto-failover groups for disaster recovery on a remote Azure region as well.

The interesting point is Business Critical tier enables offloading reporting queries on a hidden RO replica by default. Bear in mind replication is asynchronous between replicas meaning data loss and delay are possible on RO replicas.

As normal AG scenarios, if we specify application intent to read only in connection string transparent redirection takes place. I did a try on my SQL MI after upgrading on Business Critical Tier as shown below:

$ (az sql mi show -g sql-mi-rg -n sql-mi-dbi2 | ConvertFrom-Json).Sku.Tier
BusinessCritical

From SSMS, I just switched my connection from READ WRITE to READ ONLY intent and I was redirected transparently to the corresponding replica as follows:

Note that the AG configuration is hidden to users and sysadmins and there is no way to get information from usual system views as follows:

However, we may get information of different states of underlying AG replication including 4 replicas as described in the Microsoft documentation.

SELECT 
	d.name as [db_name],
	drs.replica_id,
	drs.group_database_id,
	drs.is_local,
	drs.is_primary_replica,
	drs.synchronization_state_desc AS syncro_state,
	drs.synchronization_health_desc AS synchro_health,
	drs.database_state_desc AS db_state,
	drs.log_send_queue_size,
	drs.redo_queue_size
FROM sys.dm_hadr_database_replica_states AS drs
join sys.databases AS d ON d.database_id = drs.database_id

Auto failover groups is a complementary solution to address DR scenarios and to protect from a failure to of Azure region. Its implementation requires more work and more Azure resources … a challenge for next year!

Season’s greetings!!

By David Barbarin

Post Views: 578