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
1 vote
1 answer
168 views

SQL Server 2017 AlwaysOn Failover Cluster issue

I have 2 Windows 2012R2 VM's running on Vmware ESXi 7 with VMDK's as the SQL disks. I have a SQL 2017 AlwaysOn Cluster running on Server 2012R2. there is no any running backup (SNAPSHOT) PROD : 10....
0 votes
1 answer
21 views

Help Understanding huge discrepancy in LDF file growth on AAG between primary and secondary

I currently have a SQL Server 2014 instance with a very basic AAG setup. There are 2 nodes in the AG. The Log drive on the primary has 90% free space, and the log drive on the secondary has 5% free ...
-5 votes
1 answer
39 views

Does enabling readable secondary in the availability group put any load on the primary server's tempdb?

Enabling readable secondary will result in addition of 14byte extra information to each row in the primary as well as secondary. This will also automatically enable the snapshot isolation level on the ...
0 votes
2 answers
41 views

Can't identify which server is primary and which is secondary in a SQL Server Availability group

I'm writing an application that queries the backup history for a number of databases on a number of SQL Server 2017 servers. Assuming a pair of servers (SQL001 and SQL002) and a database called "...
3 votes
0 answers
33 views

Always On Availability Groups connection with secondary database established for primary database

I'm seeing this messages almost everyday in one of the production server. I don't feel comfortable seeing this..is there anything we can do to suppress this messages ? though it's showing No user ...
-3 votes
1 answer
51 views

Does max SQL memory include SQL AG memory requirements?

I have sql server 2019 enterprise on windows server 2019, with 30 databases totalling 500GB size. RAM is 100GB. Of this 94GB is allocated as Max sql server memeory. So 6GB remains for OS. There is ...
0 votes
1 answer
21 views

Software to help automate windows os patching with SQL Server high availability/failover clusters

is there a third party tool or a way to automate installing windows updates on windows clusters with sql server high availability groups installed? right now we have a manual process of installing ...
-4 votes
1 answer
32 views

Why is SQL AG sync commit mode classified as no data-loss HA solution even when the session timeout can make the mode async?

Assuming the SQL AG is in sync commit mode. Suppose the secondary becomes unresponsive or has secondary has network issues, then upon the session timeout expiry the AG will automatically get converted ...
1 vote
1 answer
44 views

Unlike FCI, doesn't AG make use of heartbeat to check for server down?

SQL AG in WSFC checks whether server is down via the lease timeout Where as SQL FCI checks whether server down via heartbeat Does the SQL AG not make use of heartbeat at all? Below is a sample log ...
1 vote
1 answer
26 views

Does SQL server provide automatic page repair in the async commit mode AG?

I understand that SQL AG supports the automatic page repair when the primary is synchronized with the secondary. Suppose the AG is configured in an async commit mode, then does also mean that the ...
-1 votes
0 answers
34 views

Does restart in FCI/AG mean node restart or sql server cluster aware services restart?

There are failure condition levels for FCI and AG. I read the keyword restart. Just want to confirm whether restart means node restart or sql server cluster aware services restart?
-4 votes
0 answers
34 views

How to check whether there was an manual or automatic failover in an AG? [duplicate]

I have configured SQL AG (WSFC). How to check whether there was an manual or automatic failover in an AG? Is there any specific extended event or log that tells this?
2 votes
2 answers
1k views

Database Log Reuse Wait shows AVAILABILITY_REPLICA but database is no longer part of an availability group

My database log is growing out of control, I recently removed the database from an availability group by when I look at the log reuse wait description it shows "AVAILABILITY_REPLICA" I have tried to ...
0 votes
1 answer
19 views

How to check whether or not dynamic quorum and dynamic witness is enabled on WSFC?

Dynamic quorum and dynamic witness are abilities of the WSFC to dynamically adjust the vote of the nodes and witness to prevent unnecessary failovers. Where can I check whether the WSFC configuration ...
9 votes
6 answers
46k views

Availability Group how to determine last Failover time

I'd just like to know if there is any way to query when the AG group failed over. eg - this is the Primary Replica now, but I'm pretty sure it was the secondary yesterday? How can i find when the ...
0 votes
2 answers
27 views

How to use async AG to test a migration without doing a failover?

I want to use an async AG to test SQL server migration on SQL server 2017 standard edition. There are 50 databases so the plan is to configure 1 AG per db in the async mode without WSFC. To test the ...
0 votes
1 answer
201 views

Increasing tempdb data files on 2 node Availability Group

I am planning to create additional tempdb files for 2 nodes in a SQL 2012 Availability Group. Unfortunately we do not have Instant File Initialisation turned on so the growth of the tempdb data files ...
0 votes
1 answer
26 views

What is the specific error to look for that guarantees wsfc initiated either restart or failover?

I am looking at sql server error log and the cluster log (cluster.log) file for each node in the cluster. I can see couple of errors scattered around like failed a periodic health check on file share, ...
-1 votes
0 answers
16 views

What is the list of alerts to be configured for sql server 2019 to monitor general/fci/ag errors?

I have configured the following alerts on sql server: https://www.brentozar.com/blitz/configure-sql-server-alerts/ I would like to have a list of any more error codes so that I can get alerted about ...
0 votes
3 answers
39 views

How to prevent primary node's SQL jobs from running when the primary node goes into resolving state?

I have 2 nodes in an AG. Non-readable secondary, sync commit mode. There are sql agent jobs setup on both machines. The schedules are turned off on secondary so those jobs wont run. If there is ...
0 votes
0 answers
27 views

AG node getting "misaligned log IOs which required falling back to synchronous IO" events in error log

SQL 2016 AOAG. 3 node cluster. Primary and 1 secondary on-prem configured with synchronous availability mode. 1 node in Azure using asynchronous availability mode. We are using AOAGs to move data ...
0 votes
1 answer
18 views

Why can't I see witness in the failover cluster manager snap-in?

SELECT * FROM sys.dm_hadr_cluster_members This query shows me 2 nodes and 1 file share witness. However, when I open the wsfc cluster manager snap-in, then click on Role, select the role, I cannot ...
-1 votes
0 answers
21 views

Why could there be huge lag between the AG Lease timeout expiry and server restart logs?

I have SQL AG configured. In the sql server logs I can see lease timeout expiry 14:15. As per the cluster policy, upon lease timeout expiry it must perform the primary node restart. But the log that ...
0 votes
1 answer
24 views

Why does FCI not have worry of split brain scenario?

SQL server AG has lease time-out to ensure there will never be a split brain scenario. Where as there is no such thing for FCI. Both AG and FCI (HA) require the windows failover cluster, but why does ...
1 vote
1 answer
51 views

Why does `Is Clustered` show as No for a AG SQL Node?

I am looking at a primary node of SQL AG, configured with sync auto-failover, no read scale. When I open the Server Dashboard report, it shows the Is Clustered value as No. Why does Is Clustered show ...
0 votes
1 answer
260 views

Is it possible for a node to host multiple cluster in relation to Always On AG

In my example, I have 3 database servers with their own DR servers, own availability group, own clusters. Is it possible to setup another server which can be part of the 3 clusters and be a secondary ...
2 votes
1 answer
117 views

Implementing HA for SQL Server for a .NET Application that uses MS DTC

My production environment uses MS DTC and SQL Server 2014. I must have an HA solution such as Mirroring or AG in place. (Not FCI). I am trying to explain to my team that MS DTC is not supported by ...
3 votes
2 answers
798 views

Applying (log backup) LSNs to the secondary node of an Availability Group

I have a test instance configured as always-on go down today due to a restart of the primary node and the file-share going down together. I had a bunch of problems in order to get the secondary ...
0 votes
2 answers
45 views

Split Brain scenario Always on Availability group

Due to maintenance our primary site will be offline for few hours. I have an availability group where there are 2 nodes at the primary site and one node at the DR Site. Since the Primary site will be ...
0 votes
1 answer
105 views

How to check backup against SQL AG by Get-DbaLastBackup?

I use the following PowerShell script to check if the last full backup successful daily: $SQL_Server_list = Get-Content D:\Tools\DBA\SQLPRD_FINAL.txt $SQL_Server_list | Get-DbaLastBackup | Where-...
0 votes
1 answer
13 views

Replication jobs are not getting created after failover

I added distribution database to the alwaysOn group of dist1 and dist2 server. I setup replication for the database which are part of AAG and act as a publisher( node 1 and node2) and subscriber ...
0 votes
1 answer
148 views

SQL server replication (publisher, subscriber, distributor) on top of AlwaysOn

I am assessing the migration from SQL 2008R2 to SQL 2017 AlwaysOn where we have replication (publisher, subscriber, distributor) setup on SQL2008R2. There are various restrictions if one wants to ...
0 votes
2 answers
414 views

Multi-Subnet Network Setup

We currently have a 4 Node cluster used for our Availability Groups. 2 nodes in local data center and 2 nodes in remote data center. Currently the 2 nodes local are Synchronous and the 2 remote ...
0 votes
1 answer
33 views

SQL Server availability group asynchronous secondary extended downtime...suspend necessary?

Our asynchronous secondary replicas will have some extended downtime over the next few weeks. 3-4 hours one night then 2-3 days a few weeks later. I know the primary replica logs will keep growing ...
2 votes
1 answer
39 views

Database stuck in a read only state when testing a DR scenario with a multi-subnet SQL Server Always On AG, how can I get the db in a healthy state?

I am testing a disaster recovery scenario with azure VMs. I have sql servers deployed across 2 regions joined together via Always On availability groups sitting on top of a multi-subnet WSFC. When I ...
2 votes
1 answer
2k views

The fulltext filter daemon host (FDHost) process stops abnormally on SQL Alwayson Cluster

We added a new SQL Always-On Cluster (FCI) 2017 and some of the databases needed full text filter service. So we also added that feature to the install. Ever since we did that we have been getting ...
0 votes
2 answers
143 views

"Test-SqlAvailabilityGroup" takes too much time

Can anyone explain me why the command Test-SqlAvailabilityGroup takes too much time ? Is there a fastest way to check if a an availability group exists or not (preferably on PowerShell) ? Thanks.
0 votes
1 answer
33 views

Creating AlwaysOn Availiability Group & Listener not showing on second node with T-SQL?

I have followed this guide and stole some of T-SQL code to edit into my own and this is what I have got: CREATE AVAILABILITY GROUP jammytest WITH ( AUTOMATED_BACKUP_PREFERENCE = ...
0 votes
3 answers
126 views

Need to configure Availability Groups between three different SQL failover instance

We have total 6 Nodes and 3 failover cluster (Each with two node). Cluster1.TestAD.com(Node1,Node2) - SQL Failover Instance (SQL1) - 1 TB Shared storage Cluster2.TestAD.com(Node3,Node4) - SQL ...
0 votes
2 answers
28 views

Failing over to DR site Availability group

I have a 4 node cluster SQL 2012 availability group database with one witness server at the primary site. 3 nodes are in the primary site and one node in the DR site. Due to maintenance at the Primary ...
2 votes
2 answers
231 views

SQL Server 2012 AlwaysOn Availability Group

We have an application that needs access to the database at all times to work properly and we are planning on deploying SQL Server 2012 AlwaysOn Availability Groups on Windows Server 2012. We have two ...
2 votes
1 answer
3k views

Error: CImageHelper::Init () Failed Load of symbol file with path

Using SQL server 2017 (14.0.3029.16) with Always On High Availability environment. Getting error: CImageHelper::Init () Failed load of symbol file with path = C:\Program Files\Microsoft SQL Server\...
1 vote
1 answer
190 views

Measuring Availability Group synchronization lag - Clarifications

Can I get some help on below As I have started and learning about the Measuring of Availability Group synchronization lag between replicas inclusive of BCP , I want to understand and learn that does ...
4 votes
2 answers
2k views

log send queue size and redo queue size in AG

I am trying to figure out the way to monitor these 2 events log send queue size - I can see this in perfmon Redo queue size - I can see in dmv but not in perfmon counter Is there any way of using ...
3 votes
1 answer
7k views

DATABASE lacks a quorum of nodes for high availability

Just after setting up successfully alwayson - availability group, testing the failover, I get this error message: TITLE: Microsoft SQL Server Management Studio Failed to resume data movement in ...
0 votes
1 answer
30 views

SQL Server 2 node availability group - deleting secondary server, to be replaced later

I found Questions regarding removing a secondary node from an availability group with Tony Hinkle's answer, but I'd like to get some clarification on 1) removing the databases from the AG, 2) removing ...
0 votes
1 answer
29 views

CVE Live Patching for Database

Quick question regarding the Database patching, especially on the CVE. Did you see if this technology of live patching is very important to have to our open-source database? Mean I don't even need to ...
2 votes
1 answer
824 views

Secondary Database Replica in Reverting/InRecovery (Long time)

Reposted from: https://www.sqlservercentral.com/forums/topic/secondary-database-replica-in-reverting-inrecovery-long-time Hi, One of my AG secondary replica has been in reverting/in recovery for ...
2 votes
1 answer
160 views

What is the difference between sys.dm_hadr_ tables and sys.availability_groups / sys.availability_replicas?

Browsing around some sys tables, I see: sys.dm_hadr_availability_replica_cluster_nodes.group_name = sys.availability_groups.name and sys.dm_hadr_availability_replica_cluster_nodes....
0 votes
1 answer
222 views

Text file report is not generating of Availability Databases other Databases during auto backup through maintenance plans in MS SQL Server 2012

I have setup Automated backup in SQL Server 2012 Enterprise Database backup through 'Maintenance Plan'. I have also configure AlwaysOn High Availability to that server .There is 5 databases in the ...

1
2 3 4 5
29