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,531
questions
0
votes
1
answer
35
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
42
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
33
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,360
0
votes
1
answer
68
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
49
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
30
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
-1
votes
0
answers
26
views
Are there any challenges with using basic AGs for multiple db in an existing FCI setup?
I'm looking to add DR capability to an existing 2 node FCI, by adding a 3rd server to be located on DR site.
Since SQL edition is standard, I can make use of to use of log shipping or Basic ...
- 3,360
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
6
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
28
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
32
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?
-2
votes
2
answers
45
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
25
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
29
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
44
views
Does availability group have 1 log send and 1 redo queue for each db, or 1 shared log send queue and 1 shared redo queue for all dbs in an AG?
As per msdn documentation, each primary replica uses 1 Log Capture thread for each primary database.
And 1 Log Send thread for each secondary database.
That means if I have a 3 node AG, with 10dbs in ...
- 3,360
0
votes
1
answer
33
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,360
0
votes
1
answer
40
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
25
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
396
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
45
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
39
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 (...
- 402
0
votes
1
answer
53
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/...
- 181
0
votes
0
answers
22
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
52
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 ...
- 331
-1
votes
1
answer
138
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,041
0
votes
1
answer
13
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
17
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 ...
- 23
0
votes
1
answer
73
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)
...
- 181
-1
votes
1
answer
51
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,360
0
votes
1
answer
90
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?
- 23
0
votes
1
answer
68
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
56
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 ...
-2
votes
1
answer
28
views
Is there support for distributor server failover?
I have done googling for this question. The answer is no. But the answer is on year 2015. How about now? Is the distributor server have failover support capibility?
0
votes
1
answer
30
views
Daily, weekly restores of a SQL database to SQL AlwaysON
Our PT environment is SQL AlwaysOn just like our Prod environment. App Team now wants to drop a PROD BAK weekly, and maybe some Daily prior to a release; then restore to the PT AO AG.
Not wanting to ...
- 35
0
votes
1
answer
27
views
How to setup transactional replication on dedicated server using AG database with auto failover
My plan is i have One dedicated server for Transactional replication as subscriber. And two nodes in Availability Group. I want to replicate one availability database on primary as publisher. When ...
0
votes
0
answers
28
views
How to use the same Azure SQL VM in two different Load Balancers
I have already configured always on availability group between VM1 and VM2 azure machines. Internal Load Balancer was created for these two VMs and mapped to AG Listener. VM2 is used for read workload ...
- 1,243
1
vote
1
answer
77
views
SQL Server - Will a blocked REDO thread cause further blocking on readable secondary replica?
I understand that a redo thread replaying a DDL operation can be blocked by a long-running SELECT query on readable secondary replica because the latter holds a Sch-S lock thus preventing the former ...
- 101
0
votes
1
answer
427
views
How to remove or disable SQL Telemetry (sqlceip)
I have SQL 2019 Enterprise Edition patched to CU18 installed on a 2-node Windows 2022 cluster and configured with an Availability Group. I have been investigating an unusual and persistent behaviour ...
- 295
0
votes
0
answers
38
views
sp_Who2 is throwing an error when executed against AAG secondary replica
I am getting following error while trying to execute sp_Who2 or select * from sys.processes against the master database of AlwaysOn secondary replica.
I am using master database but it is still ...
- 117
0
votes
0
answers
30
views
Waiting until the AOG secondary node is in sync
I'm helping ETL developers on offloading some SSIS workload from the SQL Server AOG primary node to the asynchronous secondary one.
The challenge comes when they need to write tables in the primary ...
-1
votes
1
answer
47
views
AlwaysOn proc doesn't stop, but it can't be telnet。SqlDumpExceptionHandler: Process {0} g fatal exception c0000094 EXCEPTION_INT_DIVIDE_BY_ZERO
I'm a DBA, I'm still learning, and I've recently encountered an Alwayson cluster that can't be used.
Storage failure 1796932693059087230, type 5
Event name: SQLException64
Response: Not applicable
Cab ...
0
votes
0
answers
21
views
Unable to place database back in "Online" status
I am looking for some help.
I have the following setup:
SQL Server 2019 Enterprise running a two node AlwaysOn HA
One of the HA databases now has this status next to the db name "Initializing / ...
- 157
0
votes
1
answer
113
views
Availability Group Automatic Seeding
I'd appreciate some advice please.
I have configured automatic seeding for my SQL 2019 Availability Group. My two cluster nodes each has 8GB of RAM and 4 processors. The smaller databases in the ...
- 295
0
votes
1
answer
139
views
Local vs Shared Storage for MS SQL Server's Always On Availability Groups: What's Required?
Is it a requirement for MS SQL Server's Always On Availability Groups to always utilize shared Storage SAN or a virtual shared storage like Storage Spaces Direct? Can I set up Always On Availability ...
fpSPQR
-1
votes
1
answer
60
views
What is the solution to prevent log file growth due to an AG node issue (other than fixing the node or removing node from AG)?
In an 2 node AG, suppose one node is not reachable or down or crashed, then the log file size on the primary node will keep growing.
In this situation, is there any way to prevent log growth other ...
- 3,360
1
vote
1
answer
49
views
What is the log backup behaviour after AG failover or other node unreachable?
Log backups are important for point in time recovery and to automatically truncate the log file (in full recovery model) to make space for log reuse.
But when the secondary is unreachable either due ...
- 3,360
0
votes
1
answer
72
views
Restoring One Node From an Availability Group With Recovery Pending
I have an availability group that is no longer valid (one node is gone). If I connect directly to the remaining node, the databases are all shown as recovery pending. In the Availability Group, the AG ...
- 23
0
votes
0
answers
18
views
AGs with async commit or Zerto for DR to a secondary data center?
We have several two node AGs configured across two data centers for DR. They're all async commit due to the lag, and the company is ok with minimal data loss. We're considering replacing the AGs ...
- 83
6
votes
1
answer
261
views
Distributed Availability Groups refusing to resync my FILESTREAM database with SUSPEND_FROM_CAPTURE
My home lab setup consists of four servers running in HyperV across two physical hosts. The SQL Server instances are SQLAG101, SQLAG102, SQLAG201, and SQLAG202.
SQLAG101 and SQLAG102 are members of ...
- 69.6k