Stack Exchange Network

Stack Exchange network consists of 175 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

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.

0
votes
0answers
19 views

Consolidating SQL Server databases into a New Server vs. Existing Server

Currently I am working on a project that requires me to consolidate 3 servers into 1 server, so I am trying to find the best method at the moment. Right now my options are: Consolidate the 3 servers ...
1
vote
1answer
27 views

DBCC check on Always On Secondary Replica

What is the best way to schedule DBCC check on all the nodes in Always On availability group. Can we just schedule the same job on all the nodes at the same time including all the secondaries. In my ...
0
votes
0answers
36 views

Anti Virus triggered some alarms on Always on Availability Group [on hold]

We have a 4-Node Always On Availability Group and have noticed Kaspersky triggered some alarms on one of the Replicas. Today I was doing some failover testing but not sure what this is all about: ...
1
vote
1answer
40 views

Multi-Subnet AlwaysOn: Is Routing and Remote Access always required?

Context I'm setting up a 2-node AlwaysOn Availability Group (SQL Server 2017 Enterprise) with the following setup: Existing DC+DNS (One NIC: 10.20.11.1/255.255.254.0) New AG-Node1 (One NIC: 10.20....
8
votes
0answers
90 views

Always On Availability groups resolving state after failover - Remote harden of transaction 'GhostCleanupTask' failed [on hold]

My explanation will be opinion based as it is based on my findings. If more information is needed, I am always happy to add it. The setup We have 4 SQL Server 2016 Instances, and all 4 underlying ...
2
votes
2answers
60 views

SQL query to check if secondary database is removed from availability group

I have an AG with one primary replica and one secondary replica. I have created a script that queries transaction log backups from these database replicas and restores them to a target database. ...
1
vote
1answer
23 views

Listener IP not changing after failover

We have a multi-subnet AlwaysOn availability group. There are 2 subnets and those are listed on the cluster and also the listener. Whenever I fail over and ping by the listener name it still shows ...
1
vote
1answer
54 views

Clarifications on Transparent Data Encryption

This is the first time we are applying TDE on production databases that are critical and I have the questions below. While TDE is in progress: Can we still access the application that is working on ...
0
votes
3answers
96 views

Creating Windows login/user for Mixed Always on Environment+ Instance without Always ON

How can i use by making changes to below script to run in the environments with Always ON enabled/and not enbaled. Below seems to be working after some really helpful suggestions from Kin. However ...
0
votes
0answers
51 views

High Availability Group configuration failure [on hold]

Trying to configure a High Availability Group using SQL Server 2017 without clustering, but it's failing when I try to add the secondary replica with the following error message: Failed to join ...
1
vote
1answer
36 views

Suggestions - Enabling EFS on FileStream Data for SQL Server

If anyone has already tried enabling EFS on FileStream data for multiple databases around 500 GB+, please share your thoughts on feasibility or issues / considerations we should keep in mind. We are ...
0
votes
0answers
26 views

Enabling EFS on FileStream Folders

Please help me on below: Below is my Environment: Windows Server 2012 R2 Standard SQL Server 2014 SP2 GDR Availability Groups 2014 with 3 Node (2 Sync and 1 BCP async) I have 7 Databases with ...
0
votes
0answers
40 views

sp_flush_commit_table_on_demand procedure running even on AG

I am not sure if this is a bug or not but i am seeing calls on sp_flush_commit_table_on_demand when running async AG. I wouldnt have a problem with that unless i wanted to capture last commit time ...
4
votes
1answer
33 views

How do I reclaim space in filestream data files for database in a SQL Server Availability Groups

I have a SQL 2016 Ent. Edition AG with 3 nodes. We have a database in the AG with 5 filestream tables. Each table is in it's own filestream data file. Today I fixed a bug where all of the files were ...
0
votes
2answers
57 views

Always On - Restore a node from a Veeam backup

I have a cluster with 2 nodes, and Veeam is backing up databases every day and logs every 5 minutes. This works great and I can easily recover a single database to any point in time. Then I was ...
3
votes
1answer
67 views

Availability Group Endpoints Windows Authentication Error

I have a two-node SQL Server 2016 Standard Always-On environment and when I create a new availability group, I receive the error message below: The Endpoints tab lists at least one endpoint that ...
2
votes
2answers
80 views

Do we really need a file share witness? [duplicate]

We have an Availability Group configured. Total number of nodes in the cluster is 3 (odd). All are in same subnet. Also we do have file share witness. Everything is working fine for now. My question ...
1
vote
2answers
56 views

Load balancing Availability groups with SQL Server Standard

So I have the current scenario, and it looks to be working fantastically, but I just want to get some input on the configuration. Is it smart? Are there any issues I am not thinking of? We have SQL ...
1
vote
2answers
38 views

Reverse SQL Server Listener?

The company that I work for runs our DMS database in an HA group with Always On. So there is a listener that we connect any applications to. We also have SSIS on the secondary replica of the HA ...
2
votes
2answers
36 views

Storage pool configuration for SQL Server

We are in the middle of building a new SQL Server 2017 always-on cluster in Azure VM. The data disk layout of VM are as follows TempDb : 2 Azure Disk Read Cache TempDBLog : 1 ...
2
votes
1answer
84 views

Availability Group cluster memory issue after network issues. How to dump HADR Log Block Msg Pool?

We have a four node Availability Group, two nodes in one site, two nodes off site in another data center. I have noticed after every WAN issue where the WAN connection is flapping, and the off site ...
1
vote
1answer
42 views

How can I make SQL Server Agent job history available after failing over an AG?

I am implementing a new HA/DR architecture using Always On AGs for use in both a HA a DR scenario. I have mastered a mechanism to ensure that jobs are disabled on the secondary replica and enabled ...
4
votes
1answer
93 views

Backups with Split Availability Groups

I have been a DBA for about 2 years now, and there are some subtleties that I still do not understand about AlwaysOn availability groups. First off, they have been nothing but trouble as far as I can ...
0
votes
1answer
35 views

Backup Structured Data and Unstructured Data seperately in SQL Server - Is it Possible?

Can I get some inputs on below: SQL Server Version : 2014 SP2 GDR with AOAG environment. Is it possible to backup Structured Data & Unstructured Data (FileStream) separately in SQL Server ? For ...
2
votes
1answer
45 views

Why am I unable to create this Availability Group? (error 35220)

I was attempting to create an Availability Group on a SQL Server 2017 instance and was running into error 35220: Could not process the operation. Always On Availability Groups replica manager is ...
1
vote
1answer
34 views

Sync Login PW across Multiple Instances

When attempting to failover many logins fail due to nonmatching passwords on both instances. Is there a way for me to replicate passwords to other servers for all logins which passwords do not match.
6
votes
3answers
807 views

Do SQL Servers in a cluster need to have the same memory?

I have two servers in always on failover configuration with windows clustering underneath. Do both servers need to have the same memory? One of our DBs that uses a lot of memory is not in the AG, so ...
3
votes
2answers
156 views

Why SQL Server Service Account connects to the DAC?

We started getting Alerts: Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated ...
1
vote
0answers
24 views

Availability Group and Transfer SQL Server Objects SSIS task

We have two db instances on Always On Availability Group. And I need to transfer data from selection of tables to the primary DB. I have created SQL Agent Job for SSIS Transfer SQL Server Objects ...
3
votes
2answers
63 views

function to check whether the current server is involved in an availability group and if so, whether it is the primary

I used to have 2 servers on an availability group, but there was a problem with one of them servers involved,while patching, and to make a long story short I removed all the databases from the ...
0
votes
1answer
42 views

AlwaysOn Availability Groups on Secondary Replica

We have a Primary Server A with a Database. It has AlwaysOn ReadOnly Asynchronous which moves data to Server B. Out of curiosity, is it possible to place Server B on AlwaysOn and move it to another ...
0
votes
1answer
30 views

Azure automatic fail-over vs always on availability groups clarification

I am trying to understand automatic failover vs always on availability groups in Azure hybrid and geo-region situations: Is it possible to have on-premises SQL databases, VM SQL databases and Azure ...
2
votes
1answer
32 views

Migrating a SQL Server 2014 AG to a new 2017 AG

Our current production server runs on SQL Server 2014 AlwaysOn AG. We are planning to upgrade it to SQL Server 2017 AlwaysOn. Due to the configuration issues with the present cluster, we can not use ...
0
votes
0answers
70 views

SQL listener connection fluctuates between primary and Secondary

We have SQL Server 2017 + CU11 Always On set up with one primary and a secondary replica. Suddenly we started facing issues where SQL Server AG listener connection started fluctuating between primary ...
0
votes
1answer
19 views

Domain Controller on Multi Subnet Always on Availability Group

This is will be my first big project. We have 4 Replica Always on availability group with 3 Replica on site and another replica offsite (different subnet) the File share witness will be placed on ...
1
vote
1answer
44 views

Redo lag on AG readable secondaries: is there a way to overcome it?

I have a quite busy (1000+ batch requests per second, 350+ workers on average) system built on sql server 2016. It's an availability group with 3 synchronous replicas. I'd like to redirect read ...
0
votes
1answer
34 views

What happens when the same application from different server tries to connect to the always on primary server

In our environment we have an always on availability group and the reporting server is acting as a secondary replica with Synchronous Mode, manual failover. XSLT report editor that is a vendor ...
0
votes
1answer
30 views

Listener Port 1433 for Always on Availability Group

In our Availability group I have a reporting server where report writers will connect directly and also crystal reports will be used, If the listener port is 1433 and the clients also gets connected ...
1
vote
1answer
202 views

Unable to join secondary replica to the availability group by SQL Server error 47106

We were trying to create availability group with 3 synchronous replicas, where we couldn't give the names specified in /etc/hosts as node specifiers in the create availability group in the primary ...
0
votes
1answer
17 views

Read only replica part of always on availability group for Reporting

As a DBA I would like to include a read only replica into the always on availability group. XSLT / Crystal Reports will only be used. Please see below 1) Primary Replica (Same Data center) ...
0
votes
1answer
21 views

Does the Always Encrypted certificates get copied over in availability groups?

We have a column set up with Always Encrypted, and we are adding it to an AG. We'd like for the encryption to keep working if/when we have to fail over to a secondary server. Will the Availability ...
1
vote
1answer
55 views

SQL Server 2016 AlwaysON

I'm looking to setup a two physical node WFC cluster (server 2016) which has no shared disks. Performing standalone install of SQL server 2016 Standard on each of the nodes and then configuring a BAG ...
0
votes
1answer
46 views

Why is my DR AG database in the “recovery pending” state?

I am trying to build an architecture where we get automatic failover for both HA and DR for our DBs using SQL Server 2016 AlwaysOn Availability Groups. When I tested this using only one Availability ...
0
votes
1answer
53 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
42 views

SQL AlwaysON. keeping Agent Job in sync, but with same JobID?

Is there a way to specify Job ID when creating a job? Or is there a way to update the Job ID of a specific Agent Job that already exists? I have a software from a vendor that uses the Job ID to ...
0
votes
0answers
43 views

SQL Server error 19456 when adding the listener

I have three replica same subnet both has a Private and public network. I was able to create the cluster and create the availability group . When I try to add the listener I get the below error ...
5
votes
1answer
86 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 ...
3
votes
3answers
99 views

Why is the Read-Only Routing not functioning in this Availability Group?

First off, my apologies if this has already been answered elsewhere. I have not been able to find any articles or threads describing my exact situation, but no one's Google-fu is perfect. With that ...
0
votes
1answer
37 views

Automatic fail over in Always on availability group with 3 Node in synchronous mode and 1 Node in Asynchronous Mode

I am laying out a plan where there will be 3 Node on site with automatic fail over with synchronous mode and 1 remote replica with asynchronous mode. My question is if the primary goes down which node ...
0
votes
1answer
21 views

Difference between Client Access Name in SQL server Fail over Cluster and Always on availability group

We are migrating from SQL Server Fail over Instance to Always On Availability group. The current network name is ABC\ABCTEST where ABC is the SQL server Virtual Name and ABCTEST is the named instance ...