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.

263 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
13 votes
2 answers
1k 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 ...
5 votes
2 answers
659 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 ...
  • 51
4 votes
0 answers
76 views

Planning for application upgrade which connects to SQL server database

I am looking for some advise/guidance on how to achieve this with minimum downtime and possibly avoid any errors or failure during production change- Background- We have SQL database servers ...
4 votes
0 answers
4k 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 ...
  • 115
4 votes
0 answers
917 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 ...
  • 41
4 votes
0 answers
5k 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
1 answer
1k views

Always On Availability Groups connection with secondary database established for primary database

I'm seeing this messages almost everyday in one of the production server. I don't feel comfortable seeing this..is there anything we can do to suppress this messages ? though it's showing No user ...
3 votes
0 answers
1k 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:...
  • 315
3 votes
1 answer
2k 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 ...
  • 382
3 votes
1 answer
8k 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 ...
3 votes
1 answer
136 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 ...
3 votes
0 answers
728 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 ...
  • 31
3 votes
1 answer
253 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
0 answers
99 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 ...
  • 1,765
3 votes
0 answers
890 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
0 answers
91 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
1 answer
387 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 ...
  • 169
3 votes
1 answer
65 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 ...
  • 512
3 votes
2 answers
866 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 ...
  • 158
2 votes
0 answers
30 views

Controlling permissions for app team while assigning them ALTER ANY AG access

There is a need where we our DEV team needs to make index changes and run other index maintenance jobs on the primary replica. We have seen that while this happens, there is a blocking chain created ...
2 votes
0 answers
92 views

Researching Performance Degradation on AO Secondary using MultipleActiveResultSets (MARS)

Environment: SQL Server 2016 Enterprise Edition on Windows 6.3 9600 two node cluster with Always-On. We have an application that executes SELECTs on the only secondary node of a SQL Server 2016 always-...
  • 9,538
2 votes
0 answers
156 views

Are single-instance cross-DB transactions fully protected in Availability Groups as is, or do they require DTC_SUPPORT = PER_DB?

In a relation to MS SQL 2017 cluster: impact of disabling distributed transactions for an availability group, which is a similar but different question. We have an SQL Server 2016 for which we are ...
  • 1,342
2 votes
2 answers
331 views

behavior of a Sql Server availability group listener and a jdbc client during a failover?

i have a question about the behavior of the AG listener during a failover... when the failover happens, I realize that all the connections on both primary and secondaries are closed. if the client ...
2 votes
1 answer
137 views

TransactionLog not getting truncated in AlwaysOn Cluster

Folks, stuck with a wierd transaction log truncation(reuse) problem in an AlwaysOn cluster and running out of options, the transaction log is not getting truncated and keeps growing. Only one database ...
  • 369
2 votes
2 answers
400 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 ...
  • 21
2 votes
1 answer
1k views

SSIS - Setup OLEDB Connection to SQL Server Always On Listener

I am trying to connect to SQL Server 2017 Always On from an SSIS package (built in SSDT 2017). I am using an OLEDB Connection Manager. 'Application Intent' is set to READWRITE. However, where do I ...
  • 513
2 votes
0 answers
627 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
1 answer
1k 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 ...
  • 91
2 votes
2 answers
469 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 ...
2 votes
1 answer
190 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
0 answers
407 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 ...
  • 941
2 votes
0 answers
231 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
0 answers
601 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 ...
2 votes
0 answers
408 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 ...
  • 21
2 votes
0 answers
95 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 ...
  • 21
2 votes
1 answer
3k views

Error: CImageHelper::Init () Failed Load of symbol file with path

Using SQL server 2017 (14.0.3029.16) with Always On High Availability environment. Getting error: CImageHelper::Init () Failed load of symbol file with path = C:\Program Files\Microsoft SQL Server\...
  • 186
2 votes
2 answers
1k views

Database Log Reuse Wait shows AVAILABILITY_REPLICA but database is no longer part of an availability group

My database log is growing out of control, I recently removed the database from an availability group by when I look at the log reuse wait description it shows "AVAILABILITY_REPLICA" I have tried to ...
2 votes
0 answers
1k 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 ...
  • 21
2 votes
0 answers
2k 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
1 answer
120 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 ...
  • 1,447
2 votes
0 answers
101 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
1 answer
1k 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 ...
  • 21
2 votes
0 answers
81 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 ...
  • 131
2 votes
0 answers
280 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,806
2 votes
0 answers
235 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 ...
  • 21
2 votes
0 answers
146 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 ...
  • 21
2 votes
0 answers
268 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 ...
  • 512
2 votes
0 answers
372 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....
  • 21
2 votes
2 answers
243 views

SQL Server 2012 AlwaysOn Availability Group

We have an application that needs access to the database at all times to work properly and we are planning on deploying SQL Server 2012 AlwaysOn Availability Groups on Windows Server 2012. We have two ...
2 votes
0 answers
237 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 ...
  • 51

1
2 3 4 5 6