Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. 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' ;


Tuesday, April 19, 2022

Lift and Shift on-prem SQL server Failover cluster instance to Azure

Hi,

In this blog I am going to share very exciting experience. I am working in migration project, where we migrate on-prem servers of our client to Azure. So recently we got one requirement where application team wanted to migrate there 2 node SQL failover cluster instance to azure as it is.

We use azure migrate tool to lift and shift on-prem servers to azure VM, In which we create exact replica of on-prem server to azure VM and then we turn off on-prem server and bring Azure VM online. So far we migrated many stand alone SQL servers to azure using same approach, but this time it was cluster. We were worried because unlike standalone servers, cluster has shared disks, virtual SQL server name, SQL IP, quorum configuration etc. So first I checked with my friends who are already working on Azure, I got response like we cant migrate cluster on Azure. Then after surfing a lot on internet, I got the below Microsoft document.

https://docs.microsoft.com/en-us/azure/azure-sql/migration-guides/virtual-machines/sql-server-failover-cluster-instance-to-sql-on-azure-vm

In the approach provided in above link, we are using Azure internal load balancer and azure premium shared disk to configure cluster. This was new for us and we were not having test environment to try it. So we checked with Microsoft engineer and confirmed steps mentioned in document will work. Then we took approval from application owners and our leadership to try directly this on production cluster.

We followed this link, prepared step by step plan for it and implemented on migration day, which went successfully. As this is large document I wont explain each step but I am sharing the below key points which you should note when you are referring same article to migrate your cluster. Because they are not covered in article and you may stuck.

1) When you migrate windows server to azure DNS entries get updated automatically with new IPs in azure, but as here we are creating load balancer for virtual SQL server name and cluster name, we need to update DNS entries manually with new IP addresses which we have used in front end of load balancer.

2) Sometimes migrated cluster does not identify azure network so cluster fails to come online. So you need login on any one node and open cluster manager. Navigate to Networks in cluster manager and open properties of cluster network. Then select below highlighted radio button, checkbox and save it. Now try to bring cluster up it will come online.




3) As we use named instance while installing SQL failover cluster, after migration we can't connect SQL server instance without mentioning port number. Means if you try to connect SQL server with SSMS by mentioning server name like servername\instancename it wont work, you need to provide SQL port also like servername\instancename,52865. You can overcome this issue by creating one more load balancing rule in azure load balancer as below which is not covered in Microsoft article.


4) In azure migrate there are two ways to migrate server, agent based and Agent less. So you should use agent based migration then only it will identify your cluster shared SAN disks.

These are all points you should note and also I suggest to go line by line through this Microsoft article and create your solid plan for migration. Apart from this if you have any doubt regarding migration you can contact me through any channel mentioned in my contact page.

That's all, thank you, b bye and happy learning !!

Tuesday, April 5, 2022

Able to connect SQL server with FQDN but not with FQDN\Instance_name

Hi Folks,

Recently we migrated SQL server from On-Prem to Azure VM with lift and shift approach using azure migrate. But after migration application team reported below issue.

Issue : Application team tried to connect SQL server using SSMS with FQDN like servername.domainname.com and also with IP address, it was connecting. But in application connection string it is like servername.domainname.com\instancename, so tried same on SSMS but it was not working.

Error msg:

TITLE: Connect to Server

------------------------------

Cannot connect to servername.domainname.com\instancename.

------------------------------

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver--1-database-engine-error

------------------------------

BUTTONS:

Troubleshooting 

1) As we are able to connect SQL server with FQDN and IP address so its sure that SQL Database engine service is online.

2) This is named instance configured on port number 1433, we checked if browser services are online.

3) Checked all 3 network protocols (Shared memory/Named Pipes/TCP-IP) are enabled.

4) Checked SPN registered, all are in place as expected

5) As all checks were fine and still it was not working, we suspected this could be security issue. So we checked NSG rules for subnet in which VM was placed after migration. In Inbound rules, port 1433 was opened but as this is named instance and we also need port 1434 with UDP protocol opened but it was not opened.

6) We created one more inbound rule for port 1434 with UDP protocol having necessary source and destination IP and tried to connect SQL server with servername.domainname.com\instancename again voilà!!! it worked!!!

Conclusion:

Whenever we are having SQL named instance and facing such connection issues, check SQL browser service is online.

Also check firewall and NSG rules if port 1434 is opened with UDP protocol.

Wednesday, May 26, 2021

Resource DB : The mysterious database

Hello Friends, 

Before I start with today’s topic let me ask you one question, what all systems databases are in SQL server? The most common answer will be 4 databases - master, model, msdb and tempdb.

But let me correct you, there is one more system database called as Resource database which is unknown to most of the people because we can’t see it in SSMS, if we expand system databases we can see above mentioned 4 databases only.



The resource database also called as RDB is read only database which contains all the system objects that are included in SQL server.  SQL server system objects like sys.objects are physically present in resource database but logically appear in sys schema of each database.

Resource DB gets written only when we upgrade SQL version. Resource DB makes upgrade activity easier and faster. In earlier versions of SQL server, upgrading used to drop and create system objects. But now resource DB contains all the systems objects so upgrade is accomplished by simply copying resource database file to server.

Location of resource database:

Each SQL instance on server has own resource database with files mssqlsystemresource.mdf and mssqlsystemresource.ldf at below location

 <drive>:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binn\

<drive>:\Program Files\ is location where you have installed SQL server, in my case I have installed at location F:\SQLServer\install



How to backup resource database:

We can’t backup resource database like other databases using backup command in SSMS.

To backup it we need to copy above mentioned mdf and ldf files manually to safe location.

What if resource database files are missing or corrupted?

If resource database files are missing or corrupted SQL services will fail with below error in event viewer.

System



Application

SQL server error logs:

2021-05-26 19:21:51.85 spid14s     Error: 17204, Severity: 16, State: 1.

2021-05-26 19:21:51.85 spid14s     FCB::Open failed: Could not open file F:\SQLServer\install\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.mdf for file number 1.  OS error: 2(The system cannot find the file specified.).

2021-05-26 19:21:51.85 spid14s     Error: 5120, Severity: 16, State: 101.

2021-05-26 19:21:51.85 spid14s     Unable to open the physical file "F:\SQLServer\install\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.mdf". Operating system error 2: "2(The system cannot find the file specified.)".

 

To fix it, there are three methods:

1)      Restore file from backup which we took to safe location as explained earlier.

2)      Copy files from other SQL instance having same version to above mentioned file location.

3)      Repair SQL instance using installation setup.

You can execute below CMD command by locating to installation folder.

 setup.exe /q /ACTION=Repair /INSTANCENAME=<instancename> 


That's all about resource database, please do comment or contact for feedback/suggestions/queries.



Monday, November 2, 2020

Are you restoring database with Full Backup? Check below properties first..

Hello DBAs,

On weekend due to some reason data file of our database got corrupted so to resolve it we decided to restore latest healthy full backup followed by log backups. After we ran DBCC CHECKDB and did database health check everything was fine, so we went to sleep.

On next day Monday, users started complaining they cant see data in application after executing application utility. So application team approached to me with their problem. Then I got tensed and started doubting, did I restore wrong database backup? hence users cant see data.

But I checked and confirmed I restored correct database backup, then what was the issue? I tried my all DBA knowledge I have, but no luck. Finally application team raised case with application vendor and in their investigation they found cross-database chaining option is off for database. Then application team came to me and we enabled it and application started working.

I was in belief that when we restore database full backup from same instance all database configuration get set itself and no need to change any setting. But after I checked Microsoft docs and got to know I was wrong. So my knowledge is not sufficient we are not aware too many minute things in SQL server which could affect great.

Below are the two settings which I know and get disabled when you restore database from Full backup or when you detach - attach database files. So you need to enable them again

1) TRUSTWORTHY Database Property

2) cross-database chaining

These properties allows database users to access objects on across other databases in same instance.

You can verify settings before and after restore backup or detach attach files by checking columns is_trustworthy_on, is_db_chaining_on in sys.databases tables.

For more information of above two settings, refer below Microsoft documents.

https://docs.microsoft.com/en-us/sql/relational-databases/security/trustworthy-database-property?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/cross-db-ownership-chaining-server-configuration-option?view=sql-server-ver15

Please do comment for suggestion or if you know any other properties which we should check before restoring database full backup.







Saturday, April 11, 2020

SQL Server Architecture

Hello Guys,

To learn any technology, we should clear about basics of technology, other knowledge we can achieve with experience and by handling different issues. Same principle applies to SQL server if you know architecture of SQL server, whether you are SQL developer or DBA you can take correct decision quickly when you are handling any issue.
In this post, I have tried to explain SQL server architecture in detail which will help you to understand SQL server more. So let’s begin...


 

Above is the basic diagram of SQL architecture. We can divide SQL engine in 2 parts – Relational engine which perform SQL OS operations and storage engine which process data at actual storage level. 

1)      Relational Engine:
Relational engine performs SQL OS operations means, this part of SQL engine where actual SQL server transactions received and operated.

Relational engine contains below blocks or operators.

a)      Network Protocol layer:
Here actual client – server communication happens at default port 1433. For this Microsoft has developed API called SNI (SQL server Network Interface). Communication happens in the form of TDS (Tabular Data String developed by Sybase and later adopted by MS), means whatever query client execute it gets converted in TDS and transferred to SQL. Same for SQL server whatever data derived converts in TDS and transfers to client.

Now SQL server have 4 types of network architecture/protocols:

                   I. Shared Memory: This the default protocol used to connect to a client residing locally (i.e. on the same machine).
                  II. TCP/IP: This protocol helps us to connect to the SQL server over the network via an IP address and a port number. The default TCP port used by SQL Server is 1433.
                 III. Named Pipes: Originally developed for LAN, this protocol is similar to TCP/IP, but is efficient for slower networks like WAN. It uses port number 445.
                  IV. VIA: Virtual Interface Adapter, is a protocol used for high performance communication between two connected systems, with the help of specialized hardware on both ends.

b)      Command Parser:
Network layer coverts query received as TDS into language event and pass it to command parser. Command parser performs syntax and semantic checks, if query is incorrect gives respective error back to the client from error library which contains more than 50k defined errors. If query is correct command parser passes it to Optimizer in the form of query tree.

c)       Optimizer:
Optimizer is the most important part of relational engine because it defines performance of query execution. When optimizer receives query tree it checks all statistics and all execution plan available from plan cache. If query is new it creates new execution plan and pass it to query executer for execution.

d)      Executer:
Executer executes query on storage engine with best execution plan provided by optimizer and transfers derived data back to client.

2)      Storage Engine:
Storage engine operates actual physical data on disk/storage and memory.
Storage engine also contains below operators/units.

a)      Access Methods:
As we know SQL server works as write log ahead, transactions which includes Update/Insert/Delete are written to log file via log manager before writing it to data file. Where in Select, data is derived direct from data file via buffer manager. So Access method checks whether transaction contains SELECT or Update/Insert/Delete and accordingly route it to buffer manager or log manager.

b)      Buffer manager:
SQL server does not read or write data directly in disk, it calls pages’ in memory and performs operation there this is called as hard parsing. Sometimes data may present in data cache already due to recent transaction so data directly read from memory this is called Soft parsing.
SQL server always wants to keep all data in Memory to access easily, but it’s not possible due to limited RAM so periodically checkpoint runs which moves dirty pages again to disk on LRU basis (Least Recently Used pages).
Now there are two different functions performs similar activity – Checkpoint and Lazy writer both moves pages from memory to disk but there is below difference
Checkpoint is executed periodically with purpose to shorten database recovery after crash down.
Where lazy writer executed only when there is memory pressure with purpose to lower down pressure on memory.
Buffer/cache also have some part reserved for plan cache which optimizer use to refer recently used execution plans.
So buffer manager operates all these activities in memory and manages transaction to use memory efficiently.

c)       Log manager:
This manages logs and locks when every transaction written to log file.

d)      Log file:
Log file keeps logging every Update/Insert/Delete transaction and it gets truncated after checkpoint or log file space usage reaches to 70% if database in simple recovery mode, where after log backup in full recovery mode.
Each transaction is written sequentially on log file as record and sequence is maintained with LSN (Log Sequence Number).
Log file is logically divided into small VLFs (Virtual log files) We can’t predict size of VLFs but number of VLFs can predict when log file created according to size of log file as below.

Size of log File
Number of VLFs
Less than 1MB
2
1 MB to 64 MB
4
64 MB to 1 GB
8
More than 1 GB
16

Later number VLFs can varied according to size of auto growth set for log file and transactions. VLFs is topic could be studied in more details but it’s not part of our post for now so just described at basic.

e)      Data file:
Data file is where actual data stored on the disk, where basic unit where data stored is PAGE of 8KB size. 8 Pages are logically grouped together and called as extent which is size of 8x8KB = 64 KB.
Now there are two types of extent mixed extent and uniform extent.
Pages in the mixed extent are shared by two or more different objects (tables or indexes) where pages in uniform extent are used only for one object.
For better performance uniform extent is always better. Till SQL version 2014 it could be controlled with trace flag 1118. Enabling trace flag starts using uniform extent. From SQL server 2016 onward uniform extent is enabled by default.

Below is the structure of page 


    Header:
Out of 8KB in page initial 96 bytes are reserved for system information of page like page number, page type, allocation ID etc.
    Row offset:
Row offset’s each unit is of 2 bytes and added at the end of pages. One entry of row offset mapped to one row on page and each row offset gives information how far 1st byte of each row is from start of page.
Data
Other part o page contains respective page data. 

             Below are the types of pages which we will discuss in another post.

  • Data page
  • Index page
  • LOB (Large Object pages) for text or image
  • GAM (Global Allocation Map)
  • SGAM (Shared Global Allocation Map)
  • PFS (Page free space)
  • IAM (Index Allocation Map)
  • BCM (Bulk Change Map)
  •  DCM (Differential Change Map)