Stack Exchange Network

Stack Exchange network consists of 174 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
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

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
22 views

Disabling TDE on AlwaysOn database

Which are the correct steps to disable (not removing) TDE on a AlwaysOn database in an availability group? I found steps for removing TDE but that involves removing the availability group and ...
0
votes
0answers
18 views

Change Database Availbility Group on Same Instance

Is it possible to move a database from one availability group to another on the same instance without having to remove the database from the current AG and adding to the new AG on SQL Server 2016 ...
1
vote
1answer
27 views

SQLServer - Detect who is connecting via an AG listener

We have multiple generations of applications connecting to SQL Server, some of which we know [their connection libraries] do not support using the MultiSubnetFailover=True connection parameter. Fine. ...
1
vote
1answer
28 views

HADR wait on servers without HA configured

I am investigating a slow SQL Server 2012 server in a virtualized (VMWare 6.5) environment, and have come across three different waits on the server that are... confusing. These are ...
1
vote
1answer
42 views

SQL Server 2016 SP2 CU2 Availability Group Online but Cluster Offline

Am I missing something basic here? My AVG is online and working, but the Cluster on which it resides is saying offline. I didn't think this is possible, so I either have a basic misunderstanding of ...
2
votes
1answer
84 views

Why SELECT query is waiting on HADR_SYNC_COMMIT?

We have two AlwaysOn nodes in sync commit. Occasionally SELECT queries are waiting on HADR_SYNC_COMMIT. What is the underlying process that causes it? SQL Server 2016 SP1 CU5. client sessions are ...
-1
votes
0answers
17 views

Always On - Question Mark on the Primary and Other Secondary Replicas in the Object Explorer? [duplicate]

Earlier today, we performed windows updates on our DB Servers and i had to fail over the AGs from the primary to the secondary replica and then vice versa once the erstwhile primary was rebooted. ...
3
votes
1answer
34 views

sys.dm_hadr_database_replica_states, understanding the different *_time columns

I am going nuts trying to understand the meaning of the different time columns in the sys.dm_hadr_database_replica_states system table. We have a three node Always On Availability Group, where two of ...
0
votes
0answers
32 views

Unable to shrink the Transaction Log File on the Primary Database - Always On Config [on hold]

We have an AlwaysOn Setup where in we have 3 replicas in all. Primary Replica and the one of the Secondary Replicas are in the same datacenter while the second secondary replica is in a different ...
0
votes
0answers
30 views

AlwaysOn Error: The target database is participating in an availability group and is currently not accessible for queries

We have a 2 Node AlwaysOn Availability Group in SQL Server 2014 running on Hyper-V (VM). Current setup: SRV01: Role: PRIMARY READABLE SECONDARY: NO SRV02: Role: SECONDARY READABLE SECONDARY: ...
1
vote
0answers
30 views

Tracking log block movement using Extended Events

If we try to track the log block movement between 2 sync commit replicas, why do we need to track the immediate next Log block ID in extended events like hadr_send_harden_lsn_message on secondary ...
0
votes
0answers
45 views

problem with service broker in always-on

one of my clients has a database that works with service broker, last week we tried to move the database to an always on environment without knowing the application uses the service broker. after ...
0
votes
1answer
23 views

Transaction log for standalone DB vs AlwaysON availability Group

I have this question on the generation of transaction logs. For a high transaction active DB what is the ratio of log generation if it is a standalone DB and when it is part of SQL Alwayson ...
2
votes
1answer
61 views

Hide databases from SQL Server with AG

I have 10 databases on a server. Each database is part of a different AG. Each database has its own listener. Can I hide all databases and show only the one database belonging to a listener? I also do ...
1
vote
2answers
134 views

AlwaysOn Availability Groups: connecting correctly?

This question is about the correct way of accessing an Availability Group Listener: Assume I have two AlwaysOn Availability Groups with the following AG's: AG1 and AG2. I also have 2 listeners called ...
2
votes
2answers
53 views

Sqlcmd: 'KReadOnly': Unknown Option

I am using SQL Server 2014 SP2 with AG, running this command: sqlcmd -S ABC-ISELL -E -d ABP_SFA_BMB -K ReadOnly Where ABC-ISELL is the listener name, I am getting this error: Sqlcmd: 'KReadOnly': ...
1
vote
0answers
47 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 ...
1
vote
1answer
47 views

Difference between High Availability data synchronization across local replicas and DAG

I see a significant difference when synchronizing data between Primary - Secondary and between Primary HA and Secondary HA as part of Distributed Availability Groups. The difference in the amount of ...
0
votes
2answers
25 views

Auto Switch DB to primary back (persistent mode?) when available in a High Availability Always On settings [duplicate]

We have Always On Availability Group settings, and we have a preferred DB that needs to be in primary all the time even after it reboots. Once a month or so, these 2 servers DB1 and DB2 reboot for ...
0
votes
3answers
67 views

SQL Server 2016 Availability Group - 2 Nodes as Primary & Readable Secondary, 1 Node as DR

I want to create 3 nodes availability group on SQL Server 2016. Node 1 and Node 2 is my primary server and stays in primary data center. Each nodes set to readable secondary replica. Node 3 stays in ...
2
votes
2answers
22 views

What is the best way to shut down a 2 node availability group

This fall we will be powering down the whole data center to replace a defective power module. There is no DR site to move anything to, everything last bit of the company resides in one DC at one ...
3
votes
0answers
46 views

Why did the last Availability Group failover occur? [duplicate]

My SQL Server 2016 Availability Group failed over to the secondary node within the cluster. I'm not sure when this happened and to save time trawling back through the logs: Is there a quick and easy ...
-3
votes
1answer
53 views

Disadvantages of TDE [closed]

Is there any effect at the application end, if you configure TDE for Production databases, which are in both replication and Always On Availability Groups in SQL Server 2014 Enterprise Edition? This ...
1
vote
1answer
45 views

Listener does not connect to the correct instance

I have an instance with Always ON and two replicas, one of primary and one of secondary. I have created another instance and enabled Always On, each group located in different instances has a ...
2
votes
1answer
205 views

How to let particular logins to work on the secondary replica only?

We are configuring an availability group with a readable secondary replica. A login can connect to the secondary replica using "ApplicationIntent=ReadOnly" in its connection string. But we came across ...
0
votes
1answer
63 views

Temporary Resolving State on Synchronous HA, is there a way around this?

So we're having an issue whereby any loss of communication between our 2 HA nodes causes the primary node to temporarily go into a 'resolving' state. This is for all of 1-3 seconds however we serve a ...
0
votes
1answer
35 views

SQL Server ReadOnly Routing Connection String

Can we route read-only connection to readable secondary replica without setting connection string (ApplicationIntent = ReadOnly)?
2
votes
1answer
138 views

Always On/SQL Server Replication for Dummies

I'm a database developer, not a DBA, but the task has fallen to me to write a brief white paper on our options for configuring a database mirror for web service APIs to use as its source, in order to ...
2
votes
1answer
50 views

Overlap in LSN's backed up

My current backup strategy for our AlwaysOn clusters is a daily full backup and hourly transaction log backups via a third party backup program. We had a request to minimize the amount of log backups ...
0
votes
1answer
180 views

Why have SQL Server services disappeared from services.msc?

I'm running an Always On Availability Group with 2 SQL Server 2014 database servers in an AD domain. Updates are supplied to all domain servers via WSUS. Also have a third MS Reporting Services server ...
2
votes
1answer
220 views

AlwaysOn commit on Primary if Secondary goes down

I have two node SQL Server AlwaysOn Availability Group configured in Synchronous with Automatic Failover mode. Now since in Synchronous mode, the transactions need to commit on secondary replica ...
1
vote
1answer
19 views

List primary replicas and non-HA databases - excluding forwarder replicas

When asking which databases are writable on any given server, I've been using the below snippet to list PRIMARY replica databases and databases that do not participate in an AG. Now that I've ...
1
vote
1answer
31 views

Can I add 1 primary node in existing cluster?

I have a 2 node windows 2012 cluster and SQL Server 2012 installed on both. In this I have added a database on nodeA as primary and one on nodeB as secondary. I want to add more 2 databases which will ...
0
votes
0answers
32 views

How can I reuse listener names when moving AO groups to new servers

We have a cluster with two AlwaysOn groups where we want to move the databases and listeners to new servers. How can we do that in the best and most secure way? There is also another problem in ...
0
votes
0answers
71 views

Availability group listener is not listening

I've successfully created an availability group with 3 nodes. I've tried to create a listener, separately trying port 1433 and 1435, but the listener is not even trying to listen - there's no entry in ...
-4
votes
1answer
90 views

Benefits of AlwaysOn Asychronous vs Mirroring Asychronous [closed]

What are the current benefits of Alwayson AvailabilityGroups Asynchronous vs SQL Mirroring High Performance Asynchronous? I know couple advantage are, 1) AlwaysOn allow Read queries on the secondary ...
-2
votes
1answer
148 views

Why has Microsoft deprecated Mirroring? [closed]

Why has Microsoft deprecated Mirroring, in favour of Always On Availability Groups? We know Always On Availability Groups have better features, no will argue that, you can actually read the replica, ...
2
votes
1answer
305 views

Automatic Page Repair without AlwaysOn or Mirroring

Do Microsoft SQL Server 2012 Enterprise regular databases (without AlwaysOn) have Automatic Page Repair in any manner? I know from below, by incorporating AlwaysOn, Secondary replicas receive ...
0
votes
0answers
113 views

Unexpected Always On Availability groups failover happening— databases changing to resolving

I recently had done a SQL patch on a two node SQL availability group of sql 2012. Everything went well as far as I can tell. now two days after the patch unexpected failover happened. Then today as ...
2
votes
1answer
50 views

Can't resume synchronization after patch failed on secondary node of Availability Group

I applied Service Pack 4 on secondary node of an Availability Group. It failed so I restored the whole VM for the secondary node to an earlier version, and now the data is not synchronizing. I have ...
0
votes
0answers
11 views

error in create listener in server testing

I have try to create listener AGSQLDBDC01 into server testing name SQLDBDC01 but failed. What another solution for solve this error?
0
votes
1answer
24 views

Logon error to readonly db

getting error: "The target database ('MyDB') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about ...
1
vote
1answer
35 views

AlwaysOn High Availability Take database offline from primary

I have below scenario where i need your help: I have configured the AlwaysOn High Availability on my server. I have two replicas. Now I have requirement to make one database offline from the primary ...
0
votes
1answer
52 views

Taking Back up on Non readable Secondary Replica Availability group

Is there any way to back up a non readable secondary replica database to restore in a dev environment and then do dbcc checkdb? Will appreciate any help!
1
vote
1answer
131 views

Can AWS Aurora High Availability allow different Schema on Replicas?

Our company is thinking of migrating from SQL Server to Amazon AuroraDB. In SQL Server, there are AlwaysOn and Transactional Replication - two different things. AlwaysOn is utilized more for ...
0
votes
1answer
25 views

What to check before attempting a read-scale replica log backup?

We have a read-scale replica that we take our backups from. The network connection is not the best and sometimes we lose sync. It comes back in a few minutes, and for our purposes we can live with it. ...
0
votes
1answer
21 views

Setup AlwaysOn with 2 Secondaries and not adding all the databases for 2nd Secondary

I have a situation where we have AlwaysOn Setup in SQL Server 2016 Enterprise editions with primary in region A and secondary in region B setup in Asynchronous-Commit mode. We have 4 databases in ...
1
vote
1answer
81 views

How to interpret AlwaysOn event hadr_db_partner_set_sync_state

We have a program that listens to the SQL Server AlwaysOn_health event session for state changes. When we simulate a secondary server going offline or online, we get the ...
0
votes
2answers
51 views

How do I automatically enable or disable report jobs at AOAG failover?

I have an AlwaysOn Availability Group with 2 DB servers. I'm attempting to use SQL Server Agent Alerts to programmatically disable a handful of SQL Server Agent jobs (that must not run on a secondary) ...
3
votes
2answers
166 views

Will matching SQL Server network packet size to mtu increase performance

We have recently upgraded our production facility to sql server 2017, and moved to clusterless availability groups. There is a primary, a secondary on site, and a remote secondary. We were ...