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
43
votes
8answers
39k 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 ...
13
votes
4answers
26k 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 ...
6
votes
5answers
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 ...
1
vote
1answer
2k 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 ...
5
votes
1answer
8k 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: ...
4
votes
4answers
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 ...
0
votes
1answer
3k 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....
12
votes
2answers
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 ...
2
votes
2answers
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 ?
0
votes
1answer
250 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 ...
0
votes
1answer
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 ...
17
votes
1answer
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 ...
16
votes
3answers
30k 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 ...
7
votes
2answers
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 ...
8
votes
1answer
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 ...
1
vote
0answers
307 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 ...
13
votes
1answer
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'...
24
votes
5answers
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 ...
5
votes
6answers
35k 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 ...
9
votes
2answers
24k 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? ...
8
votes
3answers
21k 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) ...
5
votes
1answer
386 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
vote
1answer
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 ...
7
votes
1answer
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 ...
2
votes
1answer
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 ...
2
votes
3answers
11k views

How to configure SSIS in SQL Server AlwaysOn

I have AlwaysOn Availability Group setup for my SQL Server. Now I would like to configure SSIS. How do I do it? Below link provides some information but since I am not DBA it's difficult to understand ...
1
vote
1answer
507 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' ...
1
vote
1answer
9k 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 ...
4
votes
2answers
9k 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 ...
3
votes
2answers
1k 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 ...
3
votes
1answer
77 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. ...
2
votes
1answer
2k 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 ...
1
vote
1answer
3k 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 ...
1
vote
1answer
275 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 ...
0
votes
1answer
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 ...
0
votes
1answer
77 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
49
votes
4answers
35k 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 ...
11
votes
6answers
8k 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. ...
5
votes
1answer
845 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 ...
14
votes
1answer
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: ...
11
votes
1answer
4k 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 ...
5
votes
2answers
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 ...
4
votes
1answer
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 ...
4
votes
2answers
3k 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 ...
20
votes
2answers
3k 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 ...
12
votes
1answer
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 ...
11
votes
1answer
50k 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 ...
7
votes
2answers
4k views

Can CDC or Change Tracking be against a read only secondary?

We will be deploying SQL Server 2012 Availability Groups with read only secondaries and would like to execute our SSIS extracts against the secondary while utilizing CDC or change tracking (the ...
7
votes
1answer
22k views

Connection AlwaysOn AG Listener Problem

I'm a newbie for microsoft technologies and I'm having problems in connectivity part for SQL Server 2014. This is probably an easy issue but I'm stuck. I've 4 servers. One of them is the Active ...
6
votes
1answer
15k views

Availability Group reporting disconnected replica

I'm trying to configure availability groups in a VM environment so I can run some tests. I think I've got the group created correctly, I can see the Always on group on both servers. However when I ...