Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [availability-groups]

Availability Groups are a new feature of SQL Server 2012 that provide continuous data synchronization, automatic failover and secondary read access for one or many SQL Server databases.

Filter by
Sorted by
Tagged with
0 votes
0 answers
40 views

Availability group failover-tempdb

Does anyone know if SQL AG will initiate automatic failover to secondary if tempdb is full in primary ? I believe Database level health detection is only for user database in AG
Mario's user avatar
  • 47
0 votes
0 answers
15 views

Connect to a Private SQL Always On Availability group listener with Azure App

We have a fairly standard Azure Webapp - connecting via public IP to a database server which is a VM hosted in Azure. NSG rules etc limit the traffic between the two. The VM hosting the database is ...
RPD's user avatar
  • 21
0 votes
1 answer
39 views

Preferred seconday backup in AG - what about the primary ? Shows no backups taken

I did setup the Ola Scripts sticking to https://www.brentozar.com/archive/2015/06/how-to-configure-alwayson-ag-backups-with-ola-hallengrens-scripts/ Options are: @Databases = 'USER_DATABASES', @...
Thomas D's user avatar
6 votes
1 answer
86 views

Running sp_repldone on a secondary node of an Availability Group with @reset = 1 not working

I have a two-node SQL Server 2019 Availability Group with a database that has CDC enabled for several tables. The CDC is used to keep track of transactions that have not been captured for population ...
Hannah Vernon's user avatar
  • 70.1k
-1 votes
1 answer
38 views

AlwaysOn Availability Secondary replica not work as read only

We have configured SQL Server - Always On Availability group with SQL SERVER 2022 Enterprise Edition. where there is 2 instance which are also configured with Readable secondary 'Yes'. According to my ...
user3024056's user avatar
1 vote
1 answer
43 views

How to determine the global primary instance in a SQL Server distributed availability group using T-SQL?

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 ...
Just a learner's user avatar
0 votes
1 answer
27 views

Can a SQL Server Distributed Availability Group be between AGs in different Active Directory Domains?

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 ...
PTL_SQL's user avatar
  • 341
0 votes
1 answer
81 views

SQL Server Assertion error On Availability Groups

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 ...
SqlData's user avatar
  • 39
0 votes
1 answer
42 views

Working with window patching for many SQL server with AOAG

We have hundreds of servers and 99% of them have AOAG strategy. Patching takes HOURS since we need to patch secondaries (Windows patch) , wait to complete, manually fail over to the other node, Patch ...
Racer SQL's user avatar
  • 7,386
0 votes
0 answers
27 views

Always on availability group removing nodes

We have a SQL 2014 enterprise with 4 node availability group database with 2 Synchronous commit , 1 read only Asynchronous for reporting and 1 DR asynchronous. Due to space shortage we need to remove ...
SQL_NoExpert's user avatar
  • 1,061
0 votes
1 answer
27 views

Removing specific databases from a secondary Availability Group in a Distributed Availability Group setup

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 ...
Just a learner's user avatar
0 votes
1 answer
25 views

Creating a SQL Server Distributed Availability Group across two Availability Groups on the same instances

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. ...
Just a learner's user avatar
0 votes
1 answer
28 views

Can a SQL Server Distributed Availability Group have multiple secondary replica Availability Groups?

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. ...
Just a learner's user avatar
0 votes
1 answer
42 views

Addressing TEMPDB FULL in SQL Server 2019 Availability Group instance

I'd appreciate some advice please? We had a situation where TEMPDB log was full and we were unable to connect to the Primary replica of our SQL 2019 AG from SSMS. We were however able to connect to ...
PTL_SQL's user avatar
  • 341
0 votes
0 answers
40 views

Always-On Secondary Replica Stuck in "Disconnected" State

I have 2 nodes in my Always-On and suddenly my secondary replica down and disconnected from AG. I keep getting this error : "An unexpected internal failure occurred when trying to marshal a ...
mbielsa's user avatar
  • 66
0 votes
1 answer
41 views

Linked Servers on SQL Server AGs

When setting up Linked Servers in an Availability Group, do I need to set up the Linked Servers on the AG Listener as well as the AG Nodes? I've inherited a 2 node AG that currently has a set of ...
Wayne Cochran's user avatar
0 votes
3 answers
41 views

Active-Active Availability Group configuration

Lets say we have two node AlwaysON availability group setup and each node has an availability group as primary. EG - Cluster Nodes - Node1, Node2 Availability Group AG1 is Primary on Node 1 and ...
DMDM's user avatar
  • 1,738
0 votes
1 answer
22 views

Configuring Distributed Availability Group with multiple SQL instances

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 ...
PTL_SQL's user avatar
  • 341
0 votes
1 answer
25 views

Multiple SQL Server instances each with one Availability Group on one Cluster of two nodes in a DAG configuration

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 ...
PTL_SQL's user avatar
  • 341
0 votes
1 answer
64 views

How does SQL Server synchronous commit work in AlwaysOn group to avoid data loss risk

The official documentation says that synchronous-commit with automatic failover will not lose data. But I'm still confused about how this is guaranteed. Actually I'm confused how the replication, ...
Godvania's user avatar
0 votes
1 answer
51 views

Dropping Always On Availability Group Owners

I really tried to find info on this but all I could find was this doc: Who owns your availability groups? We have many Always On Availability Groups. All of them are owned by a past user not in a ...
Racer SQL's user avatar
  • 7,386
0 votes
2 answers
36 views

AD DNS manager records duplication with SQL always on failover cluster

We are managing our SQL Always On listeners records in 2 active directory servers (they are syncing each other) and we came across an issue which we couldn't pinpoint the cause for.We are testing ...
Yishai's user avatar
  • 1
0 votes
1 answer
46 views

How to handle primary site cluster if there is a network issue due to which I invoke a forced failover to DR site?

Assuming I have AG in a 4 node cluster, with 2 nodes on primary site and 2 nodes on DR site, 1 cloud witness. Failover mode is manual. Now for some reason, suppose primary site network goes down, ...
variable's user avatar
  • 3,634
1 vote
0 answers
59 views

HADR_LOGPROGRESS_SYNC Wait Type on SELECT Queries

Very recently we started to notice a new wait type (HADR_LOGPROGRESS_SYNC) pop up in our environment and cause very brief (< 60 seconds) "outages"/issues noticeable to users. Here is what ...
Data Dill's user avatar
  • 245
0 votes
1 answer
55 views

Implementing an automatic failback in SQL Server 2019 Availability Group

I am looking at how best to implement an automatic failback in one of our AGs. Can anyone advise please, on how best to go about this? My thoughts are as follows: a failover has happened for whatever ...
PTL_SQL's user avatar
  • 341
0 votes
1 answer
46 views

What is the SQL Server behaviour if an AG node is restored from a VM snapshot?

I'm looking at 2 node synchronous AG hosted on Azure VMs. SQL server version is 2022 enterprise edition. There is file share witness as well. Each VM has a VM level nightly backup configured so that ...
variable's user avatar
  • 3,634
0 votes
0 answers
18 views

Error after failing over always encrypted database

I started capturing the following error after performing failover into secondary replica for the database which was using the always encrypted feature of SQL Server 2017 Enterprise Edition: Some ...
Rauf Asadov's user avatar
  • 1,273
0 votes
1 answer
29 views

Which SQL Availability Group Replica should the AG cluster name connect to in SSMS

My AGs are all healthy, different ones on different clusters, all SQL 2019. When I connect to the cluster name from SSMS, should I expect it to connect to the current primary replica? If so, any ideas ...
PTL_SQL's user avatar
  • 341
0 votes
2 answers
47 views

database involved in availability group - showing state as online but it is synchronizing and does not accept connection - how can I find that out?

how can I find the list of databases I actually can connect to? I am using sys.master_files to check the state of my database files however, on the secondary, the database is synchronizing and it is ...
Marcello Miorelli's user avatar
0 votes
0 answers
141 views

Microsoft SQL Always On failover cluster goes offline when adding a new node

I'm in a process of migrating my windows servers which contains SQL from windows 2012 to windows 2016. At the moment my Always On failover cluster contains 3 2012 servers and i want to replace 2 of ...
Yishai's user avatar
  • 1
0 votes
1 answer
53 views

MS SQL 2019 Always on AG - Readable to secondary

I have a 2-node cluster that uses Always on AG, with multiple AGs and databases. All the AGs have the same properties: Connections in primary role: Allow all connections Readable secondary: no I ...
fisken's user avatar
  • 3
1 vote
1 answer
64 views

SQL availability group failover latency experienced is 35 seconds, which is 10 seconds higher than older SQL Mirror failover

Encountered problem: Our use-case has 3 x Server 2022 in a topology of a domain controller with two node cluster. This is a SQL Always On AG architecture. The cluster hosts the SQL availability ...
AllYourBaseAreBelongToUs's user avatar
0 votes
1 answer
19 views

Routing List Empty, and cant add using SQL either

Question: Why is my routing list empty? I have removed the secondary from the AG and re-added back, everything is healthy and synchronized. lets assume for the example that server1 is currently the ...
Mike Kay's user avatar
1 vote
2 answers
101 views

SQL Developer edtion as a new node within an Enterprise Availability Group

We currently have a 2 node Availability Group in our test environment running SQL Server 2017 Enterprise Edition. I need to downgrade these both to Developer edition, since we are not using this in a ...
user3346030's user avatar
0 votes
1 answer
89 views

Does application need to do anything to handle database failover?

I'm trying understand database failover (mainly in SQL Server) and what handling is required from application side to have zero downtime (no failures) to handle DB failover? Assuming that DBAs have ...
Hitesh's user avatar
  • 3
0 votes
1 answer
38 views

Is It Normal to See Warnings in the Availability Group Dashboard for an Empty SQL Server Availability Group?

I've recently set up a new SQL Server Availability Group using T-SQL scripts. The setup was slightly unconventional as I initiated the availability group without including any databases initially. ...
Just a learner's user avatar
0 votes
1 answer
100 views

what to do when database is not synchronizing in availability groups?

I am using Jonathan Kehayias - Monitoring Availability Group Replica Synchronization SELECT ar.replica_server_name, adc.database_name, ag.name AS ag_name, drs.is_local, drs....
Marcello Miorelli's user avatar
0 votes
1 answer
22 views

SQL AG with Synchronus Commits Latency Conerns same network but high transactions

We want to switch an SAP SQL database cluster to SQL Availability Groups. The plan is to have two replica servers in the same data center with synchronous commits, and one in the DR location with ...
Onlinerack's user avatar
0 votes
0 answers
34 views

How Does Time Sync Affect SQL Server AG Log Sync?

I'm overseeing an Always On Availability Group (AG) setup in SQL Server, configured with five servers. One server functions as the quorum disk, with the remaining servers designated as primary and ...
Ali varzeshi's user avatar
0 votes
1 answer
64 views

Help Needed: Setting Up a Distributed Availability Group with Manual Seeding in SQL Server

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) ...
Just a learner's user avatar
0 votes
0 answers
54 views

SQL 2022 replication does not work on non-standard port

Consider the following: 3 identical Windows Server 2019 VMs running WSFC (no shared storage) 3 SQL 2022 PROD instances across those 3 VMs (using WSFC/AG for easy hostname, three instance AG) 1 SQL ...
JulioQc's user avatar
  • 143
0 votes
1 answer
47 views

Clarification on Automatic Failover Rules in SQL Server Availability Groups

I'm configuring a SQL Server Availability Group with three replicas: node1\inst, node2\inst, and node3\inst. I have set node1\inst for automatic failover with synchronous commit, while node2\inst and ...
Just a learner's user avatar
-1 votes
1 answer
95 views

Redo thread getting blocked while capturing index physical stats

Noticed REDO thread getting blocked for the whole period of running following query, what could be the reason? It is on SQL Server 2019 CU21, The blocking was for almost more than 1hr+, for the entire ...
S.D.'s user avatar
  • 746
0 votes
1 answer
55 views

CDC cleanup doesn't work even when using sp_cdc_cleanup_change_table which completes successfully

I'm running the query below to find CDC tables with data beyond the configured retention depth: SELECT DB_ID() AS database_id, ct.capture_instance, object_id, source_object_id , rcounts....
Eitan Blumin's user avatar
0 votes
1 answer
147 views

Adding Database to SQL Availability Group seeding status

I have a SQL 2019 AG cluster and I add and remove databases to the AG successfully with automatic seeding, using the Wizard. We are now trying to develop Powershell automation for the process. So what ...
PTL_SQL's user avatar
  • 341
0 votes
0 answers
42 views

AG Listener goes offline during comms link failure

I wanted to try and get some clarity on some behavior we recently experienced with a number of our production clusters during an event where we lost both communication links. Ours clusters span two ...
bn25's user avatar
  • 53
0 votes
2 answers
35 views

setting up WSFC for AlwaysOn with one node acting as read replica for both instnaces ( AWS EC2)

I am trying to setup WSFC so that I can configure AlwaysOn. I have 3 nodes(A,B,C) and each has one instance (1, 2 and 3). I am trying to setup WSFC between A and C and another WSFC between B and C ...
SqlDBA's user avatar
  • 117
1 vote
1 answer
37 views

Secondary Replica can not be joined again

I had three replica in my availability group without cluster. The installed SQL Server version is: Microsoft SQL Server 2019 (RTM-CU14) (KB5007182) - 15.0.4188.2 (X64) Nov 3 2021 19:19:51 ...
Meyssam Toluie's user avatar
0 votes
1 answer
96 views

What could be causing tempdb version store growth on primary node in the absence of open/active transactions on readable secondary db?

This is a SQL server 2022 CU10 enterprise edition server, 2 node AG with readable secondary. 30 user dbs are in the AG. Via an inhouse SQL monitoring tool I observed tempdb version store growth on ...
variable's user avatar
  • 3,634
0 votes
1 answer
143 views

SQL Availability group primary not visible from secondary via sys.dm_hadr_database_replica_states

Trying to create a single pane of glass for all our servers as to the Availability Group State and Health. I want to do this by writing an app and calling t-sql statements to gather data from each of ...
Keith Clark's user avatar

1
2 3 4 5
32