Skip to main content

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

The Windows Server Failover Clustering (WSFC) resource control API returned error code 19

What is root cause of this error. I couldn't find any related information. There are two nodes on Availability Group and if you reboot second node, database wont up.
5 votes
2 answers
718 views

SQL AlwaysOn & System Center Configuration Manager TP 4 1601

Recently Microsoft released Technical Preview 4 for Configuration Manager (1601). Per TechNet, "Configuration Manager now supports using a SQL Server AlwaysOn availability groups to host the ...
0 votes
0 answers
21 views

Manual Failover of SQL Server Distributed Availability Group

Is anyone able to kindly shed some light on an issue for me please? I have two Distributed Availability Group environments, one is SQL 2019 and the other is SQL 2022. The SQL 2019 environment is fine, ...
0 votes
0 answers
28 views

Process to remove High availability and Break the cluster

We have a 4 node High availability group. We now will have to make the primary a standalone server and remove all the replicas and break the cluster. Before we attempt this on production below is what ...
3 votes
1 answer
412 views

SQL Server 2014 Availability Group failed

I have 3 servers on VMware workstation: Win 2012 R2 as ADDC -Cluster service. Win 2012 R2 as SQL server primary replica . Win 2012 R2 as SQL server secondary replica (auto fail-over). My ...
3 votes
2 answers
846 views

If the SIDs for ##certificate-based## SQL Server Logins don't match across replicas in an Availability Group, will this be a problem on failover?

I am reading mixed messages about server level object syncing between replicas in an Availability Group. As I understand it, server-level objects must be kept in sync (aside: then why isn't it part ...
0 votes
1 answer
1k views

SSRS subscriptions after failover

Shortly I will be migrating a 2008 R2 SSRS installation to a new SQL 2019 Always on AG in AWS. This one will contain subscriptions, and I have never dealt with them before. I read the documentation ...
-1 votes
0 answers
16 views

In a 3 node AG setup, when the third node is in a different data center, how to ensure it doesn't participate in cluster voting?

In a 3 node AG, where 2 nodes are in data center one, and 3rd node is in a data center in another region, how to ensure that the 3rd node doesn't participate in failover voting? That is - how to zero ...
1 vote
2 answers
2k views

Changing startup accounts on alwayson setup

I have a 2 node Always On Availability Group cluster where we are planning to take backups to a file share which will then be moved to Azure cloud. We have a service account from the Windows team for ...
0 votes
1 answer
39 views

DLL switch over issue while AOAG automatic failover

We have configured AOAG in our environment, but when performing automatic failover from one node to another, the DLL is not enabled automatically in the second node. We are manually enabling the DLL ...
0 votes
1 answer
4k 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 ...
0 votes
1 answer
7k views

(Solved) Sql Server Failover Clustering Error

I want to install always on on Sql server 2019. I have two windows server 2016. I will not use active directory. I do the installation as described on the site where I will leave the link below. When ...
0 votes
1 answer
17 views

Login via SQLServer AlwaysOn "Listener" Failed (Error: 18456, Severity: 14, State: 5)

I have setup an AlwaysOn Availability Group on SQLServer 2022. I have created a login on both replicas with same SID. But I could not login via listener IP or DomainName via SSMS. I could login ...
-3 votes
2 answers
79 views

2x SQL Server 2019 in AlwayOn group - how to remove transaction log [duplicate]

We have a problem with our Microsoft SQL Server 2019. We have 2 servers in an Always On group with one database. Problem is the actual (real) database size which is about 1 GB, but it takes up around ...
1 vote
1 answer
82 views

How to determine the global primary instance in a SQL Server distributed availability group using T-SQL?

I have a SQL Server distributed availability group set up, and I'm currently connected to the forwarder instance. I would like to know how to use T-SQL to query the appropriate DMVs or catalog views ...
2 votes
1 answer
2k views

Add shared folder to AlwaysOn AG Failover

We have setup a 2 node Synchronous AG with automatic failover to provide HA for our ERP application (Dynamics GP) The GP desktop client loads a number of shared reports and dictionaries from a file ...
0 votes
2 answers
496 views

SQL server alwayson 3 Node Primary Down

I am using three node alwayson Availability Group SQL 2012 Enterprise Edition. 2 Node in DC with File Share Witness quorum also in DC and third node to be setup in DR I need to perform a DR switchover ...
0 votes
2 answers
787 views

Always on, SQL Server Enterprise 2022, 3 node cluster: cannot run with only one node up

I have a new 3 node Windows 2022, SQL Enterprise 2022 cluster with file share witness. I am testing this before it goes fullly into production. I was hoping that it would run from only one node, but ...
1 vote
2 answers
2k views

Failover multiple Basic Availability Groups

We are planning to create multiple BAGs for SQL Server 2016 Standard Edition. My question is, should I need to same listener for all BAG? Let's say we have 3-4 AG's i.e AG1, AG2, AG3 and so on. If ...
1 vote
1 answer
729 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 ...
0 votes
1 answer
78 views

Failed to open the explicit specified database

When our application tries to connect to the Availability Group Database, we get the following error message on the primary replica: Login failed for user 'xxx'. Reason: Failed to open the explicitly ...
0 votes
1 answer
1k views

Unable to connect to Listener after rebuild

I dropped an AG with DROP AVAILABILITY GROUP [MyDb] it had a listener. I left the listener open in the object Explorer of SSMS 17.3. While the AG and Listener were down. I hit refresh on the ...
0 votes
1 answer
556 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) ...
0 votes
1 answer
1k views

Always On Instance Service Broker/Database Mirroring transport connection endpoint Error

I have a production instance that has 2 AOG. There is no mirroring configured for both groups. Both groups are also in 'Healthy' state. Upon checking the error log on SQL Server, I noticed that ...
1 vote
1 answer
76 views

Deny access to Sql Server replica when it becomes primary

TL;DR How to allow read-only access to some users on one replica (in an Sql Server availability group), only when the replica is secondary, and deny it when it becomes primary? Explanation I'm ...
1 vote
1 answer
1k views

SQL Server 2017 AlwaysOn Failover Cluster issue

I have 2 Windows 2012R2 VM's running on Vmware ESXi 7 with VMDK's as the SQL disks. I have a SQL 2017 AlwaysOn Cluster running on Server 2012R2. there is no any running backup (SNAPSHOT) PROD : 10....
0 votes
1 answer
149 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 ...
0 votes
0 answers
21 views

Is there a way to prove whether a connection used the MultiSubnetFailover argument? [duplicate]

I know that consulting sys.dm_exec_connections can show whether a connection is using an AOAG listener or was made directly to the instance. And I know that if a connection is successful that it was ...
0 votes
1 answer
47 views

Azure MS SQL Always On Availibity Group Setup Issue

I have 2 VMs on azure where MS SQL server is configured with always on high availability, one VM is acting as primary and other one is secondary. On top of that, there is domain controller VM which is ...
0 votes
1 answer
42 views

SQL Server Standard and Enterprise editions in AlwaysOn Availability Groups

Can two different versions of SQL Servers participate in AlwaysOn Availability Groups? I am specifically talking about SQL Server 2019 Standard and SQL Server 2019 Enterprise.
2 votes
2 answers
511 views

behavior of a Sql Server availability group listener and a jdbc client during a failover?

i have a question about the behavior of the AG listener during a failover... when the failover happens, I realize that all the connections on both primary and secondaries are closed. if the client ...
1 vote
1 answer
49 views

How to configure listener when using read scale AG?

I have a cluster-less AG (read scale AG) without a listener. Our network admin has set DNS entry to point to primary node. All applications use that DNS name for database connections. Post failover ...
0 votes
1 answer
48 views

Does availability group listener runs on both SQL Server nodes or does it run on a separate machine

Lets say I am setting up AlwaysOn Availability Groups and following are the nodes: Primary 192.168.1.10 Secondary 192.168.1.11 I try to create a DNS entry for availability group listener called ...
0 votes
1 answer
322 views

Increasing tempdb data files on 2 node Availability Group

I am planning to create additional tempdb files for 2 nodes in a SQL 2012 Availability Group. Unfortunately we do not have Instant File Initialisation turned on so the growth of the tempdb data files ...
0 votes
1 answer
486 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, ...
1 vote
1 answer
382 views

SQL AAG Cluster - Diagnosing Partner Quorum Failure

Recently we had a failure of our SQL Server (2016) Always On Availability Group, where our fileshare witness node was briefly inaccessible to our two nodes due to a CPU spike. We have two SQL server ...
1 vote
1 answer
1k 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
482 views

My availability group doesn't work correctly

I recently created an "availability group" on SQL Server. I created my Listener, all is well and the availability group appears in the roles of my cluster. However, I encountered a small ...
0 votes
0 answers
28 views

Configure multiple instances on a SQL Availability group cluster

I need some guidance please. My Distributed AG configuration is to be with: Two SQL 2022 named instances each with its own AG and AG listener on the Primary Cluster hence two DAGs on the one cluster ...
0 votes
1 answer
811 views

Using custom port for listener

I have a requirement of migrating a cluster instance to an AlwaysON instance without changing the connection string from the application. My Existing connection string looks like- Server1\Instance1:...
2 votes
1 answer
39 views

Can we take a copy-only backup for filegroups on the SQL Server from a secondary HA replica?

I am getting the below error while executing the script for taking filegroup backups from a secondary replica. Msg 3059, Level 16, State 1, Line 18 This BACKUP or RESTORE command is not supported on ...
0 votes
1 answer
78 views

Availability group failover-tempdb

Does anyone know if SQL AG will initiate automatic failover to secondary if tempdb is full in primary ? I believe Database level health detection is only for user database in AG
0 votes
0 answers
29 views

Connect to a Private SQL Always On Availability group listener with Azure App

We have a fairly standard Azure Webapp - connecting via public IP to a database server which is a VM hosted in Azure. NSG rules etc limit the traffic between the two. The VM hosting the database is ...
1 vote
1 answer
286 views

Measuring Availability Group synchronization lag - Clarifications

Can I get some help on below As I have started and learning about the Measuring of Availability Group synchronization lag between replicas inclusive of BCP , I want to understand and learn that does ...
0 votes
1 answer
837 views

Load Balancing SSRS inside an AlwaysOn Availability Group

We have 3 nodes in an Availability Group in SQL 2014: Primary Site: Server1 (synchronous primary) Server2 (synchronous, readable secondary) DR: Server3 (async, nonreadable secondary) We use ...
0 votes
1 answer
373 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
1 answer
53 views

Preferred seconday backup in AG - what about the primary ? Shows no backups taken

I did setup the Ola Scripts sticking to https://www.brentozar.com/archive/2015/06/how-to-configure-alwayson-ag-backups-with-ola-hallengrens-scripts/ Options are: @Databases = 'USER_DATABASES', @...
0 votes
1 answer
304 views

How can i get AG dashboard values via SQL Query across all supported versions of SQL server

I am trying to ease the monitoring of availability groups across multiple SQL servers. Came across few dmvs which got me the data. But cannot figure out some important columns which i am seeing in ...
1 vote
1 answer
486 views

Bringing an Availability Group online after taking it offline

Answer in the following thread says: activate Service Broker on a database in an Availability Group "You need to set RegisterAllProvidersIP to 0 Thereafter take the availability group offline and ...
0 votes
1 answer
1k 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 ...

1
2 3 4 5
33