Skip to main content

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
50 votes
8 answers
60k 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 ...
nojetlag's user avatar
  • 2,897
13 votes
4 answers
28k 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 ...
John's user avatar
  • 507
6 votes
1 answer
11k 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://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover-clustering-wsfc-with-sql-server, it says: ...
Just a learner's user avatar
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 ...
AllenValk66's user avatar
1 vote
1 answer
9k 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 ...
JYOTI PRAKASH MALLICK's user avatar
12 votes
2 answers
6k 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 ...
Matt O'Brien's 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 ...
Razvan Zoitanu's user avatar
2 votes
2 answers
5k 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 ?
sebeid's user avatar
  • 1,381
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 ...
Learning_Learning's 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....
Kin Shah's user avatar
  • 62.3k
0 votes
1 answer
484 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 ...
Marcello Miorelli's user avatar
0 votes
2 answers
742 views

How does SQL Browser work when default port is missing and AG Listener specifies port 1433?

Background: To keep things simple, we have a 2 node cluster where each node in the cluster has 3 instances of SQL installed: default + 2 named. I understand instance stacking is bad and it is ...
Data Dill's user avatar
  • 245
16 votes
4 answers
48k 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 ...
Greg Bray's user avatar
  • 1,003
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 ...
MichelZ's user avatar
  • 183
8 votes
2 answers
12k 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 ...
retif's user avatar
  • 427
6 votes
2 answers
7k 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 ...
Tony Hinkle's user avatar
  • 7,964
2 votes
0 answers
861 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
49 votes
4 answers
38k 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 ...
marc_s's user avatar
  • 8,962
25 votes
5 answers
4k 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 ...
Brent Ozar's user avatar
13 votes
1 answer
23k 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'...
Stew's user avatar
  • 133
13 votes
7 answers
72k 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 ...
Trubs's user avatar
  • 752
12 votes
1 answer
7k 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 ...
Dan Stef's user avatar
  • 321
10 votes
2 answers
26k 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? ...
Tim Brigham's user avatar
9 votes
3 answers
25k 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) ...
haresh.makwana's user avatar
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 ...
Adam Belebczuk's user avatar
5 votes
1 answer
1k 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 ...
Frederik Vanderhaegen's user avatar
5 votes
1 answer
540 views

How to connect to microsoft azure?

We have been saving the backups of one of our production servers (which is actually an Always On availability group) this is how I am doing my backups and saving to azure: sqlcmd -E -S $(...
Marcello Miorelli's user avatar
5 votes
1 answer
2k views

Should I create two endpoints for two availability groups on the same SQL Instance?

I have a two node cluster running SQL Server 2012. I want to create two Availability groups within the same instance of SQL Server. Should I add one endpoint for each AG, or shall I allow both AG's ...
George.Palacios's user avatar
4 votes
3 answers
21k 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 ...
Jonathan's user avatar
  • 179
4 votes
2 answers
16k 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 ...
Luis de Haro's user avatar
3 votes
1 answer
523 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. ...
paul's user avatar
  • 63
3 votes
1 answer
716 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 ...
Denis Rubashkin's user avatar
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 ...
Jim Geurts's user avatar
2 votes
1 answer
3k 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
2 votes
2 answers
4k 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 ...
maurice's user avatar
  • 41
1 vote
1 answer
3k 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 ...
Fza's user avatar
  • 652
1 vote
1 answer
11k 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 ...
kudlatiger's user avatar
1 vote
1 answer
1k 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' ...
Tito's user avatar
  • 13
1 vote
1 answer
426 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 ...
BDA the DBA's user avatar
1 vote
1 answer
5k 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 ...
ssd_rider's user avatar
0 votes
1 answer
711 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
uncaged's user avatar
  • 189
0 votes
1 answer
2k 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 ...
buddinggeek's user avatar
21 votes
2 answers
5k 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 ...
yegnasew's user avatar
  • 487
19 votes
1 answer
1k views

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 ...
Paul White's user avatar
  • 86.4k
18 votes
1 answer
4k 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 ...
Tarzan's user avatar
  • 529
17 votes
1 answer
68k views

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 ...
juFo's user avatar
  • 299
15 votes
1 answer
8k 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: ...
Elijah W. Gagne's user avatar
12 votes
1 answer
86k 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 ...
Greg Bray's user avatar
  • 1,003
12 votes
2 answers
3k views

SQL Server 2012 Availability Group is it "AlwaysON"?

In a traditional SQL Server cluster when failover occurs, all clients that are connected to the SQL Server failed instance lose connections and each client must re-establish a new connection to ...
SQL Learner's user avatar
  • 3,011
11 votes
6 answers
14k 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. ...
DBAuser's user avatar
  • 447