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

Routing List Empty, and cant add using SQL either

Question: Why is my routing list empty? I have removed the secondary from the AG and re-added back, everything is healthy and synchronized. lets assume for the example that server1 is currently the ...
Mike Kay's user avatar
1 vote
2 answers
60 views

SQL Developer edtion as a new node within an Enterprise Availability Group

We currently have a 2 node Availability Group in our test environment running SQL Server 2017 Enterprise Edition. I need to downgrade these both to Developer edition, since we are not using this in a ...
user3346030's user avatar
0 votes
1 answer
76 views

Does application need to do anything to handle database failover?

I'm trying understand database failover (mainly in SQL Server) and what handling is required from application side to have zero downtime (no failures) to handle DB failover? Assuming that DBAs have ...
Hitesh's user avatar
  • 3
0 votes
1 answer
28 views

Is It Normal to See Warnings in the Availability Group Dashboard for an Empty SQL Server Availability Group?

I've recently set up a new SQL Server Availability Group using T-SQL scripts. The setup was slightly unconventional as I initiated the availability group without including any databases initially. ...
Just a learner's user avatar
0 votes
1 answer
28 views

what to do when database is not synchronizing in availability groups?

I am using Jonathan Kehayias - Monitoring Availability Group Replica Synchronization SELECT ar.replica_server_name, adc.database_name, ag.name AS ag_name, drs.is_local, drs....
Marcello Miorelli's user avatar
0 votes
1 answer
20 views

SQL AG with Synchronus Commits Latency Conerns same network but high transactions

We want to switch an SAP SQL database cluster to SQL Availability Groups. The plan is to have two replica servers in the same data center with synchronous commits, and one in the DR location with ...
Onlinerack's user avatar
0 votes
0 answers
27 views

How Does Time Sync Affect SQL Server AG Log Sync?

I'm overseeing an Always On Availability Group (AG) setup in SQL Server, configured with five servers. One server functions as the quorum disk, with the remaining servers designated as primary and ...
Ali varzeshi's user avatar
0 votes
0 answers
53 views

MSSQL 2022 always on contained audit not logging

when i try to create an audit from the listener he goes on the node directly not in the contained instance. Then the audit log only show's the node instance not the AG log How to create an audit on ...
Tisax190's user avatar
0 votes
1 answer
43 views

Help Needed: Setting Up a Distributed Availability Group with Manual Seeding in SQL Server

Hello SQL Server Experts, I'm in the process of configuring a Distributed Availability Group (DAG) in SQL Server and am looking for guidance on implementing it with manual seeding (back up restore) ...
Just a learner's user avatar
0 votes
0 answers
35 views

SQL 2022 replication does not work on non-standard port

Consider the following: 3 identical Windows Server 2019 VMs running WSFC (no shared storage) 3 SQL 2022 PROD instances across those 3 VMs (using WSFC/AG for easy hostname, three instance AG) 1 SQL ...
JulioQc's user avatar
  • 133
0 votes
1 answer
34 views

Clarification on Automatic Failover Rules in SQL Server Availability Groups

I'm configuring a SQL Server Availability Group with three replicas: node1\inst, node2\inst, and node3\inst. I have set node1\inst for automatic failover with synchronous commit, while node2\inst and ...
Just a learner's user avatar
-1 votes
1 answer
86 views

Redo thread getting blocked while capturing index physical stats

Noticed REDO thread getting blocked for the whole period of running following query, what could be the reason? It is on SQL Server 2019 CU21, The blocking was for almost more than 1hr+, for the entire ...
S.D.'s user avatar
  • 746
0 votes
1 answer
31 views

CDC cleanup doesn't work even when using sp_cdc_cleanup_change_table which completes successfully

I'm running the query below to find CDC tables with data beyond the configured retention depth: SELECT DB_ID() AS database_id, ct.capture_instance, object_id, source_object_id , rcounts....
Eitan Blumin's user avatar
0 votes
1 answer
65 views

Adding Database to SQL Availability Group seeding status

I have a SQL 2019 AG cluster and I add and remove databases to the AG successfully with automatic seeding, using the Wizard. We are now trying to develop Powershell automation for the process. So what ...
PTL_SQL's user avatar
  • 325
0 votes
0 answers
33 views

AG Listener goes offline during comms link failure

I wanted to try and get some clarity on some behavior we recently experienced with a number of our production clusters during an event where we lost both communication links. Ours clusters span two ...
bn25's user avatar
  • 53
0 votes
2 answers
27 views

setting up WSFC for AlwaysOn with one node acting as read replica for both instnaces ( AWS EC2)

I am trying to setup WSFC so that I can configure AlwaysOn. I have 3 nodes(A,B,C) and each has one instance (1, 2 and 3). I am trying to setup WSFC between A and C and another WSFC between B and C ...
SqlDBA's user avatar
  • 117
1 vote
1 answer
36 views

Secondary Replica can not be joined again

I had three replica in my availability group without cluster. The installed SQL Server version is: Microsoft SQL Server 2019 (RTM-CU14) (KB5007182) - 15.0.4188.2 (X64) Nov 3 2021 19:19:51 ...
Meyssam Toluie's user avatar
0 votes
1 answer
69 views

What could be causing tempdb version store growth on primary node in the absence of open/active transactions on readable secondary db?

This is a SQL server 2022 CU10 enterprise edition server, 2 node AG with readable secondary. 30 user dbs are in the AG. Via an inhouse SQL monitoring tool I observed tempdb version store growth on ...
variable's user avatar
  • 3,551
0 votes
1 answer
53 views

SQL Availability group primary not visible from secondary via sys.dm_hadr_database_replica_states

Trying to create a single pane of glass for all our servers as to the Availability Group State and Health. I want to do this by writing an app and calling t-sql statements to gather data from each of ...
Keith Clark's user avatar
0 votes
0 answers
109 views

SQL Server 2022: AlwaysOn AG databases can not sync after applying CU6 or higher

The setup is: OS - Win Server 2022 & MSSQL - SQL Server 2022. AlwaysOn Availability Groups are enabled on two (primary + secondary) replicas and use WSFC. Several availability groups are created ...
Tom_HJ's user avatar
  • 1
2 votes
0 answers
94 views

How to use Custom Certificate for SQL Server Availability Group Endpoint Authentication

I am setting up a SQL Server Availability Group using the commands provided in the Microsoft documentation. The process involves creating a certificate, backing it up with the private key, and ...
Neaj Morshad's user avatar
3 votes
1 answer
61 views

Can I use different endpoint URLs for the primary replica in a SQL Server Availability Group?

I am setting up a SQL Server Availability Group with three synchronous replicas within a cluster (one primary and two secondary) and an additional asynchronous replica from another cluster in another ...
Neaj Morshad's user avatar
2 votes
1 answer
68 views

Understanding SQL Server Availability Groups: Automatic Seeding of Databases

I'm delving into SQL Server Availability Groups (AGs) and have come across a fascinating feature that seems to simplify the process of setting up replicas – the concept of automatic seeding. From my ...
Just a learner's user avatar
0 votes
1 answer
63 views

Log .ldf file not truncate after several times of log backup on secondary database

i have always on setup in production environment. Scheduled backup (transaction log backup) already in place every 2 hours using netbackup on secondary database. The issue i am facing is sometimes the ...
zul nasir's user avatar
1 vote
0 answers
49 views

Automatically reconfigure SQL Server Reporting Services to connect to primary Availability Group node after failover

I have a SQL Server Availability Group for HA and a Distributed Availability Group for DR. I also have SQL Server Reporting Services running on all Availability Group nodes. For various historical ...
Hannah Vernon's user avatar
  • 70k
1 vote
0 answers
48 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
  • 56
4 votes
2 answers
404 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
  • 231
0 votes
0 answers
38 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
117 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
56 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
58 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,551
0 votes
0 answers
26 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
90 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
85 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
66 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
  • 325
0 votes
1 answer
281 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
98 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
39 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,392
0 votes
2 answers
362 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
  • 59
0 votes
0 answers
54 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,551
0 votes
1 answer
36 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
218 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
293 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
97 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
  • 345
0 votes
1 answer
298 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
81 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
54 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,551
0 votes
1 answer
143 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
124 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
34 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

1
2 3 4 5
32