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.

Availability Groups are different from Database Mirroring in several ways. The major differences are mostly centered around the ability to read the data on multiple other instances and the capabilities around automatic failover.

Official Info: An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of primary databases and one to four sets of corresponding secondary databases. Source: http://msdn.microsoft.com/en-us/library/ff877884.aspx

Provides an integrated set of options including automatic and manual failover of a group of databases, support for up to four secondaries including two synchronous secondaries and seamless application failover using availability group listener. Source: http://www.microsoft.com/sqlserver/en/us/solutions-technologies/mission-critical-operations/SQL-Server-2012-high-availability.aspx

Resource: For more information on the capabilities and latest improvements to SQL Server Availability Groups you can visit the AlwaysOn Team Blog

Note that Availability Groups are part of and sometimes referred to as "AlwaysOn" - but "AlwaysOn" is a marketing term that refers to all of Microsoft's high availability and disaster recovery features over multiple server technologies.

1650 questions
51
votes
8 answers

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 Agent is not really integrated with this great…
49
votes
4 answers

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 am struggling to understand when to use which…
25
votes
5 answers

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 whatever), can I tell if a user connected with…
Brent Ozar
  • 43,325
  • 51
  • 233
  • 390
22
votes
3 answers

SQL Server Distributed Availability Group databases not syncing after a server reboot

We're getting ready to perform a large upgrade on our SQL Servers and are noticing some unusual behavior with Distributed Availability Groups that I'm trying to resolve before moving forward. Last month, I upgraded a remote secondary server from…
21
votes
2 answers

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 recommended way of doing this?
20
votes
1 answer

Forced plans on readable secondaries

If a plan is forced on the primary in an Availability Group, is it applied to queries run on a secondary? I'm looking for answers that cover both possibilities for plan forcing: Plan Guides Query Store Forced Plan I have read the following that…
18
votes
1 answer

Messages about parallel redo

Parallel redo is shutdown for database '' with worker pool size [2]. Parallel redo is started for database '' with worker pool size [2]. Starting up database '' I see this on a client PC a lot in the Windows Event Viewer (Event Id 49930 or…
18
votes
1 answer

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 query on a secondary database in an availability…
Tarzan
  • 557
  • 1
  • 5
  • 17
16
votes
4 answers

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 had been offline for a few hours while we installed…
Greg Bray
  • 1,053
  • 3
  • 13
  • 20
15
votes
1 answer

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: I have a Windows Failover Cluster (WSFC) with…
Elijah W. Gagne
  • 705
  • 2
  • 12
  • 22
15
votes
4 answers

RAID0 instead of RAID1 or 5, is this crazy?

I'm considering using a RAID0 setup for one of our SQL Server clusters. I'll outline the situation and am looking for why this may be a bad idea. Also if someone you have use cases, white papers or other documentation you can point me to on this…
zsqlman
  • 159
  • 1
  • 6
15
votes
2 answers

SQL Server 2017 with 500 databases - Frequent AG disconnects since CU9

Hi everyone and thanks in advance for your help. We are experiencing challenges with SQL Server 2017 Availability Groups. Background Company is a retail B2B back-end software. About 500 single tenant databases, and 5 shared databases used by all…
SQLRaptor
  • 4,108
  • 1
  • 14
  • 26
14
votes
4 answers

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 DBINSTANCE->Security->Logins are missing on one…
John
  • 517
  • 1
  • 6
  • 11
13
votes
1 answer

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'm new to high availability and I've been advised…
Stew
  • 133
  • 1
  • 1
  • 4
13
votes
7 answers

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 failover took place is there something specific in…
Trubs
  • 774
  • 1
  • 6
  • 14
1
2 3
99 100