Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

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

AG lag / high Redo queue issue [on hold]

I am having issue with Availability Groups, the Redo Queue Size on secondary server increases for some reason and after restart of secondary the queue syncs. The AG stays healthy for week or two and ...
0
votes
2answers
23 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
16 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
26 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
32 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
33 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
20 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
21 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
17 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
48 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
15 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
39 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
41 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
42 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
52 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
40 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 ...
0
votes
0answers
32 views

We have 2 SQL server 2016 servers that are having communication issues

We have 2 SQL server 2016 servers that are having communication issues when setting up Availability groups and replication. The servers can ping each other and can be connected using a UDL file and an ...
0
votes
1answer
27 views

Selecting ports for Availability Groups Listener and SQL Instances

Experts, I have 2 node Availability Groups with File share witness. We chose the non standard port 17333 for the Listener and 15333 for the 2 SQL Server instances. One of our newer application was ...
1
vote
1answer
24 views

Ignore a database in an Availability group when using @AvailabilityGroups = instead of @Databases with ola-hallengren backup solution

I’m using the @AvailabilityGroups= parameter to backup availability groups, is it possible to NOT backup (exclude) a database in the Availability group? If using the @Databases parameter I could use -...
1
vote
2answers
68 views

SQL Server Always-On Availability Groups LeaseTimeout

One of my AG environments encountered the error The lease between availability group XXXXXXX and the Windows Server Failover Cluster has expired. A connectivity issue occurred between the ...
2
votes
2answers
52 views

Are Availability Group Databases on a Secondary replica automatically set to read-only by the AG?

I have read through the MS docs on read-only routing, but I am not talking about that. Currently, if I execute an UPDATE against a database that is on a Secondary replica and is part of the ...
2
votes
1answer
52 views

Secondary Database Replica in Reverting/InRecovery (Long time)

Reposted from: https://www.sqlservercentral.com/forums/topic/secondary-database-replica-in-reverting-inrecovery-long-time Hi, One of my AG secondary replica has been in reverting/in recovery for ...
2
votes
1answer
67 views

Availability Group Notification when new DB is added

When new DB is added to SQL Server's AG setup, a notification email like below is sent to us. The availability group database "XXXX" is changing roles from "SECONDARY" to "SECONDARY" because the ...
4
votes
1answer
102 views

Where did new database 'BackupLocDb_###" come from?

Yesterday I was doing some work on a 3 Node AlwaysOn group of instances, SQL 2014 SP3, and this morning I found there was a database named BackupLocDb_1260a388-153d-4c86-a30b-0fd3feafb116 on the ...
3
votes
3answers
162 views

SQL Server Always-On Availability Groups Patching

My SQL server farm has been neglected with patching OS level & SQL server level (because they are critical systems, outages are hard to come by). An option is to patch the secondary nodes of our ...
1
vote
1answer
36 views

If the SIDs for ##certificate-based## SQL Server Logins don't match across replicas in an Availability Group, will this be a problem on failover?

I am reading mixed messages about server level object syncing between replicas in an Availability Group. As I understand it, server-level objects must be kept in sync (aside: then why isn't it part ...
1
vote
1answer
54 views

SERVERPROPERTY('HadrManagerStatus') equating to 1 even if Always On is not configured

We have a SQL Server Enterprise 2017. I would like to get insights as to why our SERVERPROPERTY('HadrManagerStatus')returns 1 even if Always On is not configured in this instance. I have a local ...
1
vote
1answer
40 views

Multiple availability groups--can each availability group have its own listener?

I have created two availability groups containing multiple databases on the same SQL Server instance. Can I have two different listeners for these two availability groups?
3
votes
2answers
275 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 ...
8
votes
1answer
68 views

Database Mirroring protocol TCP ports used. One default, one dynamic?

When running below query on the primary/secondary replica of a SQL Server Always On Availability Group™ SELECT DISTINCT local_tcp_port,protocol_type,num_reads,num_writes FROM sys.dm_exec_connections ...
1
vote
1answer
35 views

SQL Server AlwaysOn ReadOnly Routing to Secondary

I configured SQL Server AlwaysOn to redirect Read-Only to Secondary and tried to check with ApplicationIntent=ReadOnly setting in SSMS by connecting to the Listener. But my connection is always going ...
2
votes
1answer
58 views

Availability Group Clustering - Cluster Group Preferred Owner

We have a three node AG. Two nodes in a local data center, one in a different subnet DR site. Am I correct that the Cluster Name should have two IPs, just like the listener? Also, is there a way to ...
5
votes
2answers
577 views

SQL Always On COPY ONLY backups - what's the point if I cant restore the AG from these backups?

Think I already know the answer to this but I'll ask anyway. Situation is: SQL 2012 Always On - 2 synchronous replicas. 7 TB worth of Sharepoint DBs in 1 AG. Before patching - I want a Full DB ...
1
vote
1answer
28 views

Log Shipping between 2 AGs

I'm having one AG with two nodes (active-passive) in one data center and another AG with two nodes (active-passive) in a different data center. I need to have log shipping enabled between these two ...
0
votes
1answer
62 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' ...
4
votes
1answer
84 views

Always On Primary Replica On Prem, Secondary Replica On Azure

We want to create an Always On Availability group where the primary replica will be On-premises and the secondary replica will be on an Azure VM. I have researched and I know it's possible to do this, ...
13
votes
1answer
383 views

Forced plans on readable secondaries

If a plan is forced on the primary in an Availability Group, is it applied to queries run on a secondary? I'm looking for answers that cover both possibilities for plan forcing: Plan Guides Query ...
2
votes
1answer
33 views

Troubleshooting high network traffic between primary and secondary replicas

We have two SQL server hosts. There are about 12 availability groups, where the primary replica is on the "primary" host and the secondary replicas are on the "secondary" host. Now, for one reason or ...
14
votes
4answers
4k views

RAID0 instead of RAID1 or 5, is this crazy?

I'm considering using a RAID0 setup for one of our SQL Server clusters. I'll outline the situation and am looking for why this may be a bad idea. Also if someone you have use cases, white papers or ...
3
votes
1answer
103 views

Availability Groups automatic failover is not so automatic

Behavior when I stop SQL Server Service : The availability group fails (resolving on SECONDARY SQL SERVER and fail state on Cluster management). SQL Server does not connect with the Cluster ip (I'm ...
1
vote
1answer
144 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 ...
0
votes
1answer
50 views

Does using secondary replica for read operations still required license? [closed]

I wanted to move some of the reporting workload to the secondary replica but the following things stopped me: no writes can be performed (including creating temporary tables); a license is required; ...
1
vote
1answer
41 views

Accessing Administrative Share Using Listener Name

Can a SQL Server AG listener name be used to access an administrative share i.e \\DB-listener\c$\Somefolder? I can RDP using the listener name but can't use the listener name to access the $ ...
3
votes
3answers
200 views

Do I need “New SQL Server Failover Cluster installation” to run Availability groups with manual failover?

I'm trying to set up a test environment here to learn more and apply this on our production environment. I've created and installed the Windows Cluster with CLuster manager. Now I'm going to install ...
6
votes
2answers
286 views

What is the best option for High availability on a data warehouse?

I have a customer who have an existing 1.5Tb data warehouse and are currently planning for a complete refresh of the DW on a new environment. Their infrastructure manager has organised 2 servers with ...
1
vote
0answers
45 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 - ...
0
votes
1answer
45 views

Which rights to grant for controlling an SQL Server Availability Group?

We are having the following setting: For an ERP Test / Accept SQL Cluster we want to follow the least privilige principle to give the external engineers rights on the SQL server. We are running SQL ...
0
votes
2answers
36 views

Multiple AGs on same Windows Failover cluster the Listeners don't work properly

Here is my case. I have Server1 and Server2, which were installed 4 SQL instances as follows: Server1 Server1\SQL1 Server1\SQL2 Server2 Server2\SQL1 Server2\SQL2 The Server1\SQL1 and ...
2
votes
1answer
33 views

Saved state for SQL Server Always On on Hyper-V

For our SQL DEV/QA environment our system-engineers did setup an Hyper-V enviroment - 3 hosts - 2 VM on each host (1 for DEV an 1 for QA) Patching for the hosts is scheduled once a month but I ...
3
votes
1answer
50 views

Availability Group Read Only Routing Not Working for SQL Logins

We have following config of Availability Group: server1.domain.com - primary server2.domain.com - sync secondary server3.domain.com - async secondary sql-listener.domain.com - availability group ...