Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
1answer
23 views

Problems with Listener in AlwaysOn AG in AWS

I have a 2 node AlwaysOn AG cluster with a single database being replicated between the 2 nodes. Windows Failover Clustering Services are installed on each node. The AG is configured to allow ...
0
votes
2answers
43 views

Multi-site Failover Cluster Instance for HA and DR to alwaysON Availability Groups

In my company we have chosen to implement Multi-site SQL Server Failover cluster. Basically Data center A has 2 nodes (Active/Passive) and Data Center B geographically located acts as the DR. I ...
1
vote
1answer
33 views

Sync Server Objects when having two or more Availability Groups

Lets imagine that i have two servers/nodes with TWO availability groups Group01 and Group02. I will need to ensure that the server objects are all the time recreated over the secondary replica. If i ...
2
votes
1answer
12 views

How many nodes can you install SSRS on when using basic availability groups?

I have migrated a SQL Enterprise standalone SSRS installation to a new SQL Standard 2 node Basic AG recently. I got the expected "scale-out-deployment is not supported error", and I resolved that by ...
1
vote
0answers
61 views

SQL alwayson manual Failover Cluster failed because a duplicate IP address detected

When I manually tried to make a failover I got the following errors: "SQL always on Cluster IP address resource listener cannot be brought online because a duplicate IP address was detected on the ...
1
vote
0answers
9 views

cent os 7 alwayson primary is in resolving state and not going to secondary

I have configured AlwaysOn in external mode between two CentOS 7 VMs. When manual failover happens, primary goes to resolving state. It never turns to secondary and secondary is always in secondary ...
0
votes
0answers
8 views

SQL Server read-replica hosted on AWS with ephemeral storage

I have a temporary need (during transition from on-prem to AWS) to provide a high-performance SQL server instance for analytics. I would like to purchase two SQL server enterprise instances. One ...
1
vote
1answer
50 views

LOG backup Always ON using Ola Hallengren database backup script does not carry out a backup

I am trying to run a LOG backup on the primary node of an Always ON SQL Server 2012 environment. The secondary server is only used for replication. no read or other access permitted. (licensing of ...
1
vote
1answer
35 views

How to show the Availability group listener instead of the nodes?

I have just installed a sql server availability group, always on, with 2 servers, SQLPROD1_TS and SQLPROD2_TS as you can see on the picture below: select distinct rcs.database_name, ar....
0
votes
0answers
26 views

How to add a Database on existing Distributed Always on? [duplicate]

I have added a database on the existing Distributed Always on between server A and B which are prod server. Now I need to replicate same DB on server C and D which are DR server.
0
votes
1answer
15 views

reportserver will not join basic availabiltiy group

I am running into a strange situation that I have not been able to resolve.  I have a 2 node Basic AG running sql server 2016 standard.  I am trying to add the reportserver DB to an AG, but it keeps ...
1
vote
1answer
26 views

Automatic Failover on SQL Server Always On Availability Groups is not triggered when primary replica becomes offline

I have set up a 2-node Always On Availability Group. Both replicas are in synchronous mode with automatic failover. If I perform a manual failover, everything works as expected: Primary and Secondary ...
3
votes
1answer
191 views

Messages about parallel redo

Parallel redo is shutdown for database '' with worker pool size [2]. Parallel redo is started for database '' with worker pool size [2]. Starting up database '' I see this on a client PC a lot in ...
1
vote
1answer
33 views

What is the Always On Sync sync frequency?

On an Always On Availability group, I understand an async secondary replica asks the primary for the blocks to be shipped. However, I would like to know how frequent is this task (to know the possible ...
1
vote
1answer
33 views

How To Prevent Live Servers From Detecting a Forced Failover?

We are using an AlwaysOn Availability Group in SQL 2017 on Windows Server 2016 which consists of two servers on our primary site and one DR server on a remote site. We recently did an internal IT DR ...
1
vote
1answer
28 views

Is there any special configuration for SQL Server implementing Routing List in a heterogeneous enviroment (Client and Server in different domains)?

I had implemented successfully SQL Server Routing List Feature many times... but I have a very special scenario where our Reporting Services Node is located in a different Domain than SQL Server Nodes....
0
votes
0answers
28 views

SQL Server LDF is is keep growing on secondary Always on

I have configured ALways-On (Async mode) on SQL server 2017, recently LDF file has been growing on secondary (80 GB), but on the primary database it is less than 50 MB, also I have set up full back ...
4
votes
1answer
32 views

Add replica from node 1 can't find node 2 while installing an Always ON AG lab on virtual machines

I am trying to set up an Always ON availability group lab on virtual machines, with Windows Server 2016 and MS SQL Server 2017. While creating a new Availability Group on node 1 by the wizard, I ...
0
votes
1answer
40 views

Prevent read access on primary AG replica, but allow it on the readable secondary

We have a unique situation where we want to allow users to query a readable secondary replica of a database with SSMS for adhoc reporting, but not allow them to ever read from the primary replica. We ...
1
vote
0answers
21 views

Asynchronous availability groups not synchronizing on SQL Server on Ubuntu

I installed SQL Server 2017 on Ubuntu and setup an availability group with cluster type = "None". I set the "Availability" Mode to "Synchronous commit" and tested it. It works fine. All transactions ...
0
votes
1answer
14 views

Combine Multiple primary databases to single AlwaysOn AG

Can i combine 2 different databases like db01 database from server1 and db02 database from server2 to single AG group on DR ? Version : SQL server 2016 enterprise edition
1
vote
1answer
38 views

Dynamic quorum and Dynamic witness in Always On AG 2016

I have a Always On AG 2016, configured on Windows Server 2012 R2 – 2 node cluster. Quorum configured as 2 nodes + Fileshare witness, totally 3 votes. Scenario 1: Now, either 1 node fails – remaining ...
0
votes
1answer
45 views

Having a backup of the master key, how can I restore a database that was encrypted by master key?

while setting up AlwaysOn - Availability Group on a test system, I generated all the restores from live and restored them to this new server. then I backup all these databases to a network share to ...
1
vote
1answer
38 views

Snapshot on Always On Availability Group in suspect mode

Our configuration is 2 SQL Servers 2016, enterprise edition, 13.0.5026.0, running on Windows Server 2012 R2. Created availability group with one database in it. Database has 3 data files in it, all ...
5
votes
0answers
65 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 ...
1
vote
1answer
49 views

Data Loss when forced failed over to secondary replica on Sql Server 2014

Setup: 2 Availability Groups 2 Replicas on Synchronous Commits. 1 Replica on Asynchronous Commit. Automatic Failover configured for the two replicas on Synchronous Commits. Manual Failover ...
0
votes
1answer
67 views

Migrating Standalone named instance to AlwaysON

Scenario: I have a named instance such as SERVER1\NAME. I need to migrate this instance to an AlwaysON, but I'd like to use the same instance name in the listener. Is it possible? I was planning a ...
1
vote
1answer
46 views

AlwaysON AG Readable Secondary is set to 'Yes'. How to connect from the client?

For AlwaysON AG, if Readable Secondary is set to 'Yes', do you use the listener name or the secondary replica instance name to connect for read-only purposes? I know when Read Secondary is set to '...
7
votes
3answers
243 views

Long Running Query on Read-Only Replica that takes moments on the Primary

I've got a 4 node AG setup as follows: VM Hardware Configuration of all nodes: Microsoft SQL Server 2017 Enterprise Edition (RTM-CU14) (KB4484710) 16 vCPUs 356 GB RAM (long story to this one...) max ...
3
votes
2answers
193 views

Max worker thread exceeded after manual AG failover

We have two SQL Server 2016 SP2 CU6 Enterprise running on physical hardware (32 cores) with one AG that contains 5 db's. The secondary is not readable. Today we did a planned manual failover, so we ...
1
vote
0answers
161 views

DATABASE lacks a quorum of nodes for high availability

Just after setting up successfully alwayson - availability group, testing the failover, I get this error message: TITLE: Microsoft SQL Server Management Studio Failed to resume data movement in ...
1
vote
1answer
29 views

SQL Server AlwaysOn Availability Groups Cluster File Share Permissions

Just need to double confirm, when setting up the file share witness to act as a quorum vote in the HA cluster - does each server node within the cluster need access to that file share or is it just ...
1
vote
2answers
40 views

SQL Server 2016 Always On with Availability Groups: add instance on both servers

I have a WSFC with SQL Server 2016 Enterprise Edition and 4 Availability Groups. Each server has 1 CPU with 6 cores @ 3.4GHz and 256GB of RAM. Currently I have one instance on each, they serve 4 ...
1
vote
2answers
40 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 ...
3
votes
1answer
51 views

Should I create two endpoints for two availability groups on the same SQL Instance?

I have a two node cluster running SQL Server 2012. I want to create two Availability groups within the same instance of SQL Server. Should I add one endpoint for each AG, or shall I allow both AG's ...
4
votes
2answers
106 views

SQL Server Always On File Share Witness (Quorum vote) on different subnet to other nodes

I am currently having issues with some of my Availability groups where node 1 and node 2 loose connectivity between each other "A connection timeout has occurred on a previously established connection ...
1
vote
2answers
53 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, ...
1
vote
1answer
40 views

Question on Enabling Kerberos Authentication using SetSPN for Availability Listener alternative DNS name

We have Availability Group, and original DNS name that was given to AG Listener, is too long (something like longername-Listener-01.mydomain.com); Its hard for us to change original DNS name of ...
0
votes
2answers
40 views

Backup MS SQL AG on a non-production environment

We've created a non-production environment for SQL Server. This environment uses the SQL Server Always-On Availability Groups as a HA-solution. To work with SQL Server AG's you have to set the ...
1
vote
1answer
36 views

cross-database and cross-Availability Group queries and updates: which node do they go to with ApplicationIntent?

This is a simplified view of the scenario we are considering: Node1 AG1 Database A (master readwrite) AG2 Database B (secondary readonly) Node2 AG1 Database A (secondary readonly) ...
1
vote
1answer
88 views

Upgrade SQL Server 2014 Failover Cluster to SQL Server 2016 Availability Group on Windows Server 2016

In our current set up we have 2 node SQL failover cluster in data center DC1 with mirroring enabled. And similarly in DC 2 a 2 node SQL failover cluster. DC1 acts as primary via DB mirroring in one ...
0
votes
1answer
50 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 ...
0
votes
1answer
53 views

Drop the user from Read only Secondary database Always ON server

How to drop a user from the secondary database in an AG when it is in read only mode and synchronizing in SQL Server 2012? The login and user have been deleted from the primary database, and we are ...
5
votes
0answers
196 views

Alwayson AG lease timeout and healthcheck timeout

We have a physical SQL Server 2016 Enterprise SP2 with two instances and each instance contains one AG. Sometimes it happens that one AG goes to resolving (without failover), the cluster resource is ...
0
votes
1answer
23 views

AlwaysOn availability group and Replication - expected behaviour

I'm currently doing a bit of testing with alwayson and replication. I currently have the below: Fully working cluster and Always on environment; DCTEST1, DCTEST2 and the listener name DCTEST_L. The ...
2
votes
1answer
94 views

An error occurred on the always on dashboard

I have a setup of Windows Server Failover Cluster and i am using SQL Server Service with one DC, One Primary Server and One Secondary Server. During Using SQL Server Management Studio, when i click ...
2
votes
0answers
18 views

Multiple Location AlwaysOn and Azure SQL Server [closed]

AS with the majority of projects, the original scope may not be a viable option. We were using Azure Data Sync and transaction replication to keep the 4 site databases in sync. However, the customer ...
1
vote
0answers
37 views

Current Host Server for Availability Groups [duplicate]

We have a 4 Node multi Subnet Availability Group. When i go to the Cluster Manager I could see the Current host server is the Node 4 whereas the AG Primary node is Node 1. Will the AG go down if Node ...
2
votes
1answer
33 views

Patching FileShareWitness for AlwaysOn AG

Situation: We have an AlwaysOn High Availability group with 4 replicas (2 for DR) a listener and an FileShareWitness. I've been tasked with creating a set of instructions for patching all of the ...
2
votes
0answers
73 views

Connection to availability group listener on non-default port doesn't require port to be specified

I inherited some 2016 SQL Servers with an availability group and listener. The SQL instances are set up to use port 30001, and the AG listener is also set up to use port 30001. When I connect using ...