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
1answer
19 views

Using CName to point to the Availability Group listener

I have SQL Server 2008 named "OldSQLServer". Its data is accessed by hundred of SQL Servers via linked server. I am moving the "OldSQLServer" to AG on SQL Server 2019, windows 2019....
0
votes
1answer
37 views

SQL Server Installation for a Distributed Availability Group

I am deploying a SQL Server 2019 Distributed Availability Group. I have created a WSFC at each of my two sites (spanning two servers each). Upon installing SQL Server, it is not clear to me if I ...
0
votes
0answers
18 views

Are Quorums necessary in a SQL Server Distributed Availability Group

I am creating a SQL Server 2016/2019 Distributed Availability Group, which will host 2 AGs (one at one location and another at a different location). Do I need to create a Quorum for each of the 2 ...
1
vote
1answer
39 views

Why sql service is still running after uninstalling failover cluster instance?

The installation of sql server failover cluster instance didn't go well. So I decided to remove whatever was installed and reinstall it again. To to uninstall sql failover cluster instance went ...
1
vote
1answer
56 views

SQL Server Always ON - LOGPOOL_CONSUMER wait and replication issue

I'm facing a strange issue in one of my customers AG. There're 4 instances under this AG. Staring a few days ago, my customer complains about some query (SELECT) timeouts issues. When I faced this ...
-1
votes
1answer
18 views

Remove 2 node Always on setup from SQL Server and failover cluster [duplicate]

We are using 2 node Always-on Availability Group, But Service provider don't support AOAG. So we need to remove one node from Always-on. How can we convert the primary replica to Standalone instance. ...
0
votes
1answer
38 views

SQL Server always-on using WSFC

I have a question about SQL Server Always on using Windows Server Failover Cluster(WSFC). I have 5 SQL Server instances in 2 datacenters using 2 subnets. My data centers: DC1 (192.168.1.0/24), DC2 (...
0
votes
0answers
16 views

SQL Server 2019 Always On Replica Disconnected

We are setting up Always On AGs in SQL Server 2019. The Availability Group will create on both nodes Node1 and Node2 but the database will not restore to the secondary node (Node2) because it is ...
0
votes
1answer
36 views

How can i get AG dashboard values via SQL Query across all supported versions of SQL server

I am trying to ease the monitoring of availability groups across multiple SQL servers. Came across few dmvs which got me the data. But cannot figure out some important columns which i am seeing in ...
0
votes
1answer
55 views

Always On Basic Availability Groups stuck as primary on both replicas

I configured Always on basic availability groups on my SQL Server 2019 Standard instances. One as our production machine and the second as a DR machine. I have primary set to synchronous commit and DR ...
0
votes
1answer
31 views

Availability Groups - Add new subnet

I have a two node Availability Group system which hosts several AvailabilityGroups. Each AG has it's own listener. The system was set up in an IP-subnet with very few free addresses left. How can i ...
0
votes
1answer
13 views

SSRS second entry never shows up in scale out deployment screen

I am trying to setup two SSRS servers in a scale-out deployment that use a Always-On availability group for the database. The AG is setup and working. I have both SSRS servers using the AG listener ...
0
votes
1answer
39 views

SQL Server data drive (.mdf files) running out of space

We have a D drive that has 999 GB of total space. Currently the production database data files are being stored there. It is running out of space. A few weeks ago DB files were shrunk to temporarily ...
0
votes
0answers
32 views

MSSQL plan guide usage on AAG replica

I'm trying to use plan guide on read only replica. I read some articles ( for instance this one ) and concluded that this is impossible. But then tried to perform some tests and got pretty unclear ...
0
votes
1answer
22 views

Turning off Secondary Replica in Always on availability group for maintenance

We are planning to turn off the secondary synchronous replica in our always on availability group for few hours for maintenance . Once we are done with the maintenance then we will need to failover ...
0
votes
1answer
34 views

Using custom port for listener

I have a requirement of migrating a cluster instance to an AlwaysON instance without changing the connection string from the application. My Existing connection string looks like- Server1\Instance1:...
0
votes
2answers
81 views

Testing WSFC failover puts secondary replica in RESOLVING mode

I have 2 locations and each has its own WSFC. Cluster 1: WSFC,Datacenter1, Node1, Node2. Listener1 Cluster 2: WSFC, Datacenter2, Node3, Node4. Listener2 Created an Availability group as EXTERNAL ...
0
votes
1answer
46 views

How to configure Always ON High Availability on this infrastructure?

I have SQL 2017 Enterprise Edition What would be the best way to set up High Availability in the below infrastructure? Datacenter 1 WSFC and FCI: Node1, Node 2, Shared Storage Datacenter 2 WSFC and ...
0
votes
0answers
37 views

Always Encrypted issue with Always On after failover

I'm running on a SQL Server 2017 Always On 14.0.3381.3 DB and I'm testing using Always Encrypted. I'm not sure if this is a SQL Server issue or a .NET issue. What happens is when I open up my .NET ...
1
vote
1answer
42 views

LISTENER_IP = ALL not working

I try to create an endpoint specifying to listen on any IP: CREATE ENDPOINT [Hadr_endpoint] AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( ...
0
votes
2answers
61 views

Solution for syncing a database between two server and secondary can be used for all DML operation

I need a solution to synchronize a database (SQL Server 2016 Enterprise) between two servers, and I would be able to connect my secondary application to the secondary database which is getting ...
0
votes
1answer
53 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 ...
1
vote
0answers
50 views

Windows Failover Cluster/Availability Groups - Issues because all servers of one site where unreachable

we had some issues because of maintenance and hope someone can explain what happened. Our cluster looks like this: Site A: Node A1 (Quorum vote) Node A2 (Quorum vote) Node A3 (Quorum vote) Node A4 (...
1
vote
1answer
72 views

Estimated recovery time and REDO queue size related to DB up time

Can someone help me figure out if my understanding is correct on the below: On the AG Dashboard for my readable secondary replica I see: Estimated recovery time (secs) - 4598 REDO queue - almost 24 ...
0
votes
0answers
27 views

Failover an Availability Group during a backup

This is a scenario I hope won't happen, but if I was to perform a manual failover on an availability group halfway through a database backup will this cause the backup to fail or would it continue? I'...
5
votes
1answer
204 views

In a multi-database AG, what decides which databases will have more than 1 redo thread?

We have a large physical server with more than 50 dbs on it; some are quite busy, others very quiet. When having multiple DBs in an AG, only some of them multiple redo threads. We wanted the databases ...
0
votes
1answer
48 views

Will AlwaysOn work with hundreds of databases in 1 group/multiple groups?

I have inherited a SQL 2017 system that had 114 databases that were synched to a second instance via an Always-On AG, with another 13 databases needing to be added. It was supposed to be providing ...
1
vote
0answers
44 views

Still can't create an AAG listener

I just built a test cluster with 2 MS SQL Server 2014 machines, 1 domain controller, an 1 file share witness. (All of them are Windows2016-based.) The 2 MS SQL Server machines are a WSFC members. When ...
0
votes
1answer
21 views

How to handle listener failure in AG?

Availability group has concept of listener that directs the requests to appropriate replica. So is listener a single point of failure? How to handle this?
0
votes
1answer
29 views

How to sync logins and jobs when using an availability group?

So I am configuring availability group which will sync the databases from primary replica to the secondary replicas. How to sync the jobs and logins so that when there is failover to secondary then ...
3
votes
1answer
65 views

Measuring Always On Availability Group potential data loss

I have read these articles few times. I do monitor potential data loss today using some of the methods described in these articles. Measuring Availability Group synchronization lag by Derik Hammer ...
0
votes
0answers
30 views

SQL Server: Always On Availability Group: getting Error: GetAccountToProcess failed: Cannot open database requested by the login. The login failed

When myself and our SA are testing our AO group, we are running a script that flows data into a table so we can monitor proper data. We are pointing this at our Listener: TestDBAG01, and it flows into ...
1
vote
1answer
47 views

Why do VSS Snapshots cause Availability Groups to fail over?

Why do VSS snapshots cause Availability Groups to fail over? Back in 2018, Ken Kellman and Erik Darling wrote that by freezing I/O on databases, VSS snapshots can cause Availability Groups to fail ...
0
votes
1answer
38 views

Why does the secondary server not failover?

I hope someone can help. I will try to summarise as best as I can. We have four servers in a cluster with an AG set with Database Health Detection. Three servers are on site1 with the fourth on a ...
0
votes
2answers
40 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
56 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
62 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
44 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
34 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
34 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
30 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
39 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
89 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
18 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
20 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
61 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
41 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
41 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
52 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
68 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) ...

1
2 3 4 5
27