Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

10
votes
1answer
147 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 ...
1
vote
1answer
96 views

Availability groups and Linked Servers

I have inherited an Availability Group and have found that a linked server was set up between the two nodes of the availability group. It is using sa for authentication unfortunately. Simultaneously, ...
0
votes
1answer
25 views

IP address to use with FCI, Always On AG and Always On (AG) FCI

My understanding is that HA can be achieved using either of the below and my concern was which IP address should client applications use when connecting to SQL Server: Failover Clustering: For this ...
0
votes
1answer
30 views

Automatic failover (Clustering/Always On AG)

Let's says we have 2 nodes (N1, N2) clustered (say C1) on-site. And an Always On AG is configured between this cluster C1 and a new SQL server instance N3 (N3 is off-site). Now, if there is a problem ...
2
votes
1answer
59 views

Always On Setup - OBJECTSTORE_SERVICE_BROKER Memory clerk eating all the memory

We have an Always On setup where in we have two secondary replicas to our primary replica. One of the secondaries is placed in the same datacenter as the primary and the other one is placed at a ...
0
votes
1answer
43 views

Always ON - What is the difference between the virtual cluster name and Listener

In clustering you specify DNS name/IP address of cluster that is then used by all applications. In case of failover, the same DNS name/IP address will point to the appropriate database server. In ...
-1
votes
1answer
72 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 ...
1
vote
0answers
38 views

Error: CImageHelper::Init () Failed Load of symbol file with path

Using SQL server 2017 (14.0.3029.16) with Always On High Availability environment. Getting error: CImageHelper::Init () Failed load of symbol file with path = C:\Program Files\Microsoft SQL ...
1
vote
1answer
17 views

Is a @CATALOG argument required in linked server to connect to READ-ONLY secondary?

I have 1 primary and two secondaries. Node 3 is READ-ONLY. I created a linked server as follows (as described in Microsoft Docs): EXEC sp_addlinkedserver @server=N'Node3', @srvproduct=N'', @...
0
votes
0answers
31 views

Always ON Connection string issue

In the environment I am currently working, the connection string which is being used is wrong ( AG_LISTENER_Name\InstanceName). This is something we need to correct in all apps that is configured. ...
-2
votes
1answer
43 views

How to move MDF file to new drive with out disturbing other databases in SQL Server 2014 AlwaysOn [closed]

I have 40 databases in AlwaysOn group.I need to move MDF file of 1 database to a new drive.Please help me with your suggestions SQL Server Details: 12.0.2269.0 RTM Enterprise
0
votes
1answer
27 views

Enabling TDE on SQL Server AOAG 2014 Databases - Guidance

I am completely new to Transparent Data encryption and please help me on below questions related to enabling the TDE on existing PRODUCTION Databases. Primary Question: What are the things which I ...
2
votes
2answers
41 views

SQL Server AAGs/Possible Endpoint contention/Synchronous replication to WAN Secondary

In the interest of saving some SQL Server licensing moolah ($$$), we’re going down the path of extreme consolidation. We’re trying to shoehorn as many databases as possible into a SQL Server 2016 ...
0
votes
1answer
24 views

Excluding Read only Log backups using ola-hallengren script

I know that Full backups are excluded from readable secondaries, but how do I exclude log backups from read-only secondaries? The is_read_only column in sys.databases does not get set on ...
1
vote
0answers
30 views

How to find/calculate time taken for synchronization in SQL Always on AG?

I have an SQL Always On Availability Group (AG) with 4 Servers (Replica). I'm going to add a new database (about 200GB) in the AG. I want to measure the time taken for the dB synchronization (...
0
votes
1answer
59 views

Backup Plan on Always On using Maintenance Wizard doing Tlogs on Primary and Secondary

I have my set my back up preference set to primary and the secondary is not readable. Then I did the following: Setup the same maintenance plan on all replicas in the AG through the maintenance ...
1
vote
2answers
70 views

AG Group failover and quorum

We have a 4 node AG set up across two data centers using 4 replicas with a file share witness. Nodes 1 and 2 are in data center 1 and Nodes 3 and 4 in data center 2. Node 1 is primary and is set to ...
0
votes
2answers
61 views

DR/Reporting solution for a large SQL Server database

We have a large (3TB) database that is a part of AOG. We now have a need for a third copy of the data. Here are some of things we need: The third copy needs to be in sync with the primary in real ...
1
vote
1answer
44 views

Setting up Always On Availability Groups with Windows Server Failover Cluster

I'm trying to setup Availability groups for SQL Server with two nodes. I have two SQL Servers at two different locations. I ran through the failover cluster validation and everything looked good ...
1
vote
1answer
52 views

Always On best practice for secondary node maintenance

In my AG, I have two nodes using synchronous commit, automatic failover, allow all connections in primary role, and readable secondary as yes. The failover cluster is using a fileshare witness as the ...
1
vote
2answers
124 views

DBCC CheckDb on Secondary Aynchronous Replica

Can I run Ola Hallengren CheckDb job on a Secondary Asynchronous Replica (DR) that is readable during business hours? Currently the Asynchronous Replica is not readable... First question is if it is ...
1
vote
0answers
33 views

Switch-SQLAvailability error during DB Restore in AlwaysON Group

Can anyone share your inputs on below and how to fix the issue and possible causes of failure? I am trying to restore a DB using PS script and at one step it is failing during the role change test ...
0
votes
0answers
28 views

MSOLEDBSQL Provider Linked Server

I've added the MSOLEDBSQL provider to our SQL Server 2012 server to try and create a link server that can use MULITSUBNETFAILOVER=true. The problem is creating a query, even a simple select, fails ...
1
vote
1answer
30 views

Availability Groups Failover Readinness “No data loss” and rollback of nonqualified transactions

I have Availability Groups with Synchronous Replication and Automatic Failover setup with SQL Server 2016 SP2 in my production environment. I want to do a manual failover for patching and I have ...
3
votes
1answer
84 views

Mixing SQL Server 2016 and 2017 in a distributed availability group

I have an old Availability Group made up of 2 SQLServer 2016 instances (2 Windows 2016 servers in WSFC) I also have 2 new SQLServer 2017 instances (2 Windows Server 2016) that I originally wanted to ...
1
vote
1answer
38 views

Pausing the secondary Replicas for a day in always on availability group

We are planning to make the DR as primary and do maintenance on the secondary site(Formerly Primary Site) and hence pausing both the secondaries in always on availability group for a day. I am aware ...
1
vote
1answer
28 views

How to create BAG on non-wsfc nodes (SQL Server 2016 STANDARD Edition) [closed]

Stumbled over this a few times now, while trying to figure out if a consultant is right or not in his claim. I hope you can shed light on it. Is it, or is it not possible to configure a Basic ...
0
votes
0answers
32 views

Always On group does not sync after failover

We have a SQL Server 2016 cluster with 4 Always On groups. We had an unscheduled failover this morning and one of the groups did not come back online. AG1 had databases that needed to rollback many ...
2
votes
2answers
33 views

Availability Group Listener - Dedicated Server?

Should an AG Listener be set up on it's own server? If so, does it require it's own SQL Server license?
3
votes
1answer
61 views

Any Reason to have Regular Failover Clustering in Azure Cloud?

Is there a reason to have Regular SQL Failover Clustering when database is in Azure Cloud? I understand the benefits having Availability Groups in Azure Cloud for Database-Level protection. People ...
1
vote
1answer
29 views

Azure Failover Clustering vs On Premise Failover Clustering

Azure AlwaysOn FCI Failover Clustering IaaS VMs seem to have a different diagram compared to On-Premise Failover Clustering. Azure has two storage for 2 Vms; Traditional On-Premise contains 1 Shared ...
1
vote
1answer
45 views

Is it a common pattern in having two AlwaysOn Availability Groups for DR and Reporting?

We have a primary OLTP server in the cloud. We intend to have Synchronous Alwayson Availability Group for Disaster Recovery, and another Availability Group AAG for Ad-hoc Query Reporting. This way ...
1
vote
1answer
50 views

Any Reason to have AlwaysOn Failover Clustering in Azure Cloud?

Is there a reason to have AlwaysOn Failover Clustering when a database is in Azure Cloud? I understand the benefits having AlwaysOn Availability Groups in Azure Cloud for Database-Level protection. ...
-1
votes
1answer
50 views

On Always On Availability Group

If a failover happened, how can I delete old backups from the Copy_only folder? Because the Copy only folder exists on the primary server, and backups are running without copy_only. Please resolve my ...
1
vote
2answers
77 views

Odd error when stress testing against an AG

We are running into an odd error when trying to stress test our application that hits an AG. If we point the application directly to the primary server, we don't see any issues with the test. ...
0
votes
0answers
54 views

Unable to remove replica from read-scale availability group in SQL Server 2017

I'm trying to remove a replica from a cluster-less (CLUSTER NONE) availability group in SQL Server 2017, but the operation is failing with this error message: ==================================== ...
1
vote
1answer
87 views

Problems of having DR as Synchronous Commit

In our always on set up we have One Primary and One Secondary as Synchronous Commit and the DR as asynchronous commit. If i want to change the DR to Synchronous Commit will there be any performance ...
0
votes
1answer
51 views

Always On Windows Server Edition

For setting up Always On Availability Groups in SQL Server I know you need to have at least Enterprise Edition for SQL Server 2012, 2014, or 2016. Do you also need the Datacenter or Enterprise ...
0
votes
1answer
27 views

Failover from Primary to Asyn Secondary Replica

In my environment we have 1 Primary 1 Synchornous Secondary and 1 Asynchronous Secondary Replica (Off site). We are planning to do a manual failover to the Asynchronous Secondary Replica (Off site) ...
0
votes
1answer
31 views

Disabling TDE on AlwaysOn database

Which are the correct steps to disable (not removing) TDE on a AlwaysOn database in an availability group? I found steps for removing TDE but that involves removing the availability group and ...
0
votes
0answers
19 views

Change Database Availbility Group on Same Instance

Is it possible to move a database from one availability group to another on the same instance without having to remove the database from the current AG and adding to the new AG on SQL Server 2016 ...
1
vote
1answer
32 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. ...
1
vote
1answer
34 views

HADR wait on servers without HA configured

I am investigating a slow SQL Server 2012 server in a virtualized (VMWare 6.5) environment, and have come across three different waits on the server that are... confusing. These are ...
1
vote
1answer
66 views

SQL Server 2016 SP2 CU2 Availability Group Online but Cluster Offline

Am I missing something basic here? My AVG is online and working, but the Cluster on which it resides is saying offline. I didn't think this is possible, so I either have a basic misunderstanding of ...
2
votes
2answers
130 views

Why SELECT query is waiting on HADR_SYNC_COMMIT?

We have two AlwaysOn nodes in sync commit. Occasionally SELECT queries are waiting on HADR_SYNC_COMMIT. What is the underlying process that causes it? SQL Server 2016 SP1 CU5. client sessions are ...
3
votes
1answer
78 views

sys.dm_hadr_database_replica_states, understanding the different *_time columns

I am going nuts trying to understand the meaning of the different time columns in the sys.dm_hadr_database_replica_states system table. We have a three node Always On Availability Group, where two of ...
0
votes
0answers
162 views

AlwaysOn Error: The target database is participating in an availability group and is currently not accessible for queries

We have a 2 Node AlwaysOn Availability Group in SQL Server 2014 running on Hyper-V (VM). Current setup: SRV01: Role: PRIMARY READABLE SECONDARY: NO SRV02: Role: SECONDARY READABLE SECONDARY: ...
1
vote
0answers
34 views

Tracking log block movement using Extended Events

If we try to track the log block movement between 2 sync commit replicas, why do we need to track the immediate next Log block ID in extended events like hadr_send_harden_lsn_message on secondary ...
0
votes
0answers
127 views

problem with service broker in always-on

one of my clients has a database that works with service broker, last week we tried to move the database to an always on environment without knowing the application uses the service broker. after ...
0
votes
1answer
26 views

Transaction log for standalone DB vs AlwaysON availability Group

I have this question on the generation of transaction logs. For a high transaction active DB what is the ratio of log generation if it is a standalone DB and when it is part of SQL Alwayson ...