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

Always-On listener not working correctly

I have an Always-On architecture with two nodes and two AG. One of AG working correctly but other AG's listener only directs to the primary node. When I fail it to the secondary node, the application ...
mbielsa's user avatar
  • 42
4 votes
2 answers
268 views

HADR_SYNC_COMMIT waits on SQL Server

Let me preface this post by saying that I am missing a few events from my trace, but I have since added them for the next time this occurs. Lately we have been seeing weird spurts of HADR_SYNC_COMMIT ...
Data Dill's user avatar
  • 235
0 votes
0 answers
24 views

sqlserver alwayson audit setting

I am running SQLSERVER ALWAYS ON 2019 ENT VERSION. I'm trying to STOCK an audit at the database level in SQLSERVER. In the master, it was confirmed that the log audit was left at DATABASE LEVEL. ...
user avatar
0 votes
1 answer
59 views

best practice for side by side sql version upgrade with Availability groups

We are attempting our first ever side by side upgrade of a 2 node(multi-subnet, enterprise edition) AG from sql 2016 to 2019. I have some questions on how best to approach this: Do we create a brand ...
DBA Greg14's user avatar
0 votes
1 answer
47 views

Creating AGL for Named Instance on SQL Server 2016 [duplicate]

I have installed a Named Instance on an existing server so the set up is like the following: Server 1: QA01 - Default Instance QA01\BU0 - Named Instance Server 2: QA02 - Default Instance QA02\BU0 - ...
kaitlyn's user avatar
  • 573
0 votes
1 answer
42 views

How to calculate the time for a transaction start to commit to be communicated to the client application?

I have a 2 node AG in sync mode. AG transaction delay value is about 60ms/s. This is the sum of all transactions delays in that second. The transactions/sec value is around 1000. So the delay per ...
variable's user avatar
  • 3,442
0 votes
0 answers
23 views

Best Strategy to copy database using Secondary Database (not touch primary) to dedicated database server

i have application team that have dedicated database server for datawarehouse. I want to know what is the best way to copy database from secondary. Current Environment Always On 1 Primary 1 Secondary ...
zul nasir's user avatar
0 votes
1 answer
47 views

Configuring SQL Server 2019 Always ON between 2 Azure reqions

I have SQL Server on Azure VM which I want to replicate across a separate region. I will only be using 2 servers, each in a separate region. I have the following questions as I need to clear all ...
dennis's user avatar
  • 1
0 votes
1 answer
50 views

In an Always On configuration, why do I have to open ports of the listener as well for the sql server engine itself for the listener ips?

We use an SQL Server 2019 Always On setup. 2 sql server 2019 nodes in same subnet : server01, server02 1 sql server 2019 node in other subnet : server03 We decided to create only 1 availability group ...
PekeDBA's user avatar
-1 votes
1 answer
57 views

Denying access to some databases

I need to deny a user access to certain databases in the SQL Server 2019 instance. The user should however be able to migrate all the other databases in the instance to another instance. What is the ...
PTL_SQL's user avatar
  • 303
0 votes
1 answer
94 views

AlwaysOn AG failure when Secondary is rebooted

I have a 3 node AlwaysOn AG Node 1 - Synchronous Node 2 - Synchronous - Primary Node 3 - Asynchronous Quorum Configuration: Node 1 - Vote Node 2 - Vote Node 3 - NO VOTE File Share Witness - VOTE ...
Sicilian-Najdorf's user avatar
0 votes
0 answers
58 views

How to activate MSDTC for SQL Server 2019 failover cluster

I have configured a failover cluster and always on SQL Server 2019 high availability group on 2 nodes and operating system with Windows Server 2019. I am trying to configure the MSDTC properties of ...
Kalusql's user avatar
0 votes
1 answer
37 views

Why do I need to create endpoints after configuring a aoag?

So recently on new servers we are building, after creating the aoag and add servers, they don't go in sync, and I need to manually run this: CREATE ENDPOINT Hadr_endpoint STATE=STARTED AS TCP(...
Racer SQL's user avatar
  • 7,374
0 votes
2 answers
122 views

Adding a Windows Server 2016 computer to a Windows Server 2022 Failover cluster

Is it possible to join a Windows Server 2016 computer to an existing Windows Server 2022 2-node Failover cluster? The cluster is domain-independent. Hypothetical setup: Node 1 (windows server 2022 + ...
Marcus's user avatar
  • 47
0 votes
0 answers
38 views

What is the significance of failback setting in a SQL AG?

I have a 2-node AG with WSFC setting configured as allow failback between 3am and 7am. I restarted node 1 at 1am which caused an automatic failover to node 2. However there was no automatic failback ...
variable's user avatar
  • 3,442
0 votes
1 answer
35 views

Can we do restoration using full backup on secondary and , differential and log backup on primary?

i have strange requirement in my environment but i need to ask this question to meet the business objective. Can we do restoration using full backup on secondary and differential and log backup on ...
zul nasir's user avatar
0 votes
1 answer
166 views

How do I modify session timeout on a replica for a Distributed Availability Group?

I have a Distributed Availability Group in my production environment that occasionally has a timeout. When it does I get the following message in the AlwaysOn_health on the forwarder: A connection ...
user3593990's user avatar
0 votes
2 answers
178 views

SQL Server Always-On adding database fails with insufficient free space

We had a crash on our SQL AAG where our secondary database ended up being corrupt. The Primary database is all goood, and still working, and was not affected in any way. So now I try to get the ...
Tim's user avatar
  • 1
0 votes
1 answer
73 views

Failover in AG without listener

We have SQL Servers with versions SQL Server 2016 and SQL Server 2019. We already check that we can do failover on SQL Server 2019 without listener but I can not test it on SQL Server 2016. Do you ...
adam.g's user avatar
  • 295
0 votes
1 answer
108 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
68 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
43 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,442
0 votes
1 answer
103 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
75 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
33 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 ...
Keivan Soleimani's user avatar
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
8 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
45 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
40 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
48 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
27 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
39 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
40 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,442
0 votes
1 answer
43 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
29 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
938 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
77 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
45 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
  • 412
0 votes
1 answer
66 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
24 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
97 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
  • 347
-1 votes
1 answer
246 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
16 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
18 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 ...
Marcus's user avatar
  • 47
0 votes
1 answer
179 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
0 votes
1 answer
89 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,442
0 votes
1 answer
147 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?
Marcus's user avatar
  • 47
0 votes
1 answer
117 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
90 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

1
2 3 4 5
31