9

Go to SSMS Object Explorer Expand Always On High Availability Expand Availability Groups Right click your AG Select Show Dashboard In the dashboard, above the list of AG databases and their status, there is Add/Remove Columns button Pick Estimated Data Loss (time) and add this column to dashboard You will be able to see the estimated data loss time for each ...


8

Parallel redo threads are assigned in database recovery order, which does follow the sys.databases creation date. That said, the separate parallel database recovery feature means each database recovery task can be assigned to a different SOS scheduler (when there are a large enough number of schedulers available). Say you had 8 databases and 32 processors. ...


6

Estimated Recovery Time is how long SQL Server thinks it will take to run the recovery process required to bring the database into read-write, usable state. 4600 seconds is a LONG TIME. You should be concerned. The REDO queue is the amount of data that needs to be replayed into the secondary database in order for the database to come online as the primary. ...


6

Check that ghost cleanup isn't disabled with trace flag 661. A query that reads all the LOB data should clean up ghosts if it can. Ensure you are on the latest build of whichever version of SQL Server you are using. Ghost cleanup bugs are rare these days, but they were prevalent in the past. You could also try sp_clean_db_free_space or ...


5

"I could use a tranlog backup, and then roll it to 12:01am" Do that. You can restore the last full backup, then apply log backups with NORECOVERY in the days or hours before the first of the month, then apply the last log backup with STOPAT and RECOVERY or STANDBY. Or you could create a Database Snapshot at midnight on the first of the month. ...


5

No. Once you fail over to the higher version, you cannot go back down. Using Distributed AGs for a mixed version topology only works when the writable main AG (specifically individual the read/write replica) is on the lower version. From the docs (bolding mine): Distributed availability groups in SQL Server 2017 or later can mix major versions of SQL Server ...


4

You can create a "canary" table which contains only one row with one datetime column and update this column on scheduled basis (every minute, for example). This way you can check this table on secondary replica (even when primary one is already unavailable) and be sure that it is synchronized at least till this timestamp (actully you may lose less ...


4

You can't depend on the system databases being cached in memory, more likely they won't be because of their less frequent access relative to the user databases. I think you'll end up in a quasi-functioning state, where your user databases will still be accessible but certain features of the server instance that rely on master and msdb will throw some weird ...


4

From documentation The Caveats section of the Availability group database level health detection failover option doc has some info that might improve our guesses on the question: It is important to note that the Database Level Heath Detection option currently does not cause SQL Server to monitor disk uptime and SQL Server does not directly monitor database ...


4

Is there any way I can handle it better than just letting the log files grow? I believe you have the ideal options covered already. You either Let the log files grow for the duration of the outage, or Remove the replicas that are offline from the AG (and re-seed them when the primary datacenter is available) I'd lean towards #1 if you have the disk space, ...


4

Read-only routing is per connection, not per batch or statement. Read-only intent is specified via the application connection string and the connection will be directed to a read-only replica (if possible) when the connection is established.


4

You can fail over to a readable secondary. The author is either wrong, or just using some confusing terminology. Reading the exact quote you included, I'd lean towards the former explanation. But I don't know all the context around that statement. Here I have an AG - all replicas are configured to be readable when they are in the secondary role, and NODE2 ...


4

Queries against read-only secondary replicas implicitly run in snapshot isolation regardless of the session isolation level or RCSI setting. This avoids blocking due to DML changes. Read-only queries still acquire schema stability locks, which will block DDL operations by redo threads and visa-versa. SPID 129 blocks a session ID 45 ( i am sure this is not a ...


4

Why can't it listen on any IP? Probably the same reason that an FCI is not allowed to listen for client connections on all IPs, only IPs that are dependencies of the instance’s virtual network name. It creates isolation between FCIs and allows multiple FCIs to use the same ports on their own IP addresses. So each FCI can use 1433 for clients and 5022 for ...


4

Is it possible to add a database to specific replicas of an availability group and not add it to another replica? Directly answering the question, yes it's possible. Giving more insight past the initial true/false answer is you can do it but it's a bad idea. Other than a host of issues that would likely ensue, if the database doesn't need to be in the AG on ...


3

As mentioned in the docs here the issue is explained: MultiSubnetFailover=True Not Supported by NET Framework 3.5 or OLEDB Issue: If your Availability Group or Failover Cluster Instance has a listener name (known as the network name or Client Access Point in the WSFC Cluster Manager) depending on multiple IP addresses from different subnets, and you are ...


3

While DR testing, we brought down nodes running on DC1 Bringing down the primary server normally will cause an automatic failover to a sync AG. However, you also brought down the only other sync secondary configured for automatic failover. This puts the AG in a situation where there is no primary, which requires forced manual failover. From Perform a ...


3

As you noted, the docs refer to "log stream transport" when performing automatic seeding. Similar to the way that you can stream movies to your device without downloading a specific file, automatic seeding streams data to the secondary without storing an actual backup file. While the technical implementation details aren't exactly this, it's ...


3

Depends on what kind of offline. I had it get itself into a state where it had no idea what transactions were committed because the failure mode it was seeing was writes to the dbs were failing at block levels. It spammed the log nicely, but couldn't recover until I manually bounced it as it would believe the in-memory copy was correct after hitting the IO ...


3

Since the log file is only growing on the secondary replica, something is preventing transaction log truncation on that database (but not on the primary). You'll need to check the value of log_reuse_wait_desc in sys.databases on the secondary replica to see why the transaction log isn't being cleared there. Since log_reuse_wait_desc is currently showing &...


3

While this might not be the answer you are looking for as it requires a migration in the end, you could try running your workload on SQL Server 2019 to be able to use "SQL Server 2019 Intelligent Performance -Worker Migration" or also commonly called Worker Stealing. Worker migration (AKA “worker stealing”) allows an idle SOS scheduler to migrate ...


3

Those two numbers are measuring slightly different things. You're right that they are both measure redo, but they do it in different ways. The Perfmon counter is updated in near-real-time - it's the number of bytes redone in the last second: Amount of log records redone in the last second to catch up the database replica The AG dashboard is based on the ...


3

The failover policies are configurable - so you can adjust the thresholds https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-flexible-automatic-failover-policy?view=sql-server-ver15 of course to do this you need to know which policy triggers the failover in the first place https://techcommunity.microsoft.com/t5/sql-...


3

Increase the hardware capacity or look for Cloud Solutions, if possible. This is from official doc: Maximum number of availability groups and availability databases per computer: The actual number of databases and availability groups you can put on a computer (VM or physical) depends on the hardware and workload, but there is no enforced limit. Microsoft ...


3

...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. This is called "split brain" and really shouldn't happen - SQL Server and WSFC have a lot of controls built in to protect against getting into this state. What should happen is that the ...


2

In your question, you included the following check to validate that you've set the RegisterAllProvidersIP to false: PS C:\> get-clusterresource "Cluster Name" | Get-ClusterParameter RegisterAllProvidersIP Object Name Value Type ------ ---- ----- ---- Cluster Name RegisterAllProvidersIP 0 ...


2

The only way to have different access policies. e.g A reader (by whatever principal - AAD group user etc) can only access the secondary- Make the 'Active Directory admin' of the logical server hosting the secondary replica an AAD group. Make the user or group that needs to read from the secondary replica is a member of the group mentioned in step 1. As a ...


2

Both the log send queue and redo queue can be found in sys.sys.dm_hadr_database_replica_states (docs), and also available using the SQLServer:Database Replica Perfmon counters (docs). Perfmon With Perfmon counters, you'll need to collect counters individually on each replica. Each replica knows about the Send/Redo(recovery) queue affecting that replica ...


2

That error means the AG failed one of the health detection timeouts (lease, session, or health check) summarized in this table from the docs: Mechanics and guidelines of lease, cluster, and health check timeouts for Always On availability groups - Summary of Timeout Guidelines Your first stop should be to review the SQL Server error log on each node in the ...


2

Yes the SA account does have an SID and you can verify what it is by logging into the SQL Server as the SA account and using this SQL function: SELECT SUSER_SID('sa'); MS Docs on it: https://docs.microsoft.com/en-us/sql/t-sql/functions/suser-sid-transact-sql?view=sql-server-ver15 There's nothing special about using SA or any other account in an AlwaysOn ...


Only top voted, non community-wiki answers of a minimum length are eligible