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
3
votes
1answer
94 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
22 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
43 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 ...
0
votes
2answers
34 views

MS SQL Server 2016 (SP1) : Back Up DB Task : No File [closed]

I am running MS SQL Server 2016 (SP1) and I have created a Maintainence plan for daily full backup of my databases into a network location, permissions all set to the owner of the task. However, ...
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 ...
0
votes
0answers
65 views

SQL Server 2019 Always On performance

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
54 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
57 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
30 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
36 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
24 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 ...
0
votes
1answer
13 views

Maintenance plan: Confirmed server as primary but switched to secondary during backups?

While investigating a failed job that occurred over night I stumbled on to a novel situation that I thought was pretty interesting. A server that was part of an AOA group was performing nightly ...
1
vote
0answers
17 views

SSRS 2016 ReportServer DBs in Basic AG

I have a 2 node cluster on which I need to setup AlwaysOn since it is a standard edition we would be going with Basic AGs. I can easily get the user databases added into the AGs but I am not sure ...
0
votes
2answers
49 views

If I manually failover my AlwaysOn AG primary replica to the secondary and take the primary database offline will it auto-sync when back online?

I have an AlwaysOn AG setup between Database A (primary replica) on Server 1 and Database B (secondary replica) on Server 2. If I force a manual failover from my primary replica on Server 1 to the ...
0
votes
0answers
24 views

Always On Availability Groups and MSDTC for SQL Server 2012

I have been told that since MSDTC is not supported for SQL Server 2012 Always On Availability Group and should be turned off. what specific settings do I need to change to turn it off? This is what ...
0
votes
1answer
17 views

Migrate to sql 2017 and add ag's

A client wants to migrate from sql 2008 to sql 2017 and add availability groups. All enterprise. All vm's. Question: (without using any words starting with Az~ smile) should I set up the ag's first ...
0
votes
1answer
25 views

Replication with Always On availability groups

I have a three server set up: SQL01 – Primary server SQL02 – Secondary server SQL04 – Remote distribution server SQL04 – Subscribe SQL01 / SQL02 DB's are part of basic availability groups and i am ...
1
vote
2answers
70 views

Setting up Availability group for report server databases

I am reading msdn article on using SSRS with AG and have few doubts on setting up the same: Server A - Will host Report Server services with scale out deployment Server N1 - Node 1 with ReportServer ...
2
votes
1answer
49 views

How to migrate SQL Server 2014 to SQL Server 2019 on Linux using AG [closed]

I would like to migrate SQL Server 2014 on Windows to SQL Server 2019 on Linux using while running AG. Is there an option to Add the SQL Server 2019 to the same AG and once the time comes gracefully ...
2
votes
1answer
62 views

Availability Group Replica delay

looking for ideas for diagnosing the root cause of the following issue. We have 3 availability nodes Primary,Synchronous replica, Asynchronous replica. no load other than T-Log backups exists on the ...
3
votes
1answer
125 views

Exactly same query and plan but different duration and total reads. I Know parameter sniffing but

I know what is parameter sniffing but this seems different, I have this simple query: select * from A400RDATA This table has only a cluster index and approximately 25000 rows. The query usually ...
0
votes
1answer
14 views

SQL server Listener removal [closed]

Hi I am planning to drop and recreate the listener with same name what is the best approach? Can i drop the listener from WFCS by removing the resorces or Drop the listener from SQL using SQL command? ...
0
votes
1answer
70 views

SQL Server AlwaysOn AG Secondary Replica is not joined [closed]

I have created an Availablity Group on SQL Server 2019. I selected 2 SQL server for Replicas but the Secondary replica has an error: This secondary database is not joined to the availability group....
0
votes
1answer
46 views

SQL Server AG secondary replica database still in Restoring mode

I created an AlwaysOn in SQL Server 2019 and I selected the Full Database and Log Backup option. Now it created a database in secondary replica but it is still in Restoring mode. My DB size is 16MB ...
3
votes
1answer
116 views

SQL Server AlwaysOn Availablity Group Automatic seeding

I created an AlwaysOn in SQL Server 2019 and I selected Automatic seeding, but it does not create my database in secondary nodes, On the other hand, if I create my database manually I give and error ...
3
votes
1answer
185 views

Record MultiSubnetFailover value at connection?

With AlwaysOn, there are 3 options for the keyword MultiSubnetFailover True (yes) False (no) (example) Not sent/used Not all applications support it's use in connection strings. Not all vendor ...
2
votes
1answer
27 views

Trigger SQL Agent job based on text in error log for an AG failover

I have a 2 node AG setup (Not an FCI). I need to configure an alert to trigger a job based on a string found in the error log showing that the node is now the new primary. The string I want to use is: ...
2
votes
3answers
87 views

Is it possible to add a DB to an Always On AG and setup replicas at different time?

I have a 3-node SQL Server Always On availability group. 2 nodes are on the same LAN, and the third one is on a remote site with high latency. I will add a huge DB (10TB) to the AG, so I would like ...
4
votes
2answers
73 views

Checking the data latency on an Always On Availability Group in ASYNC mode

We have a number of SQL Servers with Always on Availability Groups in asynchronous mode between a primary and secondary server with Manual fail over. I created a latency report utilizing the below ...
0
votes
0answers
16 views

Usage of AGLatency V2.2 for multiple secondary replica

I am having issue on AG setup. Basically I can see blocking on primary replica whenever log backup runs on primary. Connection timeout happnening on sync commit secondary replica(only one). So for ...
0
votes
0answers
82 views

Help with set up replication distribution database in Always On availability group

After following official microsoft guide and sample walkthrough: https://docs.microsoft.com/en-us/sql/relational-databases/replication/configure-distribution-availability-group?view=sql-server-2017 ...
0
votes
0answers
48 views

Auto Seeding SQL Server AG Combined with Backup/Restore

Are there special conditions under which the below paragraph from MS Docs is true? I tested this process on SQL Server 2017 Enterprise Edition and ended up with databases saying not synchronized/...
4
votes
3answers
338 views

SQL Server: Multiple Availability Groups, one cluster, how do I keep the primary cluster node as primary?

I have a database server setup where there are three SQL Server database instances set up on three nodes of a Windows Server Failover Cluster (WSFC). The sysadmin, who is responsible for the ...
-1
votes
1answer
20 views

Primary replica in primary DC freezes when delocalised DR replica DC crashes

The Always On hasn't got the flexibility of mirroring. The DR replica's data center crashed and the primary replica also froze as a result (see following error when trying to access the primary). Is ...
1
vote
2answers
48 views

For AlwaysOn AGs, can a secondary replica cause contention on the primary when the resources of the secondary are maxed out?

Is it possible and in what ways can the secondary replica cause contention against the primary replica when the secondary replica's resources are maxed out (e.g. does the primary "lock" up while ...
0
votes
1answer
29 views

SQL Server Distributed Availability Group Usage Pattern

I have got a client with DAG setup on 2016 & 2017 servers. I understand that their usage is most likely for DR purposes. What I'm a little unclear on (and I can't find any info on it) is how they ...
0
votes
2answers
37 views

Configure Always On to use secondary site as DR

I have two SQL Server 2017 Standard Edition clusters, each in a different datacenter, only a pair of servers each. I was able to configure and test the Windows Cluster, Quorum for it, AG, including ...
4
votes
1answer
111 views

Distributed AG on SQL Server 2017 Standard Edition?

Can I configure Distributed AG with SQL Server 2017 Standard Edition?
0
votes
1answer
13 views

Upgrading Microsoft SQL 2014 and Always On AG Configurations

I'm getting ready to perform an upgrade from SQL 2014 to 2017 on two servers that are configured for Always On. The concern on the upgrade is how to break Always On to stop replication and to keep any ...
3
votes
2answers
79 views

SQL Server 2017 Distributed Availability Groups

My Environment is SQL Server 2017 Distributed Availability Groups, details as follows: Configured SQL Distributed AG from DC1 to DC2. DC1 (Production): TEMPDB1 AND TEMPDB2 (HOSTED ON WSFC1) DC2 (DR)...
7
votes
1answer
246 views

Is there a way to determine what kind of data is sitting in the Redo Queue for an AlwaysOn AG Secondary Replica?

Do any of the DMVs or is there another way to expose what kind of data is in the Redo Queue that's in process of being synced up to a secondary replica? (E.g. is it table data and which table, or is ...
2
votes
1answer
276 views

What's the cause for an AlwaysOn AG Secondary Replica to have a high Redo Queue Size and Estimated Recovery Time with a good Redo Rate?

SQL14 is the Primary server, SQL16 is the Secondary Replica, and they're setup using synchronous commit availability mode: As of yesterday it appeared that data stopped synchronizing from the primary ...
0
votes
2answers
54 views

Sql Server AG assigning wrong listener

I have 2 instances of SQL Server on two Windows Servers, namely: D-MSSQL01-DE (v.2014) D-MSSQL02-DE\SQL2019 (v.2019) And seperate server for AGs: D-MSSQL02-DE (v.2014) D-MSSQL02-DE\SQL2019 (v....
0
votes
0answers
43 views

Database on secondary replica cannot join availability group [duplicate]

I am trying to add a database to an Availability Group Always On. I already have the other two DBs working fine on the same SQL Server 2016 Standard Edition Cluster (running on Windows 2016 Standard). ...
2
votes
0answers
127 views

Missing log block for availability database in SQL Server 2016

I've recently begun receiving alerts like this one: Always On Availability Groups transport has detected a missing log block for availability database "myDb". LSN of last applied log block is (xxxx:...
0
votes
1answer
37 views

Blocking in AG that causes app not to launch

If lag between primary and secondary is huge, then can it cause persistent blocking on app sessions? Coz recently we had an issue where blocking sessions were having wait type as HADR_SYNC_COMMIT and ...
0
votes
1answer
43 views

Upgrading to SQL Server 2014 SP3 _ Clarifications help needed [closed]

We have SQL Server 2014 SP2 GDR with below: Availability Groups TDE enabled for structured data EFS enabled for unstructured data Now the DBs are with AOAGs and encrypted , I am planning to ...
2
votes
2answers
74 views

what happens to user connections when an AG fails over?

I'm not sure quite how to word this, but I am looking for anything a DBA can do (things outside of changing code in the application) to reduce the impact to users when an AG fails over. We have a 2 ...
2
votes
2answers
53 views

Using SQL Server 2017, can log backups be safely taken on primary when using Log Shipping or Availability Groups?

We have a CommVault server taking log 15-minutely log backups of our primary (on-premise) DBs (combined with full nightlies), for point-in-time restores. We want to enable high availability for DR ...
1
vote
1answer
65 views

Row Compression in AlwaysOn Availability Groups

I want to implement row compression on some databases that are in an availability group. Are row and page compression supported in availability group databases? Does it affect the replication, ...

1 2 3 4 5 23