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.
1,545
questions
1
vote
1
answer
556
views
Azure AG Listener not accessible from secondary Nodes
We are building PoC SQL 2017 Availability Group up in Azure. This is part of a longer-term plan to both provide resiliency & a path to upgrade in the Spring
I have been able to stand up the AG &...
CommunityBot
- 1
2
votes
2
answers
427
views
How to replicate jobs between nodes with SQL Server 2022
I created a contained availability group but I don't know why the jobs are not synchronized between the nodes.
What could be the problem? Shouldn't it do it automatically?
In response to the answer I ...
- 21
1
vote
1
answer
576
views
Any idea what Virtual Machine Cluster WMI is for?
We have a Windows Failover Cluster to host our SQL Server Availability Group.
We have a resource that popped up that no one recalls creating named "Virtual Machine Cluster WMI". We have ...
CommunityBot
- 1
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 ...
- 42
1
vote
1
answer
1k
views
Always On shared network location
To use the wizard from SSMS, we had to provide full access to "everyone" to the shared network location.
Is there a way to avoid this and just limit the access to a specific user?
Already tried the ...
CommunityBot
- 1
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 ...
- 30.1k
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.
...
- 35.2k
3
votes
1
answer
357
views
TransactionLog not getting truncated in AlwaysOn Cluster
Folks, stuck with a wierd transaction log truncation(reuse) problem in an AlwaysOn cluster and running out of options, the transaction log is not getting truncated and keeps growing.
Only one database ...
CommunityBot
- 1
2
votes
1
answer
2k
views
SSIS - Setup OLEDB Connection to SQL Server Always On Listener
I am trying to connect to SQL Server 2017 Always On from an SSIS package (built in SSDT 2017).
I am using an OLEDB Connection Manager. 'Application Intent' is set to READWRITE. However, where do I ...
CommunityBot
- 1
2
votes
2
answers
516
views
SQL server AlwaysOn alternate option for readintent=true
I know applications need certain(newest) version of ODBC drivers to connect an AlwaysOn SQL instance replica and to use the option "applicationIntent= readonly"
Now my question is , if some ...
CommunityBot
- 1
0
votes
2
answers
447
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 ...
CommunityBot
- 1
0
votes
2
answers
114
views
Name in sys.availability_groups_cluster returns double character length
'select name, len(name) as length from sys.availability_groups_cluster'
Returns some double values:
name
length
AG_QBUS_MFG01
13
AG_QBUS003
20
TEST_ONLY
18
- 83k
0
votes
1
answer
603
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.
CommunityBot
- 1
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 ...
CommunityBot
- 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 ...
- 45.9k
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 ...
- 30.1k
5
votes
2
answers
668
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 ...
CommunityBot
- 1
3
votes
2
answers
684
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 ...
CommunityBot
- 1
0
votes
2
answers
961
views
Can't remove "synchronizing/suspect" database after removed from availability group Sql Server 2019 Enterprise
I've removed a db from a synchronous availability group.The db shows "synchronizing/suspect" as it should on the secondary. I'm trying to remove this database cannot. I have tried:
DROP ...
0
votes
1
answer
5k
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 ...
CommunityBot
- 1
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(...
- 30.1k
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 - ...
- 30.1k
0
votes
1
answer
2k
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 ...
CommunityBot
- 1
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 ...
- 30.1k
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 ...
CommunityBot
- 1
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 ...
- 137
2
votes
1
answer
1k
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 ...
CommunityBot
- 1
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 ...
- 11
-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 ...
- 303
0
votes
2
answers
389
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 ...
CommunityBot
- 1
3
votes
1
answer
265
views
Upgrade and migrate SQL Server 2014 AlwaysOn AGs to SQL Server 2016, using the existing WSFC name
Here are the components:
WSFC Name: SQLPROD1
Existing Nodes in this "cluster": SQL2014OnSite and SQL2014OffSite
New Nodes to be added (?) to SQLPROD1: SQL2016OnSite and SQL2016OffSite
All 4 servers ...
CommunityBot
- 1
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
...
- 30.1k
3
votes
1
answer
402
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 ...
CommunityBot
- 1
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 ...
- 1
0
votes
1
answer
871
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 ...
CommunityBot
- 1
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 + ...
- 30.1k
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 ...
- 3,442
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 ...
- 1
0
votes
2
answers
387
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 ...
CommunityBot
- 1
1
vote
1
answer
904
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....
CommunityBot
- 1
1
vote
2
answers
1k
views
seeding mode in SQL Server 2019
Does activating the TF9567 seeding mode trace flag speed up the synchronization process in AlwaysOn?
I am using SQL Server 2019.
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 ...
- 35.2k
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 ...
CommunityBot
- 1
1
vote
1
answer
452
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 ...
CommunityBot
- 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 ...
- 38.8k
0
votes
1
answer
383
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, ...
CommunityBot
- 1
0
votes
1
answer
299
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 ...
CommunityBot
- 1
2
votes
2
answers
1k
views
Database Log Reuse Wait shows AVAILABILITY_REPLICA but database is no longer part of an availability group
My database log is growing out of control, I recently removed the database from an availability group by when I look at the log reuse wait description it shows "AVAILABILITY_REPLICA"
I have tried to ...
CommunityBot
- 1
1
vote
1
answer
874
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 ...
CommunityBot
- 1
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 ...
- 30.1k