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
0
votes
0answers
31 views

SQL Server Availability Groups and CDC

I have a simple SQL Server 2014 Availability Group, one database on a two-node cluster. We need to use CDC to move data to another environment, in my case AWS. This is a super-critical OLTP very large ...
0
votes
1answer
28 views

How do I dictate the order of preferred replica on a Always On cluster?

I have a problem, I look after a massive estate. the newer AO clusters I built myself and they work fine. I have inherited once AO cluster which is often problematic. However, migrating isn't an ...
1
vote
2answers
34 views

SQL server AlwaysOn alternate option for readintent=true

I know applications need certain(newest) version of ODBC drivers to connect an AlwaysOn SQL instance replica and to use the option "applicationIntent= readonly" Now my question is , if some ...
0
votes
0answers
17 views

Availability Group Performance [duplicate]

If I give a user the rights to read on my secondary replica which is in sync (not async) with primary replica, and if that user runs a query with a lot of Select into ##Temptable statements on the ...
0
votes
0answers
23 views

SQL Failover and Failback Script for SRM Recovery Plan(preferably power shell or CMD or Batch script)

OS: Windows Server 2016 Standard SQL: SQL Server 2016 Enterprise SQL Setup: Two Nodes(Primary and Secondary Node) in PDC(Primary Data Center) having Always On Availability Group with Synchronous Mode ...
0
votes
1answer
31 views

TempDB Version Store used by DB STARTUP background process

I have two availability group on the same SQL Server 2012 Instance (synchronous commit). Trace flags 1118 and 1117 are both enabled. TempDB has been growing for about two weeks: it starts from 36GB ...
-1
votes
1answer
34 views

SQL alwayson AG Disaster Recovery

Need advise on below: I have 2 node alwayson AG on Primary Sync mode in the current configuration and fsw is in Primary DC only Actually during Switchover the entire DC 2 node and fsw will be down ...
0
votes
0answers
25 views

SQL Replication Subscriber with AlwaysON setup

We have 2 AlwaysON cluster and we want that to set up as a SQL Replication server which is the Always-on Cluster 1 is the Publisher and Always-on Cluster 2 is the SUBSCRIBER, my question is it ...
0
votes
1answer
29 views

Automatic Failover At Always On High Avability Group SQL SERVER

after i read on documentation https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-clustering-and-always-on-availability-groups-sql-server?view=sql-server-ver15 i ...
0
votes
1answer
32 views

MS SQL Server 2016 - Always On Node-2 Stuck In Resolving State

For testing the Always On structure; we suddenly shut down the machine on which Node-1 was running. So we wanted to see that Node-2 turns primary. However, Node-2 was stuck in resolving mode. 3 ...
0
votes
0answers
12 views

How to setup transaction in partitioned table with Always On?

I am currently creating a project that will use a table that will contain around 40 columns (including varchar (max)) and will be partitioned. About 200-250 records per second are to be inserted into ...
0
votes
0answers
12 views

Copy DWH for reports

I have a heavy load DWH server. I wish to create a copy of that server for report purposes (Cognos reports to be exact). In today's situation I have many reports and can cause locks on other processes,...
0
votes
1answer
59 views

Cannot connect after Availability Group automatic failover

We have a SQL Server setup with Availability Groups, in the cluster we have four nodes, two in one datacenter and two in another datacenter. There are four AGs on these cluster nodes, each primarily ...
1
vote
1answer
25 views

Why did full backup file size grow after data movement for Availability Group replica was suspended?

Found an error log informing that data movement to a secondary availability replica was suspended. I'm not sure whether the cause of the suspension is relevant to answering my question, but the cause ...
0
votes
1answer
20 views

How to do failover between AG setup for FCI-FCI servers

Hope I can portrait my question well because i really get confused with AG's :) We have the setup like below- Windows cluster WKCLU01 with 4 nodes (Node1,2,3,4) and a file share witness in DC3, total ...
-1
votes
1answer
34 views

Power shell script for failover from primary to DR

SQL server version is SQL Version 2016 Current setup is two nodes in primary having always on availability group. Adding a 3rd node which is DR(Disaster and Recover). Does anyone knows a power shell ...
1
vote
2answers
224 views

AlwaysOn availability group DR failover with Powershell

I am setting up AlwaysOn availability group for Disaster Recovery (DR) in the test environment. For AG1, node1 and node2 (synchronous commit/automatic failover) are in DC1; node 3 and node 4 (...
0
votes
1answer
72 views

Good practices for patching SQL nodes in High Availability solution [duplicate]

Our database structure utilizes two roles in the Window Failover Cluster Manager. One is for FCI - switching between the active/passive node in the Primary Data center. One is for SQL Availability ...
0
votes
0answers
30 views

Combine multiple alerts in to single alert

I'm trying to find a way to generate a single alert by combining multiple alerts for AlwaysOn setup (for example error codes- 1480 , 19406) which gets logged in SQLERRORLOG file during planned or ...
0
votes
0answers
33 views

Dumps created on on secondary instance of always on

We have an two SQL server 2017 instances with CU20 that are configured in a read scale always on with Windows 2016 OS machines. The database in these instances uses: In memory optimized tables, ...
19
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 ...
0
votes
0answers
59 views

Troubleshooting an automatic failover in 2 node multi subnet AG

I am trying to troubleshoot an automatic fail over that happened on our below infra- both of below running on HYPERV VMs Windows version 2019 SQL1 - 8 cores 64 GB RAM - No load running (Primary) ...
0
votes
1answer
45 views

SQL server replication (publisher, subscriber, distributor) on top of AlwaysOn

I am assessing the migration from SQL 2008R2 to SQL 2017 AlwaysOn where we have replication (publisher, subscriber, distributor) setup on SQL2008R2. There are various restrictions if one wants to ...
0
votes
1answer
43 views

AG listener with MultiSubnetfailover True on Named Instance

I have an AG configuration with databases on a Named instance. I have enabled multisubnetfailover =true and registerallprovidersip =1 I just saw some blurb on the web that these settings are not ...
0
votes
0answers
27 views

Removing a Windows Server Cluster Node and Then Adding Back - Do I need to remove the Availability Group replica?

We had a secondary server bite the dust... we were remarkably able to resurrect it. Everything is great and intact... except for the windows server cluster configuration. We basically need to evict it ...
0
votes
2answers
32 views

SQL server alwayson 3 Node Primary Down

I am using three node alwayson Availability Group SQL 2012 Enterprise Edition. 2 Node in DC with File Share Witness quorum also in DC and third node to be setup in DR I need to perform a DR switchover ...
2
votes
1answer
78 views

How to implement High Availability for all nodes in a sharded database?

I'm doing some research into implementing sharding for one of my large (~2 TB) production databases. Currently the database has HADR via a multi-replica AlwaysOn Availability Group. Since sharding a ...
0
votes
1answer
82 views

SQL Server 2016 - Availability Groups - How To Copy Permissions To Secondary Node [duplicate]

SQL Server 2016 SSMS v18.4 I want to create the same logins and give the same privileges on both node. In primary node I have no problem about this operation. But when I create the same login on ...
1
vote
1answer
43 views

Setting up AG listeners

I am relatively knew to MSSQL so please bare with me. My infrastructure is currently in AWS. I have 3 servers running MSSQL. I have a primary replica and two secondary replicas. All three servers are ...
0
votes
0answers
26 views

SSRS Encryption key error after failover

I recently encountered an error after a planned failover of an AG to the secondary node after patching it. I am on SQL 2016 enterprise. we have the reportserver in the AG, but after failover I got ...
0
votes
1answer
65 views

Secondary replica database file location in Availability Group

I inherit a setup of two nodes (primary & secondary replica) Availability Group. On my secondary replica node, the SQL server instance default files are stored at: My default primary replica node ...
0
votes
0answers
36 views

Cannot not connect to Availability Group Listener

I have a HADR cluster running on Windows 2012 R2 and SQL Server 2016 with Availability Groups. The services were running under a high level domain account. Due to internal audits, I have been asked to ...
0
votes
1answer
158 views

Help with Replication from an Always On Availability Group

I have a two node SQL Server 2016 Multi subnet Availability group. I am trying to setup transnational replication from the AG to a standalone instance for reporting, which is also acting as a remote ...
1
vote
1answer
82 views

Implement SQL Server 2012 AlwaysOn

I have two SQL Server instances running on Windows Server, SQL Server cluster with 2 nodes with shared storage (Active, Active type). The SQL Server version running is SQL Server 2012 Enterprise ...
0
votes
1answer
169 views

SQL Server 2019 Always On using Distributed Network Name

We're running a couple SQL servers in Azure that are set up with an Always On availability group and Windows Failover Clustering. The servers are Windows 2019 and we're running SQL Server 2019. When ...
0
votes
0answers
271 views

SQL Server Error - The wait operation timed out (provider: TCP Provider, error: 0 - The wait operation timed out.) [duplicate]

I'm running a couple web applications on the same set of servers. In my logs across a number of different queries in both apps (as well as calls to the ASPState session state database) I see errors ...
1
vote
1answer
65 views

Need advice on how to migrate from existing database mirroring to availability groups

Need some practical/general guidance on moving from database mirroring to AOAG Current setup - 2 standalone servers with database mirroring configured as async. Server A: Windows 2016 running SQL ...
0
votes
0answers
12 views

I have a question : coming from my latest interview: On Always on readable secondary

I was asked that if a user has to be connected to secondary replica of always on 2 node environment how will we access to read only requests to secondary and we don't want to let the user access ...
2
votes
1answer
70 views

How to get certificate expiration date for certificate in mirroring endpoint in SQL Server 2016 Always On Availability Group?

I created a domain-independent 2-node SQL Server 2016 Always On Availability Group using the Microsoft instructions: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/...
1
vote
2answers
65 views

What are the benefits of using Manual Failover Mode in an AlwaysOn Availability Group instance as opposed to Automatic Failover Mode?

Why wouldn't I want to use Automatic Failover as the Failover Mode for an AlwaysOn Availabiltiy Group instance? Are there pros for Manual Failover Mode that don't exist for Automatic Failover Mode?
0
votes
1answer
114 views

What is the max network latency between synchronous-commit AlwaysOn Availability Groups [closed]

Is there a maximum supported network latency (ms) between two synchronous AlwaysOn replica? I need to give this information to network administrators for configuring the link between the two nodes of ...
0
votes
1answer
96 views

SQL Server Management Studio cannot Connect to Always On Availability Listener

I am trying to connect to the AG Listener in SSMS. I choose [Connect] Then [Database Engine] I type in the name of the listener I created. SQLListener [Windows Authentication] Click [Connect] ...
0
votes
1answer
40 views

SQL Server AlwaysOn Availability Group - Creating an Index Online in SSMS

I am running a 2-node SQL Server 2016 AlwaysOn Availability Group. I would like to create a non-clustered index online in SSMS for one of the availability databases in the AlwaysOn Availability Group....
2
votes
2answers
158 views

SQL Server 2019 Core - Always on Availability Group - Availability replica is disconnected

all. (I have read through all the other topics related to this one, and I cannot find anything to is similar to this issue, unless there is one hidden that I have missed.) OK. SQL Server Always On ...
0
votes
2answers
30 views

How do you configure a 4-node AlwaysOn cluster to have two listeners?

Looking to build a 4-node (servers A, B, C & D) AlwaysOn Windows 2016 failover cluster with 2 nodes in the primary datacenter for HA, and 2 other replica nodes in the DR datacenter for disaster/...
0
votes
1answer
44 views

High Availability with Transactional Replication

We are using SQL 2017 Enterprise and setting up a new DR site. One of our servers (Server B) is used for reporting and has many databases, one of which has tables populated through Transactional ...
0
votes
1answer
16 views

Questions regarding removing a secondary node from an availability group

I have a 2 node AG in a non-prod environment and we are looking to shut down the secondary node for cost savings, leaving this with just the primary node. I have some questions based on this process, ...
0
votes
1answer
67 views

How to enable email notification for failover in SQL Server

We have a few servers in a High Availability group and need to configure email notification for any failover that happens on the server.
0
votes
0answers
41 views

Why is my Availability Group in an unhealthy state and showing synchronizing?

I have an active directory and 3 SQL Server nodes in an availability group. The primary and secondary node are on different subnet to active directory: 104.92.173.x The third node which is in async ...
1
vote
1answer
64 views

SQL Server Availability Group - Listener configuration problem

Platforms: Windows Server 2016 SQL Server 2016 SSMS 2018 Problem: Create failed for Availability Group listener 'lsn'. None of the IP addresses configured for the availability group ...

1
2
3 4 5
26