Having the strangest issue I've ever encountered. I have a batch of SQL statements to pull a chunk of data from SQL to a text file. The batch runs in our development, sandbox environments with no problem. In production, under the SQL agent service account, the batch fails on a simple select statement, but runs successfully for other users. Through trial and error, I confirmed the script fails on simple Select statement with the following error: > [Microsoft ODBC Driver 17 for SQL Server][SQL Server]The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. (3930) (SQLExecDirectW) While debugging, I found the first query that the batch fails in. There are two statements that have a problem. Both are SELECTS that cross databases on the same server. I'll focus on the first one here for simplicity. This query does a select from a small table and pivots the data into a grid with text strings for TRUE or FALSE for 8 columns. Query is run in database #1, selecting data from database #2, from a table like this ( ignore the poor coding standards for this sample ): Selecting data from the following schema for

Database2.dbo.table1(
field1 uniqueidentifier not null Primary Key Clustered
, field2 datetime2(7) not null
, field3 varchar(50) not null
, field4 varchar(50) not null
, field5 varchar(50) not null
, field6 tinyint not null
, field7 tinyint not null
, field8 varchar(50) not null
, field9 varchar(50) not null
, field10 datetime not null
, field11 varchar(50) not null
, field12 datetime not null
, field12 uniqueidentifier not null

Table has about 4,200 rows, relatively static. no other indexes on the table.

SELECT 
   field4
   , cast(field5 as uniqueidentifier) as field2
   , isnull(max(case when field4 = 'x' AND field8 = 'a' then 'TRUE' else 'FALSE' end), 'FALSE') as ALabel
   , isnull(max(case when field4 = 'x' AND field8 = 'b' then 'TRUE' else 'FALSE' end), 'FALSE') as BLabel
   , isnull(max(case when field4 = 'x' AND field8 = 'c' then 'TRUE' else 'FALSE' end), 'FALSE') as CLabel
   , isnull(max(case when field4 = 'x' AND field8 = 'D' then 'TRUE' else 'FALSE' end), 'FALSE') as DLabel
   , isnull(max(case when field4 = 'x' AND field8 = 'E' then 'TRUE' else 'FALSE' end), 'FALSE') as ELabel
   , isnull(max(case when field4 = 'y' AND field8 = 'L' then 'TRUE' else 'FALSE' end), 'FALSE') as LLabel
   , isnull(max(case when field4 = 'y' AND field8 = 'M' then 'TRUE' else 'FALSE' end), 'FALSE') as MLabel
   , isnull(max(case when field4 = 'y' AND field8 = 'N' then 'TRUE' else 'FALSE' end), 'FALSE') as NLabel
FROM database2.dbo.Table1
WHERE 
FIELD8 not in ( 'J', 'K' )
and field4 in ( 'x','y' )
AND FIeld10 >= '2024-01-01'
GROUP BY 
   field4
   , cast(field5 as uniqueidentifier)

When I run the above select, I get less than 450 rows in the result, and takes less than 1 second. When the SQL Agent Service account runs the select cross database it get's the error. All runs are being done by a Python script reading the SQL batch in from a text file, and connecting to SQL server to execute. The SQL Agent runs the process with other scripts. Software versions match on all the servers, MS SQL Server 2017, current patches. DBAs assure me that service agent account has access to all the databases Securities for the service account match in all environments, sysadmin. All python software versions including libraries, match between environments, and my machine. Other environment factors: Both the Sandbox and Production servers are the primary of secondary group in a Distributed Availability Group. SQL databases have free space in files, and auto growth enabled so it shouldn't be a space issue, Driver might return false error if no space available. DBAs tell me there's no locks being held for long time on any of the tables Python script creates new connection for each batch run. No other SQL commands issued in the connection but the above select, and I get the error from the SQL agent. Problem consistently fails for service account and always works for users. My thoughts: Could there be a Server setting that causes cross database queries to behave differently in production? Could the fact it's the secondary in the DAG cause this? Could the DBAs be wrong about it being a security issue? Is there anything else I'm missing?

We have a Distributed AG instance with two AGs, each with two replicas. The local AG Backup preference is 'Any' on each AG. Backup jobs run successfully on the global primary but fail on the Forwarder. Microsoft documentation suggests that it should be possible to run backup jobs on the Forwarder: > In a distributed availability group, backups can be performed on > secondary replicas in the same availability group as the active > primary replica, **or on the primary replica of any secondary > availability groups.** Backups cannot be performed on a secondary > replica in a secondary availability group because secondary replicas > only communicate with the primary replica in their own availability > group. Only replicas that communicate directly with the global primary > replica can perform backup operations. I have tried to research this issue unsuccessfully. I see other people have asked the question but there doesn't seem to have been a helpful response. Can anyone help please? Is the backup job expected to fail on the Forwarder? In which case, the backups take place **only** on the global primary? Also, should there be any concern about transition logs filling up on the secondary AG, from not getting backed up? Thank you.

I'm trying to write a script for a SQL Agent job. As part of this, the first thing I need the job to do is to test for the global primary replica, for which I have come up with the following code: select * from sys.dm_hadr_availability_group_states where primary_replica like '%_AG1' and primary_recovery_health = 1 All our DAG global primary replicas are always xx_AG1 Does anyone see any situation where the above code will not uniquely select my healthy global primary please? thank you

I am using dba_CopyLogins stored proc to sync logins on replicas in Distributed Availability groups, but the database level permissions are not transferred due to database sync operation is going on.. Is there any way to sync all logins and permissions from global primary to forwarder and other replicas? Also, how to sync SQL agent jobs using any automated way?

Following the steps in Manual Failover of SQL Server DAG To verify if both Availability Groups are ready for failover, use the T-SQL query below, running in SQLCMD Mode: :CONNECT TDPRD071 SELECT ag.name, drs.database_id, db_name(drs.database_id) as database_name, drs.group_id, drs.replica_id, drs.last_hardened_lsn FROM sys.dm_hadr_database_replica_states drs INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id; :CONNECT TDDR071 SELECT ag.name, drs.database_id, db_name(drs.database_id) as database_name, drs.group_id, drs.replica_id, drs.last_hardened_lsn FROM sys.dm_hadr_database_replica_states drs INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id; The screenshot of the results of running the above queries from the global primary, as shown in the article, shows connection to each AG in the DAG. However, when I run the queries on the global primary of my DAG, the results I get are only from connection to the Forwarder. Hence the two sets of results displayed are from connection to AG02 only. If however, I run the two queries individually (both from the global primary) they connect correctly to AG01 or AG02 as the case may be. Any suggestions as to why I may be getting this behaviour please? thank you.

I have a Distributed Availability Group (DAG) from cluster *Main* to cluster *Alt* - both with two replicas. The *Main* is the primary and *Alt* is a forwarder One (of many) databases called *MyDb* in the AG on the forwarder side's secondary replica was in a bad state and was dropped. I've used automatic seeding to bring back the database but it failed with a failure_state_desc = SQL Error Now the database is stuck in RESTORING state and the error log says: > Starting up database 'MyDb'. > The database 'MyDb' is marked RESTORING and is in a state that does not allow recovery to be run. When I run

ALTER DATABASE [MyDb] SET HADR AVAILABILITY GROUP = [fwdAg];

I get an error > Msg 41145, Level 16, State 1, Line 46 > Cannot join database 'MyDb' to availability group 'fwdAg'. The database has already joined the availability group. This is an informational message. No user action is required. When I try

ALTER DATABASE [MyDb] SET HADR resume;

I get error > Msg 35242, Level 16, State 16, Line 50 > Cannot complete this ALTER DATABASE SET HADR operation on database 'MyDb'. The database is not joined to an availability group. After the database has joined the availability group, retry the command. which contradicts the previous error. I get the same error if I try

ALTER DATABASE [MyDb] SET HADR off;

And when I query the DMVs

select
	adc.database_name as dbName
    , ag.name as agName
    , drs.synchronization_state_desc
from sys.availability_databases_cluster as adc
join sys.availability_groups as ag
    on adc.group_id = ag.group_id
left join sys.dm_hadr_database_replica_states AS drs
    on drs.group_id = adc.group_id 
    and drs.group_database_id = adc.group_database_id
where ag.name = 'fwdAg'

I get back the *agName* but not the database_replica_states info. How do I re-add the DB to the AG on the secondary (forwarder side)? I'm on SQL Server version: Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2022 Datacenter 10.0 (Build 20348: ) (Hypervisor)

I have setup a distibruted availability group and trying to understand behaviour of backups on it. While I can take backup from primary replica of Primary AG, I can not take backup from primary replica from Secondary(Forwarder) AG. MS documentation says I should be able to. > In a distributed availability group, backups can be performed on secondary replicas in the same availability group as the active primary replica, **or on the primary replica of any secondary availability groups**. Backups cannot be performed on a secondary replica in a secondary availability group because secondary replicas only communicate with the primary replica in their own availability group. Only replicas that communicate directly with the global primary replica can perform backup operations. Backup Command BACKUP DATABASE [mydb] TO DISK = 'C:\TEMP\mydb_Full.BAK' WITH COMPRESSION Error I am getting is - Msg 3059, Level 16, State 1, Line 1 This BACKUP or RESTORE command is not supported on a database mirror or secondary replica. Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally. Is this bug or I am misunderstanding something? Backup preference is set to 'Primary' on both AG. select sys.fn_hadr_backup_is_preferred_replica('mydb') returns '1' on primary replicas of both Availability groups.

**In short:** Is it possible to define separate security specifically on the forwarder server in a SQL Server Distributed Availability Group, which crosses domains, in order to allow access to the forwarded database(s)? **Longer Explanation:** For a myriad of reasons my team had settled on using a Distributed Availability Group to satisfy a need to make an existing multi-terabyte database (in an existing AG) available for another section of our company (in a separate domain), and we were under the impression we could define separate security on the forwarder environment to grant access to that domain’s accounts. We have everything working so far, aside from this security question/issue. I have combed over as much of the documentation, blogs, and forums as I care to, but I have not been able to find anything referencing this either in the negative or affirmative. I’m hoping there is some way to address this without having to find a way to add the other domain’s users to our main instance, mostly because we aren’t currently setup to support that from a domain standpoint and something like snapshot replication was/is off the table.

I have a SQL Server distributed availability group set up, and I'm currently connected to the forwarder instance. I would like to know how to use T-SQL to query the appropriate DMVs or catalog views to determine which instance is currently serving as the global primary in the distributed availability group. I'm looking for a T-SQL script or query that I can execute on the forwarder instance to retrieve this information. Any help or guidance on this topic would be greatly appreciated. Thank you in advance!

We are having constant memory dumps from our SQL 2019 cu21 instance. We upgraded this instance to cu26 (latest patch) hoping this can resolve the issue but it did not. The error log is filled with this error. We have Availability Group configured on this server. *SQL Server Assertion: File: , line=373 Failed Assertion = 'cbDecoded == cbDecodedData'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.* Any idea on what could be causing this and how to address this? History ------- This happened in staging environment. We are migrating from old staging server to new staging server. The old staging server is windows 2016 and SQL 2019. The new staging server is windows 2022 and SQL2022. There are 15 AGs (each DB is in its own AG) on old staging server and 15 AGs on the new staging server. We created a DAG between the old and new AG. All this work was done few weeks ago and working fine as we verified that by comparing the LSNs between the AGs (old and new). We verified the DAG status before initiating the failover and it was fine. After we started the migration, On the global primary (Old AG), the first memory dump reported "Access Violation occurred writing address" and the Old AG's rolled over. The instance never recovered from that event and the AG's were stuck in resolving state ( they would go offline\not synchronizing state etc). We could not even connect to both the old staging instance. The new staging instance is fine. Based on the above suggestion, I removed few AGs from the WSFC and it stabilized after that. There are 5 AGs left on the old infra and they are stable now. Other Observations ------------------ We have about 15 AGs on these servers ( 2 replicas). After dropping few AG's, the memory dumps stopped and instance has stabilized. We dropped the AG's sort of randomly. So, I assume, we must have dropped the AG or few AGs that have the corrupted registry. The old staging servers were on - SQL 2019 CU 21. The first memory dump was - Access Violation occurred writing address 0000000000000000 The command in the input buffer that generated the access violation was " Drop Availability Group > Noticed this fix in CU22 which talks about Access violation issue when dropping DAG if the AG is in suspect state. I am wondering if something like that might have happened. New Questions ------------- I reviewed the registry settings for these AG, they have entries in the configuration folder. I am not sure what they is correct or expected. 1. Is it possible for DAG migration to have caused this? 2. Is it possible for something like this happen even on regular AG ( no DAG)? 3. Can we remove the AG from WSFC, if we cannot access the instance from SQL?

Three questions please: - Can we have a single DAG across 3 WSFC clusters i.e. spanning 3 AGs? - If so, is it possible to configure it such that two AGs are in the same Active Directory domain while a third one is in a different AD domain? - If configuring a single DAG across 3 AGs is not recommended, is one across two AGs in two different AD domains possible? Thank you

I recently set up a Distributed Availability Group (DAG) between an existing on-premises Always On Availability Group (AG) and a new AG in AWS. The on-premises AG serves as the primary, while the AWS AG acts as the secondary. I inadvertently configured automatic seeding without realizing that it would start seeding all the databases immediately. My issue is that I only need to replicate about 10 databases to the secondary AG, and out of those, only five databases need to be replicated to the secondary replica of the secondary AG. I tried setting "HADR OFF" for the unwanted databases, but it only works on the secondary replica. The command runs successfully on the forwarder, but the databases never actually get removed. I've found several articles explaining how to remove a database from a DAG, but they all suggest running the commands from the primary replica of the primary AG. However, I'm concerned that this approach might remove the database from the on-premises AG as well, which is not my intention. I only want to remove the databases from the secondary replica of the secondary distribution AG. So, my questions are: * Has anyone encountered a similar situation or knows how to remove specific databases from the secondary AG in a DAG setup without affecting the primary AG? * If I remove the DAG and start over, but choose to seed manually, would that allow me to select which databases get replicated via the DAG? I would greatly appreciate any insights, suggestions, or step-by-step guidance on how to achieve this. Thank you in advance for your help!

I have a scenario where I want to create a SQL Server Distributed Availability Group (DAG) that spans across two existing Availability Groups (AGs) residing on the same set of SQL Server instances. Here's the setup: - I have an Availability Group named "ag1" with two replicas: "instance1" and "instance2". - I also have another Availability Group named "ag2" with the same two replicas: "instance1" and "instance2". My goal is to create a Distributed Availability Group named "distag1" that encompasses both "ag1" and "ag2". I followed the documentation and attempted to create "distag1" on the global primary (the primary replica of "ag1"). It was created successfully. However, when I tried to join on the other end of "distag1" (the instance that ag2's listener points to), I encountered the following error: ALTER AVAILABILITY GROUP distag1 JOIN AVAILABILITY GROUP ON N'ag1' WITH ( LISTENER_URL = N'TCP://ag1lsnr:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, SEEDING_MODE = MANUAL ), N'ag2' WITH ( LISTENER_URL = N'TCP://ag2lsnr:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, SEEDING_MODE = MANUAL ); Msg 19509, Level 16, State 0, Line 38 Cannot create a distributed availability group 'distag1'. An availability group with the same name already exists. My question is: * Is it supported to create a Distributed Availability Group across two Availability Groups that reside on the same set of SQL Server instances? * If it is supported, what could be causing the error message I encountered? Are there any specific requirements or considerations I need to take into account when creating a DAG in this scenario? I would greatly appreciate any insights, clarifications, or guidance on this matter. Thank you in advance for your help!

I have a question regarding the configuration of SQL Server Distributed Availability Groups (DAGs). I understand that a DAG is used to replicate data from one Availability Group (AG) to another. However, I'm curious about the possibility of having multiple secondary replica AGs for a single DAG. Specifically, let's say I have a Distributed Availability Group named "dag1". Is it possible for "dag1" to replicate data from a primary Availability Group "ag1" to multiple secondary Availability Groups, such as "ag2" and "ag3"? In other words, can a single DAG have a one-to-many relationship with secondary replica AGs? I would greatly appreciate if someone could clarify whether this configuration is supported and provide any additional insights or considerations when setting up a DAG with multiple secondary replica AGs. Thank you in advance for your help!

I am trying to create multiple SQL instances each with its own AG (and listener) on 2 Windows clusters in a Distributed AG as follows: - Two sites - Primary & DR - Each site has a WSFC configuration - PrimCluster & DRCluster. - PrimCluster has two nodes - PrimNodeA & PrimNodeB - DRCluster has two nodes - DRNodeA & DRNodeB - Each node has two SQL AG replicas each with an AG (with its own listener) as follows: - on PrimNodeA & B - Instance1_P_AG - on PrimNodeA & B - Instance2_P_AG - on DRNodeA & B - Instance1_DR_AG - on DRNodeA & B - Instance2_DR_AG - DAG configuration asynchronous between the two clusters. - Each local AG is synchronous availability mode and holds its own AG databases. - all the secondaries are non-readable This is my first experience with multiple SQL instances on a cluster, each configured with AG and DAG between the two clusters. My Question please: - In order to create the DAG, do I need to create a DAG per AG pair as follows: - Instance1_P_AG and Instance1_DR_AG - Instance2_P_AG and Instance2_DR_AG In other words, do I need multiple DAGs or just one between Instance1_P_AG and Instance1_DR_AG will cover all instances in each cluster? Also, I would appreciate a link to an article on multiple SQL instances in a DAG. Thank you

I'd appreciate some advice please. I am about to get to work on implementing a SQL 2019 Distributed Availability Group configuration with following architecture: - Two sites - one primary & one DR - Each site has a WSFC configuration with two nodes. - DAG configuration asynchronous between the two clusters. - Each local AG is synchronous availability mode and holds the AG databases for one component of the application. - The application in question has about 3 or 4 components, each with its own set of back-end databases - The databases for each application component are hosted in one SQL instance - so the 3/4 SQL instances are all hosted on the pair of nodes on each cluster. - each AG has it's own listener, hence one listener in each instance, all on the same local cluster. - all the secondaries are non-readable This is my first experience with multiple SQL instances on a cluster, each configured with AG and DAG between the two clusters. Could you advise me please if there are any specific issues I need to pay particular attention to in such a configuration? Thank you.

Hello SQL Server Experts, I'm in the process of configuring a Distributed Availability Group (DAG) in SQL Server and am looking for guidance on implementing it with manual seeding (back up restore) instead of the automatic seeding. The DAG I plan to set up involves two normal Availability Groups (AGs) named ag1 and ag2, each serving as a replica within the DAG. My aim is to replicate the following topology:

+--------------------+         +------------------------+
|  WSFC1             |         |   WSFC2                |
|  +--------------+  |         |  +------------------+  |
|  | AG1 (P)      |  |         |  |  AG2 (S)         |  |
|  | +---------+  |  |         |  |  +-------------+ |  |
|  | |node1 (P)|  |======DAG=====>|  |node3 (P)(F) | |  |
|  | +----+----+  |  |         |  |  +-----+-------+ |  |
|  |      |       |  |         |  |        |         |  |
|  | +----v----+  |  |         |  |  +-----v-------+ |  |
|  | |node2 (S)|  |  |         |  |  |node4 (S)    | |  |
|  | +---------+  |  |         |  |  +-------------+ |  |
|  +--------------+  |         |  +------------------+  |
+--------------------+         +------------------------+
In this topology, we have:
- 4 nodes: node1, node2, node3, node4
- 2 WSFC clusters: WSFC1 and WSFC2
- 2 AGs: AG1 and AG2
- 1 DAG, from AG1 to AG2
- (P) = primary replica
- (S) = secondary replica
- (F) = forwarder

The official Microsoft documentation (Configure Distributed Availability Groups) primarily covers the setup using automatic seeding. However, I want to utilize the traditional backup and restore method. Unfortunately, I am uncertain about the correct sequence of operations regarding when and where to take backups and where to restore them within this topology. Could anyone provide a step-by-step guide or clarify the process in plain English on how to create a DAG using manual seeding? Below is what I have pieced together so far, but I'm stuck on the ordering and appropriate steps: * Enable the Always On Availability Groups feature on all instances. * Create an endpoint on all instances. * If ag1 and ag2 span across different domains, configure the endpoints to authenticate via certificate. * Take a full and log backup on node1, then restore them to node2, node3, and node4 using the NORECOVERY option??? * Create ag1 on node1. * Create ag2 on node3??? * On node1, create the DAG that includes ag1 and ag2. I'd greatly appreciate any help or resources that could point me in the right direction. Thank you for your time and assistance!

I am looking for a step by step walk through of how to setup a distributed availability group with manual seeding. I can get the automatic seeding working but when I try to manually seed I cannot get the secondary database into the AG on the forwarder. If I add the distributed AG to the secondary before I try to add the database to the regular AG I get the following message: Msg 41190, Level 16, State 7, Line 22 Availability group 'MYDB' failed to process add-database command. The local availability replica is not in a state that could process the command. Verify that the availability group is online and that the local availability replica is the primary replica, then retry the command. If I try to add the DB first without joining the Distributed AG on the secondary, I get the following message because it thinks it should be a primary: Msg 927, Level 14, State 2, Line 22 Database 'MYDB' cannot be opened. It is in the middle of a restore. I have none of these problems with automatic seeding. Everything just magically works. All of the examples I find online use automatic seeding. thanks in advance

I am troubleshooting latency for a specific replica in a Distributed AG setup (with 2 remote replicas). Looking at perfmon I see clear indication as to why one replica shows delay. I see Bytes Sent to Transport/sec is in sync with one replica (*Red & Purple lines*) - but not on the other. (*Green Line*). I was wondering what could contribute to this behaviour. I checked there is no delay at Flow Control Gates. Edit: SQL Server Version is 2019 (CU22) Answers to questions from comments: - Is this for the same AG and a different distributed replica? Different AGs? > This is for one single AG only. - Where are the replicas? > All 3 distributed replicas are in different DCs across the globe. Each AG has 2 nodes (sync mode). - Are the distributed replicas setup the same (sync/async)? > Yes, both are ASYNC - Is the hardware the same? > YES - Are all of the databases on the forwarder side ok? > YES

We have Distributed AG across two windows clusters. - Clus01 - Has an AG set up with no DB inside it (AG1) - Clus02 - Has an AG set up with no DB inside it (AG2) - Clus01 - Has a DistAG set up with AG1 (primary) and AG2 (secondary) We - Joined Clus02 to the DistAG - Added a database to AG1 on Clus01 - Restored this DB in recovery to AG2 - Added this DB to AG2 on Clus02 (Showing as Primary, but really is secondary within the DistAG) DB on Clus02 was now working as expected...any updates on DB on Clus01 would use DistAG to update Clus02 DB. We often refresh this environment from live (this is preprod). so we have to restore the DB onto CLus01. For our normal AGs, we just remove the DB from CLUS01, restore to CLUS01, backup and restore to CLUS02 and join to AG again. For DistAG, I cannot 1. Remove the DB from AG2 on CLUS02. ALTER AVAILABILITY GROUP [AG_G2CoreReporting] REMOVE DATABASE [Genesis]; GO We get the error > Msg 41190, Level 16, State 8, Line 6 Availability group 'AG2' failed to process remove-database command. The local availability replica is not in a state that could process the command. Verify that the availability group is online and that the local availability replica is the primary replica, then retry the command. 2. Remove the DB from AG1 on CLUS01. DB on AG2 on CLUS02 goes into not syncronizing state and I cannot remove (same error as above), so cannot restore DB over the top. 3. Remove the replica on the DistAG by running on CLUS01 ALTER AVAILABILITY GROUP [DistAG] REMOVE REPLICA ON N'AG2'; We get the error > Msg 15151, Level 16, State 1, Line 5 Cannot alter the availability group 'AG2', because it does not exist or you do not have permission. Is there anyone that knows how I can refresh an environment with DistAG without having to remove the whole DistAG and set it up again?