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
0 answers
24 views

Can SQL AG be used across 2 sites over the internet to create replica readonly db on remote server?

I have SQLSVR1 having multiple dbs located in Scotland. One of these dbs is used in an ETL process running on SQLSVR2 located in a remote data center located in London via linked server. The ETL ...
  • 2,792
1 vote
1 answer
29 views

Databases in Recovery Pending or Suspect Mode after SQL Server service restart

This issue applies for SQL Server 2019 Always on environment with the latest CU applied. When SQL service needs a restart or the entire OS needs a reboot, some of the DBs come back in recovery pending/...
0 votes
2 answers
45 views

Remove MSSQL database out of FCI

I have three database nodes, node 1 & 2 are participating in HA using Failover Cluster Instance. Node 3 is acting as DR using SQL Always On Availability Group similar to the image below: I ...
  • 202
0 votes
1 answer
21 views

Design options: multi clusters, multi instances or multi availability groups

We have a Microsoft platform, where within it, we have two sets of databases. One set of databases for the platform services etc. and one set of databases of more custom nature with some data that is ...
0 votes
0 answers
21 views

server trigger for Always On failover

I am working on a solution for a quite specific problem with environments (SQL Server using AO, with two replicas) having SSRS in scale-out, each node has MSSQL and SSRS installed, and when there is a ...
0 votes
1 answer
28 views

Dynamic Quorum behaviour with a witness

I was going through this article and trying to understand the behaviour described in the article. Summary - It is not a common scenario to see cluster nodes go offline until the last node and the FSW ...
  • 1,718
0 votes
1 answer
54 views

WSFC Dynamic Quorum not removing votes

Environment is Windows Server 2019 in Azure I have a 3 node multi-region cluster. Server 1 & 2 are in the same region while server 3 is in a remote region. Server 3 does not have a vote in the ...
-1 votes
1 answer
29 views

Do I need to stop Log backups when I stop data sync on AOAG?

I was curious about this specific topic. If I stop data sync to alter primary without affecting secondaries, do I need to stop log backup jobs? does a log backup during the stop data sync would mess ...
  • 7,156
-2 votes
1 answer
31 views

Failover to a readable secondary

Is it possible to failover to a readable secondary (which always has an asynchronous-commit mode) using FORCE_FAILOVER_ALLOW_DATA_LOSS?
  • 655
0 votes
1 answer
47 views

Monitoring/Logging database on a non-readable secondary instance

I want to create a database on a non-readable secondary replica to collect some data for troubleshooting and monitoring the AG. I have read that there is no license cost when the server is not queried ...
  • 655
0 votes
0 answers
22 views

Offline secondary replica brought primary replica to halt

I have an AG built on a 2 node WSFC. When the secondary replica went down for patching last weekend, the primary basically locked up and wouldn't allow any connections or queries. 2 nodes are on the ...
0 votes
1 answer
63 views

Does an asynchronous replica in Always On Availability Group need to be part of windows failover cluster?

I have a Windows failover cluster up and running, with a primary and secondary server. I have also created an Always On Availability Group with the two servers, with both the primary and the secondary ...
-2 votes
1 answer
151 views

Long running sp_server_diagnostics

sp_server_diagnostics is running from past 28 days, i.e., from last server reboot. This is going on every month. It never stops and gets notified as long running sessions often. It starts again after ...
  • 340
0 votes
0 answers
21 views

Trigger agent jobs on failover

I currently have a 2 node AG with automatic failover enabled. Some of the databases in the AG are also using change data capture, as such we have CDC_capture and CDC_cleanup jobs. The jobs are ...
  • 388
1 vote
1 answer
59 views

New DMVs related to Availability Groups in SQL Server 2019 CU9

In SQL Server 2019 since Cumulative Update 9, there are two new DMVs related to Availability Groups: sys.dm_hadr_cached_replica_states sys.dm_hadr_cached_database_replica_states I found out about ...
-1 votes
1 answer
45 views

Cluster Resource fails every 8 hours for SQL AG

Every ~8 hours, I receive the error below in my Windows Cluster events. During this same time connections to SQL server are being lost. I am struggling to find any resources that help determine what ...
0 votes
2 answers
120 views

SQL Server 2019 DAG WFC - Manual Failover won't work (MSSQL Error 41131)

We set up a distributed failover cluster with 2 Windows Server 2019 Datacenter nodes, each of them running SQL Server 2019 Enterprise + SSMS18. The two nodes are located in two different sites with ...
2 votes
3 answers
210 views

What affects log_send_rate in Normal or Distributed AG other than OS resources?

I am observing low log_send_rate in my distributed AG setup. I understand that AG uses log stream and so I assume it should not have anything to do with data, but I was wondering if this has anything ...
2 votes
1 answer
21 views

How does an async AG secondary sync itself with the primary when secondary missed tlog record due to a power failure on secondary server?

In a SQL AG (read-scale) async mode, which doesn't require a WSFC cluster, suppose the following scenario: Primary writes the data into its transaction log and sends this log record to the secondary ...
  • 2,792
-1 votes
1 answer
69 views

upgrade OS and SQL Server version on SQL Cluster [closed]

We have a critical application that uses SQL Server 2016 Cluster running on Windows server 2012. In order for us to upgrade the application, we need to upgrade the OS and the SQL Server version. What ...
-3 votes
1 answer
42 views

What does FCI owners in a WSFC mean and how does it prevent adding replica to same WSFC?

Link: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/prereqs-restrictions-recommendations-always-on-availability?view=sql-server-ver16#RestrictionsFCI The cluster ...
  • 2,792
-1 votes
1 answer
33 views

Whether the disconnected state caused by session timeout is to be manually or automatically resolved?

Link: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver16#SessionTimeoutPerios Once the session ...
  • 2,792
-2 votes
1 answer
25 views

Does the SQL AG based corruption resolution work for a enterprise edition read scale AG and for basic AG?

SQL provides 2 kinds of AG architecture. Basic AG (when either of the server has standard SQL edition) Enterprise edition AG for HA Enterprise edition AG for read scale (this doesn't require WSFC) I ...
  • 2,792
0 votes
0 answers
45 views

Can I do SQL AG for read scale when both source and destination are FCIs?

I am reading this link: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-clustering-and-always-on-availability-groups-sql-server?view=sql-server-ver15#WSFC ...
  • 2,792
0 votes
1 answer
49 views

Can I use SQL AG to create readable secondary when primary is enterprise and secondary is standard edition?

1 have 2 sql servers. Svr1 is enterprise edition and Svr2 is standard edition. Both have their own independant FCI. Svr1 has got 60 production dbs. Svr2 has got 10 reporting dbs and multiple etls. ...
  • 2,792
0 votes
0 answers
44 views

mssql 2019 alwayson cluster backup log chain is broken

now i had one mssql 2019 alwayson cluster.i backup database on master node using monthly fullbackup. weekly differential backup. daily log backup. but unfortunately then backup log chain always ...
0 votes
0 answers
13 views

Two node AG (async, manual failover)...Zerto migration of primary node AND file share witness vm...quorum will fail temporarily

We've been migrating (using Zerto) individual sql vms to a new data center, and our AGs are up next. With a two node AG (async, manual failover, file share witness) where the primary node and the vm ...
0 votes
1 answer
22 views

Can you create AlwaysOn in Azure (primary) with a readable secondary replica on premises?

Is it possible to create the following setup using Always on? Primary replica: in Azure Secondary: readable replica on premises I know usually people would create the opposite structure, but I ...
0 votes
0 answers
26 views

Has anyone implemented TDE on two node AlwaysOn Cluster with Replication?

Looking for instructions to implement TDE on AlwaysOn with Replication. Current set up is Failover Cluster with Replication. Any gotchas to add TDE and AlwaysOn combination with Replication?
  • 327
1 vote
1 answer
38 views

Always on Availability Groups - Disaster Recovery + High Availability in SQL Server 2019

I'm reading some articles on how to create an Always on Availability Groups - Disaster Recovery (HA + DR) environment. In many blogs it shows that to create this environment I will need three servers, ...
0 votes
1 answer
118 views

nslookup returning secondary IP of a listener (different subnet)

I'm pretty confused about this issue. We noticed that we sometimes receive some errors from the app servers causing timeouts. after a lot of digging, we discovered that sometimes when using a listener ...
  • 7,156
0 votes
2 answers
60 views

AlwaysOn Availability Group creation - Is it possible to delay the adding of a secondary replica?

Currently I have a windows failover cluster with a single node only, because the second node will only become available on an production go live date. Prior to this, I was wondering if it would be ...
0 votes
0 answers
44 views

Moving SQL database with FCI to AWS

We have a MSSQL database which has two nodes and is using Always On FCI for High Availability purpose at our on-premise location. As most of you aware FCI SQL requires the two nodes to share the same ...
  • 202
7 votes
2 answers
697 views

ReadOnly queries on secondary server do not return correct values

I have two servers A and B. Both are in an Always On Group. The B server is configured for ReadOnly requests. This works fine so far, but recently there is a problem for queries for a service. The ...
0 votes
1 answer
35 views

Availability Group - Core cluster resources switch to being owned by secondary node after windows patching reboots

Some of our AG clusters (async, manual failover) shift the ownership of their core cluster resources (e.g. cluster IP) from the primary node to the secondary node, which is in a different datacenter, ...
0 votes
0 answers
17 views

Slow Database queries when on Secondary Node of Availability Group

Failover happens almost immediately, but an application connecting to several databases on the server runs WAY slower (Queries return in 4 minutes compared to 30 seconds). The hardware and software on ...
3 votes
1 answer
418 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 ...
0 votes
1 answer
27 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 ...
  • 388
-3 votes
1 answer
61 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 ...
  • 2,792
-5 votes
1 answer
87 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 ...
  • 2,792
0 votes
1 answer
49 views

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

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 ...
0 votes
2 answers
433 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 "...
-4 votes
1 answer
57 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 ...
  • 2,792
1 vote
1 answer
59 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 ...
  • 2,792
1 vote
1 answer
35 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 ...
  • 2,792
0 votes
1 answer
73 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 ...
  • 2,792
0 votes
2 answers
35 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 ...
  • 2,792
0 votes
0 answers
211 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
33 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, ...
  • 2,792
0 votes
1 answer
72 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 ...
  • 2,792

1
2 3 4 5
29