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
47 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
35 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
22 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
16 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
17 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
66 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
55 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
35 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
19 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
44 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
26 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
40 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 ...
0
votes
1answer
54 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
37 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
51 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
58 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
10 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
35 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
34 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
44 views

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

Does exists any best practice/guide lines of the maximum network latency (ms) between two synchronous AlwaysOn replica? I need to give this information to network administrators for configuring the ...
0
votes
1answer
23 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
27 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
1answer
61 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
22 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
30 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
15 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
35 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
36 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
39 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 ...
0
votes
1answer
32 views

How to find the last data backup on secondary replica?

To determine when database has been backed up the last time usually is enough to look into backup tables in msdb database. However, it doesn't work well with Availability Groups - after the failover ...
0
votes
1answer
10 views

Is it possible to setup multiple RW Replicas for a database in an AlwaysOn Availability Group?

Can I have multiple RW replicas of an AlwaysOn Availability Group database across different servers? (I.e. two-way replication so that multiple servers can write to the same DB.)
0
votes
1answer
35 views

Distributed Always-On without Domain

We have a requirement to configure a distributed availability group from Data center1 to Data Center2 using SQL Server 2017 Enterprise Edition. DC1 (two nodes WSFC setup): SQLDB1, SQLDB2 DC2 (two ...
0
votes
2answers
53 views

In SQL Server Always On 2019 environment should SSRS/SSIS be on a separate server?

I am looking for some advice in setting up a new SQL Server 2019 Always On instance. We have a 2 server cluster right now, moving to Always On with a similar two virtual server setup. I want to ...
1
vote
1answer
29 views

64 Availability groups max on SQL STD : Failure when attempting to create a new NetBIOS interface while bringing a resource online

I was patching our DB Servers this morning and encountered a really strange error when failing over some Availability Groups : In our environment, we are running a somewhat unconventional setup, but ...
0
votes
2answers
34 views

Backup Files growing quickly on SQL Server 2016 Always On Cluster

I have a two-node SQL Server 2016 Always On Cluster. I also have a job that updates about 200 records in one table every 3 minutes. Even when there are no updates, it does change a TimestampDt field ...
0
votes
1answer
22 views

Get availability group and listener of the current DB (dynamic SQL)

I have hundreds of DB on an instance and I want to remove the DB from their AG (each DB is in a separate AG), remove the listener and then delete that AG. I have written the script below to automate ...
0
votes
2answers
63 views

How to find the current Log Sequence Number (LSN) of a database that is in Restoring state?

I removed one of my databases from the availability group on my secondary replica in my AlwaysOn group for an experiment. I want to add it back, but I cannot do so because the databases needs to be ...
3
votes
2answers
72 views

Should tempdb be larger on a secondary replica?

I am working on SQL Server 2012 with Availability Groups. On the secondary replica, the size of the tempdb datafile is roughly double that of the primary. I am struggling to find information on ...
0
votes
0answers
15 views

What are the best practises to configure SSIS catalog and jobs on AlwaysOn [duplicate]

I have a 2 node Always On setup with shared nothing storage. If I want to run SSIS packages/jobs on any of the nodes(whichever is primary) in a seamless manner what is the best course of action?This ...
0
votes
1answer
24 views

How to bring all replicas on a SQL availability group to an earlier state / point in time?

I have a SQL Always on availability group, with 4 replicas. One of them is the primary. Lets say someone accidentally drops a table on the primary. This transaction would go to all of the secondary ...
0
votes
1answer
43 views

Always-On secondary Replica's Read Committed Snapshot Isolation

When you use Readable Secondary Replicas in an SQL Server Availability Group, SQL Server will also use internally Read Committed Snapshot Isolation. When fail-over occurs to a readable secondary ...
0
votes
1answer
74 views

How to Set Recovery Mode in SQL Server Always On Availability Group without downtime?

I am running a SQL Server 2016 2-node Always On Availability Group, and I want to do some backups. I normally run this query to do my backup: -- To permit log backups, before the full database ...
0
votes
0answers
22 views

Cumulative Update 3 & 4 Caused Synchronous Commit Failures in Availability Groups

So this week I was patching our SQL Servers that host our availability groups. Never had an issue with them in the past, fairly standard process. I have two servers (call them SQL1 and SQL2) in the ...
0
votes
2answers
38 views

How to change/add to schema in a SQL Server Always On Availability Group?

I have a 2-node Always On Availability group cluster in SQL Server 2016. I am wondering how should I go about adding a new table, or modifying the schema of an existing table in an Always On ...
1
vote
0answers
36 views

How to install PolyBase in an availability groups architecture for high availability?

Background: I’m designing a 2 node SQL 2019 availability groups installation hosted in VMs. We would have limited (single node) usage of PolyBase. The Microsoft Docs don’t reference installation for ...
0
votes
2answers
23 views

Always-on Avaialbility Group Secondary replica NOT identical to Primay

Can I have Always-on availability group primary node not identical to secondary? example: Primary Replica RAM 64, Cores 8 Secondary Replica RAM 256, Cores 32 As I estimate read-only queries to be ...
0
votes
1answer
18 views

Removing AG and clustering - make nodes stand-alone

I am wanting to make a 2-node Always On cluster no longer a cluster (make them (2) stand-alone machines). I am trying to upgrade Sql and the wizard keeps getting stopped before it does it checks ...
1
vote
1answer
18 views

ALTER USER statement modifies users on off-air node

I had previously asked a question about Orphaned Users here and was directed to this link in an effort to fix it, but this didn't seem to answer my questions, so I am re-asking after some further ...
0
votes
1answer
28 views

backup history using availability groups

When using availability groups it is hard to get backup history in situation of failover to secondary node. It would good to have msdb backup set data to be in-sync with all the servers involved in ...
0
votes
2answers
34 views

SQL Server AlwaysOn cluster having issues seeding large availability database creating strange behavior

I set up a 2-node SQL Server 2016 AlwaysOn Availability Group cluster (1 primary, 1 secondary). The underlying hardware is fairly powerful; 4 CPUs, 16 GB of RAM and local-attached SSD disk. Then I ...

1
2 3 4 5
24