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.

230 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
10
votes
1answer
992 views

HADR high worker thread usage

Why would an availability group's number of worker threads in an HADR pool increase well beyond minimum usage of "typically, there are 3–10 shared threads" per replica? In one case we've observed ...
7
votes
0answers
198 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 ...
4
votes
0answers
3k views

Safely drop master key

I've restored a database in my Dev environment (SQL version 14.0.1000.169) and I'm using the wizard to create a "Availability Groups", when I try to select the database, I get a pop up "This database ...
4
votes
0answers
851 views

Always On DTC Support on new database?

Is it possible in SQL Server 2016 Enterprise to add a new database to an Always On availability group and have it enabled for DTC support without recreating the AG? Our Availability Group is already ...
4
votes
1answer
402 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. ...
4
votes
2answers
605 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 ...
3
votes
2answers
128 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)...
3
votes
0answers
465 views

On-prem Availability Group secondary replica for Azure SQL Managed Instance

I am investigating migrating an on-premises SQL Server availability group to an Azure SQL Managed Instance (2 databases, 600 tables, 3TB, of which 2TB is blobs). A significant amount of on-premises ...
3
votes
0answers
4k views

Database not able to resume data movement

For some unknown reason, a few of my databases went into a Not Synchronizing / Recovery Pending state on my secondary node in my AG. I was able to resume data movement on all of them except one. I ...
3
votes
1answer
223 views

Upgrade and migrate SQL Server 2014 AlwaysOn AGs to SQL Server 2016, using the existing WSFC name

Here are the components: WSFC Name: SQLPROD1 Existing Nodes in this "cluster": SQL2014OnSite and SQL2014OffSite New Nodes to be added (?) to SQLPROD1: SQL2016OnSite and SQL2016OffSite All 4 servers ...
3
votes
0answers
93 views

Mysterious alwaysOn breakage on SQL Server 2012

I've just bumped into a new situation regarding alwaysOn availability. We have a setup with a large database (terabyte range) with one primary and one secondary replica. Geographically the servers are ...
3
votes
1answer
312 views

SQL Server High Availability and Sharepoint - log file grows when no database activity

Just started as a dba with a company running SQL Server 2012 High Availability and SharePoint. Short description: Database with no user activity still has growing log file. Long description: They ...
3
votes
0answers
735 views

Connection timeout via AlwaysOn Listener IP on local computer

I have AlwaysOn availability group and it has one Listener IP. I can connect this instance using Listener name but, cannot connect using Listener IP from my local computer. It returns following error ...
3
votes
0answers
81 views

Incremental statistics with failover

We have two AlwaysOn availability replicas - secondary readable. We do have an incremental statistics on a large table. When a failover happens, SQL Server tries to update statistics on that table ...
3
votes
1answer
347 views

SQL Server 2014 Availability Group failed

I have 3 servers on VMware workstation: Win 2012 R2 as ADDC -Cluster service. Win 2012 R2 as SQL server primary replica . Win 2012 R2 as SQL server secondary replica (auto fail-over). My ...
3
votes
1answer
47 views

SQL Server 2012 Explicit transactions taking time with Always on Setup

We have a weird issue while we setup the AlwaysOn on Azure VM. We have explicit transactions in our stored procedures, and which are causing some delays and blocking when till the time we get the ...
3
votes
2answers
515 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 ...
2
votes
0answers
336 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:...
2
votes
1answer
233 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
793 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
1answer
106 views

What is the difference between sys.dm_hadr_ tables and sys.availability_groups / sys.availability_replicas?

Browsing around some sys tables, I see: sys.dm_hadr_availability_replica_cluster_nodes.group_name = sys.availability_groups.name and sys.dm_hadr_availability_replica_cluster_nodes....
2
votes
0answers
196 views

Distributed Availability Group Endpoint_URL Setup Question

I have two sites: SiteA and SiteB. Both sites are running SQL Server 2017 Enterprise v14.0.3048.4 on Windows 2012. Both sites are linked with a dedicated VPN connection. We have a series of ...
2
votes
2answers
461 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 ...
2
votes
0answers
78 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 ...
2
votes
0answers
3k 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 ...
2
votes
0answers
167 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 ...
2
votes
0answers
69 views

Failure to create availability group

I"m having a problem creating a new Availability group in a windows cluster. I have 3 nodes in the cluster. Two nodes are running as a FCI and a single stand-alone node that's a member of the windows ...
2
votes
1answer
59 views

Migrating a SQL Server 2014 AG to a new 2017 AG

Our current production server runs on SQL Server 2014 AlwaysOn AG. We are planning to upgrade it to SQL Server 2017 AlwaysOn. Due to the configuration issues with the present cluster, we can not use ...
2
votes
0answers
487 views

Secondary Replica Read-Only Routing via Linked Server working from one server, but not the other

I have been given the task of setting up a read-only linked server on ServerA to Availability Group listener ListnerX. ListnerX points to ServerY or ServerZ (ServerY is usually the primary). Read-only ...
2
votes
2answers
2k views

Availability Group, asynchronous mode, manual/forced failover - handling identity overlap

We have a two replica 2017 Standard Availability Group in asynchronous mode. With a failover (manual/forced, since can't do automatic), we have the potential for overlapping/reused identity values if ...
2
votes
0answers
759 views

Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statemen

We have a three node AlwaysOn availability group with Node 3 being used for reporting. There is a job which runs on Node 3 and queries a bunch of tables and selects data out and it runs every three ...
2
votes
1answer
99 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 ...
2
votes
0answers
68 views

Veeam HotAdd causing Worker Thread Exhaustion

I have a SQL 2012 Always ON AG with 2 nodes and a file share witness. We have 207 Customer DBs in the AG on a box with 12 Cores and Instance configured for 60 GB RAM. I have 1 Secondary configured ...
2
votes
1answer
717 views

Add shared folder to AlwaysOn AG Failover

We have setup a 2 node Synchronous AG with automatic failover to provide HA for our ERP application (Dynamics GP) The GP desktop client loads a number of shared reports and dictionaries from a file ...
2
votes
2answers
5k 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 ...
2
votes
0answers
58 views

MSSQL Page Split Spike on AlwaysOn Readable Secondary

Recently I caught a massive page-split spike on an AlwaysOn Secondary instance that was not mimicked in any way by the active primary. I'm interested in what might cause this on a readable secondary ...
2
votes
0answers
178 views

Replication log reader not updated to new primary after availability group failover

We set up transactional replication on a database that is part of an AlwaysOn Availability Group on our work environment: The primary is SP1 and the secondary replica is SS2. The distributor is on ...
2
votes
1answer
849 views

How to remove a listener IP from a SQL Availability Group Listener by T-SQL

The title says it all. I have an availability group that was a multiple subnet, but now the environment have changed ,all replicas exist within a single subnet. How to remove the superfluous second ...
2
votes
0answers
214 views

AlwaysOn Multi Subnet Configuration

Environment Two Nodes A & B A - Site/Subnet 1 B - Site/Subnet 2 File Share - at Site 3 We have an AG listener that has two IP addresses spanning the two subnets (1 up, the other down ...
2
votes
0answers
107 views

How to use a different vlan for mirroring in an availability group

I want to setup the following with SQL Server 2014 Enterprise and Windows Server 2012 R2. I have two nodes and a fileshare for witness. Both are virtual machines in the same vsphere cluster. To minize ...
2
votes
0answers
257 views

How to avoid Network issues for SQL Server 2012 AlwaysOn

I have SQL Server 2012 and have setup AlwaysOn for High Availability. The infrastructure is on Azure VM's. After moving to this setup, we are frequently observing Network issues which either causes ...
2
votes
0answers
357 views

SQL Server Always On Failover Cluster Design

I have two databases. One I'd like to set up in an availability group between two servers (two instances, one on each server). The database is not huge and I'm not expecting it to grow exponentially....
2
votes
0answers
195 views

Alwayson Database Recovery Failure during TDE encryption

Last week I enabled TDE encryption on my AlwaysOn Availability Group databases of SQL Server 2012. During the encryption of one of the larger databases, bigDB (~200gb) a failover event occurred. Of ...
2
votes
0answers
852 views

SQL Server Management Studio with Multi subnet HADR cluster

We are starting to deploy Multi-Subnet HADR AlwaysOn clusters for our environment. Using MultiSubnetFailover=True in application connection strings works. However, SSMS connections require manual ...
2
votes
0answers
195 views

SQL Server 2012 High availability Groups for OLTP and Reporting environments

I would like to seek your advice in designing a high availability architecture at my current work place. We are moving to SQL Server 2012 platform and we currently have a set of OLTP databases ...
2
votes
0answers
360 views

always on availability group with different speed disks

I'm looking to set up SQL Server 2012 installation with an Always On Availability Group, where the 'passive' replica will be hosted at another site on the WAN and using synchronous data commit - the ...
2
votes
0answers
76 views

What is correct process to physically move an availability replica to another location

Current situation is we have 3 SQL Servers with one availablity group and we plan on moving one of the secondary replicas out of HQ to another location. There will be the span of a few hours between ...
2
votes
0answers
3k views

SQL Server connection with multiSubnetFailover fails, claims mirroring is enabled

I have two SQL Server instances in an availability group. They are not mirrored. I'm trying to connect to the AG using the MS JDBC drivers (4.0) with a connection string including multiSubnetFailover=...
2
votes
1answer
366 views

GHOST_CLEANUP not freeing up space on SQL Server Availability Group

Had a problem in my SQL Server Cluster for 2 weeks, I had the GHOST_CLEANUP process running 24/7 never stopping even if the users were doing nothing. I was checking the "Free Space" parameter in the ...
1
vote
2answers
33 views

SQL server AlwaysOn alternate option for readintent=true

I know applications need certain(newest) version of ODBC drivers to connect an AlwaysOn SQL instance replica and to use the option "applicationIntent= readonly" Now my question is , if some ...

1
2 3 4 5