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
2
votes
0answers
30 views

Researching Performance Degradation on AO Secondary using MultipleActiveResultSets (MARS)

Environment: SQL Server 2016 Enterprise Edition on Windows 6.3 9600 two node cluster with Always-On. We have an application that executes SELECTs on the only secondary node of a SQL Server 2016 always-...
-1
votes
0answers
16 views

First Responder Kit and Availability Groups

Is there anything in the First Responder Kit that deals specifically with Availability Group issues? Is there anything new developing? Thanks
0
votes
1answer
42 views

How do you handle maintenance window for DDL commands on Primary replicating to readable secondary AGs

We are currently running into this issue for one of our availability group design:- On Primary :- Nightly Table switch/Purges are happening (DDL) for approx. 2-3 hours On Secondary which is configured ...
0
votes
1answer
56 views

What is the fastest way to make a copy of a 5TB SQL Server DB on the same server without having to fully reinitialize the DB to add it to the AG

I have a 3 node Always On AG that hosts a 5TB database. Due to business requirements, at 12:01am on the first day of every month, I need to create a copy of that database on the 3rd node (replica) ...
0
votes
1answer
26 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.
0
votes
0answers
42 views

Readers blocking reader on readable secondaries

We recently migrated to AG setup for one of our VLDB's around 20+TB. Current setup is Multi subnet AG with primary accepting mix workload while heavy reporting offloaded to readable secondary replicas....
3
votes
2answers
87 views

Blocking on readable secondary replica

We recently migrated from LogShipping standby/read-only setup to Multi Subnet AG setup with readable secondaries. Generally on old setup we have select queries running for longer duration as the ...
1
vote
1answer
73 views

SQL Server Always On AG – Is a Passive Secondary Required for Automatic Failover?

It is my understanding that for automatic failover to occur in an Always On AG environment, the replicas just need to be configured with "automatic failover," "synchronous commit" ...
0
votes
0answers
26 views

SQL Cluster (Always On) - Database table replication

I have the following scenario at this moment. Two old sql servers - they have setup some kind of publisher subscriber replication from one database to another but only some of the tables at a 12 hour ...
0
votes
0answers
23 views

Aborting long running query under AG configuration [duplicate]

Currently we have a process where any query running over 2 mins (only select) in primary database participating in db mirroring is killed, running every 5 mins. As we have moved to Availability groups ...
0
votes
1answer
15 views

Accelerated Database Recovery with AlwaysON — long recovery time for secondary unexpeccted

So I was intrigued by the new SQL 2019 Feature Accelerated Database Recovery. It has some great use cases, such as being able to kill a long-running transaction and have it instantly rollback. I had ...
1
vote
0answers
42 views

SQL Always on Availability Groups Direct Seeding

Setting up a new SQL cluster and HA using SQL Availability Groups. SQL 2019 STD 2 Node Multi Subnet Basic AG Primary Node AWS Secondary Node VMWare on premise I am using the newer Automatic Seeding ...
1
vote
1answer
21 views

SQL Multi Subnet Always on High Availability

I am configuring a SQL Always on High Availability solution using SQL Availability groups, this will be spanning two subnets. Using Windows 2019 and SQL 2019 one node in AWS and one on Premise. When I ...
0
votes
1answer
107 views

Error 41145 after joining database to availability group

I keep getting from my SQL monitoring service the following alert: Error: 41145, Severity: 16, State: 1. Cannot join database to availability group. The database has already joined the availability ...
2
votes
1answer
64 views

SELECT query in a SP in AlwaysOn Availability Group Read Only Routing

There is a stored procedure which has select statement as well as insert and other statements. What will be the behaviour of the stored procedure in a Read Only Routing configured in AlwaysOn ...
1
vote
1answer
38 views

SPN for Availability group(AG) listener for SQL Server 2016

Requesting some inputs.. We have a 2 node SQL 2016 Availability group configured and apps currently use primary replica for connection. We are now planning to use listener instead. Questions is, how ...
1
vote
0answers
25 views

Are single-instance cross-DB transactions fully protected in Availability Groups as is, or do they require DTC_SUPPORT = PER_DB?

In a relation to MS SQL 2017 cluster: impact of disabling distributed transactions for an availability group, which is a similar but different question. We have an SQL Server 2016 for which we are ...
1
vote
2answers
33 views

Bypassing version store for changing column datatype

Database is Always On High Availability synchronised database on SQL Server 2019 with READ_COMMITTED_SNAPSHOT on and we are updating datatype from varchar to nvarchar (with "alter table" ...
0
votes
0answers
61 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
1answer
52 views

Log files in Always on Disaster Recovery Situation

In my Always on Avaliability group setup there are four nodes in the cluster in two datacenters, an avaliability group has for replicas, one on each node. Two replicas in the same datacenter have sync ...
1
vote
2answers
89 views

SQL Server Availability Group LeaseTimeout and slow IO

Our 5 main databases runs on a physical (2 * 8 cores, 512GB, Hypertreading) SQL Server 2016 SP2 Enterprise in a single Availability Gruop and sometimes we get errors that the lease-timeout has expired....
2
votes
1answer
42 views

Limit connection to specific database with AlwaysOn Primary

Using AlwaysOn, I have a user which previously we had granted rights on a database on the primary, disabled the login on the primary server and then created the same login on the secondary so that ...
0
votes
1answer
33 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
vote
1answer
63 views

Why does the log file grow larger than data file for an availability group database on the secondary replica?

I've got a SQL Server 2016 AG set up with only two replicas (primary and secondary). Both synchronous, non-readable secondary, and backup the primary only. Transaction Log backups occur on the primary ...
2
votes
3answers
272 views

Will SQL Server go offline if it loses network connectivity to SAN where master and msdb system databases reside?

I have a setup where 3 servers combined into Availability Group All 3 servers have local directly attached SSD drives, and user database files are hosted on these drives But the system databases (...
-1
votes
1answer
38 views

Availability Group is not failovering when a cluster core resource is failedover

I have created always on availability group.The cluster core resources failover is happening. The availability group (Automatic) failover is happening. But, when the cluster core resource is failed ...
0
votes
1answer
68 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 ...
0
votes
1answer
24 views

Does listener gets affected when we replace secondary replica with another server in Availability Group?

I have created Multi Subnet Always On Availability Group. Now, I need to replace secondary replica with another server in Availability Group. In this case, Do I need to re-create listener? OR The ...
0
votes
1answer
21 views

AAG trigger a query when failing over

I'm running a Failover cluster on windows 2016 servers (2 nodes) where I set up an AG in SQL 14.0.2027.2 This works good and I'm happy. But when it failover I need to fix my logins so I need to run a ...
0
votes
1answer
154 views

is Sql Server AlwaysOn AG a multi-site active-active replication technology? [closed]

Scenario FCI: I understand multi-site active-active replication in SQL Server can be installed with an FCI (Failover Cluster Instance). The replication part occurs at the storage level with a ...
0
votes
1answer
29 views

what will cause a sql server AG readonly replica to stop serving readonly clients?

I understand that if a failover happens (among any nodes in an AG), then all nodes lose their existing connections. And I assume that if a primary suspends data movement, that will have no effect on ...
2
votes
2answers
47 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 ...
0
votes
0answers
35 views

SQL Server 2019 AlwaysOnAG DBCC CHECKDB on synchronous replica setup as not readable

I've seen answers in this exchange that say DBCC CHECKDB should be able to run on AOAG synchronous replicas that are not open for read. That MS fixed that because it was a bug in 2016. I'm using SS ...
1
vote
3answers
42 views

Always On Availability Groups on single VM

Is there any way to implement Always On Availability Groups using SQL Server 2019 Enterprise Edition on a Single Virtual Server. The goal is not to have any HA or DR capability, but merely Read-Scale ...
1
vote
1answer
38 views

Multiple differences of opinion about transaction log autogrowth settings

We have a two-node SQL Server 2017 AG. There's a job that looks at autogrowth settings for our databases and makes sure they're not set to use a percentage. For the past day, its been continually ...
2
votes
1answer
33 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 ...
0
votes
1answer
29 views

Which is the best approach to “suspend” the activity of a whole AOG cluster thinking on start it again later?

My company is going to migrate some services from one on-prem site to another but the database servers will remain in the same location (for now). I've been asked for "suspend" all the ...
0
votes
1answer
74 views

Downgrade SQL Server from Enterprise to Developer edition in an Availability Group

I will be transitioning some non-production SQL environments from Enterprise to Developer edition over the next few months. I'm pretty settled on the procedure I will be using, which is laid out in ...
1
vote
1answer
438 views

Error Failover in Always on high availability groups

We have a problem with Always on high availability groups in Microsoft SQL Server 2016 (SP2) , When we want to failover to the secondary node manually, it fails because of this error: Failed to ...
0
votes
1answer
32 views

multisubnet listener

Good morning all , I have an alwayson instance with a multisubnet listener I configured RegisterAllProvidersIP = 0 in order to register a single DNS entry HostRecordTTL is configured at 1200 seconds I ...
1
vote
2answers
54 views

SQL Server 2017 Availability Group Deployment - Some availability replicas are not synchronizing data

I'm in the process of configuring my first cluster using SQL Server 2017's availability groups. I have some experience with SQL Server failover clusters, but wanted to migrate to the Availability ...
1
vote
1answer
226 views

SQL Server AlwaysOn: Lost of heartbeat and connection with secondary replica

Virtual Enviroment description: Hypervisor: VMWare WSFC composed by two nodes with Windows server 2016 Standard SQL Server AlwaysOn with synchronous replica and automatic failover SQL Server 2014 (...
1
vote
1answer
44 views

Why am I seeing poor performance on SQL server 2019 and asynchronous AlwaysOn

I have recently migrated a 2008 sql dB that’s about 170 GB to 2019 with asynchronous always on. The application that uses this dB hits it pretty hard and I’m seeing an increase of about 2 ms on every ...
0
votes
1answer
146 views

The connection is no longer usable because the server response for a previously executed statement was incorrectly formatted

One of our application AX2013 is connecting to our DB's in an Availability group spanning three SQL2016 nodes. End users are getting some error in the application that always point to the same error ...
2
votes
1answer
58 views

AlwaysOn failover DR testing

I have always-on group running on 4 node cluster. Each DC has 2 node running on it Data Center1: Node1: Sync Replica - Automatic failover Node2: Sync Replica - Automatic failover Data Center2: ...
0
votes
1answer
69 views

“Test-SqlAvailabilityGroup” takes too much time

Can anyone explain me why the command Test-SqlAvailabilityGroup takes too much time ? Is there a fastest way to check if a an availability group exists or not (preferably on PowerShell) ? Thanks.
0
votes
1answer
26 views

Biztalk in alwayson SQL 2019

Good morning all I am working on a Biztalk migration project On an alwayson SQL environment Who has an idea if BIztalk is compatible with ALWAYSON if YES it is from Which version? another question for ...
-1
votes
3answers
255 views

DNN Listener resolving to a secondary server in WSFC & AO AG

I have an Azure AO AG which consists of three servers (all WS-2019 & SQL-2019 CU8): A - Subnet 1 - Principal Server (Sync) B - Subnet 1 - Secondary Server (Sync) C - Subnet 2 - Secondary Server (...
3
votes
1answer
154 views

SSISDB master key in an Availability Group

I activated Always On support for SSIS and everything works fine until a fail over operation happens. Turns out the secondary nodes have a different service master key than the primary node and after ...
1
vote
1answer
57 views

SQL Always on High Availabity Groups Multi Subnet

SQL 2017 Standard in SQL always on two node Basic AG. There are two DNS records for the listener with the different subnet IP's The servers are across subnets and we have hit issues with an ...

1
2 3 4 5
26