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
1answer
68 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
66 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
34 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
12 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
42 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
119 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
30 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
41 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
432 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
83 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
26 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
83 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
292 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
23 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
107 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
54 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
20 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
45 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
56 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 ...
0
votes
1answer
79 views

Using listener_name in 4 part naming convention gives sys.servers error

App uses cross db connections, the dbs are in different AG's, so I need them to use 4 part naming convention - in the place of servername would be listener name. But when I run a simple query like: ...
1
vote
0answers
120 views

SSIS - Setup OLEDB Connection to SQL Server Always On Listener

I am trying to connect to SQL Server 2017 Always On from an SSIS package (built in SSDT 2017). I am using an OLEDB Connection Manager. 'Application Intent' is set to READWRITE. However, where do I ...
0
votes
1answer
71 views

My availability group doesn't work correctly

I recently created an "availability group" on sql server, I created my Listener, all is well the high availability group appears well in the roles of my cluster. However I encounter a small problem, ...
0
votes
0answers
179 views

SQL Distributed Availability Group between SQL 2016 and SQL 2019 doesn't seed

Appologies for a long first post :-) I'm planning on implementing a distributed availability group between an existing SQL2016 AG and a new SQL2019 AG, and am validating the design in a LAB prior to ...
0
votes
1answer
44 views

How to check backup against SQL AG by Get-DbaLastBackup?

I use the following PowerShell script to check if the last full backup successful daily: $SQL_Server_list = Get-Content D:\Tools\DBA\SQLPRD_FINAL.txt $SQL_Server_list | Get-DbaLastBackup | Where-...
0
votes
2answers
302 views

How to debug a growing Redo Queue Size in the Availability Group Dashboard?

The Redo Queue Size has been growing pretty big over the last couple of hours on my secondary replica's server (SQL16). There's nothing long running or heavy running that I've found over the last hour ...
0
votes
2answers
40 views

SQL Server Availability Group configuration steps

I was preparing for a testing and got into question: In which order should you perform the actions (5 total actions) to create availability group ? I arranged actions as below: Launch the ...
0
votes
1answer
31 views

Add AWS EC2 instance to existing Availability Group

I currently have an AG with 2 replicas both running SQL Server 2016. These are both located on premise. I was looking to add a third node using a AWS EC2 instance and asynchronous replication. ...
0
votes
1answer
54 views

Why distributed AG is preferred to normal AG for cross datacenter failover scenario?

I have two node WSFC and currently I'm working on DR solution with availability group.I already read microsoft doc about distributed AG.So my question is what are benefits of using distributed AG ...
2
votes
1answer
56 views

What are the main principles of SQL Server AGs depending on WSFC?

We have WSFC with 4 nodes + a file share witness. Only nodes 1-3 have SQL server on it with Availability Groups (AGs), and node 4 is shut down gracefully. Node 1 has the primary AAG server. Now,...
1
vote
6answers
68 views

Are availability groups required for AlwaysOn

Beginning a project to create an HA SQL server environment. We are limited to using SQL Server Standard edition, currently 2016. From what I have found, AO(AlwaysOn) for standard edition limits you ...
0
votes
1answer
92 views

Why do I need witness in WSFC with two nodes?

I have Windows Server 2019 and SQL Server 2019 running in both nodes of my cluster. In my case everything is working fine without witness. I need Windows Server Failover Clustering (WSFC) for ...
0
votes
2answers
63 views

SQL server always ON patching [duplicate]

Always on(SQL server 2016) is configured between four standalone servers Server1,Server2,Server3,Server4. These servers contains one availability Group(AG01) which contains 3 databases. How to apply ...
1
vote
1answer
42 views

How to control reindexing that results in high log send queue

During reindexing using Ola's IndexOptimize stored procedure, the log send queues for remote asynchronous replicas can grow quite large if there are a lot of large index defrags, requiring gigantic ...
0
votes
1answer
23 views

Are there any security implications with enabling “Data Access” on a sever?

I'm trying to run an OPENQUERY statement within a local server but am getting the following error: Server 'MyLocalServer' is not configured for DATA ACCESS. If I set MyLocalServer's Data Access ...
-2
votes
1answer
51 views

Can't create availability group , big error message

hello I am setting up my "high availability group" and I have a little problem, I have this error message . maybe I have to validate my cluster first? I already tried to validate but I also receive ...
3
votes
1answer
161 views

What happens if Always On High Availability nodes have different connection default languages?

I have been working on databases in a Always On High Availability group which - for reasons that I do not understand - have different default languages for an application's login on the primary and ...
0
votes
0answers
122 views

CreatingDC not updating after Domain controller Migration

Recently we have switched our domain controller and during the migration I have noticed on the cluster manager the below error     Cluster network name resource 'Cluster Name' encountered an error ...
0
votes
1answer
138 views

Architecture of table for lot of inserts per second in SQL Server 2019

We are in process of development of project which will process around 51 000 000 records daily (~around 500-600 inserts per second). We choose Always On solution which will contain: One Primary ...
7
votes
2answers
1k views

AlwaysOn High Availability doesn't work , can't activate it

I am trying to activate the high availability on my SQL server and getting below message: SQL server version: Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) ...
0
votes
0answers
109 views

SQL Server 2019 Always On performance [closed]

We are in process of development of project which will process around 51 000 000 records daily (~around 500-600 inserts per second). We choose Always On solution in SQL Server 2019 which will contain: ...
1
vote
2answers
386 views

Single node availability group - is it possible?

Is it possible to have an availability group with just one node in it? The reason I ask is because I am considering a distributed availability group spanning two single node always on availability ...
3
votes
1answer
181 views

where can I find the DNS name associated to the listener of an Availability Group?

I have an availability group that has a listener. The listener is called SQLPROD as you can see on the picture below. It uses port 1433 When I run the following script: ALTER AVAILABILITY GROUP [...
0
votes
1answer
32 views

Does a login need to be created on MSSQL Always On replica before being granted roles on the primary? [duplicate]

Consider a SQL Server Login MyLogin that exists on PrimaryServer but not SecondaryServer in an Always-On Availability group. MyLogin has a number of roles already assigned. If the login MyLogin is ...
2
votes
1answer
272 views

Removing Database from AlwaysOn Availability Group

I have a 2-node SQL Server AlwaysOn Availability Group. When I execute this T-SQL command on the Primary server: USE master go ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE TestDB it removes the ...
0
votes
2answers
31 views

Log file truncation on AG database

I am POCing AlwaysON DAG for my company, and i have come into a very interesting debacle. Seems like even though primary and replica's and all synced up, the log file on the primary DB does not get ...

1 2
3
4 5
26