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,545
questions
0
votes
0
answers
28
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 ...
- 42
4
votes
2
answers
268
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 ...
- 235
0
votes
0
answers
24
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
59
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
47
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
42
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,442
0
votes
0
answers
23
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
47
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
0
votes
1
answer
50
views
In an Always On configuration, why do I have to open ports of the listener as well for the sql server engine itself for the listener ips?
We use an SQL Server 2019 Always On setup.
2 sql server 2019 nodes in same subnet : server01, server02
1 sql server 2019 node in other subnet : server03
We decided to create only 1 availability group ...
- 1
-1
votes
1
answer
57
views
Denying access to some databases
I need to deny a user access to certain databases in the SQL Server 2019 instance. The user should however be able to migrate all the other databases in the instance to another instance.
What is the ...
- 303
0
votes
1
answer
94
views
AlwaysOn AG failure when Secondary is rebooted
I have a 3 node AlwaysOn AG
Node 1 - Synchronous
Node 2 - Synchronous - Primary
Node 3 - Asynchronous
Quorum Configuration:
Node 1 - Vote
Node 2 - Vote
Node 3 - NO VOTE
File Share Witness - VOTE
...
- 319
0
votes
0
answers
58
views
How to activate MSDTC for SQL Server 2019 failover cluster
I have configured a failover cluster and always on SQL Server 2019 high availability group on 2 nodes and operating system with Windows Server 2019.
I am trying to configure the MSDTC properties of ...
- 1
0
votes
1
answer
37
views
Why do I need to create endpoints after configuring a aoag?
So recently on new servers we are building, after creating the aoag and add servers, they don't go in sync, and I need to manually run this:
CREATE ENDPOINT Hadr_endpoint STATE=STARTED
AS TCP(...
- 7,374
0
votes
2
answers
122
views
Adding a Windows Server 2016 computer to a Windows Server 2022 Failover cluster
Is it possible to join a Windows Server 2016 computer to an existing Windows Server 2022 2-node Failover cluster? The cluster is domain-independent.
Hypothetical setup:
Node 1 (windows server 2022 + ...
- 47
0
votes
0
answers
38
views
What is the significance of failback setting in a SQL AG?
I have a 2-node AG with WSFC setting configured as allow failback between 3am and 7am.
I restarted node 1 at 1am which caused an automatic failover to node 2. However there was no automatic failback ...
- 3,442
0
votes
1
answer
35
views
Can we do restoration using full backup on secondary and , differential and log backup on primary?
i have strange requirement in my environment but i need to ask this question to meet the business objective.
Can we do restoration using full backup on secondary and differential and log backup on ...
- 11
0
votes
1
answer
166
views
How do I modify session timeout on a replica for a Distributed Availability Group?
I have a Distributed Availability Group in my production environment that occasionally has a timeout.
When it does I get the following message in the AlwaysOn_health on the forwarder:
A connection ...
- 95
0
votes
2
answers
178
views
SQL Server Always-On adding database fails with insufficient free space
We had a crash on our SQL AAG where our secondary database ended up being corrupt.
The Primary database is all goood, and still working, and was not affected in any way.
So now I try to get the ...
- 1
0
votes
1
answer
73
views
Failover in AG without listener
We have SQL Servers with versions SQL Server 2016 and SQL Server 2019. We already check that we can do failover on SQL Server 2019 without listener but I can not test it on SQL Server 2016. Do you ...
- 295
0
votes
1
answer
108
views
Configuring a Failover Cluster Fileshare Witness?
I'm relatively new to high availability groups in SQL Server, and am hoping for a bit of advice on best practices for configuration. I didn't set up this configuration, I inherited it and am trying to ...
- 101
0
votes
1
answer
68
views
Configuring Always On Availability Groups sql Server 2019 and log backup
We want migrate to sql 2019 and I did the test and set up Hight availability with twho 2 nodes,but and the set up of AG I have four option,
I gave that option a shared path for the transaction log ...
- 1
0
votes
1
answer
43
views
When using multiple basic AGs is it recommended to use 1 listener or mirroring endpoint?
I want to setup HA DR for a single instance SQL server by adding a new instance on another machine and configuring multiple basic AGs (with manual failover mode).
I want all client apps to be able to ...
- 3,442
0
votes
1
answer
103
views
SQL Server 2017 Availability Group now thinks it is in a cluster - it isn't and never was!
We have a read-scale availability group on SQL 2017 that for years has worked without fault. Now when we tried to add a new replica (with CLUSTER_TYPE=NONE), it fails because the primary now "...
0
votes
2
answers
75
views
Report to get difference in SQL Agent jobs across nodes in Availability Group
Is there a script or an article out there to check differences between SQL Agent jobs across nodes in AG and create a report based on the difference? I am not looking for dbatools.io as it syncs the ...
- 5
0
votes
0
answers
33
views
Problem with using @@DBTS between the primary and secondary in an AG
I have an ETL process that uses rowversion field and had an issue, because the ETL process was using the source database which had become out of sync in an AG, so I read the rowversion data from the ...
- 159
0
votes
1
answer
16
views
Can I distribute a distributed availability group?
Server1 and Server2 make up a NEW availability group: AGServer.
DMZ1 and DMZ2 already make up an existing availability group: AGDMZ.
Currently DMZ1 & DMZ2 are part of a Distributed Availability ...
- 33
0
votes
0
answers
8
views
Moving Primary AG Replia to new location
DBA by accident here so apologies if I'm a little vague!!
My current workplace use a three node SQL 2016 cluster, not virtual. One of the nodes is a primary replica and requires moving to a new data ...
- 1
0
votes
0
answers
45
views
DNS permissions for Availability Groups
can anyone tell me the correct permissions for the Listener and Cluster records in DNS?
At the moment I have a single subnet AG where the Listener VNN owns the cluster DNS entry and vice versa, so the ...
- 1
0
votes
1
answer
18
views
On which node must the failover manager for the AG be?
I have an availability group for Windows SQL Server. It is so that instances hosting replicas must be in a Windows failover cluster. However, if the node where the failover manager is on fails, what ...
0
votes
1
answer
40
views
do log backup on secondary node (sync or async) can truncate transaction log on primary?
i have 4 nodes. 2 nodes in sync mode, 2 nodes in asyn mode. Can i relocate all backup jobs (full, tlog) on any secondary? does the backup truncate the transaction log on primary?
- 11
-2
votes
2
answers
48
views
Does a DR plan need to be in place for an AlwaysOn availability group
I have two SQL servers 2022 in a normal availability group. I have heard that AG is capable of disaster recovery. Is this true or do I need to make an extra plan?
Another question: Why is Failover ...
0
votes
1
answer
27
views
SQL Server Sync Connection Usage
We're seeing very high waits for HADR_SYNC_COMMIT at times and after some diagnostic work with iperf3 and the like we think there might a network bandwidth problem.
However we're not central sql staff ...
0
votes
0
answers
39
views
SSRS 2016 compatibility with SQL 2017 after SQL2016 upgrade
We have an SQL Server database engine on the 2016 version. We also have SSRS in the 2016 version. I plan to upgrade the SQL database engine from the 2016 version to the 2017 for it to participate in ...
- 5
0
votes
1
answer
40
views
Does delayed durability have a possibility to cause large redo queue in a synchronous AG db?
I have an issue where-in index rebuild is causing large redo queue on the synchronous AG secondary node.
This issue occurs only on 1 db, and the only difference between this db and all other dbs is ...
- 3,442
0
votes
1
answer
43
views
Doubt on Always-on ApplicationIntend-ReadOnly and ApplicationIntend=ReadWrite
I Have Deployed Always-on, and I have routed read query into secondary server, but my question is that, how can I make connection string on application side which will divert. Insert, Update and ...
- 23
0
votes
1
answer
29
views
What is the correct value (GB) to shrink a DB?
My database has an MDF file of 56 GB and an LDF file of 263 GB, recovery model is setting to Full.
So, I would like to shrink the LDF file because we are out of space. What specific size should I ...
0
votes
1
answer
938
views
The remote copy of database has not been rolled forward to a point in time that is encompassed in the local copy of the database log
Can someone help me with the issue I'm dealing with? I had to force failover from my primary datacenter to my secondary datacenter and I forgot to disable the log backup job. Meanwhile the new ...
- 1
0
votes
0
answers
77
views
SQL Server Always On Availability Groups are green, but manual failover attempts consistently failing ("duplicate IP address", etc. in cluster logs)
We're running SQL Server 2017 Enterprise Edition on a pair of EC2 instances (we'll call them "P1" and "P3" here) in AWS. Each EC2 instance is in its own subnet, and runs two ...
0
votes
0
answers
45
views
SQL Server Replication between two AlwaysOn HADR Clusters
We got a requirement to replicate data between two different HA Clusters.
---Current Prod Environment-------
SQL Server 2016 Servers
HADR Cluster 1 (HA1) - Primary Replica (PR1), Secondary Replica (...
- 412
0
votes
1
answer
66
views
AlwaysOn Readable secondaries - Version Store
The environment is:
SQL 2019
3 Node AG
Synchronous between Node 1 & Node 2
Asynchronous to Node 3
Following this article:
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/...
- 319
0
votes
0
answers
24
views
Stored procedure execution issue on always on 3rd node
we have an alwayson three node setup. our client is executing a stored procedure on 3rd node. The stored procedure runs for 30 minutes and it is terminated and giving below error message without any ...
0
votes
1
answer
97
views
Availability group configuration problems - listener not created, error 19407 appears
To start with a description of an environment in which the problems are happening:
2 SQL servers 2022 Standard in their /28 subnet
1 AD domain for several sites, the SQL servers are members of this ...
- 347
-1
votes
1
answer
246
views
What does the question mark on availability group replica mean in SQL Server Management Studio?
I'm doing some testing with availability group in SQL Server. When I'm on the primary replica, I can see all replica nodes have a refresh icon on them.
But when I check on a secondary replica, all ...
- 1,159
0
votes
1
answer
16
views
always on ha availabity group force read only connection without specifying ApplicationIntent=ReadOnly
We have an AlwaysOn HA sql cluster set up, with read-only replicas in place, and read-only routing in place. We know how to properly route a connection to the read only replica (by specifying ...
- 101
0
votes
1
answer
18
views
Can a user connect to multiple Basic Availability Groups via listeners?
Assuming multiple, let's say 50, basic availability groups are on the primary replica. Each AG has its own listener.
The primary replica goes down, and all the AG's fail over to the secondary. How ...
- 47
0
votes
1
answer
179
views
AlwaysOn failures and failovers
This is the setup:
Firstly, it's in Azure
3 SQL Servers: SQL Server 2019 RTM CU18.
AlwaysOn configured between these 3 servers.
Server 1 & 2 in one region (Synchronous + Manual Failover Mode)
...
- 319
0
votes
1
answer
89
views
How does delayed durability impact a database which is part of synchronous availability group?
Delayed durability (forced) setting on a database ensures that client application gets the commit without waiting for the log to be flushed (written) to disk.
Synchronous AG requires that the ...
- 3,442
0
votes
1
answer
147
views
Basic Availability Groups: can each AG share a listener?
What are the potential consequences of using the same listener for each Availability Group when using Basic AGs? Is it even possible?
- 47
0
votes
1
answer
117
views
Installing failover cluster after SQL installation
Will there be any issue if WSFC is installed after SQL is already installed for the purpose of configuring Always ON?
SQL Server version 2017 Enterprise
Windows version 2019 standard
- 5
0
votes
0
answers
90
views
Easiest way to migrate Availability Group databases from one cluster to another?
Consider this scenario:
OLDCLUSTER (WSFC, all nodes SQL Server 2016 SP2)
OLDSQL01 AG primary node
OLDSQL02 AG secondary node
OLDSQL03 AG secondary node (for DR)
NEWCLUSTER (WSFC, all nodes SQL ...
