Introduction
This guide provides the steps needed to create a named replica on a Zone Redundant Secondary Replica (Geo-Replica). Creating a named replica is available in the azure portal for the primary region. However, for some use cases such as offloading the read workloads due to application maintenance activities in the geo replica region during DR test it is vital to have the named replica created on top of geo replica (secondary) database.
Named Replica Overview
A named replica, just like an HA replica, uses the same page servers as the primary replica. Similar to HA replicas, there is no data copy needed to add a named replica. Named replicas are a key additional component of Azure SQL Database Hyperscale which enable a broad variety of read scale-out scenarios, to improve Hybrid Transactional and Analytical Processing (HTAP) workloads, be it highly concurrent OLTP workloads or HTAP solutions for real-time data analytics. Named replicas offer flexibility and elasticity to satisfy many use cases such as Access Isolation, Workload-dependent service level objective and Workload-dependent routing.
Hyperscale secondary replicas - Azure SQL Database | Microsoft Learn
Create Read-Only Database using Named Replica on a Zone Redundant Secondary Replica.
Creating a Named replica on an Azure SQL Hyperscale Primary Database in the same region can be done using the Azure Portal, T-SQL , PowerShell or via Azure CLI.
However, creating a Named replica on a Zone Redundant Secondary Replica via the Azure Portal may result in the following error message:
A Named replica on a Zone Redundant secondary replica can be created using the following T-SQL script or via a PowerShell script.
T-SQL
Connect to the Zone Redundant replica using SSMS and run the following command:
Syntax :-
ALTER DATABASE [<database name>]
ADD SECONDARY ON SERVER [<Server Name>] -- the server name created as a part of step 1
WITH (SERVICE_OBJECTIVE = <Service Level objective/ Tier >', SECONDARY_TYPE = <Named>, DATABASE_NAME = [<Named Replica db Name>]); -- Select the appropriate Service Objective probably same as Geo Replica and give the database name for Named replica
Example:-
ALTER DATABASE [testdb]
ADD SECONDARY ON SERVER [hypergeonamedserver]
WITH (SERVICE_OBJECTIVE = 'HS_Gen5_2', SECONDARY_TYPE = Named, DATABASE_NAME = [testdb_geo_NamedReplica]);
Powershell
New-AzSqlDatabaseSecondary -ResourceGroupName "MyResourceGroup" -ServerName "hypergeonamedserver" -DatabaseName "testdb" -PartnerResourceGroupName "MyResourceGroup" -PartnerServerName "hypergeonamedserver" -PartnerDatabaseName "testdb_geo_NamedReplica" -SecondaryType Named -SecondaryServiceObjectiveName HS_Gen5_2
Feedback and Suggestions
If you have feedback or suggestions for improving this data migration asset, please send an email to Azure Databases SQL Customer Success Engineering Team. Thank you for your support!