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

I would like to migrate SQL SERVER 2014 to SQL SERVER 2019 on Linux using AG

I would like to migrate SQL SERVER 2014 on Windows to SQL SERVER 2019 on Linux using while running AG. Is there an option to Add the SQL SERVER 2019 to the same AG and once the time comes gracefully ...
2
votes
1answer
37 views

Availability Group Replica delay

looking for ideas for diagnosing the root cause of the following issue. We have 3 availability nodes Primary,Synchronous replica, Asynchronous replica. no load other than T-Log backups exists on the ...
3
votes
1answer
110 views

Exactly same query and plan but different duration and total reads. I Know parameter sniffing but

I know what is parameter sniffing but this seems different, I have this simple query: select * from A400RDATA This table has only a cluster index and approximately 25000 rows. The query usually ...
0
votes
1answer
11 views

SQL server Listener removal [closed]

Hi I am planning to drop and recreate the listener with same name what is the best approach? Can i drop the listener from WFCS by removing the resorces or Drop the listener from SQL using SQL command? ...
0
votes
0answers
33 views

Node is Losing Connection to Quorum in an AG Cluster

I have a question about a cluster with an availability group. We have a 12 node cluster in pairs of 2 (6 roles). 2 of the nodes were added to the cluster this year, say X & Y. Since October, one ...
0
votes
1answer
44 views

SQL Server AlwaysOn AG Secondary Replica is not joined [closed]

I have created an Availablity Group on SQL Server 2019. I selected 2 SQL server for Replicas but the Secondary replica has an error: This secondary database is not joined to the availability group....
0
votes
1answer
32 views

SQL Server AG secondary replica database still in Restoring mode

I created an AlwaysOn in SQL Server 2019 and I selected the Full Database and Log Backup option. Now it created a database in secondary replica but it is still in Restoring mode. My DB size is 16MB ...
3
votes
1answer
102 views

SQL Server AlwaysOn Availablity Group Automatic seeding

I created an AlwaysOn in SQL Server 2019 and I selected Automatic seeding, but it does not create my database in secondary nodes, On the other hand, if I create my database manually I give and error ...
3
votes
1answer
182 views

Record MultiSubnetFailover value at connection?

With AlwaysOn, there are 3 options for the keyword MultiSubnetFailover True (yes) False (no) (example) Not sent/used Not all applications support it's use in connection strings. Not all vendor ...
2
votes
1answer
22 views

Trigger SQL Agent job based on text in error log for an AG failover

I have a 2 node AG setup (Not an FCI). I need to configure an alert to trigger a job based on a string found in the error log showing that the node is now the new primary. The string I want to use is: ...
2
votes
3answers
72 views

Is it possible to add a DB to an Always On AG and setup replicas at different time?

I have a 3-node SQL Server Always On availability group. 2 nodes are on the same LAN, and the third one is on a remote site with high latency. I will add a huge DB (10TB) to the AG, so I would like ...
4
votes
2answers
66 views

Checking the data latency on an Always On Availability Group in ASYNC mode

We have a number of SQL Servers with Always on Availability Groups in asynchronous mode between a primary and secondary server with Manual fail over. I created a latency report utilizing the below ...
0
votes
0answers
15 views

Usage of AGLatency V2.2 for multiple secondary replica

I am having issue on AG setup. Basically I can see blocking on primary replica whenever log backup runs on primary. Connection timeout happnening on sync commit secondary replica(only one). So for ...
0
votes
0answers
44 views

Help with set up replication distribution database in Always On availability group

After following official microsoft guide and sample walkthrough: https://docs.microsoft.com/en-us/sql/relational-databases/replication/configure-distribution-availability-group?view=sql-server-2017 ...
0
votes
0answers
37 views

Auto Seeding SQL Server AG Combined with Backup/Restore

Are there special conditions under which the below paragraph from MS Docs is true? I tested this process on SQL Server 2017 Enterprise Edition and ended up with databases saying not synchronized/...
4
votes
3answers
258 views

SQL Server: Multiple Availability Groups, one cluster, how do I keep the primary cluster node as primary?

I have a database server setup where there are three SQL Server database instances set up on three nodes of a Windows Server Failover Cluster (WSFC). The sysadmin, who is responsible for the ...
-1
votes
1answer
19 views

Primary replica in primary DC freezes when delocalised DR replica DC crashes

The Always On hasn't got the flexibility of mirroring. The DR replica's data center crashed and the primary replica also froze as a result (see following error when trying to access the primary). Is ...
1
vote
2answers
38 views

For AlwaysOn AGs, can a secondary replica cause contention on the primary when the resources of the secondary are maxed out?

Is it possible and in what ways can the secondary replica cause contention against the primary replica when the secondary replica's resources are maxed out (e.g. does the primary "lock" up while ...
0
votes
1answer
26 views

SQL Server Distributed Availability Group Usage Pattern

I have got a client with DAG setup on 2016 & 2017 servers. I understand that their usage is most likely for DR purposes. What I'm a little unclear on (and I can't find any info on it) is how they ...
0
votes
2answers
35 views

Configure Always On to use secondary site as DR

I have two SQL Server 2017 Standard Edition clusters, each in a different datacenter, only a pair of servers each. I was able to configure and test the Windows Cluster, Quorum for it, AG, including ...
4
votes
1answer
108 views

Distributed AG on SQL Server 2017 Standard Edition?

Can I configure Distributed AG with SQL Server 2017 Standard Edition?
0
votes
1answer
11 views

Upgrading Microsoft SQL 2014 and Always On AG Configurations

I'm getting ready to perform an upgrade from SQL 2014 to 2017 on two servers that are configured for Always On. The concern on the upgrade is how to break Always On to stop replication and to keep any ...
3
votes
2answers
73 views

SQL Server 2017 Distributed Availability Groups

My Environment is SQL Server 2017 Distributed Availability Groups, details as follows: Configured SQL Distributed AG from DC1 to DC2. DC1 (Production): TEMPDB1 AND TEMPDB2 (HOSTED ON WSFC1) DC2 (DR)...
7
votes
1answer
241 views

Is there a way to determine what kind of data is sitting in the Redo Queue for an AlwaysOn AG Secondary Replica?

Do any of the DMVs or is there another way to expose what kind of data is in the Redo Queue that's in process of being synced up to a secondary replica? (E.g. is it table data and which table, or is ...
2
votes
1answer
133 views

What's the cause for an AlwaysOn AG Secondary Replica to have a high Redo Queue Size and Estimated Recovery Time with a good Redo Rate?

SQL14 is the Primary server, SQL16 is the Secondary Replica, and they're setup using synchronous commit availability mode: As of yesterday it appeared that data stopped synchronizing from the primary ...
0
votes
2answers
45 views

Sql Server AG assigning wrong listener

I have 2 instances of SQL Server on two Windows Servers, namely: D-MSSQL01-DE (v.2014) D-MSSQL02-DE\SQL2019 (v.2019) And seperate server for AGs: D-MSSQL02-DE (v.2014) D-MSSQL02-DE\SQL2019 (v....
0
votes
0answers
42 views

Database on secondary replica cannot join availability group [duplicate]

I am trying to add a database to an Availability Group Always On. I already have the other two DBs working fine on the same SQL Server 2016 Standard Edition Cluster (running on Windows 2016 Standard). ...
2
votes
0answers
86 views

Missing log block for availability database in SQL Server 2016

I've recently begun receiving alerts like this one: Always On Availability Groups transport has detected a missing log block for availability database "myDb". LSN of last applied log block is (xxxx:...
0
votes
1answer
34 views

Blocking in AG that causes app not to launch

If lag between primary and secondary is huge, then can it cause persistent blocking on app sessions? Coz recently we had an issue where blocking sessions were having wait type as HADR_SYNC_COMMIT and ...
0
votes
1answer
42 views

Upgrading to SQL Server 2014 SP3 _ Clarifications help needed [closed]

We have SQL Server 2014 SP2 GDR with below: Availability Groups TDE enabled for structured data EFS enabled for unstructured data Now the DBs are with AOAGs and encrypted , I am planning to ...
2
votes
2answers
62 views

what happens to user connections when an AG fails over?

I'm not sure quite how to word this, but I am looking for anything a DBA can do (things outside of changing code in the application) to reduce the impact to users when an AG fails over. We have a 2 ...
2
votes
2answers
46 views

Using SQL Server 2017, can log backups be safely taken on primary when using Log Shipping or Availability Groups?

We have a CommVault server taking log 15-minutely log backups of our primary (on-premise) DBs (combined with full nightlies), for point-in-time restores. We want to enable high availability for DR ...
1
vote
1answer
60 views

Row Compression in AlwaysOn Availability Groups

I want to implement row compression on some databases that are in an availability group. Are row and page compression supported in availability group databases? Does it affect the replication, ...
0
votes
1answer
34 views

Adding ReadOnly Domain-less replica to existing AG with two replicas on two nodes WSFC

I have AG setup as follows: Multi sub-net WSFC (Windows Server 2016) - All nodes part of same domain AG with two replicas (SQL 2017) - Each node (mentioned above) contains one standalone instance. ...
-2
votes
1answer
21 views

SQL Server 2012 AlwaysOn Availability

Environment: SQL Server 2012 AlwayOn with 3 Replicas. Primary ((Manual Failover)) Secondary1 (Manual Failover) -->Read_Intent Only --This is using for some reporting Secondary2 (Manual Failover)-->...
1
vote
2answers
37 views

Different Maximum server memory across Availability Replicas

I have a scenario in SQL Server 2014 AlwaysOn High Availability where other services are running in one of the Secondary read-only replicas. These services obviously demand additional memory. Is ...
0
votes
0answers
17 views

ALLOW_SNAPSHOT_ISOLATION ON is possible on Basic Availability Group?

Is it possible to enable ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT on a basic availability group? If yes, is there a specific procedure? Drop the database from the Availability Group ? ...
0
votes
1answer
48 views

AlwaysOn database not synchronizing/recovery pending due to cluster loss quorum

I have this error in cluster event 'Cluster node 'MMPDB1NEW' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to ...
0
votes
1answer
39 views

Restore database with Availability Groups

I have high availability enabled with two servers and when trying to restore a database I realised I had to un-synchronize the database and then remove it from the availability group. I use Ola ...
1
vote
2answers
112 views

Regarding Always On Licensing (Secondary replica no license required) [closed]

I have few questions regarding the licensing of SQL Server when it comes to Always On: Under which scenario / settings we can say that secondary replica is truly passive and will not be considered ...
1
vote
0answers
70 views

REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT for Readscale AG failover

I have 2 node Readscale AG setup where each node is on a different data center. I want to know if SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1); needs to be run even after setting both the ...
2
votes
1answer
24 views

What's better - setting up Always On fresh and migrating data, or using existing server with data and adding Always On?

I have an existing SQL Server instance with three databases and a small amount of data. I've been asked to create an Always On Availability Group now. The existing server would need to have the ...
0
votes
0answers
21 views

What is the simplest method to get a Readable Copy of the database, one having more historic data

I am hoping to get some input as to what the best replication method/tool would be for my situation. I have to make a new system and the business needs are as follows : They need to have access to ...
0
votes
2answers
54 views

SQL Server AlwaysOn and FailoverCluster

We have one project which has Failover Cluster with two nodes and one standalone server in AlwaysON. For example: Project 1 Node1 (ip:192.168.100.101) Node2 (ip:192.168.100.102) in Failover ...
0
votes
0answers
19 views

Sp_server_diagnostics results are show in every 1 min and 40 seconds

As per my know sp_server_diagnostics is supposed to run every 1/3rd of the HealthCheckTimeout Value. Currently in the WSFC Manager, HealthCheckTimeout is set to 30 seconds. So it should run in every ...
2
votes
0answers
49 views

What happens when a SQL Server node goes down in an always on clustering?

We have an application (vendor provided) writes/reads data to/from SQL Server 2016 whenever the application is accessed. The database is located on shoreside, and the network connection between the ...
3
votes
1answer
76 views

SQL Server secondary replica snapshot with Basic Availability Group

I have the following scenario: I created an HA group of two nodes in SQL Server 2016 Standard (which is limited to two nodes, one database per group and a non readable secondary replica). I tried to ...
0
votes
1answer
53 views

Mixed Editions in a 3 Node SQL Server Always on Availability Group [closed]

I could not find any solid documentation anywhere, checking any of you have seen/built an AG that looks like below. Appreciate your responses. SQL SERVER 2016 Primary - Enterprise edition ...
3
votes
1answer
55 views

User Mapping on a DB that's part of an AG. DB is READ only? [duplicate]

I'm a sys admin being tossed in to a DBA role. I currently have two SQL Server boxes, 2016 Enterprise, in an AG. The DB has synchronized without any issues. A third party vendor needs to have an ...
-1
votes
1answer
140 views

Problem adding a replica to an Availability Group

I'm getting an odd problem in trying to add a replica to an AG. This is part of a POC project to upgrade a 2-node AG running on Windows 2012 and SQL Server 2012 to a new 2-node AG running under ...