Showing posts with label Always On. Show all posts
Showing posts with label Always On. Show all posts

Sunday, February 1, 2026

YES, you can configure backups on secondary node of AG now.

 Hi Friends,

If you’ve worked with SQL Server high availability, you know that Always On Availability Groups, introduced in SQL Server 2012, brought a major leap forward in HADR capabilities. However, one long-standing limitation always held us back—we couldn’t run a complete backup strategy on the secondary replica.

Yes, we could take copy_only full backups and transaction log backups on secondary replicas, but differential backups and normal full backups were restricted to the primary. As a result, DBAs were forced to run complete backup cycles on the primary node to maintain point‑in‑time restore capability.

And this came with challenges:

  • Backups consume CPU, I/O, and memory, often slowing down regular database transactions.
  • For 24×7 workloads, finding the “perfect time” for backups on the primary was often impossible.
  • Offloading backups to a replica wasn’t fully feasible because of those limitations.


SQL Server 2025 Changes Everything

With SQL Server 2025, this limitation is finally gone!

You can now run all backup types on a secondary replica, including:

  • ✔️ Normal Full Backup
  • ✔️ Copy_only Full Backup
  • ✔️ Differential Backup
  • ✔️ Transaction Log Backup

This means you can completely shift your backup strategy to the secondary node, keeping the primary replica laser‑focused on serving application workloads. For busy OLTP systems, this is a massive win.

How to Enable This Feature

To unlock this functionality, enable the following TRACE flags on the respective secondary node:

  • 3261 – Enables differential backups on secondary replicas
  • 3262 – Enables full backups on secondary replicas

Once these are enabled, your backup strategy is fully offloadable to the secondary node.

Final Thoughts

This enhancement is going to be a game‑changer for SQL Server administration. Reduced load on the primary, better performance for applications, and far more flexible backup scheduling—what more can we ask for?

That’s all for today. See you in the next blog — Happy learning! 🚀

Monday, January 26, 2026

Contained Availability Group

 Hi Friends,

In today’s blog, let’s dive into Contained Availability Groups (Contained AGs) in SQL Server—first introduced in SQL Server 2022 and further enhanced in SQL Server 2025.

We all know Availability Groups (AGs) were introduced back in SQL Server 2012, bringing a revolutionary shift in SQL Server’s high availability and disaster recovery (HADR) capabilities. In a traditional AG setup, two or more SQL instances or nodes participate as part of a Windows Failover Cluster. The cluster handles the availability of the entire group of databases. The AG resource stays active on the primary node, and whenever the primary node fails, all databases within the AG fail over to the secondary node.

While AGs solved many challenges, they also introduced some pain points. For example, when adding a new node to an existing AG, DBAs had to manually copy logins, permissions, SQL Agent jobs, and other instance-level objects to ensure consistency across nodes. If any dependent login or job was missing on a secondary node, a failover could easily lead to application failures—a scenario many of us have encountered.

That’s why I’m genuinely excited about Contained Availability Groups, which beautifully solve this problem. In a Contained AG, all these dependent objects are maintained at the AG level instead of at each SQL instance. SQL Server achieves this by adding two special system databases within the AG:

  • <AGName>_master
  • <AGName>_msdb



These databases become part of the AG and fail over along with the user databases. This means any logins, permissions, or jobs created inside these contained system databases automatically remain consistent across all nodes—no manual copying needed! Whether you are adding a new replica or performing a failover, your application-dependent objects remain intact and available. Isn’t that amazing?

This is just an introductory overview. You can explore more details in the official Microsoft documentation on Contained AGs.

That’s all for today’s blog. See you next time with another exciting learning!
Happy learning!!

 

Saturday, March 2, 2024

Take care when you are changing owner of HADR endpoint

Issue: 
I recently configured an SQL AG between 4 nodes and as you know whenever you configure new AG it creates new hadr_endpoints on each node to communicate with other nodes. Whoever is configuring the AG his/her account set as owner of hadr_endpoint, but as per standard practice we should configure 'SA' login as owner of endpoints so even if in future that person leaves organization and respective account get removed, our AG set up will not get affected. 

So to follow above mentioned standard practice I changed owner of hadr_endpoint  to 'SA' on all AG nodes using below command. 

                ALTER AUTHORIZATION ON ENDPOINT::hadr_endpoint TO sa;

After changing the owner AG was working fine I dint observe any issue. But on next I did failover of AG from one node to other node then I observed AG was in sync with previous primary node but other two nodes went out of sync and status in AG health dashboard for those 2 nodes was showing Not Synchronized. Of course in this case I would not be able to configure failover on these 2 nodes.

Analysis: 

When I checked error logs on faulty unsynchronized nodes, I found below logs.


A connection timeout has occurred while attempting to establish a connection to availability replica 'XXXXXX' with id [XXXXXXX]. Either a networking or firewall issue exists, or the endpoint address provided for the rep

Database Mirroring login attempt by user 'XXXX\XXXXX.' failed with error: 'Connection handshake failed. The login 'xxxxx\xxxxx' does not have CONNECT permission on the endpoint. State 84.'.  [SERVER: X.X.X.X]

  

This error occurs when SQL server service account does not have connect permission on HADR endpoint. When we configure AG this permission get granted itself but when I changed the owner of hadr_endpoint it got revoked.


Resolution:

To fix issue I executed below command on every node participated in AG.

GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [<SQL server service account>]


Conclusion: 
Whenever you change owner of HADR endpoint, connect permission of SQL service account endpoint gets revoked. So you should grant it again. So to do same I have shared below script which will take care of  both. You can execute it whenever you want to change owner of hadr_endpoint to 'SA'.



select 'Pre-Check' as [Check]

select E.*, p.name as Owner from sys.endpoints E left join sys.server_principals P on E.principal_id=p.principal_id where E.type=4

 

SELECT ep.endpoint_id, p.class_desc, p.permission_name, ep.name, sp.name

FROM sys.server_permissions p

    INNER JOIN sys.endpoints ep ON p.major_id = ep.endpoint_id

    INNER JOIN sys.server_principals sp ON p.grantee_principal_id = sp.principal_id

WHERE class = '105' and ep.name='Hadr_endpoint' ;

 

declare @serviceaccount nvarchar (512)

declare @endpointname as sysname

declare @sql as varchar(1000)

select @endpointname= name from sys.endpoints where type=4 and principal_id <>1

if @endpointname is not null

begin

set @sql = 'ALTER AUTHORIZATION ON ENDPOINT::' + @endpointname + ' TO sa;'

--select (@sql)

Exec (@sql)

end

 

 

select @serviceaccount = service_account from sys.dm_server_services where servicename = 'SQL Server (MSSQLSERVER)'

set @sql= 'GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO ['+@serviceaccount+']'

--select @sql

exec (@sql)

 

 

select 'Post-Check' as [Check]

select E.*, p.name as Owner from sys.endpoints E left join sys.server_principals P on E.principal_id=p.principal_id where E.type=4

 

 

SELECT ep.endpoint_id, p.class_desc, p.permission_name, ep.name, sp.name

FROM sys.server_permissions p

    INNER JOIN sys.endpoints ep ON p.major_id = ep.endpoint_id

    INNER JOIN sys.server_principals sp ON p.grantee_principal_id = sp.principal_id

WHERE class = '105' and ep.name='Hadr_endpoint' ;