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

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.

2
votes
2answers
44 views

do we really need file share witness

We have always on 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 is do we ...
1
vote
2answers
40 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
33 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
29 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 ...
-1
votes
0answers
16 views

AlwaysOn Recovering Databases [on hold]

We rebooted Last Saturday, the AO groups failed over to DR except 1. When DR was rebooted, everything went into 'Recovering' mode on both nodes. Nothing is standing out to me to find the root cause. I ...
2
votes
1answer
68 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
31 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
88 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
33 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
28 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
26 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.
2
votes
0answers
58 views

Blue/Green via AlwaysOn [closed]

Full disclosure, definitely not a DBA. However, I've put together the following solution design to try to achieve a Blue/Green SQL Deployment. See for Definition of Blue/Green: https://martinfowler....
6
votes
3answers
801 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 ...
2
votes
2answers
109 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
23 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 ...
0
votes
0answers
42 views

Sql Server 2017: Transaction Log rapid growth on primary replica [Always on availability]

We have an issue in production where our transaction log is growing exponentially; eventually we might run out of space. We have 3 replicas. Recovery model is Full. Node A [primary replica] Node B [...
3
votes
2answers
61 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
40 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
29 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
30 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
27 views

Upgrading SQL Server 2014 AlwaysOn cluster to 2017 [closed]

Our current production server runs on SQL Server 2014 AlwaysOn cluster. We are planning to upgrade it to SQL Server 2017 AlwaysOn cluster. As part of this project, we will be building a new cluster ...
0
votes
0answers
64 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
17 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
39 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
33 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
23 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
92 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
13 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
15 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
51 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
43 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
44 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
38 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
37 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
81 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
62 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
31 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
19 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 ...
1
vote
1answer
43 views

is there a way (clean or unclean) to eliminate a filestream data file on a production SQL Server database?

Is there a way (clean or unclean) to eliminate a filestream data file on a production database? I forced a garbage collection on an empty filestream : sp_filestream_force_garbage_collection @dbname =...
1
vote
0answers
30 views

Adding Databases to Availability Group

I'm attempting to add databases to an existing availability group using automated seeding but I randomly receive this error: "Seeding Check Message Timeout". I'm pulling this error message from sys....
0
votes
1answer
21 views

Don't show local databases in SQL Always-On AG

We're installing a SQL Server Always On AG solution(SQL Server 2016) in a test-enviroment at our organisation. When testing the features we notice that all databases from a node are listed when we ...
0
votes
0answers
28 views

Availability Group being PARTIALLY_HEALTHY

I'm having a strange issue with a SQL server hosting 11 Availabilty Groups. All those groups have 1 or 2 databases in it and they are synchronous commit with the automatic failover. I noticed ...
0
votes
0answers
23 views

Moving the FileStream Data Drive to New Location and enabling EFS

Can any of the MS FileStream experts help me on below: I do have the 5 App DBs with FileStream enabled. Current the size of **C: for FileStream Blob data is 5 TB** All the DBs have below drive ...
1
vote
0answers
58 views

Configure Always On for publisher

We have a pull transaction replication.Publisher runs MSSQL 2016. Distributer runs on separate server with MSSQL 2016. Is it possible to define Always On over publisher and another new server, so ...
6
votes
1answer
202 views

Single Server (Replica) Availability Group

First time caller, long time listener. I have a situation where I need to stand up a single server Availability Group for around 2 months (i.e. move DBs to a new server, rebuild the current server, ...
2
votes
1answer
36 views

CPU cost in SQL Availability Group for synchronous and asynchronous DB's

We are looking at setting up a new SQL Always On Availability Group environment for SQL Server. We are wondering if there's a difference in CPU usage of a synchronous or an asynchronous node? Also ...
1
vote
2answers
65 views

Cluster Configuration

I’m looking at some of the SQL servers in a (new to me) SQL estate. The servers are configured for AOAGs; there are two nodes, both are part of the WSFC. However, if I look at the SQL Server advanced ...
0
votes
1answer
15 views

WildCard to Skip Synchronized DB

I am running a security RAG on reporting server that updates our security and put database to read_only mode. I am using wildcard to skip system and some other databases, but what can I use to skip ...
2
votes
2answers
48 views

availability groups - checking for primary server - cannot call a procedure with parameters

In my servers that are involved in alwayson availability groups if there are jobs running, inside the jobs I always test whether the current server is the primary in the availability group. So the ...
0
votes
1answer
67 views

Redo thread pending work

I've got a real tricky one here, there so much conflicting evidence it's difficult to write up the question! Here goes.. I've got a 2 node AOAG: node 1 is currently the secondary replica (but I ...