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

Configuring a Failover Cluster Fileshare Witness?

I'm relatively new to high availability groups in SQL Server, and am hoping for a bit of advice on best practices for configuration. I didn't set up this configuration, I inherited it and am trying to ...
fuandon's user avatar
  • 101
0 votes
1 answer
42 views

Configuring Always On Availability Groups sql Server 2019 and log backup

We want migrate to sql 2019 and I did the test and set up Hight availability with twho 2 nodes,but and the set up of AG I have four option, I gave that option a shared path for the transaction log ...
Kalusql's user avatar
0 votes
1 answer
33 views

When using multiple basic AGs is it recommended to use 1 listener or mirroring endpoint?

I want to setup HA DR for a single instance SQL server by adding a new instance on another machine and configuring multiple basic AGs (with manual failover mode). I want all client apps to be able to ...
variable's user avatar
  • 3,360
0 votes
1 answer
68 views

SQL Server 2017 Availability Group now thinks it is in a cluster - it isn't and never was!

We have a read-scale availability group on SQL 2017 that for years has worked without fault. Now when we tried to add a new replica (with CLUSTER_TYPE=NONE), it fails because the primary now "...
Stephen H's user avatar
0 votes
2 answers
49 views

Report to get difference in SQL Agent jobs across nodes in Availability Group

Is there a script or an article out there to check differences between SQL Agent jobs across nodes in AG and create a report based on the difference? I am not looking for dbatools.io as it syncs the ...
mk SQL's user avatar
  • 5
0 votes
0 answers
30 views

Problem with using @@DBTS between the primary and secondary in an AG

I have an ETL process that uses rowversion field and had an issue, because the ETL process was using the source database which had become out of sync in an AG, so I read the rowversion data from the ...
Keyvan Soleimani's user avatar
-1 votes
0 answers
26 views

Are there any challenges with using basic AGs for multiple db in an existing FCI setup?

I'm looking to add DR capability to an existing 2 node FCI, by adding a 3rd server to be located on DR site. Since SQL edition is standard, I can make use of to use of log shipping or Basic ...
variable's user avatar
  • 3,360
0 votes
1 answer
16 views

Can I distribute a distributed availability group?

Server1 and Server2 make up a NEW availability group: AGServer. DMZ1 and DMZ2 already make up an existing availability group: AGDMZ. Currently DMZ1 & DMZ2 are part of a Distributed Availability ...
CrushingIT's user avatar
0 votes
0 answers
6 views

Moving Primary AG Replia to new location

DBA by accident here so apologies if I'm a little vague!! My current workplace use a three node SQL 2016 cluster, not virtual. One of the nodes is a primary replica and requires moving to a new data ...
Darren's user avatar
  • 1
0 votes
0 answers
28 views

DNS permissions for Availability Groups

can anyone tell me the correct permissions for the Listener and Cluster records in DNS? At the moment I have a single subnet AG where the Listener VNN owns the cluster DNS entry and vice versa, so the ...
alexP012's user avatar
0 votes
1 answer
18 views

On which node must the failover manager for the AG be?

I have an availability group for Windows SQL Server. It is so that instances hosting replicas must be in a Windows failover cluster. However, if the node where the failover manager is on fails, what ...
Hector234's user avatar
0 votes
1 answer
32 views

do log backup on secondary node (sync or async) can truncate transaction log on primary?

i have 4 nodes. 2 nodes in sync mode, 2 nodes in asyn mode. Can i relocate all backup jobs (full, tlog) on any secondary? does the backup truncate the transaction log on primary?
zul nasir's user avatar
-2 votes
2 answers
45 views

Does a DR plan need to be in place for an AlwaysOn availability group

I have two SQL servers 2022 in a normal availability group. I have heard that AG is capable of disaster recovery. Is this true or do I need to make an extra plan? Another question: Why is Failover ...
Hector234's user avatar
0 votes
1 answer
25 views

SQL Server Sync Connection Usage

We're seeing very high waits for HADR_SYNC_COMMIT at times and after some diagnostic work with iperf3 and the like we think there might a network bandwidth problem. However we're not central sql staff ...
Simon Bradley's user avatar
0 votes
0 answers
29 views

SSRS 2016 compatibility with SQL 2017 after SQL2016 upgrade

We have an SQL Server database engine on the 2016 version. We also have SSRS in the 2016 version. I plan to upgrade the SQL database engine from the 2016 version to the 2017 for it to participate in ...
mk SQL's user avatar
  • 5
0 votes
1 answer
44 views

Does availability group have 1 log send and 1 redo queue for each db, or 1 shared log send queue and 1 shared redo queue for all dbs in an AG?

As per msdn documentation, each primary replica uses 1 Log Capture thread for each primary database. And 1 Log Send thread for each secondary database. That means if I have a 3 node AG, with 10dbs in ...
variable's user avatar
  • 3,360
0 votes
1 answer
33 views

Does delayed durability have a possibility to cause large redo queue in a synchronous AG db?

I have an issue where-in index rebuild is causing large redo queue on the synchronous AG secondary node. This issue occurs only on 1 db, and the only difference between this db and all other dbs is ...
variable's user avatar
  • 3,360
0 votes
1 answer
40 views

Doubt on Always-on ApplicationIntend-ReadOnly and ApplicationIntend=ReadWrite

I Have Deployed Always-on, and I have routed read query into secondary server, but my question is that, how can I make connection string on application side which will divert. Insert, Update and ...
kedar's user avatar
  • 23
0 votes
1 answer
25 views

What is the correct value (GB) to shrink a DB?

My database has an MDF file of 56 GB and an LDF file of 263 GB, recovery model is setting to Full. So, I would like to shrink the LDF file because we are out of space. What specific size should I ...
luis.huallpa's user avatar
0 votes
1 answer
396 views

The remote copy of database has not been rolled forward to a point in time that is encompassed in the local copy of the database log

Can someone help me with the issue I'm dealing with? I had to force failover from my primary datacenter to my secondary datacenter and I forgot to disable the log backup job. Meanwhile the new ...
alireza's user avatar
0 votes
0 answers
45 views

SQL Server Always On Availability Groups are green, but manual failover attempts consistently failing ("duplicate IP address", etc. in cluster logs)

We're running SQL Server 2017 Enterprise Edition on a pair of EC2 instances (we'll call them "P1" and "P3" here) in AWS. Each EC2 instance is in its own subnet, and runs two ...
Skip Daly's user avatar
0 votes
0 answers
39 views

SQL Server Replication between two AlwaysOn HADR Clusters

We got a requirement to replicate data between two different HA Clusters. ---Current Prod Environment------- SQL Server 2016 Servers HADR Cluster 1 (HA1) - Primary Replica (PR1), Secondary Replica (...
Kris's user avatar
  • 402
0 votes
1 answer
53 views

AlwaysOn Readable secondaries - Version Store

The environment is: SQL 2019 3 Node AG Synchronous between Node 1 & Node 2 Asynchronous to Node 3 Following this article: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/...
Sicilian-Najdorf's user avatar
0 votes
0 answers
22 views

Stored procedure execution issue on always on 3rd node

we have an alwayson three node setup. our client is executing a stored procedure on 3rd node. The stored procedure runs for 30 minutes and it is terminated and giving below error message without any ...
Jefferson Lourthusamy's user avatar
0 votes
1 answer
52 views

Availability group configuration problems - listener not created, error 19407 appears

To start with a description of an environment in which the problems are happening: 2 SQL servers 2022 Standard in their /28 subnet 1 AD domain for several sites, the SQL servers are members of this ...
Sranda's user avatar
  • 331
-1 votes
1 answer
138 views

What does the question mark on availability group replica mean in SQL Server Management Studio?

I'm doing some testing with availability group in SQL Server. When I'm on the primary replica, I can see all replica nodes have a refresh icon on them. But when I check on a secondary replica, all ...
Fajela Tajkiya's user avatar
0 votes
1 answer
13 views

always on ha availabity group force read only connection without specifying ApplicationIntent=ReadOnly

We have an AlwaysOn HA sql cluster set up, with read-only replicas in place, and read-only routing in place. We know how to properly route a connection to the read only replica (by specifying ...
bradvido's user avatar
  • 101
0 votes
1 answer
17 views

Can a user connect to multiple Basic Availability Groups via listeners?

Assuming multiple, let's say 50, basic availability groups are on the primary replica. Each AG has its own listener. The primary replica goes down, and all the AG's fail over to the secondary. How ...
Miranda's user avatar
  • 23
0 votes
1 answer
73 views

AlwaysOn failures and failovers

This is the setup: Firstly, it's in Azure 3 SQL Servers: SQL Server 2019 RTM CU18. AlwaysOn configured between these 3 servers. Server 1 & 2 in one region (Synchronous + Manual Failover Mode) ...
Sicilian-Najdorf's user avatar
-1 votes
1 answer
51 views

How does delayed durability impact a database which is part of synchronous availability group?

Delayed durability (forced) setting on a database ensures that client application gets the commit without waiting for the log to be flushed (written) to disk. Synchronous AG requires that the ...
variable's user avatar
  • 3,360
0 votes
1 answer
90 views

Basic Availability Groups: can each AG share a listener?

What are the potential consequences of using the same listener for each Availability Group when using Basic AGs? Is it even possible?
Miranda's user avatar
  • 23
0 votes
1 answer
68 views

Installing failover cluster after SQL installation

Will there be any issue if WSFC is installed after SQL is already installed for the purpose of configuring Always ON? SQL Server version 2017 Enterprise Windows version 2019 standard
mk SQL's user avatar
  • 5
0 votes
0 answers
56 views

Easiest way to migrate Availability Group databases from one cluster to another?

Consider this scenario: OLDCLUSTER (WSFC, all nodes SQL Server 2016 SP2) OLDSQL01 AG primary node OLDSQL02 AG secondary node OLDSQL03 AG secondary node (for DR) NEWCLUSTER (WSFC, all nodes SQL ...
Saverio Parlato's user avatar
-2 votes
1 answer
28 views

Is there support for distributor server failover?

I have done googling for this question. The answer is no. But the answer is on year 2015. How about now? Is the distributor server have failover support capibility?
zul nasir's user avatar
0 votes
1 answer
30 views

Daily, weekly restores of a SQL database to SQL AlwaysON

Our PT environment is SQL AlwaysOn just like our Prod environment. App Team now wants to drop a PROD BAK weekly, and maybe some Daily prior to a release; then restore to the PT AO AG. Not wanting to ...
user3191894's user avatar
0 votes
1 answer
27 views

How to setup transactional replication on dedicated server using AG database with auto failover

My plan is i have One dedicated server for Transactional replication as subscriber. And two nodes in Availability Group. I want to replicate one availability database on primary as publisher. When ...
zul nasir's user avatar
0 votes
0 answers
28 views

How to use the same Azure SQL VM in two different Load Balancers

I have already configured always on availability group between VM1 and VM2 azure machines. Internal Load Balancer was created for these two VMs and mapped to AG Listener. VM2 is used for read workload ...
Rauf Asadov's user avatar
  • 1,243
1 vote
1 answer
77 views

SQL Server - Will a blocked REDO thread cause further blocking on readable secondary replica?

I understand that a redo thread replaying a DDL operation can be blocked by a long-running SELECT query on readable secondary replica because the latter holds a Sch-S lock thus preventing the former ...
Timbalero's user avatar
  • 101
0 votes
1 answer
427 views

How to remove or disable SQL Telemetry (sqlceip)

I have SQL 2019 Enterprise Edition patched to CU18 installed on a 2-node Windows 2022 cluster and configured with an Availability Group. I have been investigating an unusual and persistent behaviour ...
PTL_SQL's user avatar
  • 295
0 votes
0 answers
38 views

sp_Who2 is throwing an error when executed against AAG secondary replica

I am getting following error while trying to execute sp_Who2 or select * from sys.processes against the master database of AlwaysOn secondary replica. I am using master database but it is still ...
SqlDBA's user avatar
  • 117
0 votes
0 answers
30 views

Waiting until the AOG secondary node is in sync

I'm helping ETL developers on offloading some SSIS workload from the SQL Server AOG primary node to the asynchronous secondary one. The challenge comes when they need to write tables in the primary ...
Roman Peralta's user avatar
-1 votes
1 answer
47 views

AlwaysOn proc doesn't stop, but it can't be telnet。SqlDumpExceptionHandler: Process {0} g fatal exception c0000094 EXCEPTION_INT_DIVIDE_BY_ZERO

I'm a DBA, I'm still learning, and I've recently encountered an Alwayson cluster that can't be used. Storage failure 1796932693059087230, type 5 Event name: SQLException64 Response: Not applicable Cab ...
Wayne Chen's user avatar
0 votes
0 answers
21 views

Unable to place database back in "Online" status

I am looking for some help. I have the following setup: SQL Server 2019 Enterprise running a two node AlwaysOn HA One of the HA databases now has this status next to the db name "Initializing / ...
sydney's user avatar
  • 157
0 votes
1 answer
113 views

Availability Group Automatic Seeding

I'd appreciate some advice please. I have configured automatic seeding for my SQL 2019 Availability Group. My two cluster nodes each has 8GB of RAM and 4 processors. The smaller databases in the ...
PTL_SQL's user avatar
  • 295
0 votes
1 answer
139 views

Local vs Shared Storage for MS SQL Server's Always On Availability Groups: What's Required?

Is it a requirement for MS SQL Server's Always On Availability Groups to always utilize shared Storage SAN or a virtual shared storage like Storage Spaces Direct? Can I set up Always On Availability ...
user avatar
-1 votes
1 answer
60 views

What is the solution to prevent log file growth due to an AG node issue (other than fixing the node or removing node from AG)?

In an 2 node AG, suppose one node is not reachable or down or crashed, then the log file size on the primary node will keep growing. In this situation, is there any way to prevent log growth other ...
variable's user avatar
  • 3,360
1 vote
1 answer
49 views

What is the log backup behaviour after AG failover or other node unreachable?

Log backups are important for point in time recovery and to automatically truncate the log file (in full recovery model) to make space for log reuse. But when the secondary is unreachable either due ...
variable's user avatar
  • 3,360
0 votes
1 answer
72 views

Restoring One Node From an Availability Group With Recovery Pending

I have an availability group that is no longer valid (one node is gone). If I connect directly to the remaining node, the databases are all shown as recovery pending. In the Availability Group, the AG ...
Soma Holiday's user avatar
0 votes
0 answers
18 views

AGs with async commit or Zerto for DR to a secondary data center?

We have several two node AGs configured across two data centers for DR. They're all async commit due to the lag, and the company is ok with minimal data loss. We're considering replacing the AGs ...
Bobogator's user avatar
6 votes
1 answer
261 views

Distributed Availability Groups refusing to resync my FILESTREAM database with SUSPEND_FROM_CAPTURE

My home lab setup consists of four servers running in HyperV across two physical hosts. The SQL Server instances are SQLAG101, SQLAG102, SQLAG201, and SQLAG202. SQLAG101 and SQLAG102 are members of ...
Hannah Vernon's user avatar
  • 69.6k

1
2 3 4 5
31