We’re rewarding the question askers & reputations are being recalculated! Read more.

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
151 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
64 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
28 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
26 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
222 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 ...
4
votes
1answer
3k 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
40 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
39 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
32 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
41 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
69 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
181 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
32 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 ...
1
vote
0answers
133 views

How to find out everything that is affected by a master key?

Following up after a situation on an important LIVE server - where I have the master key backed up, but the decryption password for that master key backup file is missing. I can't open the master ...
0
votes
1answer
16 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
73 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 ...
1
vote
1answer
80 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
103 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 ...
6
votes
0answers
122 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
115 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
73 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
188 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 '...
8
votes
3answers
472 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
288 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
1k 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
151 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
55 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
69 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
312 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
673 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
263 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
81 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
42 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
54 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
114 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
366 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
67 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 ...
6
votes
0answers
731 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
40 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
172 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
24 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
38 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
57 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
129 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 ...
1
vote
1answer
563 views

How to monitor a file share witness for accessibility

In the context of a SQL Server Availability Group, what's the best way to monitor a file share witness for accessibility? I want to make sure the witness can be accessed by the Availability Group ...
0
votes
1answer
58 views

Installing Anti-Virus on Availability Group databases

Is it worth the risk to use Anti-Virus on Availability Group databases? Even following the guidelines given by Microsoft in How to choose antivirus software to run on computers that are running SQL ...
5
votes
1answer
173 views

How do I find out when a node was added to an availability group?

Ideally, I am looking for T-SQL that returns two columns: the node name, and the date/time that node was added to the availability group, for all nodes in a given availability group.
0
votes
2answers
29 views

Monitoring Maintenance Tasks in AlwaysOn Clusters

I have an environment with multiple AGs on a single server and they encounter a split situation in which some end up on one node and some end up on the other node (in a failover event, perhaps). When ...
0
votes
1answer
201 views

Network Packet Payload Error when Trying to Create Distributed Availability Group

I am attempting to create a distributed AG on SQL 2016 SP1, but keep getting the following errors. I have tried this in a VirtualBox (local desktop) and actual VMWare virtual environment but getting ...
1
vote
2answers
238 views

Always On Availability Group Failover Issue

I'm testing a PowerShell Disaster Recovery script and trying to fail over to an off-site AG DR (asynch) replica with this command ALTER AVAILABILITY GROUP [MyAG] FORCE_FAILOVER_ALLOW_DATA_LOSS On ...