Sql jobs failed randomly to acquire connection after the activation of the Always ON feature

user_3468578 0
Viewed 284

Since we activated the Always On mode on our server, we have some issues regarding our SQL jobs (running SSIS package) which failed randomly with this message :

Failed to acquire connection "DB". Connection may not be configured correctly or you may not have the right permissions on this connection.

More details :

  • This issue did not happen before the activation of the Always On
  • The connection is defined in an OLEDB component
  • We have another environment/server with this kind of architecture and there is no issue with it

Trace of the listener :

Message Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 15.0.2000.5 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved. Started: 2:18:01 PM Error: 2022-02-15 14:18:30.07 Code: 0xC0202009 Source: TestImport Connection manager "DB_PRIMARY" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [53]. ". End Error Error: 2022-02-15 14:18:30.94 Code: 0xC00291EC Source: Execute SQL Task Link coupons movement to collection Execute SQL Task D escription: Failed to acquire connection "DB_PRIMARY". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:18:01 PM Finished: 2:18:30 PM Elapsed: 29.984 seconds. The package execution failed. The step failed.

alwayson ssis sql-job sql-server
1 Answers

When enabling AlwaysOn, there is only one active database ("master") and synchronous or asynchornous copies that are call replicas ("slave") that you can't access to.

The SQL Agent operates on local database, that can be in the active or slave mode. When active, your job will operate. When slave your job will fail.

So is is necessary to ask is the database is active prior to all execution of SQL server Agent job.

I wrote some UDF to test if the database is reachable or not that you can have at :

And also to reproduce the SQL Server jobs on all replicas... !

user_126598720
Related