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
0answers
12 views

Job to remove logins on secondary replica that don't exist in primary replica

How to remove logins on a secondary replica that don't exist in primary replica? I want to create automated jobs that should check if the login still exists on a secondary after the login on the ...
3
votes
1answer
50 views

Availability Group- Redo Rate displayed on AG Dashboard vs Perfmon counter

I am bit confused on checking the metrics for REDO Rate KB/Sec from Always on AG dashboard, which for some scenarios matches with perfmon counter Database replica : Redone Bytes/Sec ( hopefully it's ...
1
vote
1answer
48 views

Alter a view that refers to a non-readable secondary database

I have a database (DB1) that is not readable because it is secondary in an AG group. I have another database (DB2) that is not replicated with the AG group. It contains some views that reference the ...
0
votes
1answer
40 views

HA options for a VLDB

We have 2 VLDB's - 6TB (primary db, not much OLTP traffic) and 4TB (archive db). Data will be moved/archived (delete from primary and insert to archive) daily from 5 large tables. Currently this ...
0
votes
1answer
31 views

Join duplicating the data - SQL Server

I am working with SQL Server Always On and I am querying the data as follows: SELECT name as AGname, replica_server_name, CASE WHEN (primary_replica = replica_server_name) THEN 1 ELSE '0' END AS ...
0
votes
0answers
27 views

Dynamic SQL and AO AG - SQL Server

I have 3 servers with an SQL Server instance (S1\PROD, S1\PROD, S3\PROD) in my cluster and I have a couple of DBs hosted on these with AG AO. I have a lot of DBs and they have 2 replicas (1 primary ...
0
votes
0answers
25 views

Linked Server Issues SQL Server to Oracle

I am trying to set up a linked server from SQL Server Production to one of our Oracle instances but experiencing this error : The test connection in ODBC succeeds, but it fails on SQL Server side. I ...
0
votes
1answer
30 views

Delay between Primary and Secondary DB

How to measure the delay between secondary node to catch up with the primary node? I am using red gate to monitor the databases and this is what mentioned there Guideline values: To estimate how long ...
3
votes
1answer
69 views

Stuck REDO Thread of an availability group

We are facing issues with REDO queue fluctuating as we have readable secondary configuration. Based on my understanding in newer versions of SQL Server, redo threads have been made parallel. Current ...
0
votes
0answers
15 views

Not able to select secondary replica using failover wizard during manual failover

When I am performing failover using ssms 18.6 GUI from primary to secondary replica the replica selection box is disabled. The same task i can able to do using other version of ssms. My team members ...
0
votes
1answer
19 views

SQL Always On automatic failover incase of multiple secondary

I have three node setup for SQL Always on in SQL 2017 enterprise edition. All three are in same subnet and in synchronous commit mode. Now when there is a failover how do I make sure it should always ...
0
votes
0answers
40 views

Always On Availability Groups deleted when server rebooted

I encountered an issue in which after a server reboot (reboot was not done gracefully), the AGs on that server were automatically deleted. The error encountered was the following: AlwaysOn: WSFC AG ...
0
votes
0answers
33 views

Ola Hallengren Maint Solution - USER_DATABASES vs @AvailabilityGroup = AG1

I have two SQL 2016 Instances that are configured to use Always On AGs (1 AG group with around 25dbs in it running between the two instances) There are also a number (5-10) of Read Only databases on ...
2
votes
0answers
35 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-...
0
votes
1answer
50 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
59 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
27 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
45 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
93 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
77 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
28 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
24 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
17 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
45 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
197 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
68 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
62 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
28 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
79 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
119 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
46 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
38 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
65 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
274 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
83 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
215 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
58 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
36 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
41 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
37 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
31 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
121 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
2 3 4 5
27