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
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 ...
44
votes
8answers
45k 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 ...
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 ...
22
votes
3answers
7k 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 ...
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 ...
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
1answer
700 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 ...
16
votes
4answers
36k 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 ...
15
votes
4answers
5k 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 ...
15
votes
2answers
2k 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
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
4answers
27k 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 ...
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'...
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 ...
12
votes
2answers
2k 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 ...
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
60k 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 ...
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 ...
11
votes
1answer
29k 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 ...
11
votes
6answers
9k 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. ...
11
votes
1answer
5k 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 ...
10
votes
4answers
10k 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 ...
10
votes
2answers
10k 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
1answer
321 views

CMEMTHREAD waits during Index Optimisation in Availability Group

In a 3 node Availability group, a secondary replica will often become subject to redo lag due to the causes covered in Microsoft's documentation: Troubleshoot: Changes on the Primary Replica are not ...
9
votes
3answers
2k 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 ...
9
votes
3answers
16k 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
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? ...
9
votes
1answer
5k 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
3answers
2k 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
3answers
27k 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 ...
8
votes
4answers
2k 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
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
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
5answers
13k 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
3answers
22k 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) ...
8
votes
1answer
3k 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
22k views

Always On Failover Clustering vs Always On Availability Groups

I have some concerns over my understanding of SQL Server Always On. Kindly can you correct me (conceptually) where needed: Always On Clustering and Always On Availability Groups are 2 separate ...
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
2k 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 ...
8
votes
1answer
548 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
3k 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
3answers
13k views

Why is Full Recovery model a requirement for an Availability Group?

We have all our production databases in simple recovery model, and are very happy about it because it fully satisfies our RPOs and RTOs. Now we want to implement AG DR solution for our databases and ...
7
votes
2answers
1k views

AlwaysOn High Availability doesn't work , can't activate it

I am trying to activate the high availability on my SQL server and getting below message: SQL server version: Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) ...
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
286 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
2k views

What is the best option for High availability on a data warehouse?

I have a customer who have an existing 1.5Tb data warehouse and are currently planning for a complete refresh of the DW on a new environment. Their infrastructure manager has organised 2 servers with ...
7
votes
2answers
7k 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
2answers
1k 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 ...

1
2 3 4 5
28