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
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 ...
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 ...
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
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 ...
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 ...
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 ...
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 ...
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 ...
user avatar
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 ...
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 ...
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 ...
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 @...

1
2 3 4 5
30