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,475
questions
0
votes
1
answer
35
views
How to redirect read queries transparently to Secondary replica in Always on [duplicate]
We have Always ON setup for our database. Users are connecting to our database using listener URL.
We are currently asking users to use ApplicationIntent = ReadOnly to redirect users to secondary ...
- 115
0
votes
0
answers
37
views
High latency of syncing for one node of AlwaysOn cluster even though network is fine
I have added a node to an AlwaysOn cluster but the sync process is slow. The node is a DR node (in adifferent data center) but the log_send_rate is so slow compared to other clusters DR node with same ...
0
votes
1
answer
13
views
Are there any implications of setting the Availability Group FailoverPeriod in FCM to zero?
In Failover Cluster Manager, in the Failover tab of the Availability Group Properties, the Availability Group default value for FailoverPeriod is 6 hours.
What is the best value for this setting?
Is ...
- 285
0
votes
2
answers
29
views
Failover taking long on one of secondary replica but fast in another replica in always on [closed]
I have one primary and two secondary replicas. Both secondary replicas are synchronous commit. At the time failover one of secondary replica faster than other replica.
Note:- secondary replica ...
- 11
2
votes
1
answer
27
views
Is there anything to be aware of in changing the underlying physical storage of database files in a SQL AG system?
The underlying physical storage in my SQL Server 2019 Availability Group databases needs to be changed on one of the nodes of my cluster.
Drive name, file path all remain the same for the database ...
- 285
0
votes
1
answer
72
views
Name in sys.availability_groups_cluster returns double character length
'select name, len(name) as length from sys.availability_groups_cluster'
Returns some double values:
name
length
AG_QBUS_MFG01
13
AG_QBUS003
20
TEST_ONLY
18
- 33
1
vote
1
answer
36
views
Bringing an Availability Group online after taking it offline
Answer in the following thread says:
activate Service Broker on a database in an Availability Group
"You need to set RegisterAllProvidersIP to 0 Thereafter take the availability group offline and ...
- 11
3
votes
1
answer
153
views
Stored Procedure Tuning Help
Actual plan: https://www.brentozar.com/pastetheplan/?id=SJviG_y1h
I'm looking for advice on where to start with tuning the above stored procedure. We need it to complete in as near to 1 second as ...
- 31
0
votes
0
answers
17
views
Using SQL Server data files in Microsoft Azure for historical data?
It is possible to create new data file for database which is stored in Azure BLOB storage. In the documentation is said that:
Always On availability groups are supported as long as you do not add new ...
- 3,717
1
vote
1
answer
52
views
Multi-Subnet AlwaysOn Cluster issue - Unable to add 3rd node
We are trying to build a multi-subnet cluster.
VM1 - 172.16.0.11 and 172.16.0.12
VM2 - 172.16.0.22 and 172.16.0.23
VM3 - 192.168.0.111 and 192.168.0.112
Windows Failover Cluster - WINCLUSTER - 172.16....
- 362
-1
votes
1
answer
36
views
Changing NICs from 1Gb to 10Gb in an availability group
We have a two-node Always On Availability Group set up. Both nodes are on SQL Server 2016 SP3.
Primary server has two 1Gb NICs. Secondary has two 10Gb NICs.
Both primary and secondary have a default ...
- 362
0
votes
1
answer
41
views
Listener Configuration for Availability Group
Apart from the Availability Group Listener, is there any other SQL Server listener object that is configured and is in Active Directory?
The Communication endpoint (which listens on port 5022) needs ...
- 285
0
votes
0
answers
35
views
Service Broker endpoint error in errorlog
Please can someone explain the meaning and source of the error message I'm getting in my errorlogs?
The Service Broker endpoint is in disabled or stopped state.
It is an AG cluster on WSFC with two ...
- 285
0
votes
2
answers
93
views
AG secondary is out of sync for hours after index maintenance
We have a 3TB OLTP database running Siemens Opcenter MES software at one of our manufacturing plants in China. This is setup in an Always-On Availability group with one secondary node. As this is a ...
-1
votes
1
answer
36
views
Does running dbcc checkdb on the restored backup file be an alternative to running dbcc checkdb on that server? [duplicate]
I have 2 node AG server.
Say db name is TestDb.
Dbcc checkdb is run on TestDb weekly on both servers.
Daily full and hourly log backups run on secondary server. After backup th, db is restored and ...
- 3,153
0
votes
1
answer
35
views
Backup Preferences in Availability Group
Can someone kindly explain this to me please. My understanding is that we can not run differential backups on the secondary replica of an AG, so if the business requirements are such that differential ...
- 285
0
votes
1
answer
63
views
Latency with hadr_capture_log_block in SQL Server AlwaysOn AG
There is 2 node AlwaysON AG in synchronous mode. In the morning (09:00 am) we noticed that queue from application side started to grow, in SQL Server there were high HADR_SYNC_COMMIT wait type.
Using ...
- 1
0
votes
1
answer
21
views
Long running full backup in secondary does not allow log truncation in primary replica
I have a 2 node WSFC and I have SQL Server 2017 synchronous AG with automatic failover.
My log file keeps on growing despite frequent successful log backups and dbcc sqlperf('logspace') shows 99.9% of ...
0
votes
0
answers
37
views
SQL Services delay to come back up after a server reboot in a Windows cluster
We have just built a 2-node Windows Server cluster and installed SQL 2019 configured with an Availability Group on both replicas.
I am still working on it, it is not live yet.
In the course of testing ...
- 285
-1
votes
1
answer
45
views
Availability Group Integrity Check [closed]
Please can someone point me in the right direction for doing an Availability Group integrity Check?
I have looked at Ola Hallengren notes on it in https://ola.hallengren.com/sql-server-integrity-check....
- 285
1
vote
2
answers
53
views
Registration of SPNs for Availability Groups
Manually registering SPNs for Availability group -
Do I need to register SPNs for the AG Listener as well as the individual nodes, for both the instance name and the listening ports for the managed ...
- 285
1
vote
1
answer
105
views
Having problems joining a database to an Availability Group on the secondary replica
Trying to join a database to an AG on the secondary replica, I'm getting the error:
The remote copy of database "ABC" has not been rolled forward to a
point in time that is encompassed in ...
- 285
-2
votes
1
answer
54
views
Why is SQL AG failover not working when service restarted on new primary?
I have all the default WSFC and AG settings. Sync and auto-failover mode.
AG has 2 nodes: Node1 & Node2
Node1 is primary.
I restarted the sql server service on Node1 which resulted in failover to ...
- 3,153
0
votes
1
answer
57
views
I am getting a strange cluster name in Cluster Events error message
I need some help please.
I have a 2-node Windows cluster configured with WSFC (cluster name = ABCCluster).
Node 1 named Node1
Node 2 named Node2
SQL 2019 instance installed and named INSTANCE
...
- 285
-3
votes
2
answers
112
views
Restarting SQL service on primary causes failover of AG to secondary even when the setting says that it must try a reboot prior to failover
In WSFC resource properties there is a config to attempt a restart prior to failover.
For testing purpose, on primary node (Node1) I restarted the sql service (from sql configuration manager), and the ...
- 3,153
1
vote
2
answers
113
views
SQL HA Cluster TempDB Version Store blocking on secondary Replica due to open transaction?
I am currently investigating a repeating error which occurs on the secondary Replica of our 2 node Alwasy on High Availability cluster. The Replica is set up with Read-Intent only because we use a ...
- 11
0
votes
1
answer
28
views
AG Replica on anther WSFC
Is it possible that an AG spans two different WSFC failover clusters, i.e. one of the nodes is part of another WSFC failover cluster.
So is it possible that one of the replicas is hosted on a node ...
- 743
0
votes
1
answer
38
views
Does secondary long running query and primary DDL operation result in blocking of redo thread only if table is participating in long running query?
In a SQL AG scenario, if during a long running query on the readable secondary, a DDL operation is performed on primary, then I understand that the redo thread will get blocked until the long running ...
- 3,153
0
votes
1
answer
27
views
Split brain scenario
We have a 4 node availability group with 3 nodes at primary site and 1 node at the DR. Suppose the Primary site entirely goes down and we have to do a forced failover at the DR.
Now if there is no ...
- 1,019
0
votes
1
answer
44
views
More than 3 Subnets with WFC and Availability Groups
I have a system design where I will have a single WFC node using 2 Server 2016 boxes and a SQL Server 2019 Availability Group. I've followed instructions easily found for setting this up, such as ...
- 3
0
votes
1
answer
19
views
Distributed Availability group with witness server for quorum
I have experience in setting up availability group and have used witness server when the nodes are even to achieve quorum.
One of the vendor provided a layout which will have two availability group ...
- 1,019
0
votes
1
answer
30
views
Availability Groups - TL back up
I have some questions on Availability Group
During server maintenance, if I fail over to Node2 and rebooted Node1. When Node1 comes back will the database sync automatically ?
Do i need to take ...
- 11
1
vote
1
answer
38
views
Database Backups Query for SQL Server High Availability Groups
I am use to working with SQL Server Database Mirroring but now we are upgrading to use High Availability Groups.
Whenever I had to restore the mirroring on one of the SQL Server database I use to take ...
- 67
1
vote
1
answer
24
views
How to manage the log file size on secondary replica (log backups)?
I am learning about SQL AG.
Currently we have 1 SQL server and we run weekly full and hourly log backups.
The log backups truncate the log and prevent from filling up the disk.
When I configure an AG ...
- 3,153
6
votes
1
answer
112
views
AG available from 1st node, but not 2nd node
I am in the process of configuring an Availability Group in SQL Server 2019 on Windows Server 2019.
We have two Windows Servers, UT01 and UT02, configured with a single network adapter, using static ...
- 68.5k
0
votes
1
answer
60
views
What are the settings to ensure that AGs failover together (or dont failover at all)?
I have an AG (say AG1) setup with 2 node wsfc (svr1/svr2) and it has got 20 dbs.
I am being asked to provide 3 of these as a readable copy on another sql server which is located in a separate wsfc (...
- 3,153
0
votes
1
answer
41
views
How does async manual failover mode handle data sync issues?
In async AG, with no automatic failover, what happens if the primary fails to harden the log to disk (due to say power failure), and secondary has received the log record and hardened it?
So, when the ...
- 3,153
2
votes
2
answers
49
views
For a query that changes data, does the change get written to tlog prior to sending it to secondary (AG) and primary data file on disk?
So there are 6 things involved when a transaction is made that writes data:
Tlog in memory
Data page in memory
Tlog on disk
Data page on disk
Send the tlog to secondary AG
Inform the client that ...
- 3,153
1
vote
1
answer
90
views
Does sql server read scale AG technology allow to setup 2 AGs such that the source of both are different FCI sql servers?
I have got 3 sql server's (2019 enterprise edition) configured in 2-node FCIs. Each FCI is in its own WSFC.
FCI1 (Svr1+Svr2)
FCI2 (Svr3+Svr4)
FCI3 (Svr5+Svr6)
FCI1 and FCI2 has got the OLTP dbs. FCI3 ...
- 3,153
0
votes
1
answer
24
views
How does the async AG technology realise whether or not data has reached the secondary?
In sync AG the primary waits for an acknowledgement from secondary.
Where as in async AG the primary doesn't wait for an acknowledgement.
In the later case, suppose the acknowledgement is lost in the ...
- 3,153
0
votes
2
answers
53
views
SQL Server Always On - security updates without DB disconnecting from application
Our real-time communication application relies on database access, and it cannot be disconnected from the DB when running.
Recently our customer’s DB maintenance team notified us that the DB will be ...
Michal.Jan008
2
votes
1
answer
54
views
Effects of SQL AG failover during full back is in progress
I have a 2 node automatic failover SQL server AG setup and backups run on the secondary replica. I wanted to know what happens if a failover is triggered for some reason and at the same time full ...
0
votes
0
answers
41
views
SELECT queries are not being sent to read-only secondary replica in 2 node AG [duplicate]
Apologies if this has already been asked. I've read through the BOL and other blog articles on this, including https://www.sqlshack.com/how-to-configure-read-only-routing-for-an-availability-group-in-...
- 1,083
0
votes
1
answer
63
views
Public role owns my availablity replica
I'm facing a strange issue in one of my availability replicas.
Always on is runing on SQL Server 2017. The availability group is composed of two nodes.
I was checking the owner user of both replicas ...
- 1
0
votes
0
answers
30
views
How to migrate from clustered SQL Servers 2016 to 2019?
I have two VMs with clustered (AG) SQL Servers 2016. Replication between servers is done (I think so) using AG alone, distributor/publisher/subscriber folders in SMSS are empty. I need to migrate this ...
- 1
0
votes
2
answers
133
views
How does SQL Browser work when default port is missing and AG Listener specifies port 1433?
Background:
To keep things simple, we have a 2 node cluster where each node in the cluster has 3 instances of SQL installed: default + 2 named. I understand instance stacking is bad and it is ...
- 195
0
votes
1
answer
102
views
Check with code if an instance is a readable secondary
How can I query if an instance is a readable secondary in an AG? E.g. to check if the instance is the primary for an AG then we can use: select sys.fn_hadr_is_primary_replica('AGDatabase'). Is there a ...
- 743
0
votes
1
answer
161
views
Is anyone aware of issues where uninstalling SQL 2012 Native Client has broken Failover Clustering?
We were recently asked to remove unsupported software from our servers and the list included SQL 2012 Native Client. Version 11 was installed, so we removed it from test & dev servers without any ...
- 13
0
votes
1
answer
55
views
AlwaysOn listeners across subnets
Can someone help me clear something up with regards to AlwaysOn listeners across 2 subnets.
Windows Server 2019
SQL Server 2019
This is in Azure
I have created a multi-subnet AG.
The listener has 2 IP ...
- 131
0
votes
0
answers
71
views
Alert Failover AG
I put the alert 1480 for the notification of the Failover AG
I need to add in the notification the primary replica and the secondary replica
Do you have an idea please?
EXEC msdb.dbo.sp_add_alert @...
