Installed new SQL Always on setup, everything working fine, but when I do fail over form exsting primary to secondary and existing secondary to primary, I am unable to connect with listener name. Both A and B are same domain joined Only one way working example there is two machines \ A - primary \ B - secondary in this when I try with listener I am able to connect. But when I changed to \ A - Secondary \ B - primary I am unable to connect using listener, getting below error: >Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. (Framework Microsoft SqlClient Data Provider) > >------------------------------ >For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18452-database-engine-error

When dealing with Contained Availability Groups are you installing Ola's Maintenance Solution while connected through the listener or separately on each replica?

To have an Availability Group, I must have a Windows Server Failover Cluster. All Windows Server Failover Clusters have the ability to form exactly one quorum. This clearly means that if I have two Failover Cluster Instances, I must have two quorums. But what happens if I make an Availability Group out of two Failover Cluster Instances? **Do I need to manage three quorums?** [The documentation](https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-clustering-and-always-on-availability-groups-sql-server?view=sql-server-ver17#WSFC) certainly gives the impression that the Availability Group is subject to the vote of a quorum, but I don't know which quorum.

I have read the SQL Server 2022 Licence Guide with great care. As far as I can tell, the following is a perfectly valid licence configuration if you have **two licences and Software Assurance**. Assume that I am talking about the same AG throughout. |Server Name | Location | Setup | Covered by Licence | |:---- |------:| -----:| -----:| |Prod Primary | Main data centre | Production Primary, hosting exactly one AG | Licence A - Primary | |Prod HA | Main data centre | Automatic failover AG replica, synchronously replicated | Licence A - Free HA server | |Prod Manual Failover | Main data centre | Manual failover AG replica, asynchronously replicated | Licence A - Free DR server| | DR Readable | DR site | Readable AG secondary, failover/replication don't matter | Licence B | |DR HA | DR site | Automatic failover AG replica, synchronously replicated | Licence B - Free HA server | |DR Manual Failover | DR site | Manual failover AG replica, asynchronously replicated | Licence B - Free DR server| I find it surprising that this seems allowed. **Am I correct that it is allowed?** Obviously, Microsoft has the final say on anything like this. However, what I'm describing here *must* be so common that the professionals here have either seen it before or already know why it does not work.

I have 3 servers on VMware workstation: 1. Win 2012 R2 as ADDC -Cluster service. 2. Win 2012 R2 as SQL server primary replica . 3. Win 2012 R2 as SQL server secondary replica (auto fail-over).

My Availability group fail in this scenario: When I disconnect primary replica from the network the cluster fail to announce a fail-over to secondary replica when I try to manual fail-over from secondary replica instance I get this error:
however this not the case when I stop primary replica from cluster manager because it will do auto fail-over.

I want to restore log backup file on copy-only full backup in restoring mode on primary replica server these backups were taken from secondary replica . I took copy only full back up on secondary replica then failover after that I took .trn backup file on primary replica(which took full backup ). both full and trn file backup from one server but full backup took when server in secondary mode and trn backup took when server be in primary backup and I want to restored both of them in restoring mode on secondary replica after failover. I wanted to add database in cluster with my hand because our databases are big it was error when they are restored on primary server the error is > The log in this backup set begins at LSN 38202000004450500001, which is too recent to apply to the database. An earlier log backup that includes LSN 38167000000015500001 can be restored.

What is root cause of this error. I couldn't find any related information.\ There are two nodes on Availability Group and if you reboot second node, database wont up.

I am unable to find an article yet, that is able to clearly help my understanding of the risks I run by running my Ola Hallengren DatabaseIntegrityCheck job with PHYSICALONLY parameter on our large databases. There is no mention of a FULL check being done: [https://ola.hallengren.com/] > Run Integrity Checks of Very Large Databases The SQL Server > Maintenance Solution has been designed to do integrity checks of very > large databases. In the DatabaseIntegrityCheck procedure you can > choose do the checks on the database level, the filegroup level, or > the table level. It also supports limiting the checks to the physical > structures of the database: > > EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'USER_DATABASES', > @CheckCommands = 'CHECKDB', @PhysicalOnly = 'Y' These checks are limited to checking only the physical consistency i.e. that all data is present and can be read. Is someone able to clarify for me please, or point me to an article that can clearly help me with exactly what are those logical checks that this PhysicalOnly check ignores, and what risks could be associated with ignoring the logical checks or doing a FULL check less frequently

I have a 2 node Always On Availability Group cluster where we are planning to take backups to a file share which will then be moved to Azure cloud. We have a service account from the Windows team for the backup job, but the startup account of the database engine and the SQL Server Agent are local accounts. Using proxies did not help me with the situation so I am now thinking of providing the service account credentials in the SQL Server Configuration Manager. What I want to know is how do I proceed? This is what I have planned. Please let me know if I have missed something in the flow 1. Change service account on the Node2 (secondary) - both **SQL Server (_InstanceName_)** (database engine) and **SQL Server Agent (_InstanceName_)** (job engine) 2. Pause synchronization 3. Restart the **SQL Server (_InstanceName_)** service and the **SQL Server Agent (_InstanceName_)** 4. Failover the **AOAG** to the secondary, then change the account details of the primary (now the seconday) 5. repeat step 2. and 3. 6. Fail back to the primary again Please let me know if I am missing any step here Thanks!

I like using Policy-Based Management to do some simple "everything's okay" sanity checks and email me if something goes out of spec. Typically, I validate the time since the last full backup, both to verify backups are running on schedule, and also to make sure that newly created databases are being included in backups. This works perfectly fine on a standalone server. However, we recently deployed a two-node Always On Availability Group. It's configured to run backups on the current primary node. As you're probably aware, backup history is stored in msdb on the server that performs the backup. This causes a problem with the @LastBackupDate property, which only checks the local backup history for the server the policy is being evaluated on. After a failover, the backup policies almost immediately go out of compliance, as the backups have been running from the other server for however long it was primary (most likely for longer than the span that the policy is checking). Is there any reasonably simple way to make these policy checks Availability-Group-aware? Or am I going to have to look for some other backup monitoring solution?

Good morning all , I have an alwayson instance with a multisubnet listener I configured RegisterAllProvidersIP = 0 in order to register a single DNS entry HostRecordTTL is configured at 1200 seconds I would like if with this configuration I will have timoeout during the failover if so how much time is needed for the application to connect to the lsitener thanks

Our current production server runs on SQL Server 2014 AlwaysOn AG. We are planning to upgrade it to SQL Server 2017 AlwaysOn. Due to the configuration issues with the present cluster, we can not use the existing cluster. The production server has 52 databases with a total size of 2 TB. The databases servers an online system, so minimum downtime is our core requirement. Our initial plan is to go for a side by side approach. 1. Provision 3 SQL Server 2017 2. Create new cluster 3. Logs from the existing 2014 AG Primary server to all the servers in the new 2017 group. On the switchover day, 1. Disconnect all applications from the existing server. 2. Do a final logshipping. 3. Apply logs to all servers in the new group WITH NORECOVERY, except WITH RECOVERY on Primary_new 4. Create AG and add all database to AG 5. Change all applications to point to the new cluster. 6. Shutdown old cluster. Is this a good approach? Are there any other approaches? If I am missing anything, please guide me. Thanks.

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 was "Error 3456 ... could not redo log record" etc. This is a known issue with a resolution involving patching SQL Server. However this condition wasn't addressed for a few days. The nightly full backup file sizes (taken against the primary replica) began increasing by about 10% per night. The database also has log backups made every 15 minutes for most hours in the day, but log backups are disabled during a window at night while indexes are rebuilt and full backups made. To get the secondary back online it was required to restore from backup. In this instance some issues were encountered - first an attempt was made to rejoin the AG but that hung for ages. Then a command was given to have that replica database removed from the AG but of course it hung too. Killed both those processes but could not kill the session that had faulted in redoing the log records (as it is not a user session - although its ID was above 50 by the way). I therefore restarted SQL Server on that replica before eventually restoring from backup and rejoining the AG. Having resolved the issue, the next nightly full backup file size (on the primary, again) had reduced back to normal size. Why would the *full* backup file increase in size until this issue was resolved? Is it because the log could not be cleared and this was being included in the full backup? If so, then what was getting backed up in the 15-minutely log backups? Imagine for a moment that the primary had suffered a failure during this period - could I not have restored from the available full and log backup files I had (ie. because obviously something remained in the log such that it wasn't clearing)? And if it was a case of the log not clearing, then why (because the secondary had been suspended, so surely the primary no longer waits for the secondary before committing its own log)? Afterthought: the system process that I could not kill without a server restart was the one that faulted while attempting to apply the logs to the secondary - leading the secondary to be suspended. I suppose this is a "long running transaction"? If this is the reason, can someone still please clarify for me the earlier questions - particularly about how "at risk" the primary was until this was resolved?

The documentation for [sys.dm_hadr_database_replica_states](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql?view=azuresqldb-current) states > 1 = Synchronizing. For a primary database, indicates that the database is ready to accept a **scan request** from a secondary database. For a secondary database, indicates that active data movement is occurring for the database. This is the first time that I've seen the phrase "scan request" in the context of Availability Groups. I cannot guess what it means nor find it elsewhere in the documentation. What is it? Where is it documented?

Our real-time communication application relies on database access, and it cannot be disconnected from the DB when running. Recently our customer’s DB maintenance team notified us that the DB will be occasionally disconnected from the app, possibly also during application working hours - due to security upgrade reasons. The blackout gap can be c.a. 2-minute. The question is: Is it possible with SQL Server Always On setup (this is what they have) to maintain **uninterrupted** DB access (from application perspective) during upgrading nodes of the SQL cluster ? Our understanding is that SQL Server **Always On** setup (as the name indicates) should be always available, regardless of a single node failure, or in case of intentionally executed upgrade on subsequent individual nodes (even if taking these offline, if required). We believe this should be possible with some kind of setup. Or alternatively does the “Always” mean “**almost** always” (with e.g. 2 minute time required to fail over to some standby instance, etc..) ? Can anyone confirm possibility to update software of **individual nodes** of the cluster (Windows Updates + SQL Server engine updates) one-by-one, eventually upgrading all nodes – with maintaining uninterrupted database access by the application during this process ? Could anyone share a link to documentation or article describing how to configure this capability? (We plan to share these links with the DB maintenance team).

We have total 6 Nodes and 3 failover cluster (Each with two node). - Cluster1.TestAD.com(Node1,Node2) - SQL Failover Instance (SQL1) - 1 TB Shared storage - Cluster2.TestAD.com(Node3,Node4) - SQL Failover Instance (SQL2)- 1 TB Shared storage - Cluster3.TestAD.com(Node5,Node6) - SQL Failover Instance (SQL3)- 1 TB Shared storage Now we want to create availability group setup between above three SQL instance. We plan to create new cluster (cluster4.TestAD.com ) with all six node and create Availability groups with all three instance. SQL Edition : SQL Server 2012 R2 Enterprise Is it possible? Any other recommendations ?

I’m currently working on a proof of concept (POC) involving column-level encryption and key management (SMK/DMK) in a SQL Server 2019 Always On Availability Group (AG) environment (CU20). I’ve tested multiple scenarios and am trying to determine the best approach for ensuring encryption consistency across replicas. In my first test, I created the Database Master Key (DMK) before adding the database to the AG. Everything worked fine — the encrypted data was accessible from the readable secondary and even after failover. In another test, I added the DMK after the database was already in the AG. In this case, encrypted data was not accessible on the readable secondary or after failover. To fix this, I removed the database from the AG and re-added it with the password — this allowed encryption to work across all replicas. In a more complex scenario, I created a DMK without explicitly encrypting it with the local Service Master Key (SMK) on the primary. After a failover, the readable secondary (which became the new primary) couldn’t access encrypted data. To resolve this, I ran OPEN MASTER KEY DECRYPTION BY PASSWORD followed by ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY on the new primary. This fixed the issue on the new primary, but now the original secondary (i.e., the old primary) couldn’t access encrypted data. I noticed that after restarting the servers, only the latest SMK used for DMK encryption allows automatic decryption, and the previous SMK is no longer valid unless re-added. Given this behavior, I have a few questions: * Is it expected that only one SMK can be used for automatic decryption of a DMK at any time? * Should DBAs routinely re-encrypt the DMK with the local SMK after every failover to ensure continuity? * Would it be better to restore the same SMK across all replicas to maintain consistency — and is that considered safe in a production environment? I’m aware that SMK restore using FORCE can affect linked servers, credentials, and SSIS, so I’m hesitant to use it in prod without clarity. I’d really appreciate input on the best practices or any Microsoft-recommended approach for managing encryption chains in AG setups with minimal risk.

I am trying to estimate likely duration of the DatabaseIntegrityCheck SQL Agent job for our AG PROD environment based on its duration in the Test stand-alone environment and I would appreciate comments please, on my approach Total size (TB) of all dbs in TEST is 1TB using following script: select CONVERT(decimal(10,2), (sum(size * 8.00)/1024.00/1024.00/1024.00))as total_all_dbs_TB from master.sys.master_files The job takes about an hour in TEST. PROD total size using same script is about 48TB. Would I be correct to expect about 48hours (2 days) duration for the DatabaseIntegrityCheck job in PROD?

Please help me on below: Below is my Environment: *Windows Server 2012 R2 Standard* *SQL Server 2014 SP2 GDR* *Availability Groups 2014 with 3 Node (2 Sync and 1 BCP async)* I have 7 Databases with FileStream enabled. Each 7 DB Filestream data folders is around 500 GB. Due to security policy I need to enable both Transparent Data Encryption (TDE for structured data) and Encrypting File System (EFS on FileStream folders). While enabling EFS on FileStream folders I am getting below error. (**NOTE :** I am doing it by turning the SQL Servers offline and before taking services offline , I am failing over AG to next available Synchronized AG Node) I cannot Ignore the error and move on , because of which please advise on below: **I even tried turn off anti virus and Firewall . No Luck** 1. What is the root cause of this issue and how can I perfectly enable EFS for the 7 DBs FS Folders . 2. Can I try enabling the EFS on multiple DB FS Folders keeping in mind their sizes (500 GB each) ? Kindly advise. Thanks

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 with all DC down And DR database up for applications. Can this solution work or I need to use Log Shipping as third node instead of alwayson AG node. Please help on this. Thanks