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.
1,586
questions
0
votes
1
answer
25
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 ...
0
votes
2
answers
27
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 ...
- 1,738
0
votes
1
answer
14
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 ...
- 327
-1
votes
0
answers
21
views
Unable to remove the transactional replication publisher after a crash
After a crash in a transactional replication process, I lost the distributor and the subscriber (in the same instance), at the moment I am trying to remove the publishers (Always ON - different ...
0
votes
1
answer
21
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
...
- 327
0
votes
1
answer
59
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, ...
- 1
0
votes
1
answer
46
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 ...
- 7,386
0
votes
2
answers
30
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 ...
- 1
0
votes
1
answer
43
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, ...
- 3,595
1
vote
0
answers
51
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 ...
- 245
0
votes
1
answer
27
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 ...
- 327
0
votes
1
answer
42
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 ...
- 3,595
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 ...
- 1,263
0
votes
1
answer
26
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 ...
- 327
0
votes
2
answers
38
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 ...
- 16.2k
0
votes
0
answers
94
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 ...
- 1
0
votes
1
answer
48
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 ...
- 3
1
vote
1
answer
59
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 ...
0
votes
1
answer
18
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 ...
- 1
1
vote
2
answers
90
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 ...
- 11
0
votes
1
answer
82
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 ...
- 3
0
votes
1
answer
34
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.
...
- 2,682
0
votes
1
answer
54
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....
- 16.2k
0
votes
1
answer
21
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 ...
0
votes
0
answers
32
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 ...
0
votes
1
answer
52
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) ...
- 2,682
0
votes
0
answers
43
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 ...
- 143
0
votes
1
answer
40
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 ...
- 2,682
-1
votes
1
answer
89
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 ...
- 746
0
votes
1
answer
41
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....
- 463
0
votes
1
answer
104
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 ...
- 327
0
votes
0
answers
41
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 ...
- 53
0
votes
2
answers
30
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 ...
- 117
1
vote
1
answer
36
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 ...
- 231
0
votes
1
answer
79
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 ...
- 3,595
0
votes
1
answer
85
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 ...
- 209
0
votes
0
answers
129
views
SQL Server 2022: AlwaysOn AG databases can not sync after applying CU6 or higher
The setup is:
OS - Win Server 2022 & MSSQL - SQL Server 2022.
AlwaysOn Availability Groups are enabled on two (primary + secondary) replicas and use WSFC.
Several availability groups are created ...
- 1
2
votes
0
answers
116
views
How to use Custom Certificate for SQL Server Availability Group Endpoint Authentication
I am setting up a SQL Server Availability Group using the commands provided in the Microsoft documentation. The process involves creating a certificate, backing it up with the private key, and ...
- 53
3
votes
1
answer
72
views
Can I use different endpoint URLs for the primary replica in a SQL Server Availability Group?
I am setting up a SQL Server Availability Group with three synchronous replicas within a cluster (one primary and two secondary) and an additional asynchronous replica from another cluster in another ...
- 53
2
votes
1
answer
89
views
Understanding SQL Server Availability Groups: Automatic Seeding of Databases
I'm delving into SQL Server Availability Groups (AGs) and have come across a fascinating feature that seems to simplify the process of setting up replicas – the concept of automatic seeding. From my ...
- 2,682
0
votes
1
answer
66
views
Log .ldf file not truncate after several times of log backup on secondary database
i have always on setup in production environment. Scheduled backup (transaction log backup) already in place every 2 hours using netbackup on secondary database.
The issue i am facing is sometimes the ...
- 11
1
vote
0
answers
54
views
Automatically reconfigure SQL Server Reporting Services to connect to primary Availability Group node after failover
I have a SQL Server Availability Group for HA and a Distributed Availability Group for DR.
I also have SQL Server Reporting Services running on all Availability Group nodes.
For various historical ...
- 70k
1
vote
0
answers
50
views
Always-On listener not working correctly
I have an Always-On architecture with two nodes and two AG. One of AG working correctly but other AG's listener only directs to the primary node. When I fail it to the secondary node, the application ...
- 56
4
votes
2
answers
567
views
HADR_SYNC_COMMIT waits on SQL Server
Let me preface this post by saying that I am missing a few events from my trace, but I have since added them for the next time this occurs.
Lately we have been seeing weird spurts of HADR_SYNC_COMMIT ...
- 245
0
votes
0
answers
42
views
sqlserver alwayson audit setting
I am running SQLSERVER ALWAYS ON 2019 ENT VERSION.
I'm trying to STOCK an audit at the database level in SQLSERVER.
In the master, it was confirmed that the log audit was left at DATABASE LEVEL.
...
dante
0
votes
1
answer
174
views
best practice for side by side sql version upgrade with Availability groups
We are attempting our first ever side by side upgrade of a 2 node(multi-subnet, enterprise edition) AG from sql 2016 to 2019. I have some questions on how best to approach this:
Do we create a brand ...
- 257
0
votes
1
answer
58
views
Creating AGL for Named Instance on SQL Server 2016 [duplicate]
I have installed a Named Instance on an existing server so the set up is like the following:
Server 1:
QA01 - Default Instance
QA01\BU0 - Named Instance
Server 2:
QA02 - Default Instance
QA02\BU0 - ...
- 573
0
votes
1
answer
64
views
How to calculate the time for a transaction start to commit to be communicated to the client application?
I have a 2 node AG in sync mode.
AG transaction delay value is about 60ms/s. This is the sum of all transactions delays in that second.
The transactions/sec value is around 1000.
So the delay per ...
- 3,595
0
votes
0
answers
26
views
Best Strategy to copy database using Secondary Database (not touch primary) to dedicated database server
i have application team that have dedicated database server for datawarehouse. I want to know what is the best way to copy database from secondary.
Current Environment
Always On
1 Primary
1 Secondary ...
- 11
0
votes
1
answer
112
views
Configuring SQL Server 2019 Always ON between 2 Azure reqions
I have SQL Server on Azure VM which I want to replicate across a separate region. I will only be using 2 servers, each in a separate region. I have the following questions as I need to clear all ...
- 1