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
6
votes
3answers
823 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
269 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
28 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
101 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
50 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
45 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
45 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
207 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
23 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 ...
2
votes
1answer
244 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
38 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
176 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 ...
2
votes
1answer
2k 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
93 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
33 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
82 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
166 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
112 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
59 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
158 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
207 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
832 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
192 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
109 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
73 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
107 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
27 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
121 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
82 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
67 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
253 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
45 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
77 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
771 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 ...
1
vote
1answer
736 views

Failover Cluster Owners

In Windows Server Failover Cluster Manager I can see "possible owners" under "Other Resource" and also under "Server Name". I have a 3 node AlwaysOn Availability Group with 2 servers in synchronous ...
1
vote
1answer
477 views

Disabling IPV6 on Always on availability group

In our environment we only have one network adapters for the Replicas and IPV4 is being used. By default the IPV6 is also enabled. My question is will there be any problems if I unchecked IPV6 from ...
1
vote
1answer
170 views

Syncing Server Objects across AG nodes

This is my first question, hope I do it right! I am working on creating a process that will sync server objects between our AG nodes. We are using SQL Server 2016 with 4 nodes. Two nodes in one data ...
6
votes
1answer
365 views

DBCC CHECKDB on Always On databases

Do we really need to run DBCC checkdb on a database configured with Always On synchronous updates? I believe the automatic page repair mechanism should identify the corruption and repair it ...
1
vote
0answers
846 views

dbcc failing with Cannot continue the execution because… alwayson having a change of state event for each db

I got a job DatabaseIntegrityCheck - USER_DATABASES, failing with the following error: Date and time: 2018-10-29 02:34:13 Command: DBCC CHECKDB ([one of my databases]) WITH NO_INFOMSGS, ...
1
vote
1answer
46 views

Can two primary AlwaysOn servers be each other's secondary--for completely seperate sets of databases?

Can two primary AlwaysOn servers be each other's secondary? We have a situation where our primary disaster recovery site it also a valid hospital satellite (SQLB) to our main campus hospital (SQLA) ...
1
vote
1answer
60 views

AG of 2 replicas on 2 nodes in WSFC : is it possible to add 3rd replica to AG and NOT add 3rd machine WSFC?

Please help to to research the possibility of below: We have availability group that consists of two replicas: replica 1: SQL Server 2017 on Windows Server 2016 replica 2: SQL Server 2017 on ...
0
votes
1answer
57 views

Availability groups

I'm trying to learn and implement availability groups clustering in Azure. How secondary replica knows that the primary replica is down? In FCI, heartbeat links are there right? like that what we have ...
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 ...
1
vote
1answer
273 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
402 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
242 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
152 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 ...
3
votes
1answer
2k 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 ...
3
votes
1answer
7k 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 ...