disable read on secondary:
LTER AVAILABILITY GROUP [AG-Something]
MODIFY REPLICA ON N'SQLSERVER-SECONDARY' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO))
do your ddll workload on primary
re enable read on secondary
ALTER AVAILABILITY GROUP [AG-Something]
MODIFY REPLICA ON N'SQLSERVER-SECONDARY' WITH (SECONDARY_ROLE(...
"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. ...
Here are the details of that error code (19):
The media is write protected.
You need to consult the cluster log (use Get-ClusterLog to get that) for additional details about what writes failed within the cluster operation being performed. Check that out and update your question with any errors you see.
That being said, ...
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 ...
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 ...
I belive that Everything in the persisten version store would be transaction-log-causing activity, right?
This means that everything affected by that would receive a performance hit:
– log size/backups
– checkdb (gets hit by db size too)
– ALWAYS ON – meaning it could get DELAYS FOR SYNCHRONOUS AG
We have now resolved this issue, was caused by the AWS DNS suffixes it appends to the domains. This caused issues with our domain names.
Have changed this setting to append primary and connect specific DNS suffixes and I was able to see both networks when building the cluster.
I've seen that recurring error about joining the database when automatic seeding was used to initialize the secondary replicas. My solution has always been to disable automatic seeding once the secondary databases have all been created and synchronized:
ALTER AVAILABILITY GROUP YourAgName
MODIFY REPLICA ON 'YourReplicaName'
WITH (SEEDING_MODE = MANUAL);
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.