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.
127
questions
49
votes
8
answers
58k
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 ...
- 2,877
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 ...
- 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:
...
- 2,602
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 ...
- 71
1
vote
1
answer
8k
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 ...
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 ...
- 365
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 ...
- 974
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 ?
- 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 ...
- 1,580
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....
- 62.1k
0
votes
1
answer
446
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 ...
- 15.8k
16
votes
4
answers
45k
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 ...
- 1,003
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 ...
- 427
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 ...
- 183
6
votes
2
answers
6k
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 ...
- 7,919
3
votes
5
answers
6k
views
AlwaysOn Secondary readable - can't connect with applicationintent=readonly
With an SQL AlwaysOn, I Set my readable secondary to read intent only.
When I try to connect with parameter applicationintent=readonly my connection is established with the primary server.
Here is ...
- 33
2
votes
0
answers
768
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 ...
- 15.8k
49
votes
4
answers
37k
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 ...
- 8,902
24
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 ...
- 42.7k
13
votes
7
answers
65k
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 ...
- 742
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'...
- 133
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 ...
- 321
10
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? ...
- 285
9
votes
3
answers
24k
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)
...
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 ...
- 146
5
votes
1
answer
872
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 ...
- 1,994
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 ...
- 5,560
5
votes
1
answer
538
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 $(...
- 15.8k
4
votes
2
answers
14k
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 ...
- 287
3
votes
3
answers
19k
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 ...
- 169
3
votes
1
answer
685
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 ...
- 2,151
3
votes
1
answer
431
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.
...
- 53
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 ...
- 121
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 ...
- 41
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 ...
user129291
1
vote
1
answer
371
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 ...
- 11
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 ...
- 652
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 ...
- 269
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 ...
- 33
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' ...
- 13
0
votes
2
answers
459
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 ...
- 193
0
votes
1
answer
585
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
- 189
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 ...
- 625
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 ...
- 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 ...
- 82.9k
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 ...
- 529
16
votes
1
answer
58k
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 ...
- 289
15
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: ...
- 705
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 ...
- 3,011
12
votes
1
answer
81k
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 ...
- 1,003