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
0 answers
26 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 ...
user avatar
  • 2,127
1 vote
0 answers
65 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 ...
user avatar
  • 397
1 vote
1 answer
35 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 ...
user avatar
  • 397
0 votes
1 answer
2k 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 ...
user avatar
2 votes
1 answer
349 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 ...
user avatar
  • 357
1 vote
1 answer
871 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 ...
user avatar
  • 73
2 votes
0 answers
103 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 ...
user avatar
  • 1,341
1 vote
2 answers
34 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" ...
user avatar
  • 256
0 votes
0 answers
540 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 ...
user avatar
  • 1
0 votes
1 answer
61 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 ...
user avatar
  • 63
1 vote
2 answers
1k 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....
user avatar
2 votes
1 answer
54 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 ...
user avatar
0 votes
1 answer
310 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 ...
user avatar
1 vote
1 answer
124 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 ...
user avatar
2 votes
3 answers
385 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 (...
user avatar
-1 votes
1 answer
157 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 ...
user avatar
0 votes
1 answer
291 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 ...
user avatar
0 votes
1 answer
26 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 ...
user avatar
0 votes
1 answer
32 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 ...
user avatar
  • 1
0 votes
1 answer
2k 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 ...
user avatar
0 votes
1 answer
33 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 ...
user avatar
2 votes
2 answers
211 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 ...
user avatar
1 vote
3 answers
54 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 ...
user avatar
1 vote
1 answer
46 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 ...
user avatar
  • 169
2 votes
1 answer
62 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 ...
user avatar
  • 369
0 votes
1 answer
49 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 ...
user avatar
0 votes
1 answer
826 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 ...
user avatar
1 vote
1 answer
2k 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 ...
user avatar
  • 21
0 votes
1 answer
47 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 ...
user avatar
1 vote
2 answers
154 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 ...
user avatar
1 vote
1 answer
794 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 (...
user avatar
  • 97
1 vote
1 answer
441 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 ...
user avatar
  • 111
0 votes
1 answer
688 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 ...
user avatar
2 votes
1 answer
225 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: ...
user avatar
  • 95
0 votes
2 answers
143 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.
user avatar
  • 101
0 votes
1 answer
62 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 ...
user avatar
-1 votes
4 answers
1k 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 (...
user avatar
  • 84
3 votes
1 answer
580 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 ...
user avatar
1 vote
1 answer
490 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 ...
user avatar
  • 397
5 votes
2 answers
519 views

Availability Group - how much data was lost after forced failover

Let's consider a SQL Server AlwaysOn cluster with two nodes in asynchronous replication. Is there any way to calculate how much data was lost after forced failover? I mean in terms of time, to be able ...
user avatar
  • 97
0 votes
1 answer
614 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.
user avatar
0 votes
2 answers
1k views

Resource Hosting Subsystem was terminated which caused Availability group to fail

The following error was occurred in cluster events and the availability group was failed which resulted the databases in non-synchronizing state. A component on the server did not respond in a timely ...
user avatar
0 votes
1 answer
117 views

SID for SA account

I want to change the SA password for the always on availability group servers. Since i wanted to keep the same password on all the replicas when I tried to transfer the login through Microsoft Stored ...
user avatar
  • 1,007
1 vote
1 answer
44 views

Sync logins and Agent jobs across all replicas in Distributed Availability Group

I am using dba_CopyLogins stored proc to sync logins on replicas in Distributed Availability groups, but the database level permissions are not transferred due to database sync operation is going on.. ...
user avatar
  • 11
1 vote
1 answer
67 views

Upgrading SQL 2016 AGs to SQL 2019 question

I am doing a rolling upgrade of my production SQL 2016 Servers to SQL 2019. I have availability groups. My current structure is 3 DR servers (secondaries), 3 HQ (on prem) secondaries and 3 HQ ...
user avatar
0 votes
1 answer
57 views

Web server accessing the database server in Always on availability group

We have a web server that is on the DMZ but that will need to query a database that resides inside the network. Now the network team wont allow port 1433 from the web server to the Database server. ...
user avatar
  • 1,007
1 vote
1 answer
228 views

Troubleshooting slow connection establishment

The client and the server are running on Windows on different subnets. I'm facing a 20s delay to establish a connection to a SQL 2012 Always On availability group using .NET Core Sql client using sql ...
user avatar
0 votes
1 answer
191 views

activate Service Broker on a database in an Availability Group

I found some older posts dealing with this(5 yrs old or more), but I am confused on the process or if this information is out of date, hence my new question. I am on sql 2016 SP2-CU11 enterprise. I ...
user avatar
2 votes
1 answer
120 views

Upgrade with DistributedAG vs adding a node with SQL Server 2019

We would like to migrate from SQL Server 2016 to SQL Server 2019. The databases are part of an Availability Group. I added a new replica using SQL Server 2019 on a new node and made that replica ...
user avatar
  • 73
0 votes
0 answers
34 views

Non-Domain Account Error while Creating Availability Group

I am creating Availability Group with two node cluster. Both servers are using same service account for SQL. But, while creating Availability Group, I getting that the SQL service account is using non-...
user avatar

1 2 3
4
5
29