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
1 vote
0 answers
267 views

Trying to understand why Availability Group replication being suspended didn't fire alert

We are running SQL Server 2019 CU14 and last night we had one of our databases within a secondary Availability Group suspend replication due to the following reason: Always On Availability Groups ...
user avatar
  • 31
0 votes
0 answers
21 views

Endpoints for more than one Availability Group [duplicate]

I have 2 Node Cluster. For each node, I have 2 instances and for each instance, I have one availability group. Do I need to define separate endpoint port for each of the availability group/Instance? ...
user avatar
  • 1
1 vote
2 answers
91 views

Move SQL Server Database Files

My current environment is Always On with asynchronous mode with readable secondary's, but no failover. I wanted to move database file which is already in AG. But I can't failover AG. Please advise ...
user avatar
  • 55
0 votes
2 answers
33 views

What is the impact on primary (in terms of locking) and restore process when secondary database is being read by large query?

I am exploring couple of solutions: Log shipping - say a large query is running and meanwhile the log shipping process is triggered. Will this stop the query or delay the log shipping restore? ...
user avatar
  • 2,452
1 vote
1 answer
79 views

Should I offload my SQL Server backups to a secondary replica?

My production server doesn't have enough horsepower to run backups locally without causing impact to production. I'd like to offload them to my secondary replicas, to free up resources on my primary. ...
user avatar
0 votes
1 answer
110 views

Updating DNS on Availability Group database servers

We have always on availability group that spans across different data centers. Our network team has decommissioned some DNS servers and those servers will need to be updated with the active ones on ...
user avatar
  • 1,007
1 vote
1 answer
79 views

What will happen if an Availability Group failover occurs while a database is undergoing encryption via TDE?

As the title says, we have an existing availability group with a few databases and we'd like to enable TDE. I know that to do so we want to make sure all the servers have a database master key and the ...
user avatar
  • 198
2 votes
0 answers
29 views

Controlling permissions for app team while assigning them ALTER ANY AG access

There is a need where we our DEV team needs to make index changes and run other index maintenance jobs on the primary replica. We have seen that while this happens, there is a blocking chain created ...
user avatar
0 votes
1 answer
187 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 ...
user avatar
  • 11
5 votes
1 answer
368 views

Is it possible to use Read Uncommitted isolation level on read-only Availability Group secondary?

We are using availability groups in SQL 2019 Enterprise edition. We are using the enterprise feature to allow the AG secondary nodes to be in Read-only mode, and then running reporting queries against ...
user avatar
  • 839
4 votes
1 answer
266 views

Why significantly worse Column Store performance on Read-only AG secondary DB?

We have a pair of SQL 2019 Enterprise instances hosted in identically spec'd and configured VMs in Azure. They for a simple primary/secondary availability group array, containing a small number of ...
user avatar
  • 839
0 votes
0 answers
12 views

Joining a Virtual Box VM AG node to an AD domain VM without a phsyical network adapter installed

I've set up one VM as the AD and domain controller server, and one VM as an Availability Group node to learn how to use AD and Availability Groups. However, I'm unable to add the the node server to ...
user avatar
  • 11
0 votes
1 answer
75 views

Rolling Upgrade Win2012R2/SQL2012 Availability Group to Win2019/SQL2019

To do a rolling upgrade of an Availability Group from Windows 2012R2, SQL 2012 to Windows 2019, SQL 2019 do I have to have an intermediary set of servers on Windows 2016? Based on the documentation ...
user avatar
0 votes
2 answers
109 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 ...
user avatar
  • 1
2 votes
1 answer
187 views

SQL Server Availability group and Backups

SQL Server availability group backup preferences. We have 3 nodes for our AG. I would like to take full and differential backups on primary, log backups on secondary. When I configured "any ...
user avatar
  • 23
0 votes
1 answer
45 views

Query plan XML: one stmt's DegreeOfParallelism is 1 for any maxdop other than 1 (maxdop 1 leads to DegreeOfParallelism = 0 with reason MaxDopSetToOne)

I have a procedure with two SELECTs. One statement respects the server's/database's/query hint's maxdop setting and uses parallelism, the other is being difficult and never going parallel. The 8 core ...
user avatar
0 votes
2 answers
245 views

Migrating from old AlwaysOn Availability Group to new AG cluster but keep ip addresses

We want to migrate our SQL Server 2012 on server 2012R2 to SQL Server 2019 on server 2016 and keep the ip addresses but change hostnames. i can not change the name of listener and ip addresses from ...
user avatar
  • 1,048
1 vote
1 answer
184 views

SQL Server Distributed Availability Group databases not syncing after a Global Primary Replica Failover

SQL Server Distributed Availability Group Environment AG1 => Node1 (AG1-Global Primary) and Node2 (AG1 - Secondary Replica) AG2 => Node3 (AG2-Forwarder) and Node4 (AG2 - Secondary Replica) DAGE ...
user avatar
1 vote
1 answer
65 views

AG failover successful, but old Primary node thinks its still primary after bringing back online

I am doing some testing on SQL 2017 Availability Groups and have the following setup: 1 SQL Clustered instance on a 2-node WSFC in DC1 1 standalone SQL node in DC2 There is 1 AG, containing 3 DBs, ...
user avatar
  • 13
3 votes
2 answers
363 views

CPU cores and sys.dm_os_workers

We recently failed over our AOAG cluster to a secondary region. In the first region, our boxes had 32 cores while in the secondary region, we have 64 cores. Traffic is similar, however on the larger ...
user avatar
0 votes
1 answer
42 views

SQL Server - Is it a good idea to persist temporary stats built on AG read-only secondary?

I am curious if there are any benefits to running the below query every hour or even once a day to track whenever a temporary stat is created on an AG secondary/read-only replica and then having a ...
user avatar
0 votes
1 answer
59 views

Can Availability Group be configured in a below structure?

I have Production and DR clusters as depicted on a picture below. My question is whether there is a way to configure Availability Group from datacenter1 to datacenter2? I tried to configure regular ...
user avatar
  • 637
0 votes
2 answers
148 views

AlwaysOn Availablity Groups and database count limits

Update: Basic answer: I asked for something that isn't part of the design of AOAG. Which in my eyes makes this a 'not a good question to ask'. Unless this offends someone, I'll keep it here in the odd ...
user avatar
2 votes
1 answer
116 views

What is the correct configuration for the SQL Server listener with 2 nodes on-prem and 1 node in Azure?

I am setting up a 3-node Availability Group with 2 nodes on-premises and the 3rd on a VM in Azure. All nodes are SQL Server 2019 Enterprise with CU14. The Availability Group is fine and the listener ...
user avatar
0 votes
0 answers
109 views

Distributed Availability Group between a domain and a workgroup

I want to implement “Distributed Availability Group” on Windows Server 2016 and SQL Server 2019 and between a domain and a workgroup. I follow the steps that described in this link: https://docs....
user avatar
2 votes
1 answer
91 views

Availability groups across sites: what happens if severs can't contact each other but both still can reach file share witness?

I'm currently planning a new SQL Server 2019 Enterprise Edition setup with HA/DR, possibly with availability groups. Let say we have the following setup: Site A: one SQL Server instance (SQL1) Site B:...
user avatar
2 votes
1 answer
80 views

Transactional Replication vs AO for MS Sql Server

When we set up web servers in AWS, we have a load balancer which allows highly scalable performance improvements per instance provided. I would like something similar for MS Sql server. I have been ...
user avatar
  • 21
1 vote
1 answer
125 views

Isolation level with SQL Server and Azure Sql Database Read-Only replica

I read here and here that, when connecting to a Sql Server read-only replica, the isolation level used is ALWAYS SNAPSHOT. It says it ignores SET TRANSACTION ISOLATION LEVEL statement and any locking ...
user avatar
  • 446
0 votes
1 answer
139 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 ...
user avatar
0 votes
0 answers
29 views

Procedures to safely perform SQL Server Always On database cluster failover during Updates & Patching?

I need some steps and guidance, to perform the Microsoft Cluster Failover (MSCS 2016) for my SQL Server 2017 Enterprise Edition – Always-On 2x nodes. This is to perform the Windows Server updates that ...
user avatar
0 votes
0 answers
253 views

Linked Server Connection Issue to SQL Server AlwaysOn Listener

I have SQL Server 2008R2 Standard Edition. I am making a Linked Server connection to a Listener of an AlwaysOn Availability Group. However, when I run a query from SSMS I intermittently get this error:...
user avatar
  • 1,344
1 vote
1 answer
277 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....
user avatar
  • 1,048
0 votes
1 answer
95 views

Create availability group between FCI and standalone node

first question here so please advise if I've missed anything. I've read this question (AG of 2 replicas on 2 nodes in WSFC : is it possible to add 3rd replica to AG and NOT add 3rd machine WSFC? ) but ...
user avatar
  • 13
1 vote
1 answer
272 views

log_reuse_wait_desc = AVAILABILITY_REPLICA, log is 100% full but AG is all fine

That's a weird scenario. The transaction log is full. It waits on AG. AG is all good. No latency, last commit/hardened time is almost real-time on all the nodes. The Estimated Recovery Time is zero. I ...
user avatar
  • 335
0 votes
1 answer
420 views

Intermittently failover of my SQL Server resources on Windows Server 2016

I have 2 Windows 2016 VM's running on Vmware ESXi VMware ESXi, 6.7.0, 17700523 with VMDK's as the SQL disks. I have a SQL 2017 AlwaysOn Cluster running on Server 2016. Basically everything is pointing ...
user avatar
  • 1,048
0 votes
0 answers
36 views

SSIS Why is "Enable Support for AlwaysOn wizard" coming up blank?

I'm setting up SSISDB into a AO on SQL Server 2017 enterprise. The AO i set some time ago and appears to work fine. I've added the database to they AO, dashboard is showing everything as green. ...
user avatar
0 votes
0 answers
35 views

Azure SQL Server two-node Workgroup Availability Group

I have two SQL Server VMs in Azure. They are not on a domain, they are in an Azure DMZ to remain secure. We currently are utilising SQL Mirroring for DR & patching purposes. However due to the ...
user avatar
1 vote
1 answer
127 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 ...
user avatar
  • 11
0 votes
2 answers
2k views

SQL Server 2019 Always On - Ensure that cluster name object (CNO) is granted permissions to the Secure DNS Zone

I have here a SQL Server 2019 Always On with a lot of errors like this one: 2 nodes configured in a cluster with file share witness. Cluster name: SQLCLS Listener name: SQLLSN Event: Cluster network ...
user avatar
  • 1,048
0 votes
1 answer
38 views

AlwaysOn availability group listener

There is a server with me that is already configured with AlwaysOn by somebody else. I am adding one more availability group to the server. Can I use the same availability group listener for the new ...
user avatar
0 votes
1 answer
113 views

Can we add FCI to AG, or a single instance from FCI to AG?

I am exploring FCI and AG. In FCI we can have 2 nodes that share the same SAN storage. And failover results in the other node starting up. Where as AG is for a set of dbs. This question is about the ...
user avatar
  • 2,452
0 votes
0 answers
45 views

Scheduling IBM Spectrum Protect (TSM) backups via IBM Workload Scheduler (IWS)

We are switching from CA7 to IWS scheduler. Currently, the SQL backups scripts are called from CA7 with no issue. That includes a couple of two node SQL Always On Availability Group (AOAG) cluster as ...
user avatar
0 votes
1 answer
68 views

Why I can't take differential backup of primary replica?

I am trying to take a differential backup of my primary database by connecting to the primary replica, but I can't do that. We use SQL Server 2017 EE with latest CU. Database is in full recovery model ...
user avatar
  • 1,183
0 votes
1 answer
41 views

Offline Server Still has Quorum Vote

I have an Availability Group with 2 replicas. One replica is completely powered off. When I look at the cluster quorum info, the server that is powered off still has a quorum vote. How can that be? ...
user avatar
0 votes
2 answers
119 views

Memory configuration and exhaustion on Always On cluster

I have a 2-node SQL Server Always On cluster hosting 3 SQL Server instances and configured as follows: Total server memory is 768G for each nodes. Memory consumption is 98%. LPIM is activated. ...
user avatar
  • 283
0 votes
0 answers
108 views

Why is the FILESTREAM data not accessible, after AG listener is created?

I'm having a hard time figuring out why my app can't access filestream data, after we enable AG listener. Background info: Initial environment: APP Server Web Server IIS Windows Server 2012 R2 DB ...
user avatar
0 votes
1 answer
111 views

Failover and Failback SQL server Distributed availability group with different versions

I know that SQL server Distributed availability groups can sync data between two different Major versions, Lets say Global Primary - 2016 Secondary AG - 2017 This sync will work and I can do failover ...
user avatar
  • 1,708
0 votes
1 answer
655 views

SQL Sever Alwayson - Increase the WSFC failover threshold

Im trying to simulate the Failover in my SQL server 2017 alwayson by stopping and starting the SQL server service. It worked 2 times, but after that, the failover is not happening, It showing an error ...
user avatar
  • 1,708
0 votes
0 answers
35 views

Hidden SQL server replica in always on availablity group

I want to deploy SQL server 2017 always on availablity group also with a replica for DR purpose. I want to use 1 primary(prod) 2 secondary(prod) 1 secondary(DR) Now my question is, the failover can ...
user avatar
  • 1,708
0 votes
2 answers
38 views

SQL server 2017 distributed availability group connectivity after failover

I'm going to deploy SQL server distributed availability groups with SQL server 2017. From the documentation, I understand that we can have listeners for primary AG and secondary AG. The application ...
user avatar
  • 1,708

1
2
3 4 5
29