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
123 views

Log truncation issue in AlwaysOn AG

We have four nodes in our AG. After the weekly index maintenance job, the log drive became full. We enabled CDC in one of our database, so we disabled CDC to truncate the log. Then we ran 'checkpoint' ...
2
votes
1answer
34 views

How to migrate SQL Server 2014 to SQL Server 2019 on Linux using AG [closed]

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 ...
8
votes
5answers
12k views

SQL Server 2012 always-on backup, full vs copy-only

I need a quick clarification on copy-only backup vs full backup as it pertains to transaction log numbering and restore if required. I have SQL Server 2012 setup with always on for multiple databases....
0
votes
1answer
446 views

Unable to connect to Listener after rebuild

I dropped an AG with DROP AVAILABILITY GROUP [MyDb] it had a listener. I left the listener open in the object Explorer of SSMS 17.3. While the AG and Listener were down. I hit refresh on the ...
0
votes
0answers
38 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/...
6
votes
3answers
11k views

How to check history of primary node in an Availability Group?

How do I see history of failovers of the primary node of a cluster? I'm trying to figure out at what point secondary node started to act as primary. We are running Windows Server 2012 with SQL Server ...
1
vote
1answer
346 views

Set up new publication on SQL Server 2014 database which is part of an availability group

I like to setup a publication (transactional replication) on a SQL Server 2014 database which is part of an availability group. We have a primary and one secondary (non-readable) replica. On the ...
0
votes
1answer
400 views

Disabling TDE on AlwaysOn database

Which are the correct steps to disable (not removing) TDE on a AlwaysOn database in an availability group? I found steps for removing TDE but that involves removing the availability group and ...
2
votes
1answer
41 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 ...
1
vote
1answer
29 views

Microsoft SQL secondary read-only DB for SSRS?

I know that Microsoft SQL has AlwaysOn with a read-only secondary replica. However the cost of enterprise edition for all of our SQL servers makes this not possible. We have SSRS configured to run ...
4
votes
2answers
593 views

SQL AlwaysOn & System Center Configuration Manager TP 4 1601

Recently Microsoft released Technical Preview 4 for Configuration Manager (1601). Per TechNet, "Configuration Manager now supports using a SQL Server AlwaysOn availability groups to host the site ...
1
vote
2answers
1k views

delete WSFC 2012 cluster role/group fails

PS C:\Windows\system32> get-cluster Name ---- sql2012poc_cl1 PS C:\Windows\system32> get-clustergroup Name OwnerNode State ----...
0
votes
1answer
240 views

Get DAG members

Via transact SQL with this query: SELECT ag.[name] as 'AG Name', ag.Is_Distributed, ar.replica_server_name as 'Replica Name' FROM sys.availability_groups ag, sys....
0
votes
0answers
34 views

Node is Losing Connection to Quorum in an AG Cluster [closed]

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 ...
3
votes
1answer
113 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
172 views

What is the behavior of MIN_ACTIVE_ROWVERSION() on replicas in SQL Server AlwaysOn?

MIN_ACTIVE_ROWVERSION() returns the lowest rowversion allocated within still-open transactions. This is important when using rowversion columns for reading data where you want to be sure that you wont ...
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? ...
2
votes
2answers
4k views

Lease Timeout VS HealthCheck Timeout - AlwaysON

After all the reading this is what I understood. Is it correct? LeaseTimeout is a simple heartbeat between SQL Server resource DLL and the SQL Server instance. HealthCheck Timeout is related to ...
1
vote
1answer
54 views

Availability Group Read Only Routing Not Working for Azure App Service that connects over Hybrid Connection

We have following config of Availability Group On-Premise: server1.domain.com - primary server2.domain.com - sync secondary server3.domain.com - async secondary sql-listener.domain.com - ...
1
vote
2answers
6k views

Can not ping Always On listener from outside primary node

I just setup SQL Server 2012 (on Windows Server 2012) Always On feature and have a problem with connecting to SQL Server Listener from non-primary node (it works from primary node though). Here is ...
0
votes
1answer
33 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 ...
0
votes
1answer
46 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....
3
votes
1answer
103 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 ...
1
vote
1answer
865 views

Changing startup accounts on alwayson setup

I have a 2 node Always On Availability Group cluster where we are planning to take backups to a file share which will then be moved to Azure cloud. We have a service account from the Windows team for ...
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: ...
0
votes
1answer
1k views

Persistent Data Collector Collection_Set_3_Upload Job Failure with AlwaysOn Groups Enabled

I have a two node SQL 2012 AlwaysOn HADR cluster (v11.0.3412) with 3 availability groups configured. The AG groups are set to synchronous mode and the secondary is not readable (we do not want the ...
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 ...
3
votes
2answers
512 views

How to remove a secondary database from an availability group and rejoin it

I have an availability group (AG) with multiple databases (DB-A, DB-B, DB-C), and multiple secondaries (SEC-B, SEC-C) and one of the databases will not resume synchronization on just one of the ...
1
vote
2answers
110 views

Multiple Instances and Availability Groups

In my scenario, I have two servers in each of them two SQL Server instances: Server 1: Instance 1 and Instance 2 Server 2: Instance 1 and Instance 2 I created availability groups between the ...
1
vote
2answers
387 views

How to fail over availability groups on multiple remote servers

We have many SQL Server installations with availability groups. During our maintenance window, we will need to fail over all the HA groups to secondaries, patch the primary servers, then fail back, ...
4
votes
2answers
67 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
46 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 ...
2
votes
2answers
149 views

Availability groups multiple instance, multiple listeners

In my work environment, I have two database machines: DB01 DB02 Each database machine has three SQL Server instances: MSSQLSERVER FBG Central I've managed to set up availability groups with SQL ...
1
vote
1answer
364 views

The fulltext filter daemon host (FDHost) process stops abnormally on SQL Alwayson Cluster

We added a new SQL Always-On Cluster (FCI) 2017 and some of the databases needed full text filter service. So we also added that feature to the install. Ever since we did that we have been getting ...
2
votes
2answers
333 views

Applying (log backup) LSNs to the secondary node of an Availability Group

I have a test instance configured as always-on go down today due to a restart of the primary node and the file-share going down together. I had a bunch of problems in order to get the secondary ...
4
votes
3answers
267 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 ...
2
votes
1answer
94 views

Implementing HA for SQL Server for a .NET Application that uses MS DTC

My production environment uses MS DTC and SQL Server 2014. I must have an HA solution such as Mirroring or AG in place. (Not FCI). I am trying to explain to my team that MS DTC is not supported by ...
0
votes
2answers
301 views

Multi-Subnet Network Setup

We currently have a 4 Node cluster used for our Availability Groups. 2 nodes in local data center and 2 nodes in remote data center. Currently the 2 nodes local are Synchronous and the 2 remote ...
0
votes
3answers
99 views

Need to configure Availability Groups between three different SQL failover instance

We have total 6 Nodes and 3 failover cluster (Each with two node). Cluster1.TestAD.com(Node1,Node2) - SQL Failover Instance (SQL1) - 1 TB Shared storage Cluster2.TestAD.com(Node3,Node4) - SQL ...
7
votes
0answers
148 views

CMEMTHREAD waits during Index Optimisation in Availability Group

In a 3 node Availability group, a secondary replica will often become subject to redo lag due to the causes covered in Microsoft's documentation: Troubleshoot: Changes on the Primary Replica are not ...
0
votes
2answers
262 views

Need to maintain transactional consistency of the data across both servers, need to achieve these goals without manual intervention

I got this question about MS SQL Server 2012 administration, two production servers in the same data center. You need to ensure that database remains available if a catastrophic server failure or a ...
-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 ...
4
votes
1answer
361 views

SQL Server logins “un-sync” when Availability Group fails over

I have 2 SQL Servers in an Availability Group. The logins are manually synced – same SID, same Password Hash, etc. on both replicas – and the user can log in via the listener to the primary replica. ...
-1
votes
1answer
551 views

SQL SERVER Checking for Compatibility of the Database File Locations on the Server Instance that Hosts Secondary Replica

I am using SQL Server 2017 and SSMS 18.0 Version. During creation of availability group at validation step at primary Server (DBN1), i am facing that error i.e. the following required directories do ...
1
vote
2answers
39 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
27 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