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
4answers
33k 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 ...
37
votes
8answers
29k 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 ...
23
votes
5answers
2k 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 ...
22
votes
3answers
4k views

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 ...
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 ...
15
votes
2answers
1k views

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 ...
14
votes
4answers
4k views

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 ...
14
votes
1answer
399 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 ...
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: ...
13
votes
1answer
21k 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'...
12
votes
4answers
23k 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 ...
12
votes
2answers
18k 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 ...
12
votes
2answers
1k views

Configure unconstrained delegation for BULK INSERT

I have a pair of Microsoft SQL Server 2016 nodes in an Always On Availability Group. I am trying to perform a BULK INSERT (using a SQL Server 2016 Management Studio query) on a file located on a ...
11
votes
2answers
2k 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 ...
10
votes
1answer
32k 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 ...
10
votes
2answers
4k 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 ...
10
votes
2answers
9k views

AlwaysOn Availability Group Automatic Failover does not work

Playing with AG setup I have the WSFC up and configured with two nodes in one availability group called DevClusterOnline. Both nodes (DEV-AWEB5 primary, DEV-AWEB6 secondary) are running Windows Server ...
10
votes
6answers
5k 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. ...
10
votes
1answer
3k 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 ...
9
votes
3answers
10k views

What to do when your Always On cluster loses quorum?

I was reviewing our company DR procedures and when I looked online for solutions to an Always On Cluster losing quorum, to compare to. I was three pages into google results before finding the first ...
9
votes
2answers
19k 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? ...
9
votes
1answer
4k views

Can Availability Groups provide seamless failover (with no query failures)?

I have been testing the Availability Groups feature in SQL Server 2012 and am finding that there is approximately 15 seconds of down time when the primary server fails over to the secondary server. ...
9
votes
1answer
3k views

Unable to truncate transaction log, log_reuse_wait_desc - AVAILABILITY_REPLICA

This morning i was woken up by a transaction log full alert on one of our database. This server is an alwayson cluster and also a transactional replication subscriber. I checked log_reuse_wait_desc ...
9
votes
3answers
1k 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 ...
9
votes
1answer
803 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 ...
8
votes
4answers
1k views

Is “AlwaysOn” not always “Always On?”

We created a Windows Failover Cluster, then added two instances of SQL Server as nodes of a SQL Server failover cluster. We set the servers to use "AlwaysOn Availability Groups" in SQL Configuration ...
8
votes
3answers
475 views

Long Running Query on Read-Only Replica that takes moments on the Primary

I've got a 4 node AG setup as follows: VM Hardware Configuration of all nodes: Microsoft SQL Server 2017 Enterprise Edition (RTM-CU14) (KB4484710) 16 vCPUs 356 GB RAM (long story to this one...) max ...
8
votes
2answers
2k views

Can Availability Groups replace transactional replication?

I've been looking at the new features of SQL Server 2012, and how it can help my current situation. Currently we're on SQL Server 2008 R2, and replicate two OLTP servers' databases to a single ...
8
votes
4answers
12k views

SQL Server 2012 always-on backup, full vs copy-only

I need a quick clarification on copy-only backup vs full backup as it pertains to transaction log numbering and restore if required. I have SQL Server 2012 setup with always on for multiple databases....
8
votes
1answer
80 views

Database Mirroring protocol TCP ports used. One default, one dynamic?

When running below query on the primary/secondary replica of a SQL Server Always On Availability Group™ SELECT DISTINCT local_tcp_port,protocol_type,num_reads,num_writes FROM sys.dm_exec_connections ...
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 ...
8
votes
1answer
414 views

Why are these two HADR DMVs reporting different states?

SQL Server 2012 (11.0.5058.0) Enterprise Edition We have 8 Availability Groups in a 2(HA)+1(DR) cluster and our monitoring DMVs are reporting results that confuse me. 6 Availability Groups are ...
8
votes
2answers
4k views

How do Availability Groups work with cheduled jobs and SSIS packages?

I have 2 SQL Servers set up with a database in an AlwaysOn Availability Group. I also have jobs set up which run against the database. During a failover how do I ensure the jobs will continue to run ...
8
votes
3answers
2k views

AlwaysOn Availability Groups log_send_rate

On all our AlwaysOn setups, running Windows 2012 and SQL Server 2012 in virtual machines and on bare-metal, I find that the log_send_rate in sys.dm_hadr_database_replica_states is consistently ...
8
votes
1answer
1k views

AlwaysON Migrate from 2014 to 2016

I have like 20 Listners on 2014 with approx 500 DBS, what is the best way to migrate these with least downtime, effort My idea is: Stop access Backup dbs Start restore dbs While restore is ...
7
votes
2answers
10k 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 ...
7
votes
3answers
18k 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
1answer
1k views

Do locks on readonly secondary databases propagate to the Read/Write Database

I'm thinking about using SQL Server Availability Groups in SQL Server 2012 to offload reporting on our main database to a second read only server that is kept in sync through the availability group. ...
7
votes
1answer
145 views

Is there a way to determine what kind of data is sitting in the Redo Queue for an AlwaysOn AG Secondary Replica?

Do any of the DMVs or is there another way to expose what kind of data is in the Redo Queue that's in process of being synced up to a secondary replica? (E.g. is it table data and which table, or is ...
7
votes
2answers
566 views

Conditional Connection in SSIS based on Availability Group status

I'm working on an SSIS package that will refresh dev databases from production backups. The package has been in place for years, but we're deploying Availability Groups on the dev instance, so the ...
7
votes
2answers
585 views

What is causing this error: None of the specified replicas for availability group ag1 maps to the instance of SQL Server to which you are connected

I am configuring Always-on for SQL Server 2017 RC1 (14.0.80.90, dated 2017-7-18) for Linux as per https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-configure-ha. This ...
7
votes
2answers
5k views

SSMS 2016, Availability Groups, database is encrypted error

While evaluating possibility for rolling upgrade of SQL Server 2012 instances hosting AGs to SQL Server 2016 we ran into strange issue that does not quite make sense. One of the easier way to ...
7
votes
1answer
1k views

SQL Server AlwaysOn to run updates and schema changes in 24/7 environment

First of all i have to say that this question comes from a software engineer-perspective. Unfortunately we have no DBA so we need to administer our own databases. We have SQL Server 2012 Enterprise ...
7
votes
1answer
2k views

Recovery time effects of Availability Groups

We did different kinds of basic Tests and AlwaysOn passed in many tests. We finally did a heavy write test for AlwaysOn and it gave surprising results. The actual Test details are here, the goal is ...
7
votes
1answer
8k views

How to link users and logins in an Availability Group?

I've got an Availability Group going and need to map some logins to database users on an active secondary replica. (Node2) Unfortunately, when I try to exec sp_change_users_login or alter user with ...
7
votes
1answer
192 views

Gathering Read Routing information using SMO is inaccurate in SQL Server 2016

I've been playing around with SMO to check out some properties recently, and have come across a problem when trying to gather information on the read-routing order in SQL Server 2016. With earlier ...
7
votes
1answer
20k 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 ...
7
votes
2answers
2k views

SQL Server 2016 AlwaysOn DBCC CHECKDB on async replica

We have an AlwaysOn environment, which includes a replica in our DR site that is setup with async commit and readable secondary = No. When we were running on SQL Server 2014 SP2 we were able to run ...
7
votes
2answers
650 views

SQL Server 2014 Always On .. can I have replication without a listener?

I am still a novice with SQL Server Always On, having recently converted from a standalone server to an always-on configuration. I currently have two nodes on the same subnet configured in my ...
7
votes
3answers
14k views

The Service Broker endpoint is in disabled or stopped state - AoAG - SQL Server 2012 - 15 minute interval

I receive this error message in SQL Server error log every 15 minutes. I have setup AOAG on this server. The database option "Service Broker - Broker Enabled" is turned on for the Blackberry related ...