Data Loss warning when trying to do failover even though Synchronized on SQL Server 2019. This is a basic availability group running in a cluster-less installation. In the dashboard I see this: This is the error I get: I also checked sys.dm_hadr_database_replica_cluster_states and I see that both show is_failover_ready 1.

I'm looking for ideas on how to determine a plausible root cause for DB_CHAINNG problems encountered when we recently performed a planned AG failover. We're using SQL Server 2019 Standard Edition with "Basic" AGs (meaning that every database is in it's own AG). In "Basic" AGs the Secondary Replicas are not readable, only one Primary and one Secondary is possible per-AG, and the commit-mode must be synchronous. Due to licencing (and cross-database queries), all AGs always have their Primary Replicas on the same Instance at any one time. The Windows cluster is two Windows Server 2019 nodes, with a File Share Witness on a third server. The purpose of our planned failover was to apply SQL Server 2019 CU10 (from CU8) to both servers, this went without issue. Patch SRV2, failover to SRV2, patch SRV1. The planned failover of all AGs was fine, no problems at all. However, the IIS application immediately started failing with permissions problems due to DB_CHAINING being lost. Altering the databases to enable DB_CHAINING addressed the issues. Patching the second server proceeded without incident. 'cross db ownership chaining' is (and has always been) disabled at the server-level on both servers. This is as per the MS recommendation:- I'm aware that restore or attach can affect this property, please see this article and the comments:- , and that DB_CHAINING can only be changed on the Primary Replica. The configuration of the servers, databases and AGs, etc. predates my time. I know DB_CHAINING can compromise security, it wasn't my choice, it's an old application. To pre-empt some potential suggestions, this was not an issue with Login passwords, SIDs, Server Role membership, database Users, Roles or permissions (anything User/Role/permission related is stored in the databases and would not have been affected by an AG failover). This was not an orphaned database User issue. This was not a problem with client connections to a 'stale' Listener address/IP or similar. Both servers are on the same subnet and all clients reconnected via their Listener network names without any issues. The problem was when the clients successfully connected, they could not see tables, or execute procedures, etc. As soon as DB_CHAINING was enabled, everything worked as expected - no other changes were needed. I've confirmed on a test environment that failing over an AG has no effect on DB_CHAINING, it neither enables it nor disables it, on either Replica. This implies that the option was not enabled before I failed-over. I would normally accept this and ensure that checking, and if necessary, enabling this option is added to the failover plan for the future, however about six weeks ago, we experienced an unplanned automatic failover caused by a switch reboot at the hosting company. On this occasion, all AGs failed-over and the application continued to function without any issues or intervention. I've reviewed the ERRORLOG files and I have a complete history of when DB_CHAINING was enabled on which databases since the two servers were installed, I can see that no-one re-enabled DB_CHAINING after the unplanned failover. Furthermore, no-one has ever disabled DB_CHAINING on any database on either server. Has anyone experienced a SQL Server update removing DB_CHAINING from User databases? I've reviewed the patching (setup) logs and can find no mention of anything like this being recorded. The closest I can find is someone reporting chaining issues sometime after applying SQL Server 2016 SP2:- . But the answer suggests changing the application behaviour to workaround it, which I'm not able to do. is_db_chaining_on currently returns 1 for all (necessary) databases on both servers (which is good evidence that patching doesn't alter this option, otherwise databases on the last patched currently passive might report 0). I cannot explain how an unplanned failover from SRV2 to SRV1 was OK, but a planned failback six-weeks later to SRV2 from SRV1 had DB_CHAINING issues (when there is no evidence in the logs that anyone/anything disabled the option). It seems like either the update silently removed the property or the property was not set correctly on SRV2 before the planned failover - in which case how was the application functioning (for months) before the unplanned failover six-weeks ago? Any ideas where I can look, or what I can test for, to identify a plausible root cause would be appreciated. **EDIT:** thanks for your response AMTwo, but no, there have been no backup/restore or detach/attach events. The databases were created from native backups on both SRV1 and SRV2 in early Dec-2020, since then, there have been no restores of the any databases in question. I'm the only DBA and there's no-one else who could/would have done this, msdb.dbo.restore_history is empty. We also have Redgate SQL Monitor coverage over this pair of servers which would have generated a lot of alerts if anything like a restore was attempted which broke the AG replication.

This will be my first project with Basic availability group so I thought would check before implementing . As per Microsoft documentation below it says we will need to have Software assurance for failover servers for disaster recovery / high availability. https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15 Will greatly appreciate if someone can clarify on this as I will need to have the capabilities to do automatic / manual failover without the need to purchase software assurance

Today, I learned the hard way that [you cannot corruption check a Basic Availability Group's secondary](https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups?view=sql-server-ver16#limitations) . Given this limitation, how can I know that it is safe to failover a BAG? For all I know, the secondary could be corrupt.

We have a few Basic Availability groups in production and I've been reading about the limitations of them. I'm concerned about the following limitations: > No backups on secondary replica. > > No integrity checks on secondary replicas. Suppose that we haven't failed over in months. In our case, that seems to imply that we haven't run DBCC CHECKDB for a long time on any of the databases on the secondary. There could have been a storage corruption issue that occurred months ago that we still don't know about. If a disaster occurs on the primary replica and we fail over to the secondary, we might end up with the production application pointing at corrupt data. Would it be considered a best practice to perform one of the following on a fixed schedule? 1. Perform a planned manual failover to switch the primary and secondary and leave the former secondary in the primary role until the next planned failover. 2. Take a database snapshot of the secondary replica and run DBCC CHECKDB against that. Or am I overthinking the risks here?

Assuming SQL Server 2022 Standard Edition on Windows, I simply cannot see any benefits to using Basic Availability Groups instead of Database Mirroring. Is there anything offered by Basic Availability Groups but not Database Mirroring? In both cases: - You can only have 1 primary and 1 secondary - The secondary is not readable - Databases cannot be grouped in any way allowing them to fail over together - Replication can either be sync or async - Automatic page repair is enabled. The only differences that I've found between the two features are that Basic Availability Groups cannot be upgraded to Enterprise Edition and that Database Mirroring is officially deprecated. I appreciate that Basic Availability Groups are harder to set up than mirrors, but I am only asking about **benefits** of Basic Availability Groups over Database Mirroring.

I have recently learnt that the SQL server standard edition's basic availability group doesn't require a windows cluster. Basic AG is considered equivalant to the deprecated mirroring technology. Like mirroring, the basic AG supports auto and manual failover. However, mirroring supported auto failover only with the presense of witness server. In availability groups there is no concept of witness server. So my question is - how is basic AG able to support automatic failover without the windows cluster? Or is the concept that - basic AG without cluster only supports manual failover, and basic AG with cluster supports automatic and manual failover? I couldn't find documentation on this so any guidance will help me here.

In my testing of SQL Server 2019 Basic Availiiblity Group on Ubuntu 20.04, I successfully created AG1 for DB1 and AG2 for DB2 with only AG1 config'ed with listener IP. But when I use sqlcmd to test, I can connect to AG1's listener and use AG2's DB, do DDL and DML. Is it OK for this configuration? Only 1 listener for several AG. Besides, I'm using pacemaker for cluster management, is it possible to config no listener for AG, just use pacemaker's IP resource to bind IP on master server?

I'm installed SQL Server 2019 Enteprisse in the first server and I have other server with SQL Server 2019 Standar Edition ,both server when I tried connect with the Integration Service SQL server 2019 .It shows me the error: Connecting to the Integration Services service on the computer MyIP failed with the following error: "Class not registered".

I want to setup HA DR for a single instance SQL server by adding a new instance on another machine and configuring multiple basic AGs (**with manual failover mode**). I want all client apps to be able to use the same connection string to reach the server. For this do I use the listener (create a listener for only 1 AG, and use this in all connection string) or do I use the database mirroring endpoint?

I want to reconfigure a Dynamics AX 2012 SQL Server to improve performance of an existing architecture where one SQL server is used for both transactions and reports. This reduces SQL performance during peak loads (large MIS report generation). In the proposed architecture, the transaction SQL replicates to a report SQL to share the burden and improve performance. I chose Transactional replication to implement this. But, here is the problem: 1. Because I'm using SQL standard, I can choose all the Tables but not all Views and SPs for replication because some contain Indexed keys and for that SQL Enterprise is required. 2. If I don't replicate all the Views and SPs then AX fails to recognise the report SQL. Is it possible to restore a backup from the Transaction SQL to Report SQL (which contains all Tables, Views and SPs) and then enable Transactional replication to replicate just the tables? Or, should I just use the Basic always-on availability group in SQL STD and setup a read replica? Or is there a smarter :) way around this? Thank you in advance for the help. Adding a screenshot of the Indexed views issue with STD SQL.

1 have 2 sql servers. Svr1 is enterprise edition and Svr2 is standard edition. Both have their own independant FCI. Svr1 has got 60 production dbs. Svr2 has got 10 reporting dbs and multiple etls. Presently the etls running on Svr2 fetch data from 5 dbs from Svr1 and insert data into one/more reporting dbs on Svr2. I want to sync 5 dbs from Svr1 into Svr2 so that reporting tools and etl on Svr2 has access to copy of those dbs on Svr2 itself. The sync need not be real time and a delay of 15 minutes is acceptable. I am exploring SQL AG asynchronous with readable secondary for this. Link: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups?view=sql-server-ver16#limitations It says: Basic AG - No read access on secondary replica. But in my case the primary is enterprise which I assume means that the AG that I create is not basic but full fledged AG. I'm not able to get documentation regarding this that- what a AG is basic and when it is not basic. This link says about the readable secondary that I am talking about: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-scale-availability-groups?view=sql-server-ver16 But there is no information about license on the secondary server. Questions: 1. Does enterprise edition primary and standard edition secondary support readable secondary? 2. Have I understood correctly that I can't create a group of 5 AG, instead I need to create 5 separate AGs because my secondary is having standard license?

I want to create a SQL 2019 Standard **BASIC** Always On Availability Group for a database in a 2-node cluster. By following this guide that should be possible although there are limitations compared to Enterprise edition. http://woshub.com/configure-always-high-availability-groups-sql-server/ > Always On is available in the Standard edition, but it has some > restrictions: > > - It is limited to 2 replicas (primary and secondary) > - The secondary replica cannot be used to read data > - The secondary replica cannot be used to backup MS SQL > - Only 1 database per availability group is supported. One of the first steps to enable Always On is to set this feature to enabled in the "SQL server configuration manager". But it shows me that my edition does not support Always On. What am i doing wrong? I already updated to SQL 2019 CU15 hoping the update would fix this, but no. Please help out. I am running standard edition: *Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64) Jan 12 2022 22:30:08 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: )*

I configured Always on basic availability groups on my SQL Server 2019 Standard instances. One as our production machine and the second as a DR machine. I have primary set to synchronous commit and DR set to asynchronous commit. It seems to be working perfectly and failover works without any problems until I tested what would happen should our primary server completely fail and become unavailable. To simulate this I turned off the SQL Server service on the primary and then did a forced manual failover to secondary. Secondary came online without any problems and all the recent changes were available (lucky for me - no data loss) but then when I started up the SQL Server service again on the original primary, the AG went into primary state while the secondary replica was also still in primary. Now when I try doing a failover, it fails saying the database is already a primary on the other side. I tried this from both Prod and DR with no luck either side. Luckily for now I was just using a test database but we would like to implement Always on to replace the current log shipping we have in place but now with this happening I am scared of ending up in such a situation with a production DB. Was I doing the failover incorrectly or is there some startup switches I am missing and how do I get the 2 replicas to act normal again (1 primary 1 secondary). I truly hope someone else has had this issue and managed to get it resolved as recovery from DR after failover with log shipping is a complete nightmare plus our data loss is always going to be high. We have tried mirroring but ended up dropping it as the performance impact was very high plus we had a few instances where it did a DB failover without any problems on the primary database so mirroring is quite scary plus MS has deprecated it since SQL Server 2012.

Setup: * Basic high availability * 2 replicas (1 primary, 1 secondary). DB01 => initial primary. DB02 => initial secondary * Synchronous commit on both * Both are in synchronized state * There is no listener configured * Cluster type None When we stop the DB01 (initial & current primary) SQL service using services.msc (simulating a friendly server crash) and then initiate a forced failover on DB02 (initial & current secondary) using: ALTER AVAILABILITY GROUP [TestHA] FORCE_FAILOVER_ALLOW_DATA_LOSS; The secondary database comes online, which is what we want. However, when the DB01 SQL Server service is started again, using services.msc, the DB01 db assumes *primary* role again. So currently there are 2 instances readable/writable and out of sync. We were expecting that the initial primary would detect that a secondary has taken the primary role and assume a secondary role or at least be inaccessible so apps cannot work on old data. The same procedure, but using the deprecated mirror setup, does behave this way.

SQL 2017 Standard in SQL always on two node Basic AG. There are two DNS records for the listener with the different subnet IP's The servers are across subnets and we have hit issues with an application seeing to randomly time out when talking to the database, other times it responds in milliseconds. After running Wireshark we can see the application is trying to talk to the database via the failover partner listener IP address, the none readable replica. This information I have read explains the 21 second timeout. https://techcommunity.microsoft.com/t5/sql-server-support/improved-multisubnet-listener-behavior-with-newly-released-sql/ba-p/318664 MultiSubnetFailover=TRUE seemed like the resolution but the application is using an older version of .NET is there anything else I can do to resolve or work around this?

In [SQL Server Basic Availability Groups docs](https://learn.microsoft.com/en-US/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups) , I found this interesting line: > Basic Availability Groups for SQL Server 2017 on Linux support an additional **configuration only replica**. 1. What is a **configuration only replica**? 2. Why is it only supported on Linux?

We have an SQL Server 2016 standard edition with Basic Availability Groups(BAG) setup between dbserver1 and dbserver2. This is our first time to implement the BAG and wanted to know what to do in a failover scenario like below: 1. In the beginning, dbserver1 is acting as a primary, dbserver2 is acting as a secondary 2. dbserver1 went down and an automatic failover takes place, and dbserver2 has become the primary 3. dbserver1 is recovered and came up, acting as the secondary since dbserver2 is now the primary 4. Should we manually trigger a failover to the dbserver1 to make it a Primary again? The reason is that any database objects (SQL agent jobs, maintenance jobs and such) only exist in the dbserver1, and if we keep dbserver2 running as a primary node, then it can potentially miss the jobs that were supposed to run. Should we always target to have a particular node to be Primary (in this case dbserver1), or each node should be exactly the same including all the objects outside of Availability Groups so that any node can take primary role at any time, and should be kept being primary node once a failover occurred? (possibly using DBAtools to keep objects in-sync)

I need to use SQL Server Standard 2016 to keep the costs low but achieve HA. In addition we need to have two Read nodes for reporting purposes. I am wondering if I can use SQL Server Replication to get data for the two Read nodes. Is it possible to have this setup? What kind of issues this kind of setup may have down the road?

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 trying to configure transaction replication of some of these databases to SQL04. SQL04 is the remote distributor. I have been following this Microsoft guide. My issues comes at the validation of the replica hosts as publishers sproc ( the final step!) USE Distribution; GO DECLARE @redirected_publisher sysname; EXEC sys.sp_validate_replica_hosts_as_publishers @original_publisher = 'SQL01', @publisher_db = 'DB', @redirected_publisher = 'AG-listener'; I receive the error stating read access is not enabled, the same error which is stated in the MS guide, but i cannot work out how to step over it! error: >The query at the redirected publisher 'SQL02' to determine whether there were sysserver entries for the subscribers of the original publisher 'SQL01' failed with error '976', error message 'Error 976, Level 14, State 1, Message: >The target database, 'db', is participating in an availability group and is currently not accessible for queries. >Either data movement is suspended or the availability replica is not enabled for read access. >To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. >For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.'.