2022 Developer Survey is open! Take survey.

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
47 votes
8 answers
47k views

SQL Server Agent Jobs and Availability Groups

I'm looking for best practice in dealing with scheduled SQL Server Agent jobs in SQL Server 2012 availability groups. Maybe I missed something, however at the current state I feel that SQL Server ...
user avatar
  • 2,747
13 votes
4 answers
27k views

Logins aren't syncing across Availability Groups

We have 2 servers in an AlwaysOn group. While the user accounts within each synchronized database exist on both servers, the database instance level logins only exist on one of the servers. Ie ...
user avatar
  • 507
6 votes
5 answers
15k views

SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell

I've installed and successfully configured our SQL Server 2012 AlwaysOn 2-node servers for our new "Intranet" that is coming out. I've gotten AlwaysOn working great, and our Front End servers for the ...
user avatar
6 votes
1 answer
9k views

Are "Always On Failover Cluster Instances" and "SQL Server Failover Clustering" the same thing?

I'm reading documentations of SQL Server. On this page at https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover-clustering-wsfc-with-sql-server, it says: ...
user avatar
1 vote
1 answer
4k views

All my database on the fail over server showing in "Synchronizing" state

Current Configuration: I have two SQL Servers, which are connected by the failover mechanism, meaning one is primary and one is secondary. I restarted both services on both servers sequentially. On ...
user avatar
4 votes
4 answers
4k views

patching SQL Server with Availability Group and Secondary Replica

Scenario: SQL Server 2014 CU6. 1 AG with 2 databases (compatibility level 100). Failover Cluster with 3 nodes on Windows Server 2012 R2. Primary Availability Replica on node 1 and node 2. Secondary ...
user avatar
0 votes
1 answer
4k views

AlwaysON Availability group query very slow

I am running below tsql to find the secondary AlwaysON servers from the primary replica : set transaction isolation level read uncommitted set statistics time, io on SELECT ISNULL(agstates....
user avatar
  • 61.1k
12 votes
2 answers
5k views

Do schema changes "break" Availability Groups or are they handled transparently?

My organization is planning to adopt SQL Server 2012 Availability Groups and I am trying to understand what impact (if any) it will have on our application upgrade process. We release application ...
user avatar
2 votes
2 answers
3k views

Quorum for 3 nodes AlwaysOn Availability Group

If I am going to set an Alwayson Availability group with 3 nodes, with read-only routing. Do I need to configure a Quorum ?
user avatar
  • 1,363
1 vote
1 answer
2k views

Apply SP3 on SQL Server 2012 AlwaysOn AAGs

If Always-On is configured, while applying Service Pack, should I: Directly move SQL Server Instance (Secondary Replica) to make it passive and apply Service Pack Or First move Availability Group ...
user avatar
0 votes
1 answer
329 views

is this time to remove the databases from the availability group? which ones?

While patching one of my servers that is part of an availability group, as a secondary, I had some problems - the real problem was that the host machine of one of the virtual servers in the ...
user avatar
16 votes
4 answers
37k views

Availability Group database stuck in Not Synchronizing / Recovery Pending mode

While upgrading the storage in a SQL Server 2014 SP1 (12.0.4422.0) instance we ran in to an issue where two of the databases would not start on the secondary after restarting SQL Server. The server ...
user avatar
  • 1,003
9 votes
6 answers
47k views

Availability Group how to determine last Failover time

I'd just like to know if there is any way to query when the AG group failed over. eg - this is the Primary Replica now, but I'm pretty sure it was the secondary yesterday? How can i find when the ...
user avatar
  • 652
8 votes
2 answers
11k views

How to query readonly replica in Always On cluster

We have an Always On "cluster" that consists of 2 servers (there will be more), so one is PRIMARY and other(s) is(are) SECONDARY. The idea was to dedicate SECONDARY as a readonly replica, so it would ...
user avatar
  • 427
9 votes
3 answers
2k views

Always On Availability Group, Always redirect user to read only instance

We have an Always On availability group with a primary and a read-enabled secondary. We have a user for the implementation team who uses the database to check the correctness of the data that they are ...
user avatar
  • 1,951
8 votes
1 answer
2k views

Recommended ways to upgrade AlwaysOn AG from 2014 to 2016

What is the recommended process of upgrading Availability Groups with one Sync Replica from SQL 2014 to 2016? My understanding is that in-place upgrades are not really liked among database ...
user avatar
  • 183
5 votes
2 answers
3k views

How to remove a secondary database from an availability group and rejoin it

I have an availability group (AG) with multiple databases (DB-A, DB-B, DB-C), and multiple secondaries (SEC-B, SEC-C) and one of the databases will not resume synchronization on just one of the ...
user avatar
  • 7,753
2 votes
0 answers
460 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 ...
user avatar
48 votes
4 answers
36k views

Clustering vs. transactional replication vs. availability groups

Assuming you need to make sure your application that relies on SQL Server 2012 as its database backend is available around the clock, even if one server machine fails. As a developer and not a DBA, I ...
user avatar
  • 8,643
17 votes
1 answer
3k views

Will running a large query on a secondary database in an availability group affect transaction performance in the primary database?

I need to provide real-time, or almost real-time, data for SSRS and Tableau reporting. I don't want the production OLTP system to be negatively impacted by long running queries. Will running a large ...
user avatar
  • 455
13 votes
1 answer
22k views

Restoring a SQL Server 2012 database in high availability

I have a database which is in always-on high availability mode synchronized with another database on a different instance. How can I restore from a .bak file into the primary database using T-SQL? I'...
user avatar
  • 133
24 votes
5 answers
3k views

From the DMVs, can you tell if a connection used ApplicationIntent=ReadOnly?

I have an Always On Availability Group set up, and I want to make sure my users are using ApplicationIntent=ReadOnly in their connection strings. From the SQL Server via DMVs (or Extended Events or ...
user avatar
  • 41.3k
9 votes
2 answers
25k views

Availability Group Listener

I'm looking at AlwaysOn availability groups. The more I look at it the more it appears that the availability listener group is a single point of failure. Where exactly does the listener actually run? ...
user avatar
8 votes
3 answers
22k views

Transaction Log Maintanance While Using AlwaysOn Availability Group

We are using HADR ( AlwaysOn Availability Group) feature of SQL Server 2012. Server and AG Configuration as below: SQL12_SRV1 --> AG1(PRIMARY) - SYNC -->> DBTest SQL12_SRV2 --> AG1(SECONDARY) ...
user avatar
5 votes
1 answer
490 views

Performance gain with MultiSubnetFailover for single subnet

On BOL I read following about MultiSubnetFailover=True: The MultiSubnetFailover connection option should be set to True even if the availability group only spans a single subnet. This allows you to ...
user avatar
1 vote
1 answer
2k views

AlwaysON Availability Groups - IP address change of secondary replica node

I have a multi-subnet AlwaysON AG with one primary and one secondary replica in synchronous commit mode. Due to hardware maintenance the IP address of the secondary node will change. Is there any ...
user avatar
  • 630
7 votes
1 answer
3k views

Service Broker & AlwaysOn Availability Groups: Odd Transmission Queue Behavior

I have also posted this question on my blog: http://www.sqldiablo.com/2012/04/15/service-broker-alwayson-availability-groups-odd-transmission-queue-behavior/. I’ve been working on a project over the ...
user avatar
2 votes
1 answer
2k views

AlwaysOn DDL and Schema Changes

Can anyone discuss how to exactly incorporate DDL Schema changes with Always On Availability Groups, step by step? We have a Primary Replica in one state, and Secondary Replica in another state ...
user avatar
1 vote
1 answer
10k views

Shared network location in Availability Groups configuration

I am configuring Availability Groups feature in azure virtual machines nodes. In the below screen wizard, it's asking me for shared location. where and how I need to create this shared location? Do I ...
user avatar
1 vote
1 answer
853 views

Log truncation issue in AlwaysOn AG

We have four nodes in our AG. After the weekly index maintenance job, the log drive became full. We enabled CDC in one of our database, so we disabled CDC to truncate the log. Then we ran 'checkpoint' ...
user avatar
  • 13
4 votes
2 answers
11k views

Rename Availability Group and AG script

I have two questions for you guys. I really appreciate your help. 1.- Is there a way to rename an Availability Group? 2.- Is there a way to get the T-SQL statements in order to re create a ...
user avatar
3 votes
1 answer
589 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 ...
user avatar
3 votes
1 answer
156 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. ...
user avatar
  • 43
3 votes
3 answers
15k views

Allow users to only view the databases they have access to

I wonder if there's any way to allow a user to only view the specific databases they have access to. For example. Let's say we have a alwaysOn with one local only database and 10 databases that is ...
user avatar
  • 169
2 votes
1 answer
3k views

How can I automatically have a node become primary in an Availability Group?

In my environment, I have Db01 and Db02 setup with SQL 2012 AlwaysOn. Db01 is the preferred master as that is where all of the backup jobs reside. Typically, the only time that Db01 is unavailable is ...
user avatar
2 votes
2 answers
3k views

Remove a node from Failover Cluster

We had 5 SQL Server 2012 servers configured with AlwaysOn. A few days ago we dismissed 2 of them, we removed them from the availability group and then stopped SQL services. We need to reuse these ...
user avatar
  • 41
1 vote
1 answer
4k views

AlwaysOn AG: Current Host Server - Owner Node - Primary Instance

I'm building up a SQL Server AlwaysOn AG and I'm on the step "failover", manual and automatic: I noticed that in the Windows Server Failover Cluster a "Current Host Server" exists. My knowledge says ...
user avatar
1 vote
1 answer
308 views

Syncing Server Objects across AG nodes

This is my first question, hope I do it right! I am working on creating a process that will sync server objects between our AG nodes. We are using SQL Server 2016 with 4 nodes. Two nodes in one data ...
user avatar
0 votes
1 answer
1k views

Proper way to create logins on Always on

What is the proper way to create logins on Always on? An application database requires an active directory login and I created that on the primary node. The database is still not in the availability ...
user avatar
0 votes
1 answer
282 views

Timeout for Always On Synchronous Commit Mode

For SQL Server Enterprise's Always On availability groups, how can you configure the timeout for Synchronous Commit mode? Is there a maximum? Can it work, for example, between continents? Thanks
user avatar
  • 179
11 votes
6 answers
10k views

SSMS Registered Servers with ReadOnly Application Intent

We are working on SQL Server 2014 POC testing with AlwaysOn and one of the user asked about saving the configuration of SSMS with ReadOnly Intent using the registered servers in Local server group. ...
user avatar
  • 447
12 votes
1 answer
64k views

SQL Server AlwaysOn database stuck in Not Synchronizing / In Recovery mode after upgrading. Error: Cannot open database '...' version 782

While testing an upgrade from SQL Server 2014 SP1 (12.0.4422.0) to SQL Server 2016 CTP 3.2 (13.0.900.73) I was following the recommended update process and ran into an issue where the database would ...
user avatar
  • 1,003
5 votes
1 answer
945 views

How to manage primary key value while multiple secondary server sync with primary server?

we have three secondary database server in different cities and one primary server. my question is i want asynchronous transaction commit to all three database but primary key data conflict occur ...
user avatar
14 votes
1 answer
7k views

AlwaysOn AG, DTC with failover

Problem: How can I run Distributed Transaction Coordinator (DTC) on all servers in an AlwaysOn Availability Group (AG)? I do NOT need to maintain transactions over failover/switchover events. Setup: ...
user avatar
11 votes
1 answer
6k views

Who is using my worker threads? SQL Server 2014 - HADR

We had recently a problem on our SQL Server 2014 HADR environment, where one of the servers ran out of worker threads. We got the message: The thread pool for AlwaysOn Availability Groups was ...
user avatar
  • 311
5 votes
2 answers
7k views

How AlwaysOn Availability Group secondary replica catches up with primary after secondary server long downtime

Can someone point me to the MS article or blog post that explains in details how AlwaysOn Availability Group secondary replica catches up with primary after secondary server long downtime? I did ...
user avatar
4 votes
2 answers
4k views

Database Mirroring login attempt failed with error: 'Connection handshake failed. The handshake verification failed. State 36.'

While performing a rolling upgrade of an Availability Group from SQL Server 2016 CU1 to 2016 SP1 we noticed an issue. Immediately after we updated the secondary replica replication stopped working for ...
user avatar
  • 1,003
4 votes
1 answer
1k views

Oldest unsent transaction alert for Availability Group

In SQL Server 2008 when using database mirroring, it was possible to set up alerts when the oldest unsent transaction exceeded a certain threshold. We could check the state of any database mirroring ...
user avatar
  • 1,404
20 votes
2 answers
4k views

Truncate a table with 17 billion rows in an AG

I need to truncate a table with 17 billion rows, the table is in a database that is part of an AG. What will be the effect of this operation on the AG latency and the size of log backups? Is there a ...
user avatar
  • 455
13 votes
1 answer
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 ...
user avatar