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
2 views

Has anyone implemented TDE on two mode 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? Thank ...
user avatar
  • 327
1 vote
1 answer
22 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, ...
user avatar
0 votes
1 answer
16 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 ...
user avatar
  • 7,104
0 votes
2 answers
28 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 ...
user avatar
0 votes
0 answers
32 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 ...
user avatar
  • 200
7 votes
2 answers
645 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 ...
user avatar
0 votes
1 answer
25 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, ...
user avatar
0 votes
0 answers
13 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 ...
user avatar
3 votes
1 answer
81 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 ...
user avatar
0 votes
1 answer
24 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 ...
user avatar
  • 388
-3 votes
1 answer
58 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 ...
user avatar
  • 2,375
-5 votes
1 answer
49 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 ...
user avatar
  • 2,375
0 votes
1 answer
26 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 ...
user avatar
0 votes
2 answers
62 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 "...
user avatar
-4 votes
1 answer
35 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 ...
user avatar
  • 2,375
1 vote
1 answer
48 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 ...
user avatar
  • 2,375
1 vote
1 answer
28 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 ...
user avatar
  • 2,375
0 votes
1 answer
29 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 ...
user avatar
  • 2,375
0 votes
2 answers
29 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 ...
user avatar
  • 2,375
0 votes
0 answers
62 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 ...
user avatar
0 votes
1 answer
27 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, ...
user avatar
  • 2,375
0 votes
1 answer
23 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 ...
user avatar
  • 2,375
0 votes
3 answers
55 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 ...
user avatar
  • 2,375
0 votes
1 answer
26 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 ...
user avatar
  • 2,375
1 vote
1 answer
53 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 ...
user avatar
  • 2,375
0 votes
1 answer
19 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 ...
user avatar
  • 95
0 votes
1 answer
39 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 ...
user avatar
2 votes
1 answer
45 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 ...
user avatar
0 votes
1 answer
38 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 = ...
user avatar
0 votes
2 answers
32 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 ...
user avatar
  • 1,007
0 votes
2 answers
93 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 ...
user avatar
  • 1,007
0 votes
1 answer
35 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 ...
user avatar
4 votes
0 answers
58 views

Planning for application upgrade which connects to SQL server database

I am looking for some advise/guidance on how to achieve this with minimum downtime and possibly avoid any errors or failure during production change- Background- We have SQL database servers ...
user avatar
0 votes
1 answer
30 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 ...
user avatar
0 votes
1 answer
35 views

Always on Server Disks

Have sql server always on configured with 2 replicas as asynchronous mode with no automatic failover. But total capacity of log file disk on primary is less than the secondary server disk.Folder ...
user avatar
  • 55
0 votes
0 answers
33 views

Azure VM with alwayson AGs - expand data disk

I have a three nodes SQL Server Alwayson cluster on Azure VM. I need to expand the data disk on all three nodes. The disk is a stripe disk build during VM configuration. Based on this: https://docs....
user avatar
  • 3,462
1 vote
0 answers
160 views

Trying to understand why Availability Group replication being suspended didn't fire alert

We are running SQL Server 2019 CU14 and last night we had one of our databases within a secondary Availability Group suspend replication due to the following reason: Always On Availability Groups ...
user avatar
  • 31
0 votes
0 answers
21 views

Endpoints for more than one Availability Group [duplicate]

I have 2 Node Cluster. For each node, I have 2 instances and for each instance, I have one availability group. Do I need to define separate endpoint port for each of the availability group/Instance? ...
user avatar
  • 1
1 vote
2 answers
69 views

Move SQL Server Database Files

My current environment is Always On with asynchronous mode with readable secondary's, but no failover. I wanted to move database file which is already in AG. But I can't failover AG. Please advise ...
user avatar
  • 55
0 votes
2 answers
32 views

What is the impact on primary (in terms of locking) and restore process when secondary database is being read by large query?

I am exploring couple of solutions: Log shipping - say a large query is running and meanwhile the log shipping process is triggered. Will this stop the query or delay the log shipping restore? ...
user avatar
  • 2,375
1 vote
1 answer
51 views

Should I offload my SQL Server backups to a secondary replica?

My production server doesn't have enough horsepower to run backups locally without causing impact to production. I'd like to offload them to my secondary replicas, to free up resources on my primary. ...
user avatar
0 votes
1 answer
60 views

Updating DNS on Availability Group database servers

We have always on availability group that spans across different data centers. Our network team has decommissioned some DNS servers and those servers will need to be updated with the active ones on ...
user avatar
  • 1,007
1 vote
1 answer
71 views

What will happen if an Availability Group failover occurs while a database is undergoing encryption via TDE?

As the title says, we have an existing availability group with a few databases and we'd like to enable TDE. I know that to do so we want to make sure all the servers have a database master key and the ...
user avatar
  • 198
2 votes
0 answers
29 views

Controlling permissions for app team while assigning them ALTER ANY AG access

There is a need where we our DEV team needs to make index changes and run other index maintenance jobs on the primary replica. We have seen that while this happens, there is a blocking chain created ...
user avatar
0 votes
1 answer
128 views

Shrink Log file in Always ON DB

We have Sql Server 2016 and set up Always ON with 2nd replica (Primary and Secondary) Currently looks like Log file filling up, getting disk pace full. I tried to resume the secondary database which ...
user avatar
  • 11
3 votes
1 answer
314 views

Is it possible to use Read Uncommitted isolation level on read-only Availability Group secondary?

We are using availability groups in SQL 2019 Enterprise edition. We are using the enterprise feature to allow the AG secondary nodes to be in Read-only mode, and then running reporting queries against ...
user avatar
  • 819
4 votes
1 answer
263 views

Why significantly worse Column Store performance on Read-only AG secondary DB?

We have a pair of SQL 2019 Enterprise instances hosted in identically spec'd and configured VMs in Azure. They for a simple primary/secondary availability group array, containing a small number of ...
user avatar
  • 819
0 votes
0 answers
12 views

Joining a Virtual Box VM AG node to an AD domain VM without a phsyical network adapter installed

I've set up one VM as the AD and domain controller server, and one VM as an Availability Group node to learn how to use AD and Availability Groups. However, I'm unable to add the the node server to ...
user avatar
  • 11
0 votes
1 answer
37 views

Rolling Upgrade Win2012R2/SQL2012 Availability Group to Win2019/SQL2019

To do a rolling upgrade of an Availability Group from Windows 2012R2, SQL 2012 to Windows 2019, SQL 2019 do I have to have an intermediary set of servers on Windows 2016? Based on the documentation ...
user avatar
0 votes
2 answers
83 views

Restoring secondary backup files on primary server in sql server

I want to restore log backup file on copy-only full backup in restoring mode on primary replica server these backups were taken from secondary replica . I took copy only full back up on secondary ...
user avatar
  • 1

1
2 3 4 5
29