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.

283 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5 votes
2 answers
678 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 ...
Adam's user avatar
  • 51
4 votes
0 answers
105 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 ...
Newbie-DBA's user avatar
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 ...
Tomasz's user avatar
  • 115
4 votes
0 answers
939 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 ...
Jaron's user avatar
  • 41
4 votes
0 answers
6k 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 ...
captkirk1701's user avatar
3 votes
1 answer
3k 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 ...
Kiran Narlapati's user avatar
3 votes
1 answer
395 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 ...
Amam's user avatar
  • 389
3 votes
0 answers
2k 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:...
GMassDBA's user avatar
  • 335
3 votes
2 answers
750 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 ...
youcantryreachingme's user avatar
3 votes
0 answers
2k 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 ...
user avatar
3 votes
1 answer
12k 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 ...
Marcello Miorelli's user avatar
3 votes
1 answer
156 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 ...
user1716729's user avatar
3 votes
0 answers
870 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 ...
Neil's user avatar
  • 31
3 votes
1 answer
265 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 ...
SQL_Hacker's user avatar
3 votes
0 answers
100 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 ...
Kahn's user avatar
  • 1,813
3 votes
0 answers
973 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 ...
Banu Akkus's user avatar
3 votes
0 answers
105 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 ...
Michael Cherevko's user avatar
3 votes
1 answer
403 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 ...
danarj's user avatar
  • 169
3 votes
1 answer
82 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 ...
MySQL DBA's user avatar
  • 512
3 votes
2 answers
953 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 ...
croxfade's user avatar
  • 156
2 votes
0 answers
91 views

How to use Custom Certificate for SQL Server Availability Group Endpoint Authentication

I am setting up a SQL Server Availability Group using the commands provided in the Microsoft documentation. The process involves creating a certificate, backing it up with the private key, and ...
Neaj Morshad's user avatar
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 ...
Newbie-DBA's user avatar
2 votes
0 answers
151 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-...
Craig Efrein's user avatar
  • 9,640
2 votes
0 answers
188 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 ...
GSerg's user avatar
  • 1,342
2 votes
2 answers
475 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 ...
user2180778's user avatar
2 votes
1 answer
2k 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 ...
Kevin's user avatar
  • 533
2 votes
0 answers
930 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 ...
buddinggeek's user avatar
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 ...
dajonx's user avatar
  • 91
2 votes
1 answer
217 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....
youcantryreachingme's user avatar
2 votes
0 answers
504 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 ...
Alf47's user avatar
  • 961
2 votes
0 answers
335 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 ...
Dominique Boucher's user avatar
2 votes
0 answers
813 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 ...
Marcello Miorelli's user avatar
2 votes
0 answers
553 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 ...
Lisa's user avatar
  • 21
2 votes
0 answers
148 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 ...
Dmitry's user avatar
  • 21
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 ...
AdamM's user avatar
  • 21
2 votes
0 answers
3k 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 ...
sercurity's user avatar
2 votes
0 answers
115 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 ...
user3115672's user avatar
2 votes
0 answers
2k views

Primary Server is "Resolving state" & Quorum node is in "failed" state

I would like to say that in my environment I have a 2 node in WSFC with the nodes SQLServer-01 (Primary Server) SQLServer-02 (Secondary Server) And for third vote in cluster also One (File share ...
Md Haidar Ali Khan's user avatar
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 ...
Jeremie's user avatar
  • 21
2 votes
0 answers
96 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 ...
vesuvious's user avatar
  • 131
2 votes
0 answers
362 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 ...
Yaroslav's user avatar
  • 2,826
2 votes
0 answers
240 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 ...
James's user avatar
  • 21
2 votes
0 answers
167 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 ...
Philipp's user avatar
  • 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 ...
MySQL DBA's user avatar
  • 512
2 votes
0 answers
387 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....
nick's user avatar
  • 21
2 votes
2 answers
252 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 ...
user72471's user avatar
2 votes
0 answers
254 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 ...
JMB's user avatar
  • 51
2 votes
0 answers
1k 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 ...
Jeremy Dearduff's user avatar
2 votes
0 answers
203 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 ...
user35813's user avatar
2 votes
0 answers
403 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 ...
paulH's user avatar
  • 1,444

1
2 3 4 5 6